Question 114274 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL users and balances – use previous balance for user in time series if value is missing

Given the following tables:

users:

name
alice
bob

balances:

id user_name date balance
1 alice 2022-01-01 100
2 alice 2022-01-03 200
3 alice 2022-01-04 300
4 bob 2022-01-01 400
5 bob 2022-01-02 500
6 bob 2022-01-05 600

I would like to get a full list of all days from the first available to the last for all users, replacing NULL balances with the last available balance for that user.

This is what I have so far:

select u.name, s.day, b.balance
from users u
cross join (select generate_series(min(day)::date, max(day)::date, interval '1 day')::date as day from balances) s
left join balances b on b.user_name = u.name and s.day = b.day
order by u.name, s.day 
;

SQL Fiddle Here

I have tried LAG() and some other examples found here but none of them seem to get the right last balance for the user.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Based on How do I efficiently select the previous non-null value?, I ended up getting successful results with the following query:

    select
  name, 
  day, 
  first_value(balance) over (partition by x.name, value_partition order by day) as balance
from (
  select 
    u.name as name, 
    s.day as day, 
    b.balance as balance,
    sum(case when b.balance is null then 0 else 1 end) over (partition by u.name order by s.day) as value_partition
  from users u
  cross join (select generate_series(min(day)::date, max(day)::date, interval '1 day')::date as day from balances) s
  left join balances b on b.user_name = u.name and s.day = b.day
) x
order by x.name, x.day

    DB Fiddle


  2. 0

    We group every balance with the nulls that come after it by using count() over() and then we use max() over() to give the entire group the same value.

    select name
      ,day
      ,max(balance) over(partition by name, grp order by day) as balance
from 
(
select      u.name
           ,s.day
           ,b.balance
           ,count(case when b.balance is not null then 1 end) over(partition by u.name order by s.day) as grp
from        users u
cross join (select generate_series(min(day)::date, max(day)::date, interval '1 day')::date as day from balances) s
left join   balances b on b.user_name = u.name and s.day = b.day
order by    u.name, s.day 
) t
    name day balance
    alice 2022-01-01 100
    alice 2022-01-02 100
    alice 2022-01-03 200
    alice 2022-01-04 300
    alice 2022-01-05 300
    bob 2022-01-01 400
    bob 2022-01-02 500
    bob 2022-01-03 500
    bob 2022-01-04 500
    bob 2022-01-05 600

    Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search