窗口函数计算时如何截断分值？SQL查询修正求助

核心规则回顾 #

当累计积分（上一年有效积分 + 当年新增积分）超过150的整数倍时，当年超出该倍数的部分将被扣除，最终得到当年的有效累计积分：

• 第2年累计148+4=152，超过150（1倍），扣除超出的2，有效积分150
• 第3年累计150+149=299，未超过300（2倍），全部保留，有效积分299

原查询的问题 #

1. 错误地预先将单年Points超过150的部分截断为150，违背规则（规则是累计后超倍数才扣减，而非单年积分超150就截断）
2. 累计积分的计算逻辑错误，导致后续扣减逻辑偏离预期
3. 扣减逻辑未基于上一年的有效积分，而是基于错误的累计值

修正后的查询（递归CTE实现） #

递归CTE能清晰处理逐年依赖的计算逻辑，直接匹配规则要求：

WITH RecursivePoints AS (
    -- 锚点：处理第一年的有效积分
    SELECT 
        Year,
        Points,
        CorrectResult,
        CASE WHEN Points > 150 THEN 150 ELSE Points END AS RelevantPoints
    FROM Courses
    WHERE Year = (SELECT MIN(Year) FROM Courses)
    
    UNION ALL
    
    -- 递归计算后续年份
    SELECT 
        c.Year,
        c.Points,
        c.CorrectResult,
        CASE
            -- 计算临时累计：上一年有效积分 + 当年新增积分
            WHEN (rp.RelevantPoints + c.Points) > 
                 -- 计算下一个阈值：若上一年有效积分是150的倍数，阈值为当前倍数+150；否则取大于上一年有效积分的最小150倍数
                 CASE 
                     WHEN rp.RelevantPoints % 150 = 0 THEN rp.RelevantPoints + 150 
                     ELSE CEILING(rp.RelevantPoints / 150.0) * 150 
                 END
            THEN
                -- 超出阈值时，取阈值作为当年有效积分（扣除超出部分）
                CASE 
                    WHEN rp.RelevantPoints % 150 = 0 THEN rp.RelevantPoints + 150 
                    ELSE CEILING(rp.RelevantPoints / 150.0) * 150 
                END
            ELSE
                -- 未超出阈值时，全部积分保留
                rp.RelevantPoints + c.Points
        END AS RelevantPoints
    FROM Courses c
    JOIN RecursivePoints rp ON c.Year = rp.Year + 1
)
SELECT Year, Points, CorrectResult, RelevantPoints
FROM RecursivePoints
ORDER BY Year;

测试结果 #

执行后将得到完全匹配预期的结果：

替代方案（窗口函数实现） #

如果偏好非递归写法，可使用窗口函数计算原始累计后，再扣除所有历史超出部分：

SELECT
    Year,
    Points,
    CorrectResult,
    -- 原始累计积分减去所有历史超出阈值的部分总和
    SUM(Points) OVER(ORDER BY Year) - 
    SUM(
        CASE
            WHEN SUM(Points) OVER(ORDER BY Year) > 
                 CASE 
                     WHEN LAG(SUM(Points) OVER(ORDER BY Year), 1, 0) % 150 = 0 
                     THEN LAG(SUM(Points) OVER(ORDER BY Year), 1, 0) + 150 
                     ELSE CEILING(LAG(SUM(Points) OVER(ORDER BY Year), 1, 0) / 150.0) * 150 
                 END
            THEN SUM(Points) OVER(ORDER BY Year) - 
                 CASE 
                     WHEN LAG(SUM(Points) OVER(ORDER BY Year), 1, 0) % 150 = 0 
                     THEN LAG(SUM(Points) OVER(ORDER BY Year), 1, 0) + 150 
                     ELSE CEILING(LAG(SUM(Points) OVER(ORDER BY Year), 1, 0) / 150.0) * 150 
                 END
            ELSE 0
        END
    ) OVER(ORDER BY Year) AS RelevantPoints
FROM Courses
ORDER BY Year;

内容的提问来源于stack exchange，提问作者Yisroel M. Olewski