如何用SQL创建遇0重置的连续计数字段MonthCount？

要实现「Payment非0时连续计数，遇0置0，恢复非0后从1重新开始计数」的需求，核心是先识别连续非0的区间，再在每个区间内生成递增计数，0值行直接返回0。以下是兼容大多数SQL方言（MySQL、PostgreSQL、SQL Server等）的实现代码：

WITH reset_groups AS (
    SELECT 
        Account_Number,
        DateYYYYMM,
        Payment,
        -- 标记计数重置的起点：当前行非0且（是账户首行/上一行Payment为0）
        CASE 
            WHEN Payment != 0 AND (
                LAG(Payment) OVER (PARTITION BY Account_Number ORDER BY DateYYYYMM) = 0 
                OR LAG(Payment) OVER (PARTITION BY Account_Number ORDER BY DateYYYYMM) IS NULL
            ) THEN 1
            ELSE 0
        END AS reset_flag,
        -- 标记当前行是否为Payment=0的行
        CASE WHEN Payment = 0 THEN 1 ELSE 0 END AS is_zero
    FROM your_table
),
interval_groups AS (
    SELECT 
        *,
        -- 累加重置标记，将同一个连续非0区间的行归为一组
        SUM(reset_flag) OVER (PARTITION BY Account_Number ORDER BY DateYYYYMM) AS interval_id
    FROM reset_groups
)
SELECT 
    Account_Number,
    DateYYYYMM,
    Payment,
    -- 0值行直接返回0，非0行在所属区间内生成连续计数
    CASE 
        WHEN is_zero = 1 THEN 0
        ELSE ROW_NUMBER() OVER (PARTITION BY Account_Number, interval_id ORDER BY DateYYYYMM)
    END AS MonthCount
FROM interval_groups
ORDER BY Account_Number, DateYYYYMM;

代码逻辑说明 #

1. reset_groups CTE：定位每个连续非0区间的起始行（重置点），同时标记所有Payment=0的行，为后续分组做准备。
2. interval_groups CTE：通过累加重置标记，把同一个连续非0区间的行分配到同一个interval_id下，确保计数只会在区间内连续递增。
3. 最终查询：对Payment=0的行直接返回0，非0行在所属区间内用ROW_NUMBER()生成从1开始的连续计数，完美实现遇0重置的需求。

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