sql - 窗口函数 - 带重置的运行总计

Question

我正在使用 SQL Server 2012 构建库存计划/重新订购引擎。

我有一堆过时的交易，称之为贷方和借方。我想同时做两件事:

1. 生成运行总计(每日净余额)
2. 生成补充建议。补充将重置运行总数(#1)回到零。

表格如下所示:

CREATE TABLE TX (TDate DATETIME, Qty   INT);

INSERT INTO TX VALUES ('2014-03-01', 20);  
INSERT INTO TX VALUES ('2014-03-02',-10); 
INSERT INTO TX VALUES ('2014-03-03',-20); 
INSERT INTO TX VALUES ('2014-03-04',-10); 
INSERT INTO TX VALUES ('2014-03-05', 30); 
INSERT INTO TX VALUES ('2014-03-06',-20);  
INSERT INTO TX VALUES ('2014-03-07', 10);  
INSERT INTO TX VALUES ('2014-03-08',-20); 
INSERT INTO TX VALUES ('2014-03-09', -5);  

我正在使用 SQL 2012 SUM OVER() 窗口函数来显示这些的运行总计。

select TDate, Qty, RunningTotal, RecommendedReplenish from (
    select 
        TDate, 
        Qty, 
        SUM(Qty) OVER (ORDER BY TDate ROWS UNBOUNDED PRECEDING)  as RunningTotal,
        -1 * (CASE WHEN Qty < 0 AND SUM(Qty) OVER (ORDER BY TDate ROWS UNBOUNDED     PRECEDING) < 0 
                THEN 
            CASE WHEN Qty >  SUM(Qty) OVER (ORDER BY TDate ROWS UNBOUNDED PRECEDING)     THEN Qty ELSE SUM(Qty) OVER (ORDER                        BY TDate ROWS UNBOUNDED PRECEDING) END
        ELSE 0 END) as RecommendedReplenish
        /* Wrong, does not account for balance resetting to zero */
    from TX 
) T order by TDate

如果运行总计(又名 RT)低于零，我需要找到一种方法将其重置为零。

我的查询中，数量和 RT 均为负数，并将其中较大的(负数较小的)作为建议的第一个补充。第一次就可以正常工作。

我不知道如何从窗口运行总数中扣除这个。如果可能的话，希望在一个语句中执行此操作。

以下是我正在寻求的输出的摘要:

TDate        Qty    R.Tot  Replenish     New RT
-----------  ----   -----  -----------  ---------
3/1/2014     20      20                    20
3/2/2014    -10      10                    10
3/3/2014    -20     -10       10            0
3/4/2014    -10     -20       10            0
3/5/2014     30      10                    30
3/6/2014    -20     -10                    10
3/7/2014     10       0                    20
3/8/2014    -20     -20                     0
3/9/2014    - 5     -25        5            0

Itzik Ben-Gan、Joe Celko 或其他 SQL 英雄，你们在吗？ :)

提前致谢!

Answer 1

这可以使用基于集合的解决方案来完成:

1.计算正常运行总计(称为RT)

2.计算RT的运行最小值(称之为MN)

当 MN 为负数时，-MN 是您迄今为止必须补充的总量。当MN 为负时，令repleple_rt 为-MN。因此，新的运行总计(称为 new_rt)是 rt +补充剂_rt。如果您需要返回当前所需的补货数量，请从当前的补货数量中减去之前的replenish_rt(使用LAG)。

这是完整的解决方案查询:

with c1 as(  select *,    sum(qty) over(order by tdate rows unbounded preceding) as rt  from tx),c2 as(  select *,    -- when negative, mn is the total qty that had to be    -- replenished until now, inclusive    min(rt) over(order by tdate rows unbounded preceding) as mn_cur  from c1)select tdate, qty, rt,  replenish_rt - lag(replenish_rt, 1, 0) over(order by tdate) as replenish,  rt + replenish_rt as new_rtfrom c2  cross apply(values(case when mn_cur < 0 then -mn_cur else 0 end)) as a1(replenish_rt);

干杯，伊兹克