PostgreSQL如何用前一个非空值填充offer_id列的NULL值？

你遇到的问题是lag()只能获取紧邻的上一行值，当遇到连续多个NULL时，第二个及以后的NULL会因为上一行也是NULL而无法得到正确的填充值。要解决这个问题，核心思路是将连续的NULL与前面最近的非NULL值归为同一组，然后在组内复用该非NULL值。

实现方法 #

通过以下两步实现：

1. 生成分组标识：使用COUNT(offer_id) OVER (ORDER BY date)创建分组，由于COUNT会忽略NULL值，每遇到一个非NULL的offer_id，计数就会递增，后续的所有连续NULL都会被归入同一个分组。
2. 填充NULL值：在每个分组内，取该组的第一个非NULL值（可以用FIRST_VALUE或MAX，因为组内只有第一个值非空）。

完整查询代码 #

WITH orders AS (
    SELECT 2 AS offer_id, '2021-01-01'::date AS date UNION ALL
    SELECT 3 AS offer_id, '2021-01-02'::date AS date UNION ALL
    SELECT NULL AS offer_id, '2021-01-03'::date AS date UNION ALL
    SELECT NULL AS offer_id, '2021-01-04'::date AS date UNION ALL
    SELECT NULL AS offer_id, '2021-01-05'::date AS date UNION ALL
    SELECT 4 AS offer_id, '2021-01-07'::date AS date UNION ALL
    SELECT 5 AS offer_id, '2021-01-08'::date AS date UNION ALL
    SELECT NULL AS offer_id, '2021-01-09'::date AS date UNION ALL
    SELECT 8 AS offer_id, '2021-01-10'::date AS date UNION ALL
    SELECT 9 AS offer_id, '2021-01-11'::date AS date UNION ALL
    SELECT NULL AS offer_id, '2021-01-12'::date AS date UNION ALL
    SELECT NULL AS offer_id, '2021-01-13'::date AS date UNION ALL
    SELECT 13 AS offer_id, '2021-01-14'::date AS date UNION ALL
    SELECT 13 AS offer_id, '2021-01-15'::date AS date UNION ALL
    SELECT NULL AS offer_id, '2021-01-16'::date AS date 
)
SELECT 
    offer_id,
    date,
    FIRST_VALUE(offer_id) OVER (PARTITION BY group_id ORDER BY date) AS updated_offer_id
FROM (
    SELECT 
        *,
        COUNT(offer_id) OVER (ORDER BY date) AS group_id
    FROM orders
) AS grouped_orders;

代码说明 #

• 子查询grouped_orders中，COUNT(offer_id) OVER (ORDER BY date)生成的group_id会为每个连续NULL段分配与前面最近非NULL值相同的分组ID。
• 外层查询用FIRST_VALUE(offer_id) OVER (PARTITION BY group_id ORDER BY date)获取每个分组的第一个非NULL值，填充该组内所有NULL。

你也可以用MAX(offer_id) OVER (PARTITION BY group_id)替代FIRST_VALUE，效果一致，因为每个分组内只有第一个值非空，MAX会直接取到该值。

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