如何为同一客户分区实现带重置条件的SQL负余额累计计数

从你的数据和期望输出可以明确需求：

• 按客户（Client_Id）分组，按日期（Date_Valid）升序排列
• 连续的负余额行从1开始递增计数，正余额行输出NULL
• 每次遇到正余额后，后续出现的负余额需重新从1开始计数

实现代码 #

WITH BalanceWithFlags AS (
    SELECT 
        Client_Id,
        Date_Valid,
        Balance,
        -- 标记当前行是否为负余额
        CASE WHEN Balance < 0 THEN 1 ELSE 0 END AS IsNegative,
        -- 生成连续负余额区间的分组ID：遇到正余额则分组ID递增
        SUM(CASE WHEN Balance >= 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY Client_Id 
            ORDER BY Date_Valid 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupId
    FROM Balance
)
SELECT 
    Client_Id,
    Date_Valid,
    Balance,
    -- 仅负余额行生成连续计数，正余额行返回NULL
    CASE WHEN IsNegative = 1 THEN 
        ROW_NUMBER() OVER (
            PARTITION BY Client_Id, GroupId 
            ORDER BY Date_Valid
        ) 
    ELSE NULL END AS Desired_Output
FROM BalanceWithFlags
ORDER BY Client_Id, Date_Valid;

代码说明 #

1. BalanceWithFlags 公共表达式（CTE）

   • IsNegative：简单标记当前行是否属于负余额，用于后续过滤
   • GroupId：核心逻辑，通过累加当前行及之前所有正余额的数量，为每个连续负余额区间分配唯一ID。只要遇到正余额，GroupId就会递增，确保下一段负余额进入新分组，实现重新计数。

2. 主查询

   • 针对每个客户的每个负余额分组，用ROW_NUMBER()按日期升序生成从1开始的连续计数
   • 通过CASE语句控制仅负余额行输出计数，正余额行返回NULL，完全匹配期望输出

验证结果 #

执行上述代码后，输出将与你提供的Desired_Output完全一致：

• 客户4001在2022-01-01的单条负余额计数为1；2022-01-10至01-12的连续负余额计数依次为1、2、3
• 客户4002在2022-01-02至01-05的连续负余额计数1到4；2022-01-07至01-08的连续负余额计数1到2，中间正余额行返回NULL

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