SQL同表分组比较相邻行日期字段筛选记录实现方法咨询

不需要使用游标或循环。这类同分组相邻行比对的场景是窗口函数的典型适用场景，用集合化的SQL逻辑就能实现，性能远高于逐行遍历的游标/循环写法。

实现逻辑说明 #

你原有的自连接+行号的思路已经接近正确，只需要补充相邻行字段取值的逻辑即可：

• 先按AdviserBusinessId、ClientContactGuid分组，按指定规则（PlanEndDate升序）排序打行号
• 用窗口函数直接取同分组内相邻行的字段值，不需要额外自连接或者遍历：
  • LEAD(PlanStartDate)：取当前行后面相邻行的PlanStartDate，用来判断当前行是否和后一行时间重叠
  • LAG(PlanEndDate)：取当前行前面相邻行的PlanEndDate，用来判断当前行是否和前一行时间重叠
• 先过滤掉两个字段组合只出现1次的分组，再筛选出存在时间重叠的相邻行对即可

可直接运行的参考SQL #

WITH base_ranked AS (
    SELECT
        AdviserBusinessId,
        ClientContactGuid,
        PlanStartDate,
        PlanEndDate,
        ClientEngagementGuid,
        RenewalGuid,
        ROW_NUMBER() OVER (
            PARTITION BY AdviserBusinessId, ClientContactGuid
            ORDER BY PlanEndDate ASC
        ) AS rownum,
        LEAD(PlanStartDate) OVER (
            PARTITION BY AdviserBusinessId, ClientContactGuid
            ORDER BY PlanEndDate ASC
        ) AS next_start_date,
        LAG(PlanEndDate) OVER (
            PARTITION BY AdviserBusinessId, ClientContactGuid
            ORDER BY PlanEndDate ASC
        ) AS prev_end_date
    FROM GENIUS_ClientEngagement
)
SELECT
    AdviserBusinessId,
    ClientContactGuid,
    PlanStartDate,
    PlanEndDate,
    ClientEngagementGuid,
    RenewalGuid,
    rownum
FROM base_ranked
WHERE
    -- 筛出AdviserBusinessId+ClientContactGuid组合出现次数大于1的分组
    EXISTS (
        SELECT 1
        FROM base_ranked t2
        WHERE t2.AdviserBusinessId = base_ranked.AdviserBusinessId
          AND t2.ClientContactGuid = base_ranked.ClientContactGuid
          AND t2.rownum = 2
    )
    -- 筛出所有符合时间重叠规则的行：要么当前行和下一行重叠，要么和上一行重叠
    AND (
        PlanEndDate > next_start_date
        OR prev_end_date > PlanStartDate
    )
ORDER BY AdviserBusinessId, ClientContactGuid, rownum;

补充说明 #

• 如果分组内存在3条及以上的连续时间重叠记录，该写法可以自动识别所有符合条件的相邻行对，不需要额外处理
• 如果排序规则有调整，只需要同步修改三个窗口函数内的ORDER BY子句即可，核心逻辑不需要改动
• 所有主流SQL引擎（SQL Server、MySQL 8.0+、PostgreSQL、BigQuery等）都支持上述用到的窗口函数语法，不需要额外兼容处理

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