英文:
;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;
英文:
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;
答案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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论