如何使用first_value()窗口函数在PostgreSQL中实现非空值向下填充？

需求说明 #

需要对brands表执行向下填充：将每个非NULL的category值，填充到后续所有连续的NULL行中，直到遇到下一个非NULL的category值为止。

表结构与测试数据 #

create table brands (
id int,
category varchar(20),
brand_name varchar(20)
);

insert into brands values
 (1,'chocolates','5-star')
,(2,null,'dairy milk')
,(3,null,'perk')
,(4,null,'eclair')
,(5,'Biscuits','britannia')
,(6,null,'good day')
,(7,null,'boost')
,(8,'shampoo','h&s')
,(9,null,'dove')
;

预期输出 #

问题分析 #

你尝试的脚本无法得到正确结果，核心原因是窗口函数的排序逻辑未正确划分填充分组：

select id,
      first_value(category)
      over(order by case when category is not null then id end desc nulls last) as category,
      brand_name
from brands

MS SQL中可以通过FIRST_VALUE(category) IGNORE NULLS结合特定窗口范围实现，但PostgreSQL在13版本前不支持IGNORE NULLS，我们可以用通用方法或适配高版本的方案解决。

修复方案 #

方案1：累加分组法（兼容所有PostgreSQL版本）

通过累加非NULLcategory的出现次数生成分组ID，再在分组内提取非NULL的category值：

select 
    id,
    max(category) over(partition by group_id) as category,
    brand_name
from (
    select 
        id,
        category,
        brand_name,
        -- 给每个非NULL category标记分组，后续NULL行沿用同一分组ID
        sum(case when category is not null then 1 else 0 end) over(order by id) as group_id
    from brands
) t
order by id;

方案2：LAST_VALUE+IGNORE NULLS（PostgreSQL 13+版本适用）

利用PostgreSQL 13新增的IGNORE NULLS选项，结合窗口范围直接提取最后一个非NULL的category：

select 
    id,
    last_value(category ignore nulls) over(order by id rows between unbounded preceding and current row) as category,
    brand_name
from brands
order by id;

说明 #

• 方案1通过分组逻辑确保同一category管辖的行被归为一组，max(category)会自动取到组内唯一的非NULL值；
• 方案2直接借助窗口函数特性，忽略NULL值后取当前行之前最后一个有效category，实现向下填充。

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