SQL Server Need to calculate running total

fcwjkofz 于 2023-04-04 发布在其他

关注(0)|答案(1)|浏览(138)

I have data like below and I need to somewhat generate running total but not really. When value changes from Green to either (Yellow or Red) then 1 for all rows until it changes again from Green to either Red or Yellow then 2 for all rows until it changes.

I think I can use lag function to check the previous row vs the current row but not sure how I can get this output.
| value | output |
| ------------ | ------------ |
| Black | 0 |
| Blue | 0 |
| Green | 0 |
| Yellow | 1 |
| Black | 1 |
| Green | 1 |
| Red | 2 |
| Black | 2 |
| Blue | 2 |
| Green | 2 |
| Green | 2 |
| Yellow | 3 |

sql-server

来源：https://stackoverflow.com/questions/75923854/need-to-calculate-running-total

1条答案

按热度按时间

xytpbqjk1#

This is a gaps-and-islands variant. Assuming that you have a column to order the records, say id , we can identify the transitions with a lag and a window sum :

select id, val,
    sum(case when lag_val = 'Green' and val != 'Green' then 1 else 0 end) 
        over(order by id) grp
from (
    select t.*, lag(val, 1, val) over(order by id) lag_val
    from mytable t
) t
order by id

id	val	grp
1	Black	0
2	Blue	0
3	Green	0
4	Yellow	1
5	Black	1
6	Green	1
7	Red	2
8	Black	2
9	Blue	2
10	Green	2
11	Green	2
12	Yellow	3

fiddle

赞(0）回复(0）举报 2023-04-04

我来回答

SQL Server Need to calculate running total

1条答案

相关问题

热门标签

最新问答