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
--https://stackoverflow.com/a/79294041/5298879
--shortened, removed `filter` clause emulation using a conditional aggregate
WITH ordered_data AS(
SELECT*, order_id<>LAG(order_id,1,order_id)OVER(ORDER BY start) AS is_diff_order
FROM test
),grouped_data AS(
SELECT*, count(*)filter(where is_diff_order)OVER(ORDER BY start)AS grp
FROM ordered_data)
SELECT order_id
, MIN(start) AS start
, MAX(end1) AS end1
, SUM(count) AS count
FROM grouped_data
GROUP BY order_id, grp
ORDER BY MIN(start);
| order_id | start | end1 | count |
|---|---|---|---|
| 1 | 2023-12-19 10:00:00 | 2023-12-19 12:00:00 | 16 |
| 2 | 2023-12-19 12:00:00 | 2023-12-19 13:00:00 | 2 |
| 3 | 2023-12-19 13:00:00 | 2023-12-19 14:00:00 | 10 |
| 1 | 2023-12-19 14:00:00 | 2023-12-19 17:00:00 | 14 |
| 3 | 2023-12-19 17:00:00 | 2023-12-19 18:00:00 | 21 |
| 1 | 2023-12-19 18:00:00 | 2023-12-19 19:00:00 | 5 |
SELECT 6
--https://stackoverflow.com/a/79294041/5298879
--original
WITH ordered_data AS (
SELECT
id,
order_id,
start,
end1,
count,
LAG(order_id) OVER (ORDER BY start) AS prev_order_id
FROM test
),
grouped_data AS (
SELECT
id,
order_id,
start,
end1,
count,
SUM(CASE WHEN order_id = prev_order_id THEN 0 ELSE 1 END) OVER (ORDER BY start) AS grp
FROM ordered_data
)
SELECT
order_id,
MIN(start) AS start,
MAX(end1) AS end1,
SUM(count) AS count
FROM grouped_data
GROUP BY order_id, grp
ORDER BY MIN(start);
| order_id | start | end1 | count |
|---|---|---|---|
| 1 | 2023-12-19 10:00:00 | 2023-12-19 12:00:00 | 16 |
| 2 | 2023-12-19 12:00:00 | 2023-12-19 13:00:00 | 2 |
| 3 | 2023-12-19 13:00:00 | 2023-12-19 14:00:00 | 10 |
| 1 | 2023-12-19 14:00:00 | 2023-12-19 17:00:00 | 14 |
| 3 | 2023-12-19 17:00:00 | 2023-12-19 18:00:00 | 21 |
| 1 | 2023-12-19 18:00:00 | 2023-12-19 19:00:00 | 5 |
SELECT 6