窗口函数

本文介绍了窗口函数 - 带重置运行总计的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在使用 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);

干杯，伊兹克