By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
create table sample(id integer, name varchar(100), timeint time);
insert into sample values(1, 'aaa', '00:00:01');
insert into sample values(1, 'aaa', '00:00:01');
insert into sample values(1, 'aaa', '00:00:02');
insert into sample values(1, 'aaa', '00:00:03');
insert into sample values(1, 'aaa', '00:00:04');
insert into sample values(2, 'bbb', '00:00:01');
insert into sample values(2,'bbb', '00:00:02');
insert into sample values(2, 'bbb', '00:00:03');
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
1 rows affected
with recursive s as (
select s.id, s.name, s.timeint, count(*) as cnt,
row_number() over (partition by id order by timeint) as seqnum
from sample s
group by s.id, s.name, s.timeint
),
cte as (
select id, timeint, name, seqnum, timeint as start_timeint, cnt
from s
where seqnum = 1
union all
select s.id, s.timeint, s.name, s.seqnum,
(case when s.timeint <= cte.start_timeint + interval '1 second'
then cte.start_timeint else s.timeint
end),
s.cnt
from cte join
s
on s.id = cte.id and s.seqnum = cte.seqnum + 1
)
select id, name, start_timeint, sum(cnt)
from cte
group by id, name, start_timeint
order by id, start_timeint
| id | name | start_timeint | sum |
|---|---|---|---|
| 1 | aaa | 00:00:01 | 3 |
| 1 | aaa | 00:00:03 | 2 |
| 2 | bbb | 00:00:01 | 2 |
| 2 | bbb | 00:00:03 | 1 |
select distinct s.*, rank() over (partition by id order by timeint) as seqnum
from sample s
order by id, timeint
| id | name | timeint | seqnum |
|---|---|---|---|
| 1 | aaa | 00:00:01 | 1 |
| 1 | aaa | 00:00:02 | 3 |
| 1 | aaa | 00:00:03 | 4 |
| 1 | aaa | 00:00:04 | 5 |
| 2 | bbb | 00:00:01 | 1 |
| 2 | bbb | 00:00:02 | 2 |
| 2 | bbb | 00:00:03 | 3 |