如何用更简洁的PostgreSQL/SQL代码填充表中缺失的日期？

现有一张名为data4的表，结构与数据如下：

原表 data4 数据 #

当前使用多层CTE的方式填充date列中以-表示的缺失日期值，代码如下：

当前使用的多层CTE填充代码 #

WITH cte1 AS (
    SELECT NULLIF(date, '-') AS dated,
    region,
    beverages_units
    FROM data4
),
cte2 AS (
    SELECT (COALESCE (dated, LAG(dated) OVER ())) AS filled,
    region,
    beverages_units
FROM cte1),

cte3 AS (
    SELECT (COALESCE(filled, LAG(filled) OVER ())) AS filled2,
    region,
    beverages_units
FROM cte2),

cte4 AS (
    SELECT (COALESCE(filled2, LAG(filled2) OVER ())) AS filled3,
    region,
    beverages_units
FROM cte3)

SELECT *
FROM cte4;

该代码已实现预期输出：

预期输出结果 #

更简洁的PostgreSQL实现方法 #

方法1：用LAST_VALUE窗口函数（PostgreSQL 11+） #

直接利用LAST_VALUE搭配窗口范围，一次性填充所有缺失日期，无需多层嵌套：

SELECT 
    LAST_VALUE(NULLIF(date, '-')) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS date,
    region,
    beverages_units
FROM data4;

方法2：用MAX()窗口函数（兼容所有PostgreSQL版本） #

如果你的PostgreSQL版本低于11，LAST_VALUE不支持IGNORE NULLS，可以用MAX()替代，效果完全一致：

SELECT 
    MAX(NULLIF(date, '-')) OVER (ORDER BY (SELECT 1) ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS date,
    region,
    beverages_units
FROM data4;

补充：保证行顺序稳定 #

如果原表没有明确的排序字段，建议用ROW_NUMBER()来固定行顺序，避免结果混乱：

WITH ordered_data AS (
    SELECT *, ROW_NUMBER() OVER () AS rn
    FROM data4
)
SELECT 
    LAST_VALUE(NULLIF(date, '-')) OVER (ORDER BY rn ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS date,
    region,
    beverages_units
FROM ordered_data
ORDER BY rn;

逻辑说明 #

原多层CTE的问题是每次LAG()只能填充一行缺失值，所以要嵌套多次。而上面的方法：

1. 先用NULLIF(date, '-')把占位符-转成NULL，方便窗口函数处理
2. 通过窗口函数指定范围为从第一行到当前行，自动抓取当前行之前最近的非NULL日期值，一次性完成所有缺失值填充，代码更简洁，逻辑也更直观

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