PostgreSQL 11.4 用同组前序非空值填充后续行null值的实现方法

解决方案 #

你使用的PostgreSQL 11版本已经原生支持窗口函数忽略空值的特性，针对你的场景可以用以下两种方案实现：

方案1：适配当前样例场景（每个food_type+country分组仅1个非空评分）

你的样例中每个分组只有1条非空的rating记录，剩余全为null，直接用聚合窗口函数即可，写法最简单性能最优：

SELECT
  food_type,
  country,
  rating,
  MAX(rating) OVER (PARTITION BY food_type, country) AS new_rating
FROM foods;

原理是聚合函数会自动忽略null值，直接取到分组内唯一的非空评分填充整个分组，执行结果和你的预期完全一致。

方案2：通用前向填充场景（分组内存在多个非空评分，需要取当前行之前最近的非空值填充）

如果后续业务中同一个分组会出现多个非空评分，需要严格按「前序第一条非空」的规则填充，可以用带IGNORE NULLS参数的LAST_VALUE窗口函数实现：

SELECT
  food_type,
  country,
  rating,
  LAST_VALUE(rating) OVER (
    PARTITION BY food_type, country
    -- 这里替换为你用来确定行顺序的业务字段，比如自增ID、创建时间
    ORDER BY id 
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    IGNORE NULLS
  ) AS new_rating
FROM foods;

注意：SQL表本身没有默认行序，必须指定明确的排序字段才能保证填充逻辑符合你的预期。如果没有专门的排序字段，只是要和你样例中的行顺序对齐，可以临时使用PostgreSQL的系统隐藏列ctid作为排序字段，正式环境建议使用业务字段保证稳定性。

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