修复历史数据：GROUP BY语句导致时间区间错误的解决问询

你遇到的问题很典型——普通的GROUP BY会把所有相同Profit+ID的行都聚合到一起，但你需要的是连续时间段内相同Profit的区间合并，而不是跨区间的合并。

原始数据 #

你的历史表temp.[wrong_archiv]数据如下：

你之前的错误尝试 #

你用了下面的GROUP BY语句：

SELECT MIN(valid_from ) AS valid_from ,MAX(valid_to ) AS valid_to ,Profit ,ID
INTO [repaired_archiv]
FROM temp.[wrong_archiv]
GROUP BY Profit ,ID

得到的结果把所有Profit=10的行都合并了，导致valid_to错误：

正确解决方案：用窗口函数标记连续区间 #

我们需要先给连续的相同Profit+ID的行打上同一个分组标记，再进行聚合。下面是实现代码：

WITH grouped_data AS (
    SELECT 
        *,
        -- 当当前行的Profit与前一行不同时，生成新分组，用SUM累加得到分组ID
        SUM(CASE 
                WHEN LAG(Profit) OVER (PARTITION BY ID ORDER BY valid_from) != Profit THEN 1 
                ELSE 0 
            END) OVER (PARTITION BY ID ORDER BY valid_from) AS group_id
    FROM temp.[wrong_archiv]
)
SELECT 
    MIN(valid_from) AS valid_from,
    MAX(valid_to) AS valid_to,
    Profit,
    ID
INTO [repaired_archiv]
FROM grouped_data
GROUP BY ID, Profit, group_id
ORDER BY valid_from;

逻辑解释 #

1. LAG(Profit) OVER (PARTITION BY ID ORDER BY valid_from)：获取当前行的前一行Profit值（按ID分组、valid_from排序）
2. CASE判断：如果前一行Profit和当前行不同，就记为1，否则0；用SUM累加这个值，这样连续相同Profit的行会得到同一个group_id
3. 最后按ID、Profit、group_id分组，取每个组的最小valid_from和最大valid_to，就能得到你想要的连续区间合并结果

预期结果 #

执行后会得到你需要的正确结果：

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