SQL查询如何补全缺失月份并自动填充上月对应记录值

我有一个首列为月份的结果集，其中部分月份存在缺失，我需要为这些缺失的月份补入对应上月的记录，直到最近一个月为止。

当前数据如下：

期望输出效果：

我自行编写了一段SQL，但运行后不是仅填充缺失月份，而是基于所有行重复生成了数据，代码如下：

select 
to_char(generate_series(date_trunc('MONTH',to_date(period,'YYYYMMDD')+interval '1' month),
              date_trunc('MONTH',now()+interval '1' day), 
              interval '1' month) - interval '1 day','YYYYMMDD') as period, 
name,age,salary,rating
from( values ('20201205','Alex',35,100,'A+'),
            ('20210110','Alex',35,110,'A'),
            ('20210512','Alex',35,999,'A+'),
            ('20210625','Jhon',20,175,'B-'),
            ('20210922','Jhon',20,200,'B+')) v (period,name,age,salary,rating) order by 2,3,4,5,1;

该查询的实际输出如下：

请问如何修改SQL才能得到期望的输出？

你现有SQL的问题是直接为每条原始记录生成后续全量月份，没有按用户做分组限制，也没有处理缺失行的字段填充逻辑，修改后的SQL如下：

WITH raw_data AS (
    -- 原始数据统一转成月末日期格式，方便后续关联
    SELECT 
        to_char(date_trunc('MONTH', to_date(period, 'YYYYMMDD')) + INTERVAL '1 month - 1 day', 'YYYYMMDD') AS month_end,
        name,
        age,
        salary,
        rating
    FROM (
        VALUES 
            ('20201205','Alex',35,100,'A+'),
            ('20210110','Alex',35,110,'A'),
            ('20210512','Alex',35,999,'A+'),
            ('20210625','Jhon',20,175,'B-'),
            ('20210922','Jhon',20,200,'B+')
    ) v (period,name,age,salary,rating)
),
user_time_range AS (
    -- 按用户统计最早记录月份、需要填充到的最新月份（当前月）
    SELECT 
        name,
        min(date_trunc('MONTH', to_date(month_end, 'YYYYMMDD'))) AS min_month,
        date_trunc('MONTH', now() + INTERVAL '1 day') AS max_month
    FROM raw_data
    GROUP BY name
),
all_user_months AS (
    -- 为每个用户单独生成连续的月末日期序列
    SELECT 
        name,
        to_char(generate_series(min_month, max_month, INTERVAL '1 month') + INTERVAL '1 month - 1 day', 'YYYYMMDD') AS period
    FROM user_time_range
)
-- 左关联原始数据，用窗口函数填充空值
SELECT 
    a.period,
    a.name,
    last_value(r.age) IGNORE NULLS OVER (PARTITION BY a.name ORDER BY a.period) AS age,
    last_value(r.salary) IGNORE NULLS OVER (PARTITION BY a.name ORDER BY a.period) AS salary,
    last_value(r.rating) IGNORE NULLS OVER (PARTITION BY a.name ORDER BY a.period) AS rating
FROM all_user_months a
LEFT JOIN raw_data r ON a.name = r.name AND a.period = r.month_end
ORDER BY a.name, a.period;

逻辑说明 #

1. 统一原始数据的日期格式为对应月份的月末，避免同月份不同日期的关联匹配问题
2. 按用户维度单独计算各自的时间范围，避免跨用户生成多余日期
3. 左关联后通过last_value() IGNORE NULLS窗口函数，取当前用户当前行之前最近的非空值填充缺失字段，即可得到预期结果。

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