I have a input table as this:
x
date ID Flag 01.01 A 001.02 A 001.03 A 101.04 A 1 01.05 A 101.06 A 001.07 A 101.08 A 101.09 A 001.01 B 101.02 B 001.03 B 101.04 B 1 01.05 B 101.06 B 101.07 B 101.08 B 001.09 B 0I would like to calculate the cumulative sum of the flag for each, but the calculation should be refreshed if the flag is equal to 0 once again.
So, the output table should be:
date ID Flag CUMSUM01.01 A 0 001.02 A 0 001.03 A 1 101.04 A 1 201.05 A 1 301.06 A 0 001.07 A 1 101.08 A 1 201.09 A 0 001.01 B 1 101.02 B 0 001.03 B 1 101.04 B 1 201.05 B 1 301.06 B 1 401.07 B 1 501.08 B 0 001.09 B 0 0So, any suggestion to solve this issue? I tried with this:
sum(FLAG) over (partition by ID order by date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)But i have no idea how can I “refresh” the cumulative calculation, any idea?
Thanks in advance!
Advertisement
Answer
You need to assign a grouping. The grouping is the number of 0s before each row. Then use that for partitioning:
select t.*, (case when flag = 1 then sum(flag) over (partition by id, grp order by date) else 0 end) as cumulative_sumfrom (select t.*, sum(case when flag = 0 then 1 else 0 end) over (partition by id order by date) as grp from t ) t;Note: The window frame specification (rows between . . .) is not needed assuming that the dates are unique (as in your sample data). So, I just left it out.
Also, you can simplify the calculation in the subquery to:
sum(1 - flag = 0) over (partition by id order by date) as grp