sql-server-2008 - T-SQL : Find the last occurence of a aggregate state before today

Question

我有一张包含库存交易的表格。一个简化的例子:

--Inventory Transactions            
Date         Sold    Purchased  Balance(not in table)
Today        1                  -5
Yesterday    6                  -4
5 days ago           5          +2
10 days ago  103                -3 
20 days ago          100        +100

要求表明报告应包含自商品出现负余额(缺货)以来的日期。在上面的示例中，它的意思是 yesterday 作为答案。

我正在尝试将其转换为 SQL，但遇到了一些麻烦。我试过使用 CTE:

with Stockouts as (
select getdate() as [Date],
       (calculation) as Balance
from [Inventory Transactions]
--some constraints to get the correct article are omitted
union all
select dateadd(dd, -1, Stockouts.[Date]) as [Date], 
       Stockouts.Balance - (calculation) as Balance
from [Inventory Transactions]
inner join Stockouts    
)

但是有一个问题是我不能在递归部分使用子查询(找到当前交易之前的最后一个交易)并且当某个日期没有交易时内部连接将停止循环(所以 dateadd 部分也会失败。

解决此问题的最佳方法是什么？

Answer 1

我认为最好的方法是像这样使用OUTER APPLY:

DECLARE @InventoryTransactions TABLE ([Date] DATE, Sold INT, Purchased INT)
INSERT @InventoryTransactions VALUES 
    ('20120504', 1, 0),
    ('20120503', 6, 0),
    ('20120501', 0, 5),
    ('20120425', 103, 0),
    ('20120415', 0, 100)

SELECT  trans.Date, 
        trans.Sold, 
        trans.Purchased, 
        ISNULL(Balance, 0) [BalanceIn],
        ISNULL(Balance, 0) + (Purchased - Sold) [BalanceOut]
FROM    @InventoryTransactions trans
        OUTER APPLY
        (   SELECT  SUM(Purchased - Sold) [Balance]
            FROM    @InventoryTransactions bal
            WHERE   Bal.Date < trans.Date
        ) bal

您的方法不太适合递归。如果您需要所有日期，那么最好创建一个日期表并将上述结果LEFT JOIN 到包含所有日期的表中。最好有一个永久的日期表(比如 dbo.Calendar)，因为它们在许多情况下都可用，但您始终可以使用 Loops、CTE 或系统 View 来创建临时表来操作它。关于如何的问题generate a list of incrementing dates之前已经回答过

编辑

只需重新阅读您的要求，我认为这是获得您真正想要的东西的更好方法(使用相同的示例数据)。

;WITH Transactions AS
(   SELECT  trans.Date, 
            trans.Sold, 
            trans.Purchased, 
            ISNULL(Balance, 0) [BalanceIn],
            ISNULL(Balance, 0) + (Purchased - Sold) [BalanceOut]
    FROM    @InventoryTransactions trans
            OUTER APPLY
            (   SELECT  SUM(Purchased - Sold) [Balance]
                FROM    @InventoryTransactions bal
                WHERE   Bal.Date < trans.Date
            ) bal
) 
SELECT  DATEDIFF(DAY, MAX(Date), CURRENT_TIMESTAMP) [Days Since Negative Balance]
FROM    Transactions
WHERE   BalanceIn > 0

编辑 2

我已经创建了一个 SQL Fiddle演示 OUTER APPLY 和 Recursion 之间查询计划的区别。您可以看到 CTE 的工作量更大，当在我的本地机器上运行相同的数据时，它告诉我，当在同一批处理中运行这两个时，外部应用方法的相对批处理成本为 17%，不到四分之一递归 CTE 方法占用了 83%。