Add RESET WHEN clause like in Teradata to restart window partition in window functions - by Itzik Ben-Gan

Status : 

 


51
0
Sign in
to vote
ID 2748755 Comments
Status Active Workarounds
Type Suggestion Repros 0
Opened 5/26/2016 2:01:13 AM
Access Restriction Public

Description

Often when using window functions we need to be able to reset the window partition based on some condition that pertains to either the last row or some running/sliding calculation up to the last row.
For example, compute a nonnegative sum, or reset a running total when it reaches a certain value. You can see examples for such needs here:
http://sqlmag.com/t-sql/t-sql-challenges-replenishing-and-depleting-quantities
As shown in this article, some of those tasks have existing relational solutions, but they are quite complex, whereas others simply don't have any good relational solutions to date. If T-SQL adds the RESET WHEN clause like the one Teradata supports (see: http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1145_111A/Ordered_Analytical_Functions.083.010.html#ww1285495), it will be much easier to solve such tasks.
The replenishing task would be solved like this:

WITH C AS
(
  SELECT *,
    SUM(val) OVER(ORDER BY txid
                  RESET WHEN
                    SUM(val) OVER(ORDER BY txid
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) < 0
                  ROWS UNBOUNDED PRECEDING) AS runsum
  FROM dbo.Transactions
)
SELECT txid, val,
  CASE WHEN runsum < 0 THEN 0 ELSE runsum END AS newrunsum,
  CASE WHEN runsum < 0 THEN -runsum ELSE 0 END AS replenishqty
FROM C;

The depleting task would be solved like this:

WITH C AS
(
  SELECT *,
    SUM(val) OVER(ORDER BY txid
                  RESET WHEN
                    SUM(val) OVER(ORDER BY txid
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) > 5
                  ROWS UNBOUNDED PRECEDING) AS rt
  FROM dbo.Transactions
)
SELECT txid, val,
  CASE WHEN rt > 5 THEN 0 ELSE runsum END AS newrt
FROM C;

Besides, this kind of feature is too cool to pass up!
Sign in to post a comment.
Posted by Vladimir Moldovanenko on 5/26/2016 at 6:21 AM
This is needed feature and well formulated request. On a number of occasions I wrestled with exactly the same problem. Most recent I can recall is a problem of creating and filling containers with max N items in each from a pool of items. If reset limit (in this case 0 value) can also be referenced inline dynamic value, correlated to PARTITION BY clause, with each partition setting its own limit, that would be awesome.
Well done again Itzik!