Postgres 12

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 data (id int, "timestamp"  timestamp, status int,  value numeric);
insert into data values
(82240589, timestamp '2020-03-01 09:13:46', 70, 22.00),
(82240589, timestamp '2020-03-01 09:13:57', 70, 34.00),
(82240589, timestamp '2020-03-01 09:14:14', 70, 21.00),
(82240589, timestamp '2020-03-01 09:14:22', 70, 47.00),
(82240589, timestamp '2020-03-01 09:14:33', 70, 32.00),
(82240589, timestamp '2020-03-01 09:14:43', 83, 37.00),
(82240589, timestamp '2020-03-01 09:14:52', 83, 44.00),
(82240589, timestamp '2020-03-01 09:15:01', 83, 39.00),
(82240589, timestamp '2020-03-01 09:15:10', 70, 40.00),
(82240589, timestamp '2020-03-01 09:15:19', 70, 40.00),
(82240589, timestamp '2020-03-01 09:16:30', 70,  5.00),
(82240589, timestamp '2020-03-01 09:16:37', 70, 43.00),
(82240589, timestamp '2020-03-01 09:16:46', 70, 46.00),
(82240589, timestamp '2020-03-01 09:16:53', 70, 53.00),
(82240589, timestamp '2020-03-01 09:17:00', 70, 55.00),
(82240589, timestamp '2020-03-01 09:17:08', 70, 50.00),
(82240589, timestamp '2020-03-01 09:17:16', 70, 46.00),
(82240589, timestamp '2020-03-01 09:17:52', 70, 10.00);
18 rows affected

select id, 
       min("timestamp") as start_at, 
       max("timestamp") as end_at,
       status,
       sum(value)
from ( 
  select id, "timestamp", status, value, 
         sum(group_flag) over (order by "timestamp") as group_nr
  from (
    select *, 
           case 
             when lag(status,1,status) over (partition by id order by "timestamp") = status then 0
             else 1
           end as group_flag
    from data
    order by id, "timestamp"
  ) t1
) t2
group by group_nr, status, id
order by id, start_at;


id
start_at
end_at
status
sum


82240589
2020-03-01 09:13:46
2020-03-01 09:14:33
70
156.00

82240589
2020-03-01 09:14:43
2020-03-01 09:15:01
83
120.00

82240589
2020-03-01 09:15:10
2020-03-01 09:17:52
70
388.00

id	start_at	end_at	status	sum
82240589	2020-03-01 09:13:46	2020-03-01 09:14:33	70	156.00
82240589	2020-03-01 09:14:43	2020-03-01 09:15:01	83	120.00
82240589	2020-03-01 09:15:10	2020-03-01 09:17:52	70	388.00