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 |
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 alag
and a windowsum
:fiddle