窗口函数 - 带重置运行总计 [英] Window Functions - Running Total with reset
问题描述
我正在使用 SQL Server 2012 构建库存计划/再订购引擎.
I am using SQL Server 2012 to build an inventory planning / reorder engine.
我有一堆过时的交易，称它们为贷方和借方.我想同时做两件事:
I have a bunch of dated transactions, call them credits and debits. I want to do two things at once:
- 生成运行总计(每日净余额)
- 生成补货建议.补充将重置运行总数(在 #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() 窗口函数来显示这些的运行总数.
I am using the SQL 2012 SUM OVER() window function to show the running total of these.
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)低于零，我需要找到一种方法将其重置为零.
I need to find a way to reset the running total (aka RT) to zero if it dips below zero.
我的查询，其中 Qty 和 RT 均为负数，并将其中较大(较小的负数)作为第一个推荐的补充.这在第一次正常工作.
My query where both Qty and RT are negative, and takes the greater (less negative) of these as the first recommended replenish. This works correctly the first time.
我不知道如何从窗口运行总数中减去这个.如果可能的话，我想在一个语句中做到这一点.
I am not sure how to deduct this from the window running total.. would like to do this in a single statement if possible.
这是我正在寻找的输出摘要:
Here is a summary of the output I am seeking:
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 英雄，你在吗?:)
Itzik Ben-Gan, Joe Celko, or other SQL hero, are you out there? :)
提前致谢！
推荐答案
这可以使用基于集合的解决方案来完成:
This can be done using a set-based solution:
1.计算正常运行总数(称为RT)
1.Compute the normal running total (call it RT)
2.计算RT的运行最小值(称之为MN)
2.Compute the running minimum of RT (call it MN)
当 MN 为负数时，-MN 是您目前必须补充的总数量.当 MN 为负时，让reply_rt 为-MN.因此，新的运行总数(称之为 new_rt)是 rt +reply_rt.如果您需要返回当前所需的补货数量，请从当前减去之前的补货_rt(使用 LAG).
When MN is negative, -MN is the total quantity you had to replenish so far. Let replenish_rt be -MN when MN is negative. So, the new running total (call it new_rt) is rt + replenish_rt. And if you need to return the current replenish quantity needed, subtract the pervious replenish_rt (using LAG) from the current.
这是完整的解决方案查询:
Here's the complete solution query:
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_rt
from c2
cross apply(values(case when mn_cur < 0 then -mn_cur else 0 end)) as a1(replenish_rt);
干杯，伊兹克
这篇关于窗口函数 - 带重置运行总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
