基于TSQL实现按ID分组，以OAC非零值为节点的累加计算

需求说明 #

按ID分组，对OAC和Adj列进行累计求和，规则如下：

• OAC非零的行，Calc值等于该行的OAC+Adj
• OAC为零的行，Calc值以最近一个非零OAC为起点，累加后续所有Adj值
• 遇到新的非零OAC时，重置累计计算

测试数据创建 #

DROP TABLE IF EXISTS #T;

CREATE TABLE #T(
    Id varchar(10),
    PeriodNum int,
    row_num int,
    OAC money,
    Adj money
) ON [PRIMARY];

INSERT INTO #T VALUES
('A','201606','1','5','0'),
('A','201905','2','0','-2'),
('A','201906','3','100','0'),
('A','202008','4','0','-6'),
('A','202009','5','0','-8'),
('A','202106','6','0','-11'),
('A','202109','7','23','0'),
('B','201606','1','3','0'),
('B','201905','2','0','25'),
('B','201906','3','60','0'),
('B','202008','4','0','12'),
('B','202009','5','0','-5'),
('B','202106','6','0','6'),
('B','202109','7','6','0');

错误尝试代码 #

SELECT *, 
    SUM(IIF(t.OAC<> 0,(t.OAC + t.Adj),0)) OVER (PARTITION BY t.ID ORDER BY t.row_num ASC) AS Calc
FROM #T t;

预期结果 #

正确解决方案 #

思路 #

1. 给每个ID内的行划分累计组：每遇到一个非零OAC，就开启一个新的累计组
2. 在每个ID和累计组的范围内，累计求和OAC+Adj，实现以首个非零OAC为起点累加后续Adj的效果

代码 #

WITH GroupedData AS (
    SELECT 
        *,
        SUM(CASE WHEN OAC <> 0 THEN 1 ELSE 0 END) OVER (
            PARTITION BY Id 
            ORDER BY row_num 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS GroupId
    FROM #T
)
SELECT 
    Id,
    PeriodNum,
    row_num,
    OAC,
    Adj,
    SUM(OAC + Adj) OVER (
        PARTITION BY Id, GroupId 
        ORDER BY row_num 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS Calc
FROM GroupedData
ORDER BY Id, row_num;

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