在SQL Server中实现类Excel滚动求和的技术问询

我需要在SQL Server里实现类似下图的Excel滚动求和效果：

核心需求是将**当日超额量（Daily Extra）与前一日结转量（Rollover）**累加，得到当日的结转量。我尝试用LAG()函数编写了代码，但没能实现正确的滚动累加逻辑，代码如下：

select *,
    case 
        when lag([daily_extra],1) over (order by [date]) = 0 and [daily_extra] > 0 then [daily_extra] 
        else 
            case 
                when lag([daily_extra],1) over (order by [date]) > 0 and [daily_extra] > 0 then [daily_extra]+lag([daily_extra],1) over (order by [date]) 
                else 0 
            end 
    end [Rollover]
from(
    select [date],
        [tickets_sold] [TICKETS],
        [max_tickets] [MAX TICKETS],
        case when [tickets_sold]-[max_tickets] > 0 then [tickets_sold]-[max_tickets] else 0 end [Daily Extra]
    from table
) t1

问题分析 #

原代码的问题在于仅引用了前一日的当日超额量（Daily Extra），而非前一日已经累加完成的结转量（Rollover），因此只能实现连续两天的超额相加，无法完成多天的滚动累加。

解决方案：使用递归CTE实现滚动累加 #

递归CTE可以逐行计算，直接引用上一行已算出的Rollover值，完美匹配需求逻辑：

WITH BaseData AS (
    -- 整理基础数据，按日期排序并添加行号
    SELECT 
        [date],
        [tickets_sold] AS [TICKETS],
        [max_tickets] AS [MAX TICKETS],
        CASE WHEN [tickets_sold] - [max_tickets] > 0 THEN [tickets_sold] - [max_tickets] ELSE 0 END AS [Daily Extra],
        ROW_NUMBER() OVER(ORDER BY [date]) AS rn
    FROM [table]
),
RecursiveCTE AS (
    -- 初始化：第一行的结转量等于当日超额量
    SELECT 
        [date],
        [TICKETS],
        [MAX TICKETS],
        [Daily Extra],
        [Daily Extra] AS [Rollover]
    FROM BaseData
    WHERE rn = 1
    UNION ALL
    -- 递归计算后续行：当日超额则累加前一日结转，无超额则清零（可按需调整逻辑）
    SELECT 
        bd.[date],
        bd.[TICKETS],
        bd.[MAX TICKETS],
        bd.[Daily Extra],
        CASE 
            WHEN bd.[Daily Extra] > 0 THEN rc.[Rollover] + bd.[Daily Extra]
            ELSE 0  -- 若需保留前一日结转，将此处改为 rc.[Rollover] 即可
        END AS [Rollover]
    FROM BaseData bd
    JOIN RecursiveCTE rc ON bd.rn = rc.rn + 1
)
-- 输出最终结果
SELECT [date], [TICKETS], [MAX TICKETS], [Daily Extra], [Rollover]
FROM RecursiveCTE
ORDER BY [date];

逻辑说明 #

1. 通过BaseData整理原始数据，计算每日超额量并按日期添加行号，为递归计算做准备。
2. RecursiveCTE的初始化部分，将第一行的结转量直接设为当日超额量。
3. 递归关联上一行数据，根据当日是否有超额，决定累加前一日结转或执行清零操作（可根据实际业务需求调整else分支逻辑）。

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