如何编写SQL查询合并Elig for Disc A无变更的连续版本？

这是典型的连续相同分组（岛屿问题），可以通过窗口函数实现精准合并，避免将非连续的相同状态错误合并。核心思路是为每个人员的连续相同状态记录分配唯一组ID，再按组聚合。

具体SQL实现 #

假设你的表名为item_versions，以下是完整查询语句：

WITH ranked_versions AS (
    SELECT
        Person,
        "Elig for Disc A" AS Elig_Disc_A,
        "Version Start" AS Version_Start,
        "Version End" AS Version_End,
        -- 按人员+版本起始时间生成全局行号
        ROW_NUMBER() OVER (PARTITION BY Person ORDER BY "Version Start") AS global_row,
        -- 按人员+折扣A资格状态+版本起始时间生成状态内行号
        ROW_NUMBER() OVER (PARTITION BY Person, "Elig for Disc A" ORDER BY "Version Start") AS status_row
    FROM item_versions
),
grouped_versions AS (
    SELECT
        Person,
        Elig_Disc_A,
        Version_Start,
        Version_End,
        -- 行号差值相同的记录属于连续相同状态组
        global_row - status_row AS group_id
    FROM ranked_versions
)
SELECT
    Person,
    Elig_Disc_A AS "Elig for Disc A",
    MIN(Version_Start) AS "Version Start",
    MAX(Version_End) AS "Version End"
FROM grouped_versions
GROUP BY Person, Elig_Disc_A, group_id
ORDER BY Person, "Version Start";

原理说明 #

1. 生成行号：
   • global_row：对每个人员的记录按版本起始时间排序，生成全局递增的行号。
   • status_row：对每个人员的同状态记录按版本起始时间排序，生成状态内递增的行号。
2. 标记连续组：
   当状态连续时，global_row和status_row同步增长，两者的差值保持不变；当状态切换时，status_row会重置为1，差值发生变化，以此区分不同的连续状态组。
3. 聚合合并：
   按人员、折扣A资格状态、组ID分组，取每组最早的版本起始时间和最晚的版本结束时间，实现连续相同状态记录的合并。

注意事项 #

• 如果存在Version End为NULL的记录（表示当前生效的版本），MAX(Version_End)会保留NULL，符合业务逻辑。
• 若需要保留Elig for Disc B字段，可在聚合时根据业务需求选择取该组内的对应值（比如取最新或最早的记录值）。

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