基于Group By按键批量选记录：按连续Country分组计算差值

嘿，这个问题属于SQL里经典的「连续相同分组（岛屿问题）」，我来帮你一步步解决！

首先，你要的是把同一RelationshipID下连续出现的相同Country划分为一个组，然后计算每组内Val的最大值减最小值。我们可以用窗口函数来标记这些连续的“岛屿”：

实现SQL（以MySQL 8.0+或其他支持窗口函数的数据库为例） #

WITH ranked_records AS (
    SELECT 
        *,
        -- 给每个RelationshipID下的行按ID顺序编号
        ROW_NUMBER() OVER (PARTITION BY RelationshipID ORDER BY ID) AS global_rn,
        -- 给每个RelationshipID+Country组合下的行按ID顺序编号
        ROW_NUMBER() OVER (PARTITION BY RelationshipID, Country ORDER BY ID) AS country_rn
    FROM records
),
island_markers AS (
    SELECT 
        ID,
        RelationshipID,
        Country,
        -- 用全局编号减去同Country内的编号，相同连续组会得到相同的标记值
        global_rn - country_rn AS island_id
    FROM ranked_records
)
SELECT 
    RelationshipID,
    Country,
    MAX(Val) - MIN(Val) AS diff_val
FROM records
JOIN island_markers USING (ID, RelationshipID, Country)
GROUP BY RelationshipID, Country, island_id
ORDER BY MIN(ID);

执行结果 #

这段SQL会输出你最初期望的结果：

RelationshipID | Country | diff_val
---------------|---------|---------
88             | UK      | 2
88             | FR      | 2
88             | UK      | 1

如果你希望把同一RelationshipID下所有相同Country的连续组差值相加（比如UK的两个连续组差值2+1=3），那可以在上面的基础上再做一次分组求和：

WITH ranked_records AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY RelationshipID ORDER BY ID) AS global_rn,
        ROW_NUMBER() OVER (PARTITION BY RelationshipID, Country ORDER BY ID) AS country_rn
    FROM records
),
island_markers AS (
    SELECT 
        ID,
        RelationshipID,
        Country,
        global_rn - country_rn AS island_id
    FROM ranked_records
),
island_diffs AS (
    SELECT 
        RelationshipID,
        Country,
        MAX(Val) - MIN(Val) AS single_diff
    FROM records
    JOIN island_markers USING (ID, RelationshipID, Country)
    GROUP BY RelationshipID, Country, island_id
)
SELECT 
    RelationshipID,
    Country,
    SUM(single_diff) AS total_diff
FROM island_diffs
GROUP BY RelationshipID, Country
ORDER BY Country;

执行结果 #

这样就能得到你想要的最优结果：

RelationshipID | Country | total_diff
---------------|---------|-----------
88             | FR      | 2
88             | UK      | 3

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