典型错误是误用GROUP BY status识别连续状态，它会将非相邻的相同状态强行合并；正确做法是用LAG()标记状态变化点，再用SUM()累计生成段ID，最后按段ID分组统计。
什么是“连续相同状态”的典型错误理解
很多人一上来就用 GROUP BY status，结果发现时间断开的同状态记录被强行合并了——比如 status='online' 出现在第1–3行、第7–9行，GROUP BY 会把它们全算作一个组，完全丢失“连续性”这个关键约束。
真正要识别的是：相邻行（按时间/序号排序后）状态未变化的片段。这本质是“分组识别变化点”，不是普通聚合。
用窗口函数标记连续段的起始位置
核心思路是：对每行判断「当前状态是否和上一行不同」，若不同，则标记为新段起点；再对所有起点做累加，生成唯一段ID。
SELECT
ts,
status,
SUM(is_new_group) OVER (ORDER BY ts) AS grp_id
FROM (
SELECT
ts,
status,
CASE
WHEN LAG(status) OVER (ORDER BY ts) != status THEN 1
ELSE 0
END AS is_new_group
FROM events
) t
-
LAG(status) OVER (ORDER BY ts)必须指定明确排序字段（如时间戳ts或自增ID），否则“连续”无意义 -
!=比较需注意 NULL：如果首行LAG()返回 NULL，NULL != 'online'结果是 UNKNOWN，会导致is_new_group为 0 —— 正确做法是用IS DISTINCT FROM（PostgreSQL）或显式处理 NULL（MySQL/SQL Server 需IFNULL(LAG(...), '_nevermatch')） -
SUM() OVER (ORDER BY ...)是累计求和，不是聚合函数，它给每个连续段分配稳定递增的grp_id
基于段ID完成区间统计
有了 grp_id，后续聚合就简单了：
SELECT MIN(ts) AS start_time, MAX(ts) AS end_time, status, COUNT(*) AS duration FROM ( -- 上面那段子查询 ) grouped GROUP BY grp_id, status ORDER BY start_time;
-
GROUP BY grp_id, status中的status其实冗余（每个grp_id内状态必然一致），但加上更安全，防止逻辑错位 - 如果原始数据有毫秒级时间戳，而你只想按秒对齐区间，记得先用
DATE_TRUNC('second', ts)（PostgreSQL）或CONVERT(DATETIME, FLOOR(CAST(ts AS FLOAT)))（SQL Server）预处理，否则微小时间差会被误判为断点 - 性能敏感时，确保
ORDER BY ts字段有索引；LAG()和SUM() OVER都依赖排序稳定性，没索引可能触发临时排序，拖慢数倍
MySQL 8.0+ 和旧版兼容写法差异
MySQL 5.7 不支持窗口函数，只能用变量模拟：
SELECT
MIN(ts) AS start_time,
MAX(ts) AS end_time,
status,
COUNT(*) AS duration
FROM (
SELECT
ts,
status,
@grp := IF(@prev_status != status, @grp + 1, @grp) AS grp_id,
@prev_status := status
FROM events
CROSS JOIN (SELECT @grp := 0, @prev_status := '') AS _
ORDER BY ts
) t
GROUP BY grp_id, status;
- 变量赋值顺序很关键：
@prev_status := status必须放在SELECT列尾，否则@prev_status在本行比较时还是上一行的值 -
ORDER BY ts绝对不能省，且必须在变量计算前生效，否则分组全乱 - 这种写法在 MySQL 8.0+ 仍可用，但不如窗口函数可读、可控；某些高并发场景下变量行为不稳定，生产环境优先选窗口函数
连续区间的难点不在语法，而在排序依据是否真实反映业务顺序、状态变化是否允许忽略中间 NULL、以及跨数据库时 NULL 比较行为的隐式差异——这些地方一错，整个区间就偏移了。
