SQL中如何使用GROUP BY合并重叠的时间区段？-SQL-PHP中文网

group by不能直接合并重叠时间区间，因其按字段值等价分组，而重叠是区间相交逻辑（如start_a = start_b）；正确解法是先按account_id和start_date排序，用lag()获取上一行end_date，再通过sum(case when start_date > prev_end or prev_end is null then 1 else 0 end) over (...)生成分组id，最后按account_id和grp_id聚合取min(start_date)与max(end_date)。

sql中如何使用group by合并重叠的时间区段？

GROUP BY 本身不能直接合并重叠时间区间，因为它只按字段值相等分组，而重叠是逻辑关系（比如 start_a = start_b），不是值相等。强行用 GROUP BY start_date, end_date 或 DATE_TRUNC 会把真正重叠但端点不同的记录拆开，结果错误。

`LAG()` + 累计求和生成分组 ID 是最常用且兼容性好的解法

核心在于：先排序，再判断“当前区间是否该开启新组”，用 SUM() 窗口函数累计标记。

必须先按 account_id（或 employee_id）分组，并按 start_date 升序排列
用 LAG(end_date) 拿到上一行的结束时间
当前 start_date > LAG(end_date)，说明不重叠 → 新组起点
用 SUM(CASE WHEN start_date > LAG(end_date) THEN 1 ELSE 0 END) OVER (...) 累计生成 grp_id

WITH sorted AS (
  SELECT account_id, start_date, end_date,
    LAG(end_date) OVER (PARTITION BY account_id ORDER BY start_date) AS prev_end
  FROM intervals
),
grouped AS (
  SELECT *,
    SUM(CASE WHEN start_date > prev_end OR prev_end IS NULL THEN 1 ELSE 0 END)
      OVER (PARTITION BY account_id ORDER BY start_date) AS grp_id
  FROM sorted
)
SELECT account_id, MIN(start_date) AS start_date, MAX(end_date) AS end_date
FROM grouped
GROUP BY account_id, grp_id;

注意点：

prev_end IS NULL 要显式包含，否则第一行会被漏判为非新组
MySQL 8.0+、PostgreSQL、SQL Server 都支持；Oracle 需用 LAG(..., 1, NULL) 显式设默认值
如果区间是左闭右开（如 [2023-01-01, 2023-01-05)），比较时应写成 start_date >= prev_end 才算衔接不重叠

自连接判断重叠再聚合适合小数据或需精确连通性分析

当重叠关系不是线性可贪心合并（比如存在环状重叠：A∩B、B∩C、C∩A，但 A 不直接交 C），LAG 会失效，必须建连通分量。

用自连接找出所有两两重叠对：t1.start_date = t2.start_date
加 t1.id != t2.id 排除自匹配
再用递归 CTE 或多次迭代聚合收敛出每个连通块的最小开始和最大结束

常见坑：

没加 t1.id != t2.id 会导致每行都和自己匹配，COUNT(*) 全 +1
缺少 PARTITION BY account_id 条件，会让不同账户的区间错误连通
全表自连接在万级数据上性能极差，务必给 (start_date, end_date) 建复合索引

PostgreSQL 的 `tsrange` 是唯一能绕过手写逻辑的方案

如果你用的是 PostgreSQL 9.2+，原生区间类型配合 && 操作符和 GIST 索引，能把重叠判断压缩成一行，还能加速。

SELECT account_id,
  LOWER(merged_range) AS start_date,
  UPPER(merged_range) AS end_date
FROM (
  SELECT account_id,
    range_agg(tsrange(start_date, end_date, '[)')) AS merged_range
  FROM intervals
  GROUP BY account_id
) t;

前提是：