不能靠GROUP BY起止时间直接合并连续时间段，必须用窗口函数构造动态分组ID；因为GROUP BY仅按字段值静态分组，无法判断“当前start_time ≤ 上一行end_time”的动态重叠关系，对[1,5]、[4,8]、[7,10]等重叠区间完全无效。
不能靠 GROUP BY 起止时间直接合并连续时间段，必须用窗口函数构造动态分组 ID。
为什么 GROUP BY start_time, end_time 会失败
连续时间段的合并不是静态分类，而是依赖前后行关系：当前记录的 start_time 是否 ≤ 上一行的 end_time。GROUP BY 没有“上一行”概念，它只看当前行字段值是否相同。用 GROUP BY start_time, end_time 只会把完全相同的起止时间归为一组，对 [1,5]、[4,8]、[7,10] 这类重叠或相接区间毫无作用。
常见错误还包括：
- 用
FLOOR((start_time + end_time)/2)做分组依据 —— 数值近似无业务含义，[1,3] 和 [2,4] 算出来都是 2.5，但实际不重叠 - 忽略 NULL 时间字段 ——
LAG(end_time)返回 NULL 后，start_time 判定结果为 UNKNOWN，整行被跳过 - 未显式排序就调用 LAG() —— 窗口函数按物理顺序执行，没
ORDER BY就等于随机扫描
用 LAG() + SUM() OVER() 构造分组 ID
核心是两步：先标记“断点”，再累计生成组号。断点定义为 start_time > LAG(end_time)，即当前区间和前一区间不重叠也不相接。
关键写法（以 PostgreSQL/MySQL 8.0+/SQL Server 为例）：
SELECT
user_id,
MIN(start_time) AS merged_start,
MAX(end_time) AS merged_end,
COUNT(*) AS segment_length
FROM (
SELECT *,
SUM(is_new_group) OVER (ORDER BY start_time, end_time ROWS UNBOUNDED PRECEDING) AS group_id
FROM (
SELECT *,
CASE WHEN start_time > LAG(end_time, 1, '-infinity'::TIMESTAMP) OVER (ORDER BY start_time, end_time)
THEN 1 ELSE 0 END AS is_new_group
FROM events
WHERE start_time IS NOT NULL AND end_time IS NOT NULL
) t1
) t2
GROUP BY group_id;
注意点：
-
LAG(end_time, 1, '-infinity')中的默认值必须足够小（如负无穷、-999999），否则首行比较会出错 -
ORDER BY start_time, end_time必须严格存在，且end_time ASC不可省略 —— 多个相同start_time时，取最小end_time会导致后续误判为断点 - 业务若定义 [1,5] 和 [5,8] 为“相接即合并”，则条件写
start_time > LAG(end_time)；若要求严格重叠（> 而非 ≥），则改为start_time > LAG(end_time) + INTERVAL '1 day'类推
聚合后如何保留原始字段（如 ID 列）
直接 GROUP BY group_id 会丢失原始行级信息。若需保留每段内的所有 ID 或其他标识，不能只用 MIN/MAX。
各数据库处理方式不同：
- PostgreSQL：用
ARRAY_AGG(id)或STRING_AGG(id::TEXT, ',') - MySQL 8.0+：用
GROUP_CONCAT(id)，注意长度限制（group_concat_max_len） - SQL Server：用
STRING_AGG(id, ',')（2017+）或FOR XML PATH（旧版）
如果字段含 NULL，GROUP_CONCAT 默认跳过，STRING_AGG 默认也跳过 —— 需提前用 COALESCE(id, 'NULL') 统一处理。
容易被忽略的边界细节
真正卡住人的往往不是逻辑，而是数据质量与引擎行为差异：
- 时间精度不一致：比如
start_time是TIMESTAMP，end_time是DATE，比较时隐式转换可能出错 - 时区混用：跨时区数据未统一转为 UTC 就排序，
LAG()取到的可能是“昨天”的end_time - SQLite 不支持窗口函数：必须改用自连接或应用层处理，无法套用这套方案
- 大表性能：
ORDER BY start_time, end_time若无联合索引，排序成本极高 —— 建议在 (start_time,end_time) 上建索引
