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 test(id, order_id, start, end1, count) AS VALUES
(1, 1, '2023-12-19 10:00:00'::timestamp, '2023-12-19 11:00:00'::timestamp, 15),
(2, 1, '2023-12-19 11:00:00', '2023-12-19 12:00:00', 1),
(3, 2, '2023-12-19 12:00:00', '2023-12-19 13:00:00', 2),
(4, 3, '2023-12-19 13:00:00', '2023-12-19 14:00:00', 10),
(5, 1, '2023-12-19 14:00:00', '2023-12-19 15:00:00', 4),
(6, 1, '2023-12-19 15:00:00', '2023-12-19 16:00:00', 7),
(7, 1, '2023-12-19 16:00:00', '2023-12-19 17:00:00', 3),
(8, 3, '2023-12-19 17:00:00', '2023-12-19 18:00:00', 21),
(9, 1, '2023-12-19 18:00:00', '2023-12-19 19:00:00', 5);
alter table test add constraint pk primary key(id);
SELECT 9
ALTER TABLE
--Last thing I tried:
WITH lp AS (
SELECT *,LEAD(order_id) OVER(ORDER BY start) AS next_id
FROM test
)
SELECT order_id,
MIN(start) AS start,
MAX(end1) AS end,
SUM(count) AS count
FROM lp
WHERE order_id = next_id
GROUP BY order_id
ORDER BY MIN(start);
/*
The result I like to get:
order_id start end count
1 10:00 12:00 16
2 12:00 13:00 2
3 13:00 14:00 10
1 14:00 17:00 14
3 17:00 18:00 21
1 18:00 19:00 5
*/
| order_id | start | end | count |
|---|---|---|---|
| 1 | 2023-12-19 10:00:00 | 2023-12-19 16:00:00 | 26 |
SELECT 1