SQL计算带重置的CurrentValue月度累计值异常问题

看起来你遇到了两个核心问题：原MAX-MIN分组在重置场景下失效，而用LAG的语句在无重置月份结果偏大。让我们一步步拆解并解决：

问题诊断 #

1. 原MAX-MIN语句的缺陷 #

当CurrentValue发生重置（突然变小）时，MAX(CurrentValue)-MIN(CurrentValue)会把重置前的最大值和重置后的最小值做差，结果包含了重置的“回退”部分，完全不符合实际累计增量（比如你例子里的错误结果101）。

2. 现有LAG语句的两个错误 #

• 跨月份增量误统计：你的LAG函数是全局排序（OVER (ORDER BY CurrentDateTime ASC)），没有按月份分区。这会导致当月第一条数据的CurrentValueLag是上月最后一条数据的值，无重置时这个跨月差值会被计入当月总和，使得结果大于正确的MAX-MIN值。
• 重置场景增量丢失：你的CASE语句在CurrentValue <= CurrentValueLag时返回0，这会忽略重置后的初始增量（比如重置后从1开始累计，这个1的增量没有被统计），导致重置场景下结果偏小。

解决方案：基于重置段的统计 #

我们可以先识别出每个重置后的连续递增段，再计算每个段的增量，最后按月求和这些段的增量。这种方法既处理了重置，也正确区分了跨月数据：

WITH Segments AS (
    SELECT 
        CurrentDateTime,
        CurrentValue,
        -- 标记每个重置段：当前值小于前一行时，开启新段
        SUM(
            CASE 
                WHEN CurrentValue < LAG(CurrentValue, 1, 0) OVER (ORDER BY CurrentDateTime) 
                THEN 1 
                ELSE 0 
            END
        ) OVER (ORDER BY CurrentDateTime) AS SegmentId
    FROM YourTable -- 替换成你的表名
),
SegmentMonthlyTotals AS (
    SELECT 
        -- 生成月度分组的起始日期
        DATEADD(MONTH, DATEDIFF(MONTH, 0, CurrentDateTime), 0) AS MonthStart,
        SegmentId,
        -- 计算每个段在当月内的增量
        MAX(CurrentValue) - MIN(CurrentValue) AS SegmentAccumulate
    FROM Segments
    GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CurrentDateTime), 0), SegmentId
)
SELECT 
    MonthStart,
    SUM(SegmentAccumulate) AS AccumulateValue
FROM SegmentMonthlyTotals
GROUP BY MonthStart
ORDER BY MonthStart;

逻辑说明 #

1. 标记重置段：用SUM() OVER ()窗口函数累计重置事件（当前值小于前一行），每发生一次重置，SegmentId就加1，这样同一连续递增段的SegmentId相同。
2. 计算段内月度增量：按月份和段分组，计算每个段在当月内的增量（段内当月最大值减最小值）。
3. 月度累计求和：将当月所有段的增量求和，得到最终的月度累计值。

对原有LAG语句的修正（可选） #

如果你想继续使用LAG的思路，需要修复两个问题：

SELECT 
    DATEADD(MONTH, DATEDIFF(MONTH, 0, CurrentDateTime), 0) AS MonthStart,
    SUM(DailyIncrement) AS AccumulateValue
FROM (
    SELECT 
        CurrentDateTime,
        CurrentValue,
        LAG(CurrentValue) OVER (ORDER BY CurrentDateTime ASC) AS PrevValue,
        -- 修正增量计算：第一行取当前值；正增量取差值；重置时取当前值（假设重置后从0开始累计）
        CASE 
            WHEN PrevValue IS NULL THEN CurrentValue
            WHEN CurrentValue > PrevValue THEN CurrentValue - PrevValue
            ELSE CurrentValue
        END AS DailyIncrement
    FROM YourTable
) AS IncrementCalculation
GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, CurrentDateTime), 0)
ORDER BY MonthStart;

但注意：这个修正后的语句如果遇到跨月的连续段，会把上月最后到当月第一条的增量计入当月，如果你需要严格区分月度内的增量，还是推荐前面的重置段统计方法。

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