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;