SQL Server中基于条件生成递增累计和并标记目标行

原始数据 #

需求说明 #

需要标记两类行，并新增NewSum和Flag列：

• 第一类：RunningTotal首次大于CutOff的行
• 第二类：RunningTotal大于「CutOff + 之前所有已达标RunningTotal」的行
• Flag列：达标行标记为1，否则为0
• NewSum列：当前达标阈值（首次为CutOff，后续为上一次达标RunningTotal + CutOff）

期望输出 #

用户原有尝试（未达需求） #

原有查询仅标记RunningTotal首次超过CutOff整数倍的行，逻辑不符合需求：

Select *,
Case
    When Min(RunningTotal) 
           Over(Partition By GroupName, (RunningTotal/CutOff) 
                Order By RunningTotal, (RunningTotal%CutOff) 
                Rows Between 1 Preceding and 1 Preceding
              ) Is Null 
        and RunningTotal >= CutOff 
     Then 1
  Else 0
End as Flag
From MyTable

正确解决方案（SQL Server） #

由于需要跟踪累计的达标阈值，我们可以使用窗口函数结合累计条件判断，通过计算每个分组内的累计达标值来实现：

WITH cte_base AS (
    SELECT 
        GroupName,
        Year,
        CutOff,
        RunningTotal,
        CutOff AS initial_threshold,
        -- 标记当前行是否超过初始CutOff
        CASE WHEN RunningTotal > CutOff THEN 1 ELSE 0 END AS is_over_initial
    FROM MyTable
),
cte_cumulative AS (
    SELECT 
        *,
        -- 累计已达标的RunningTotal总和
        SUM(CASE WHEN is_over_initial = 1 THEN RunningTotal ELSE 0 END) 
            OVER (PARTITION BY GroupName ORDER BY Year ROWS UNBOUNDED PRECEDING) AS total_met,
        -- 累计需要叠加的CutOff总和
        SUM(CASE WHEN is_over_initial = 1 THEN CutOff ELSE 0 END) 
            OVER (PARTITION BY GroupName ORDER BY Year ROWS UNBOUNDED PRECEDING) AS total_cutoff_add
    FROM cte_base
),
cte_final AS (
    SELECT 
        *,
        -- 计算NewSum：首次达标前用初始CutOff，达标后用上一次达标值+CutOff
        CASE
            WHEN total_met = 0 THEN initial_threshold
            ELSE LAG(RunningTotal + CutOff, 1, initial_threshold) 
                OVER (PARTITION BY GroupName ORDER BY Year)
        END AS NewSum,
        -- 计算Flag：判断当前行是否是首次超过当前阈值
        CASE
            WHEN RunningTotal > CASE
                                    WHEN total_met = 0 THEN initial_threshold
                                    ELSE LAG(RunningTotal + CutOff, 1, initial_threshold) 
                                        OVER (PARTITION BY GroupName ORDER BY Year)
                                END
                 AND (LAG(RunningTotal, 1, 0) OVER (PARTITION BY GroupName ORDER BY Year) 
                      <= CASE
                              WHEN total_met = 0 THEN initial_threshold
                              ELSE LAG(RunningTotal + CutOff, 1, initial_threshold) 
                                  OVER (PARTITION BY GroupName ORDER BY Year)
                          END)
            THEN 1
            ELSE 0
        END AS Flag
    FROM cte_cumulative
)
SELECT GroupName, Year, CutOff, RunningTotal, NewSum, Flag
FROM cte_final
ORDER BY GroupName, Year;

逻辑解释 #

1. 基础CTE：标记每一行是否超过初始CutOff，保留初始阈值。
2. 累计计算CTE：统计分组内到当前行的累计达标RunningTotal，以及累计需要叠加的CutOff总和。
3. 最终标记CTE：
   • NewSum：首次达标前为CutOff，达标后取上一次达标RunningTotal与CutOff的和。
   • Flag：判断当前行是否首次超过当前NewSum阈值，同时前一行未超过该阈值，确保是首次达标行。

备注 #

该方案支持同一GroupName内CutOff不固定的情况，每次达标都会使用当前行的CutOff更新后续阈值。

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