PostgreSQL实现用户时间序列缺失余额填充历史值

现有users表和balances表，需要生成从balances表最早到最晚日期的全量日期列表，每个用户每日都要有记录，缺失的balance用该用户最近可用的余额填充。

表结构与数据 #

users表 #

balances表 #

现有SQL #

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

这段SQL能生成全量的用户-日期组合，但缺失的balance为NULL，尝试用LAG()函数无法正确填充最近可用余额——因为LAG()仅取前一行的值，无法处理连续缺失的情况。

解决方案 #

使用LAST_VALUE()窗口函数结合IGNORE NULLS参数，按用户分组、日期排序，取当前行及之前的最后一个非NULL余额值：

select 
    u.name,
    s.day,
    LAST_VALUE(b.balance IGNORE NULLS) OVER (
        PARTITION BY u.name 
        ORDER BY s.day 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as filled_balance
from users u
cross join (
    select generate_series(min(date)::date, max(date)::date, interval '1 day')::date as day 
    from balances
) s
left join balances b on b.user_name = u.name and s.day = b.date
order by u.name, s.day;

逻辑说明 #

1. 生成全量日期范围：通过generate_series生成balances表中最早到最晚的所有日期，再和users表交叉连接得到每个用户的每日记录框架。
2. 关联余额数据：左连接balances表，匹配用户和日期对应的余额，缺失时为NULL。
3. 填充缺失余额：用LAST_VALUE(b.balance IGNORE NULLS)在每个用户的日期序列中，从起始行到当前行范围内，取最后一个非NULL的余额值，实现“最近可用余额填充”的效果。

执行后得到的结果如下：

内容的提问来源于stack exchange，提问作者sge