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?.
WITH incomplete_table(date, group_name, value) AS (
VALUES
('2022-01-01'::date, 'a', 1)
, ('2022-01-15'::date, 'a', 0.5)
, ('2022-01-31'::date, 'a', 0.2)
, ('2022-01-02'::date, 'b', 0.1)
, ('2022-01-10'::date, 'b', 0.15)
, ('2022-01-20'::date, 'b', 0.15)
)
SELECT d.date, g.group_name, i.value
FROM (
SELECT date::date
FROM generate_series (timestamp '2022-01-01'
, timestamp '2022-01-31'
, interval '1 day') date
) d
CROSS JOIN (SELECT DISTINCT group_name FROM incomplete_table) g
LEFT JOIN LATERAL (
SELECT i.group_name, i.value
FROM incomplete_table i
WHERE i.group_name = g.group_name
AND i.date <= d.date
ORDER BY i.date DESC
LIMIT 1
) i ON true
ORDER BY g.group_name, d.date DESC;
| date | group_name | value |
|---|---|---|
| 2022-01-31 | a | 0.2 |
| 2022-01-30 | a | 0.5 |
| 2022-01-29 | a | 0.5 |
| 2022-01-28 | a | 0.5 |
| 2022-01-27 | a | 0.5 |
| 2022-01-26 | a | 0.5 |
| 2022-01-25 | a | 0.5 |
| 2022-01-24 | a | 0.5 |
| 2022-01-23 | a | 0.5 |
| 2022-01-22 | a | 0.5 |
| 2022-01-21 | a | 0.5 |
| 2022-01-20 | a | 0.5 |
| 2022-01-19 | a | 0.5 |
| 2022-01-18 | a | 0.5 |
| 2022-01-17 | a | 0.5 |
| 2022-01-16 | a | 0.5 |
| 2022-01-15 | a | 0.5 |
| 2022-01-14 | a | 1 |
| 2022-01-13 | a | 1 |
| 2022-01-12 | a | 1 |
| 2022-01-11 | a | 1 |
| 2022-01-10 | a | 1 |
| 2022-01-09 | a | 1 |
| 2022-01-08 | a | 1 |
| 2022-01-07 | a | 1 |
| 2022-01-06 | a | 1 |
| 2022-01-05 | a | 1 |
| 2022-01-04 | a | 1 |
| 2022-01-03 | a | 1 |
| 2022-01-02 | a | 1 |
| 2022-01-01 | a | 1 |
| 2022-01-31 | b | 0.15 |
| 2022-01-30 | b | 0.15 |
| 2022-01-29 | b | 0.15 |
| 2022-01-28 | b | 0.15 |
| 2022-01-27 | b | 0.15 |
| 2022-01-26 | b | 0.15 |
| 2022-01-25 | b | 0.15 |
| 2022-01-24 | b | 0.15 |
| 2022-01-23 | b | 0.15 |
| 2022-01-22 | b | 0.15 |
| 2022-01-21 | b | 0.15 |
| 2022-01-20 | b | 0.15 |
| 2022-01-19 | b | 0.15 |
| 2022-01-18 | b | 0.15 |
| 2022-01-17 | b | 0.15 |
| 2022-01-16 | b | 0.15 |
| 2022-01-15 | b | 0.15 |
| 2022-01-14 | b | 0.15 |
| 2022-01-13 | b | 0.15 |
| 2022-01-12 | b | 0.15 |
| 2022-01-11 | b | 0.15 |
| 2022-01-10 | b | 0.15 |
| 2022-01-09 | b | 0.1 |
| 2022-01-08 | b | 0.1 |
| 2022-01-07 | b | 0.1 |
| 2022-01-06 | b | 0.1 |
| 2022-01-05 | b | 0.1 |
| 2022-01-04 | b | 0.1 |
| 2022-01-03 | b | 0.1 |
| 2022-01-02 | b | 0.1 |
| 2022-01-01 | b | null |