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 tbl (id int, amount int, p_id int, date timestamp);
INSERT INTO tbl VALUES
(1, 5, 1, '2020-01-01T01:00:00'),
(2, 10, 1, '2020-01-01T01:10:00'),
(3, 15, 2, '2020-01-01T01:20:00'),
(4, 10, 3, '2020-01-01T03:30:00'),
(5, 10, 4, '2020-01-01T03:50:00'),
(6, 20, 1, '2020-01-01T03:40:00')
;
CREATE TABLE
INSERT 0 6
-- simple case for given hour
SELECT timestamp '2020-01-01 03:00:00', sum(amount)
FROM (
SELECT DISTINCT ON (p_id) amount
FROM tbl
WHERE date < timestamp '2020-01-01 03:00:00' + interval '1h'
ORDER BY p_id, date DESC
) sub;
| timestamp | sum |
|---|---|
| 2020-01-01 03:00:00 | 55 |
SELECT 1
-- "simple" solution for every distinct hour in the table
SELECT *
FROM ( -- or some other (cheaper?) source of hours?
SELECT DISTINCT date_trunc('hour', date) AS hour
FROM tbl
) h
CROSS JOIN LATERAL (
SELECT sum(amount)
FROM (
SELECT DISTINCT ON (p_id) amount
FROM tbl
WHERE date < h.hour + interval '1h'
ORDER BY p_id, date DESC
) sub1
) sub2
ORDER BY h.hour;
| hour | sum |
|---|---|
| 2020-01-01 01:00:00 | 25 |
| 2020-01-01 03:00:00 | 55 |
SELECT 2
-- possibly (much) faster for many distinct hours
WITH RECURSIVE
values AS (
SELECT DISTINCT ON (1, 2)
date_trunc('hour', date) AS hour, p_id, amount
FROM tbl
ORDER BY 2, 1, date DESC -- !
)
, hours AS (
SELECT hour, row_number() OVER (ORDER BY hour) AS hnr
FROM (SELECT DISTINCT hour FROM values) sub
)
, cte AS (
SELECT p_id, h.hour, v.amount, 2 AS next_hnr
FROM (SELECT DISTINCT p_id FROM values) i -- all IDs
JOIN hours h ON hnr = 1 -- first hour
LEFT JOIN values v USING (p_id, hour)
UNION ALL
SELECT c.p_id, h.hour, COALESCE(v.amount, c.amount), c.next_hnr + 1
FROM cte c
JOIN hours h ON h.hnr = c.next_hnr
LEFT JOIN values v ON v.p_id = c.p_id
AND v.hour = h.hour
)
SELECT hour, sum(amount)
FROM cte
GROUP BY 1
ORDER BY 1;
| hour | sum |
|---|---|
| 2020-01-01 01:00:00 | 25 |
| 2020-01-01 03:00:00 | 55 |
SELECT 2