PostgreSQL: How to get row number of duplicate values in a sequence?-CodePudding

We have a table on PostgreSQL, containing job results (success/failure). For each row, I need to get how many failed runs happened just before. Success result should reset the counter

Simplified example would be like this:

drop table if exists test;
create local temporary table test (
    idx serial,
    is_failure boolean
);

insert into test (is_failure)
values (true), 
       (false),
       (true), 
       (true), 
       (true), 
       (true), 
       (false),
       (true), 
       (true);

I need to write a query that would return

select idx,
       is_failure
       --fail_count <what should go here?>
from test
order by idx;


-- expected:
idx,is_failure,fail_count
1,true,1
2,false,0
3,true,1
4,true,2
5,true,3
6,true,4
7,false,0
8,true,1
9,true,2

Which approaches would you suggest?

Thanks!

CodePudding user response：

with g as (
    select *,
        count(case when is_failure = false then 1 end) -- or sum((not is_failure)::int)
            over (order by idx desc) as grp
    from test
)
select idx, is_failure,
    count(case when is_failure = true then 1 end)      -- or sum(failure::int)
        over (partition by grp order by idx desc) as fail_count
from g
order by idx;

https://dbfiddle.uk/?rdbms=postgres_10&fiddle=65596ac5314644dbc3991b7bcd7b4bc8

CodePudding user response：

Found a solution thanks to How to count consecutive duplicates in a table?

with base_prepare as (select idx,
                             is_failure,
                             case
                                 when lag(is_failure) over (order by idx desc) is null
                                     then 0
                                 when lag(is_failure) over (order by idx desc) = is_failure
                                     then 0
                                 else 1
                             end as is_new_group
                      from test
                      order by idx),
     base_prepare2 as (select idx,
                              is_failure,
                              is_new_group,
                              sum(is_new_group) over (order by idx desc) grp_num
                       from base_prepare
                       order by idx)
select idx,
       is_failure,
       is_new_group,
       grp_num,
       case
           when not is_failure
               then 0
           else row_number() over (partition by grp_num order by idx)
       end as error_count
from base_prepare2
order by idx;