I have a table in postgres with three columns, one with a group, one with a date and the last with a value.
grp | mydate | value |
---|---|---|
A | 2021-01-27 | 5 |
A | 2021-01-23 | 10 |
A | 2021-01-15 | 15 |
B | 2021-01-26 | 7 |
B | 2021-01-24 | 12 |
B | 2021-01-15 | 17 |
I would like to create a view with a sequence of dates and the most recent value on table for each date according with group.
grp | mydate | value |
---|---|---|
A | 2021-01-27 | 5 |
A | 2021-01-26 | 10 |
A | 2021-01-25 | 10 |
A | 2021-01-24 | 10 |
A | 2021-01-23 | 10 |
A | 2021-01-22 | 15 |
A | 2021-01-21 | 15 |
A | 2021-01-20 | 15 |
A | 2021-01-19 | 15 |
A | 2021-01-18 | 15 |
A | 2021-01-17 | 15 |
A | 2021-01-16 | 15 |
A | 2021-01-15 | 15 |
B | 2021-01-27 | 7 |
B | 2021-01-26 | 7 |
B | 2021-01-25 | 12 |
B | 2021-01-24 | 12 |
B | 2021-01-23 | 17 |
B | 2021-01-22 | 17 |
B | 2021-01-21 | 17 |
B | 2021-01-20 | 17 |
B | 2021-01-19 | 17 |
B | 2021-01-18 | 17 |
B | 2021-01-17 | 17 |
B | 2021-01-16 | 17 |
B | 2021-01-15 | 17 |
SQL code to generate the table:
CREATE TABLE foo (
grp char(1),
mydate date,
value integer);
INSERT INTO foo VALUES
('A', '2021-01-27', 5),
('A', '2021-01-23', 10),
('A', '2021-01-15', 15),
('B', '2021-01-26', 7),
('B', '2021-01-24', 12),
('B', '2021-01-15', 17)
I have so far managed to generate a visualization with the sequence of dates joined with the distinct groups, but I am failing to get the most recent value.
SELECT DISTINCT(foo.grp), (date_trunc('day'::text, dd.dd))::date AS mydate
FROM foo, generate_series((( SELECT min(foo.mydate) AS min
FROM foo))::timestamp without time zone, (now())::timestamp without time zone, '1 day'::interval) dd(dd)