SQL Server中用LAG与SUM实现带重置的累计求和问题

看起来你在计算1级老虎机累进奖金的历史累计金额时遇到了两个核心问题：一是当前用LAG+SUM的组合没能实现逐行累加的运行总和，二是缺少达到Reset阈值时的累计重置逻辑。我来帮你梳理问题并给出可行的解决方案：

原SQL的核心问题分析 #

1. 聚合函数与窗口函数误用：你原查询里的SUM(B.RateofProg/100 * A.Coinin/100)是聚合SUM（因为搭配了GROUP BY），而LAG()是窗口函数，两者混搭无法实现真正的逐行累加效果，这就是为什么你只得到了前两行的相加结果。
2. 缺失重置逻辑：原查询没有处理当累计金额达到Reset值时的重置操作，无法满足“重置为当前行的reset金额乘以Coinin金额”的需求。

修正后的解决方案：使用递归CTE处理动态累计与重置 #

因为重置是动态触发的（不是固定分区），递归CTE是处理这类动态累计场景最可靠的方式。下面是调整后的SQL：

WITH BaseData AS (
    -- 第一步：整理基础数据，计算每一行的累进贡献值与派奖金额
    SELECT 
        A.AID,
        A.BID,
        B.Level,
        A.Date,
        B.Reset,
        B.Cap,
        Description,
        B.RateofProg,
        A.Coinin,
        -- 计算当前行对累进奖金的贡献值（转换为元单位）
        (B.RateofProg / 100.00) * (A.Coinin / 100.00) AS ProgContribution,
        -- 标记当前行是否有派奖及派奖金额
        CASE 
            WHEN C.Eventcode = 10004500 THEN C.ProgressivePdAmt / 100.00 
            ELSE 0 
        END AS ProgressivePdAmt,
        -- 生成连续行号，避免AID不连续导致递归关联失败
        ROW_NUMBER() OVER (ORDER BY A.AID, A.BID, B.Level) AS RowNum
    FROM Payout A
    JOIN Slot_Progression B ON A.Mnum = B.Mnum
    JOIN Events C ON A.Date = C.Date
    WHERE 
        A.Mnum = '102026' 
        AND B.Level = '1' 
        AND A.Coinin > 0
),
RunningTotalCTE AS (
    -- 第二步：递归CTE起始行（处理第一行的初始累计）
    SELECT 
        AID,
        BID,
        Level,
        Date,
        Reset,
        Cap,
        Description,
        RateofProg,
        Coinin,
        ProgContribution,
        ProgressivePdAmt,
        RowNum,
        -- 初始累计：如果第一行有派奖则重置为Reset*Coinin，否则取贡献值
        CASE 
            WHEN ProgressivePdAmt > 0 THEN (Reset * Coinin)/100.00
            ELSE ProgContribution 
        END AS RunningTotal
    FROM BaseData
    WHERE RowNum = 1

    UNION ALL

    -- 第三步：递归处理后续每一行，判断是否需要重置累计
    SELECT 
        bd.AID,
        bd.BID,
        bd.Level,
        bd.Date,
        bd.Reset,
        bd.Cap,
        bd.Description,
        bd.RateofProg,
        bd.Coinin,
        bd.ProgContribution,
        bd.ProgressivePdAmt,
        bd.RowNum,
        CASE
            -- 情况1：当前行有派奖，直接重置累计值
            WHEN bd.ProgressivePdAmt > 0 THEN (bd.Reset * bd.Coinin)/100.00
            -- 情况2：累计值达到Reset阈值，执行重置
            WHEN rtc.RunningTotal + bd.ProgContribution >= bd.Reset THEN (bd.Reset * bd.Coinin)/100.00
            -- 情况3：正常累加
            ELSE rtc.RunningTotal + bd.ProgContribution
        END AS RunningTotal
    FROM BaseData bd
    JOIN RunningTotalCTE rtc ON bd.RowNum = rtc.RowNum + 1
)
-- 最终输出结果
SELECT 
    AID,
    BID,
    Level,
    Date,
    Reset,
    Cap,
    Description,
    RateofProg,
    Coinin,
    ProgContribution,
    ProgressivePdAmt,
    RunningTotal
FROM RunningTotalCTE
ORDER BY RowNum;

关键逻辑说明 #

1. BaseData CTE：先统一整理基础数据，计算每行的贡献值、派奖金额，并生成连续行号（避免AID不连续导致递归关联出错）。
2. 递归CTE：
   • 起始行处理第一行的初始累计值，考虑了第一行就有派奖的情况。
   • 递归部分逐行判断三种场景：有派奖则重置、累计达到阈值则重置、否则正常累加，完全匹配你的需求。
3. 单位转换：所有金额都转换为元单位（除以100），你可以根据实际数据的存储单位调整这个转换逻辑。

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