PostgreSQL：如何将NULL/No值替换为前一个有效产品值？

问题背景 #

我有一张包含多个邮箱的表，其中product列可能包含NULL或No值，表数据如下：

需求 #

将product列中的NULL或No值替换为该列中前一个非NULL且非No的有效值，期望结果如下（加粗为填充值）：

尝试的方法及问题 #

我尝试使用LAG()窗口函数，执行了如下SQL：

SELECT email,
    date,
    product,
    CASE    
        WHEN product='No' THEN lag(product) OVER(PARTITION BY email ORDER BY date)
        WHEN product IS NULL THEN lag(product) OVER(PARTITION BY email ORDER BY date)
    END AS product2
    FROM your_table_name;

得到的结果如下：

该方法无法处理连续的No值，因为LAG()只会取上一行的值，而连续的No行的上一行也是No，无法获取到之前的有效产品值。

正确的PostgreSQL解决方案 #

可以通过分组标识+窗口函数的方式实现需求，具体SQL如下：

WITH grouped_data AS (
    SELECT 
        email,
        date,
        product,
        -- 生成分组ID：每遇到有效产品（非NULL且非No）则分组号递增，否则沿用之前的分组号
        SUM(CASE WHEN product IS NOT NULL AND product != 'No' THEN 1 ELSE 0 END) 
            OVER (PARTITION BY email ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS group_id
    FROM your_table_name
)
SELECT 
    email,
    date,
    -- 取当前分组内的第一个有效产品值，填充NULL/No
    FIRST_VALUE(product) OVER (PARTITION BY email, group_id ORDER BY date) AS filled_product
FROM grouped_data
ORDER BY email, date;

原理说明 #

1. 分组标识生成：使用SUM()窗口函数，遍历每行数据时，若当前行是有效产品则加1，否则加0。这个累加值会形成连续的分组ID，所有后续的NULL/No行都会和最近的有效产品行同属一个分组。
2. 填充有效值：在每个分组内，使用FIRST_VALUE()窗口函数获取该分组的第一个有效产品值，将其作为当前行的填充值，从而实现连续NULL/No值的替换。

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