2

So I have a table looks like this:

id    value    ts
123     T      ts1
123     T      ts2
123     F      ts3
123     T      ts4
456     F      ts5
456     T      ts6
456     T      ts7
456     F      ts8
......

What I want to do is to count the times when consecutive 'T' appears under each id partition(each id partition should be ordered by column ts). But not only that, I want to know how many times two consecutive 'T's appear; how many times three 'T's appear...

So finally, I want a table that has two columns:

  1. num_of_consecutives

  2. times_of_occurrences_for_this_number_of_consecutives

In this case, 2 consecutive 'T's appear one time and 1 consecutive T appears one time for id 123; 2 consecutive 'T's appear one time for id 456. Therefore, summing them up, the final table should look like this:

num_of_consecutives    times_of_occurrences_for_this_number_of_consecutives
1                      1
2                      2
Shenghao Xu
  • 35
  • 4

1 Answers1

0

Please check this solution (fiddle):

with cte(id, value, ts) as (
select 123,  'T' , 'ts1'
union all
select 123,  'T' , 'ts2'
union all
select 123,  'F' , 'ts3'
union all
select 123,  'T' , 'ts4'
union all
select 456,  'F' , 'ts5'
union all
select 456,  'T' , 'ts6'
union all
select 456,  'T' , 'ts7'
union all
select 456,  'F' , 'ts8'
)
select cnt as num_of_consecutives, count(cnt) as times_of_occurrences_for_this_number_of_consecutives from(
    select value, count(*) cnt from(
       select *,row_number() over (order by ts) - row_number() over (partition by value order by ts) grp
       from cte)q
    group by grp, value 
)q1
where value = 'T'
group by value, cnt
order by cnt;

This discussion could be also be useful.

Grigory Frolov
  • 184
  • 4