如何在SQL中按条件重置计算累积求和（连续未付款月数）

问题分析 #

你需要生成CONSECUTIVE_MTHS_UNPAID字段，规则是：

• 当PAYMENT = 0时，连续累加1
• 当PAYMENT ≠ 0时，重置为0

你原有的SQL使用SUM()窗口函数直接累加，但这种方式无法在遇到非0付款时重置计数，会导致所有0付款行的计数持续累加，不符合需求。

解决方案 #

核心思路是先将数据按“非0付款”分割成独立分组，再在每个分组内对0付款行进行连续计数：

WITH grouped_data AS (
    SELECT
        CUSTOMER,
        CONTRACT_NO,
        INVOICE_DATE,
        PAYMENT,
        -- 生成分组ID：每遇到非0付款，分组ID递增，将后续的0付款归为同一组
        SUM(CASE WHEN PAYMENT != 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY CUSTOMER, CONTRACT_NO 
            ORDER BY INVOICE_DATE
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS group_id
    FROM project_one.database_one.contracts_table
)
SELECT
    CUSTOMER,
    CONTRACT_NO,
    INVOICE_DATE,
    PAYMENT,
    -- 在每个分组内，对0付款行计数，非0行设为0
    CASE 
        WHEN PAYMENT = 0 THEN 
            ROW_NUMBER() OVER (
                PARTITION BY CUSTOMER, CONTRACT_NO, group_id 
                ORDER BY INVOICE_DATE
            )
        ELSE 0 
    END AS CONSECUTIVE_MTHS_UNPAID
FROM grouped_data
ORDER BY CUSTOMER, CONTRACT_NO, INVOICE_DATE;

代码解释 #

1. 分组ID生成：

   • SUM(CASE WHEN PAYMENT != 0 THEN 1 ELSE 0 END)会为每一行生成一个标记，非0付款行标记为1，0付款行标记为0
   • 窗口函数累加这个标记，得到的group_id会在每次遇到非0付款时递增，这样所有连续的0付款行会被归到同一个group_id下

2. 连续计数：

   • 在每个CUSTOMER, CONTRACT_NO, group_id分组内，用ROW_NUMBER()对0付款行进行排序计数，实现连续累加
   • 非0付款行直接返回0，符合重置规则

验证结果 #

用你提供的示例数据测试，该SQL会输出与期望完全一致的结果：

• 2010-04的0付款行计数为1，后续2010-05的非0付款行重置为0
• 2011-02开始的连续3个0付款行，计数分别为1、2、3，遇到2011-05的非0付款行再次重置为0

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