PostgreSQL中实现遇Null值重置的累计求和方法问询

我需要在PostgreSQL中计算延迟天数的累计求和，但要求累计和遇到Null值时重置。尝试用以下窗口函数实现，未得到正确结果：

sum(delay_days)
       over (partition by id, status order by id, upper_range_effective_date asc rows between unbounded preceding and current row)  as delay_days_sum

表结构与测试数据 #

CREATE TEMPORARY TABLE temp_table(
          id INT,
          status varchar,
          lower_range_effective_date date,
          upper_range_effective_date date ,
          delay_days int -- 如果为null，说明lower_range_effective_date是付款日期
        );

INSERT INTO temp_table VALUES
    (1, 'failed', '2019-11-06', '2019-12-05',29),
    (1, 'failed', '2019-12-05', '2019-12-16',11),
    (1, 'completed', '2019-12-16', '2020-01-06',NULL),
    (1, 'failed', '2020-01-06', '2020-01-08',2),
    (1, 'completed', '2020-01-08', '2020-02-05',NULL),
    (1, 'failed', '2020-02-05', '2020-02-07',2),
    (1, 'completed', '2020-02-07', '2020-03-05',NULL),
    (1, 'failed', '2020-03-05', '2020-03-19',14),
    (1, 'completed', '2020-03-19', '2020-04-06',NULL),
    (1, 'failed', '2020-04-06', '2020-04-10',4),
    (1, 'completed', '2020-04-10', '2020-05-05',NULL),
    (1, 'failed', '2020-05-05', '2020-06-05',31),
    (1, 'failed', '2020-06-05', '2020-07-05',30);

当前尝试的查询语句 #

select *, sum(delay_days)
           over (partition by id, status order by id, lower_range_effective_date asc rows between unbounded preceding and current row)  as delay_days_sum 
from temp_table
order by lower_range_effective_date;

示例输入数据集 #

id  |status    | lower_range_effective_date | upper_range_effective_date| delay_days
----+----------+----------------------------+---------------------------+--------------
1   |failed    | 2019-11-06                 | 2019-12-05                | 29
1   |failed    | 2019-12-05                 | 2019-12-16                | 11
1   |completed | 2019-12-16                 | 2020-01-06                | <null>
1   |failed    | 2020-01-06                 | 2020-01-08                | 2
1   |completed | 2020-01-08                 | 2020-02-05                | <null>
1   |failed    | 2020-02-05                 | 2020-02-07                | 2
1   |completed | 2020-02-07                 | 2020-03-05                | <null>
1   |failed    | 2020-03-05                 | 2020-03-19                | 14
1   |completed | 2020-03-19                 | 2020-04-06                | <null>
1   |failed    | 2020-04-06                 | 2020-04-10                | 4
1   |completed | 2020-04-10                 | 2020-05-05                | <null>
1   |failed    | 2020-05-05                 | 2020-06-05                | 31
1   |failed    | 2020-06-05                 | 2020-07-05                | 30

期望输出结果 #

id  |status    | lower_range_effective_date | upper_range_effective_date| delay_days     | delay_days_sum
----+----------+----------------------------+---------------------------+----------------+----------------- 
1   |failed    | 2019-11-06                 | 2019-12-05                | 29             |29
1   |failed    | 2019-12-05                 | 2019-12-16                | 11             |40
1   |completed | 2019-12-16                 | 2020-01-06                | <null>         |<null> 
1   |failed    | 2020-01-06                 | 2020-01-08                | 2              |2
1   |completed | 2020-01-08                 | 2020-02-05                | <null>         |<null> 
1   |failed    | 2020-02-05                 | 2020-02-07                | 2              |2
1   |completed | 2020-02-07                 | 2020-03-05                | <null>         |<null> 
1   |failed    | 2020-03-05                 | 2020-03-19                | 14             | 14
1   |completed | 2020-03-19                 | 2020-04-06                | <null>         | <null>
1   |failed    | 2020-04-06                 | 2020-04-10                | 4              | 4 
1   |completed | 2020-04-10                 | 2020-05-05                | <null>         | <null>
1   |failed    | 2020-05-05                 | 2020-06-05                | 31             | 31
1   |failed    | 2020-06-05                 | 2020-07-05                | 30             | 61

解决方案 #

要实现遇到Null时重置累计和，核心是先为连续的非Null行创建分组标识，再基于分组计算累计和：

SELECT
    id,
    status,
    lower_range_effective_date,
    upper_range_effective_date,
    delay_days,
    CASE
        WHEN delay_days IS NULL THEN NULL
        ELSE SUM(delay_days) OVER (PARTITION BY id, group_id ORDER BY lower_range_effective_date)
    END AS delay_days_sum
FROM (
    SELECT
        *,
        -- 计算每行之前（含当前）的Null数量，作为分组ID，遇到Null则分组递增
        SUM(CASE WHEN delay_days IS NULL THEN 1 ELSE 0 END) OVER (
            PARTITION BY id ORDER BY lower_range_effective_date
        ) AS group_id
    FROM temp_table
) t
ORDER BY lower_range_effective_date;

逻辑说明

1. 生成分组ID：内层查询中，SUM(CASE WHEN delay_days IS NULL THEN 1 ELSE 0 END) OVER (...)会统计从当前行往前所有行中delay_days为Null的数量，每出现一个Null，分组ID就会加1，这样连续的非Null行会被归为同一个分组，Null行则单独成为一个分组。
2. 计算累计和：外层查询基于id和group_id分组，对delay_days做累计求和，同时用CASE语句确保Null行的delay_days_sum显示为Null，完全匹配预期结果。

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