基于年度条目创建ID与AssocID的区间映射记录

我完全懂你的困扰——当同一个ID断开与某个AssocID的关联后，又重新关联回去时，用DISTINCT (ID, AssocID)加MIN(Start)的方式会直接把两次关联合并成一条，彻底丢失了二次关联的时间分段信息。这本质是典型的连续相同分组问题，我们可以用SQL窗口函数完美解决这个问题，核心思路是先识别每个连续关联期的边界，再基于边界分组聚合。

具体实现思路 #

我们需要给每个连续的关联期打上唯一的分组标记，即使ID和AssocID与之前的某段完全相同，只要中间出现过切换，就会被标记为新的分组。步骤如下：

1. 用LAG()窗口函数获取每个ID的上一条记录的AssocID，判断当前记录是否属于新的关联期
2. 用累计求和的方式生成每个连续期的分组ID
3. 最后按ID、AssocID和分组ID聚合，提取起止时间

示例SQL代码 #

假设你的表名为id_assoc_history，包含字段ID、AssocID、RecordYear（因为每年一条记录，用年份作为时间标识）：

WITH period_markers AS (
    SELECT
        ID,
        AssocID,
        RecordYear,
        -- 标记当前行是否是新关联期的起点：如果上一条的AssocID和当前不同，就是新起点
        CASE 
            WHEN LAG(AssocID) OVER (PARTITION BY ID ORDER BY RecordYear) != AssocID 
            THEN 1 
            ELSE 0 
        END AS new_period_flag,
        -- 累计新起点标记，生成每个连续关联期的唯一分组ID
        SUM(
            CASE 
                WHEN LAG(AssocID) OVER (PARTITION BY ID ORDER BY RecordYear) != AssocID 
                THEN 1 
                ELSE 0 
            END
        ) OVER (PARTITION BY ID ORDER BY RecordYear) AS period_group_id
    FROM id_assoc_history
)
SELECT
    ID,
    AssocID,
    MIN(RecordYear) AS StartYear,
    MAX(RecordYear) AS EndYear
FROM period_markers
GROUP BY ID, AssocID, period_group_id
ORDER BY ID, StartYear;

代码解释 #

• LAG(AssocID) OVER (...)：按ID分组、年份排序，获取当前记录的上一条AssocID，用来判断关联关系是否发生变化
• new_period_flag：标记当前记录是否是新关联期的开始（关联关系变化时标记为1）
• period_group_id：对每个ID的新期标记做累计求和，这样同一个连续关联期的所有记录会拥有相同的分组ID，即使AssocID和之前的某段相同，分组ID也会不同，从而区分开两次关联
• 最后分组聚合时，通过period_group_id确保两次相同的关联不会被合并，完美保留分段信息

效果演示 #

比如原数据是：

运行上述SQL后会得到：

这样就完整保留了ID=1两次关联到AssocID=a的分段记录，不会被错误合并。如果你的时间字段是具体日期而非年份，只需要把RecordYear替换成对应的日期字段即可，逻辑完全一致。

内容的提问来源于stack exchange，提问作者I Am Root