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?.
--https://stackoverflow.com/q/75462304/5298879
CREATE TABLE IF NOT EXISTS test (
id VARCHAR,
date DATE,
value_1 INT,
value_2 VARCHAR );
INSERT INTO test VALUES
(1, '2022-01-04', 5, 'asdf'),
(1, '2022-01-03', NULL, NULL),
(1, '2022-01-02', NULL, 'def'),
(1, '2022-01-01', 4, NULL),
--(1, '2021-12-31', 99, 'zz'),--id 1's max value1 and max value2
(2, '2022-01-04', 1, 'a'),
(2, '2022-01-03', NULL, NULL),
(2, '2022-01-02', 2, 'b'),
--(2, '2022-01-01', 77, 'yy'),--id 2's max value1 and max value2
(2, '2021-12-31', NULL, NULL);
SELECT *
FROM test
ORDER BY id, date DESC;
CREATE TABLE
INSERT 0 8
| id | date | value_1 | value_2 |
|---|---|---|---|
| 1 | 2022-01-04 | 5 | asdf |
| 1 | 2022-01-03 | null | null |
| 1 | 2022-01-02 | null | def |
| 1 | 2022-01-01 | 4 | null |
| 2 | 2022-01-04 | 1 | a |
| 2 | 2022-01-03 | null | null |
| 2 | 2022-01-02 | 2 | b |
| 2 | 2021-12-31 | null | null |
SELECT 8
SELECT id,
date,
(JSONB_AGG(value_1)FILTER(WHERE value_1 IS NOT NULL)OVER w->>-1)::int AS value_1,
(JSONB_AGG(value_2)FILTER(WHERE value_2 IS NOT NULL)OVER w->>-1) AS value_2
FROM test
WINDOW w AS (PARTITION BY id ORDER BY date)
ORDER BY id, date DESC;
| id | date | value_1 | value_2 |
|---|---|---|---|
| 1 | 2022-01-04 | 5 | asdf |
| 1 | 2022-01-03 | 4 | def |
| 1 | 2022-01-02 | 4 | def |
| 1 | 2022-01-01 | 4 | null |
| 2 | 2022-01-04 | 1 | a |
| 2 | 2022-01-03 | 2 | b |
| 2 | 2022-01-02 | 2 | b |
| 2 | 2021-12-31 | null | null |
SELECT 8
--https://stackoverflow.com/a/75631505/5298879
update test t1
set value_1=(select distinct on (t2.id) value_1--The top value_1 (latest by date)
from test t2 --that's in the same table ^
where t2.date < t1.date --in an earlier row |
and t2.id = t1.id --for the same id |
and t2.value_1 is not null --and isn't a null. |
order by t2.id, date desc) --The top meaning the latest_/
where t1.value_1 is null;
update test t1
set value_2=(select distinct on (t2.id) value_2--Use the top (latest by date) value_2
from test t2 --that's in the same table ^
where t2.date < t1.date --in an earlier row |
and t2.id = t1.id --for the same id |
and t2.value_2 is not null --and isn't a null. |
order by t2.id, date desc) --The top meaning the latest_/
where t1.value_2 is null;
select * from test order by id,date desc;
UPDATE 4
UPDATE 4
| id | date | value_1 | value_2 |
|---|---|---|---|
| 1 | 2022-01-04 | 5 | asdf |
| 1 | 2022-01-03 | 4 | def |
| 1 | 2022-01-02 | 4 | def |
| 1 | 2022-01-01 | 4 | null |
| 2 | 2022-01-04 | 1 | a |
| 2 | 2022-01-03 | 2 | b |
| 2 | 2022-01-02 | 2 | b |
| 2 | 2021-12-31 | null | null |
SELECT 8