Selecting the most recent value of a column from a Postgres table

Question

I've got some data in a Postgres table that looks like:

Name | Date      | Balance
--------------------------
A    |2020-01-01 |    1
B    |2020-01-01 |    0
B    |2020-01-02 |    2
A    |2020-01-03 |    5

(note that A is missing a value for 2020-01-02 and B for 2020-01-03)

I'd like to fill in the missing date with it's most recent value for that name. In other words, I'd like

Name | Date      | Balance
--------------------------
A    |2020-01-01 |    1
B    |2020-01-01 |    0
A    |2020-01-02 |    1 <--- filled in with previous balance
B    |2020-01-02 |    2
A    |2020-01-03 |    5
B    |2020-01-03 |    2 <--- filled in with previous balance

Note that in reality, several dates may be missing in a row, in which case the most recent one for that name should always be selected.

GMB · Accepted Answer · 2020-05-03 19:19:05Z

2

I am thinking generate_series() and window functions:

select 
    n.name, 
    s.date, 
    coalesce(t.balance, lag(balance) over(partition by n.name order by s.date) balance
from (select generate_series(min(date), max(date), interval '1 day') date from mytable) s
cross join (select distinct name from mytable) n
left join mytable t on t.name = n.name and t.date = s.date
order by n.name, s.date

If you may have several missing dates in a row, then a little more logic is needed - this basically emulates lag() with the ignore nulls option:

select
    name,
    date,
    coalesce(balance, first_value(balance) over(partition by name, grp)) balance
from (
    select 
        n.name, 
        s.date, 
        t.balance,
        sum( (t.balance is not null)::int ) over(partition by n.name order by s.date) grp
    from (select generate_series(min(date), max(date), interval '1 day') date from mytable) s
    cross join (select distinct name from mytable) n
    left join mytable t on t.name = n.name and t.date = s.date
) t
order by name, date

edited May 3, 2020 at 19:19

answered May 3, 2020 at 19:13

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Octodone Over a year ago

This is great, thanks! One last question, what if I wanted to be able to filter above a certain date, but still have the coelesced balance include the most recent value (even if the value is before the given date)?

GMB Over a year ago

@Octodone: you would need to turn the query to a subquery, and do the date filtering in the outer query.

Collectives™ on Stack Overflow

Selecting the most recent value of a column from a Postgres table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related