I have a snowflake table like this:
x
date_col | artc | locl | flag
d1 | a1 | l1 | 0
d2 | a1 | l1 | 1
d3 | a1 | l1 | 1
d4 | a1 | l1 | 0
d5 | a1 | l1 | 0
d6 | a1 | l1 | 1
d7 | a1 | l1 | 0
d8 | a1 | l1 | 1
d9 | a1 | l1 | 1
d10 | a1 | l1 | 1
d1 | a1 | l2 | 0
d2 | a1 | l2 | 1
d3 | a1 | l2 | 1
d4 | a1 | l2 | 1
d5 | a1 | l2 | 1
d6 | a1 | l2 | 1
d7 | a1 | l2 | 0
d8 | a1 | l2 | 1
d9 | a1 | l2 | 1
d10 | a1 | l2 | 1
and I want to count sequence of 1 on column “flag” group by artc and locl, order by date_col
The desire output is:
flag
2
1
3
5
3
I was trying things like:
select
count(*) Over(PARTITION BY art, locl, flag
ORDER BY date_col rows
unbounded preceding) as flag
from table
where flag=1
but it doesn’t start fresh count on group changes. Thanks for any help!
Advertisement
Answer
Assign each group of “1”s a grouping by counting the number of “0”s before them. Then filter and aggregate:
select count(*), min(date_col), max(date_col)
from (select t.*,
sum(case when flag = 0 then 1 else 0 end) over (order by date_col) as grp
from t
) t
where flag = 1
group by grp
order by min(date)