如何创建在 NULL 值出现时重置的累积总和 T-SQL

huangapple go评论67阅读模式
英文:

;How to create running total that resets upon a NULL value T-SQL

问题

我试过以下代码,但它打乱了顺序,并且不能按我想要的方式处理空值。

SELECT ID
	   , date
	   , value
       ,Running_T = SUM(value) OVER(PARTITION BY ID, (CASE WHEN [value] IS NOT NULL THEN [value] ELSE NULL END) ORDER BY [date])
FROM dim.event_data
英文:

I am trying to create a running total column that resets each time it encounters a null or multiple null values. I expect to maintain the ID partition and date order.
I expect the result as the table below

ID date value Running_T
SA223 10/07/2018 3 3
SA223 11/07/2018 6 9
SA223 25/07/2018 NULL NULL
SA223 16/08/2018 11 11
SA223 17/08/2018 9 20
SA555 15/05/2019 4 4
SA555 27/05/2019 13 17
SA555 11/07/2019 NULL NULL
SA555 21/11/2019 NULL NULL
SA555 20/02/2020 4 4
SA555 27/05/2020 1 5
SA555 23/07/2020 2 7

I tried the code below but it messed up the ordering and does not deal with Null values as I want.

SELECT ID
	   , date
	   , value
       ,Running_T = SUM(value) OVER(PARTITION BY ID, (CASE WHEN [value] IS NOT NULL THEN [value] ELSE NULL END) ORDER BY [date])
FROM dim.event_data

答案1

得分: 2

这是一个经典的“间隙与岛屿”问题。有更高效的解决方案,但一个简单的方法如下:

您可以使用条件运行COUNT来为每组行编号。然后在该组编号上执行运行的SUM以获得最终结果。

WITH Counted AS (
    SELECT *,
      GrpId = COUNT(CASE WHEN value IS NULL THEN 1 END)
              OVER (PARTITION BY ID ORDER BY date ROWS UNBOUNDED PRECEDING)
    FROM event_data ed
)
SELECT *,
    Running = SUM(value) OVER (PARTITION BY ID, GrpId ORDER BY Date)
FROM Counted;

db<>fiddle

英文:

This is a classic Gaps And Islands problem. There are more efficient solutions but a simple one is as follows:

You can use a conditional running COUNT to number each set of rows. Then do a running SUM over that group number to get the final result

WITH Counted AS (
    SELECT *,
      GrpId = COUNT(CASE WHEN value IS NULL THEN 1 END)
              OVER (PARTITION BY ID ORDER BY date ROWS UNBOUNDED PRECEDING)
    FROM event_data ed
)
SELECT *,
    Running = SUM(value) OVER (PARTITION BY ID, GrpId ORDER BY Date)
FROM Counted;

db<>fiddle

答案2

得分: 0

以下是翻译好的内容:

你可以尝试像这样

    SELECT ID,
        date,
        value,
        CASE
            WHEN value IS NULL THEN NULL
            ELSE SUM(value) OVER (PARTITION BY ID ORDER BY date) 
        END AS Running_T
    FROM YourTable;

如果您需要更多帮助,请告诉我。

英文:

you can try like this

SELECT ID,
    date,
    value,
    CASE
        WHEN value IS NULL THEN NULL
        ELSE SUM(value) OVER (PARTITION BY ID ORDER BY date) 
    END AS Running_T
FROM YourTable;

huangapple
  • 本文由 发表于 2023年6月13日 09:51:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/76461255.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定