Postgres 13

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