How to get last value with condition in postgreSQL?

user_10852782 0
Viewed 547

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)
postgresql
1 Answers

step-by-step demo:db<>fiddle

SELECT
    grp,
    gs::date as mydate,
    value
FROM (
    SELECT
        *,
        COALESCE(                                                             -- 2
             lead(mydate) OVER (PARTITION BY grp ORDER BY mydate) - 1,        -- 1 
             mydate
        ) as prev_date
    FROM foo
) s,
    generate_series(mydate, prev_date, interval '-1 day') as gs               -- 3
ORDER BY grp, mydate DESC                                                     -- 4
  1. lead() window function shifts the next value of an ordered group (= partition) into the current one. The group is already defined, the order is the date. This can be used to create the required date range. Since you don't want to have the last date twice (as end of the first range and beginning of the next one) the end date stops - 1 (one day before the next group starts)
  2. This is for the very last records of the groups: They don't have a following record, so lead() yield NULL. To avoid this, COALESCE() sets them to the current record.
  3. Now, you can create a date range with the current and the next date value using generate_series().
  4. Finally you can generate the required order
user_39842210
Related