SQL查询连续日期块对应的最小起始日期与最大结束日期

问题排查 #

你的SQL没有得到预期结果主要有以下几个问题：

• 字段名不匹配：外层查询引用了ID字段，但子查询中输出的是MEMBER_ID，会直接触发语法错误。
• 窗口帧逻辑缺陷：仅按物理行取前序最大结束日期，没有正确处理重叠、包含类的日期区间，同时分区后排序重复写MEMBER_ID属于冗余逻辑。
• 分组标记逻辑不稳定：用COUNT(IS_GAP)做分组标记时，空值处理规则在不同数据库可能有差异，容易出现分组错位。

修正后SQL #

以下SQL支持所有支持标准窗口函数的数据库（MySQL 8.0+/PostgreSQL/Hive/Spark SQL等）：

SELECT 
  MEMBER_ID AS Id,
  MIN(STARTDATE) AS Startdate,
  MAX(ENDDATE) AS Enddate
FROM (
  SELECT 
    MEMBER_ID,
    STARTDATE,
    ENDDATE,
    -- 累计间隙标记，同一连续区间的行标记值相同
    SUM(IS_GAP) OVER (PARTITION BY MEMBER_ID ORDER BY STARTDATE, ENDDATE) AS RANGE_ID
  FROM (
    SELECT 
      MEMBER_ID,
      STARTDATE,
      ENDDATE,
      -- 当前行起始日期大于之前所有行的最大结束日期时，标记为新分组起点
      CASE 
        WHEN STARTDATE > MAX(ENDDATE) OVER (
          PARTITION BY MEMBER_ID 
          ORDER BY STARTDATE, ENDDATE 
          ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        ) THEN 1 
        ELSE 0 
      END AS IS_GAP
    FROM 你的表名 T
  ) T1
) T2
GROUP BY MEMBER_ID, RANGE_ID
ORDER BY MEMBER_ID, Startdate;

逻辑说明 #

1. 最内层子查询先逐行判断是否为新连续区间的起点：如果当前行的起始日期大于该用户所有历史记录的最大结束日期，说明出现了日期断层，记为1，否则记为0。
2. 中间层对间隙标记做累加求和，同一个连续日期区间的所有行会得到相同的RANGE_ID。
3. 最外层按用户ID和RANGE_ID分组，取每组最小起始日期和最大结束日期，即可得到你需要的连续区间合并结果。

代入你提供的样例数据执行，输出和你给出的预期结果完全一致。

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