如何用SQL窗口函数合并连续状态记录？-SQL-PHP中文网

“连续状态”需先识别：SQL无原生连续语义，必须用LAG(status)找状态变化点，再用SUM() OVER构造分组标识；关键依赖严格有序字段（如id或event_time），并显式处理NULL和边界情况。

如何用sql窗口函数合并连续状态记录？

什么是“连续状态”需要先识别出来 SQL 本身不自带“连续性”语义，`ROW_NUMBER()`、`LAG()`、`GROUPS` 这些函数不会自动感知“连续”。必须靠时间戳或序号字段（如 `id`、`created_at`）+ 状态字段（如 `status`）联合判断：同一状态在物理顺序上是否相邻。关键思路是：用 `LAG(status)` 找出状态变化点，再用 `SUM()` 窗口函数打组。不是所有数据库都支持 `GROUPS` 模式（PostgreSQL 14+、Oracle），所以更通用的做法是构造分组标识。

实操建议：

确保有严格递增/有序的排序字段（比如 id 或 event_time），否则“连续”无意义
先用 LAG(status) OVER (ORDER BY id) 判断当前行与上一行状态是否不同
用 CASE WHEN status != LAG(status) THEN 1 ELSE 0 END 标记变化点
再对这个标记做 SUM() OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) 得到每个连续段的唯一组号

PostgreSQL / MySQL 8.0+ 的典型写法以订单状态流转表为例，字段为 id、order_id、status、updated_at：

SELECT
  order_id,
  status,
  MIN(updated_at) AS start_time,
  MAX(updated_at) AS end_time,
  COUNT(*) AS duration_count
FROM (
  SELECT *,
    SUM(is_new_group) OVER (ORDER BY id ROWS UNBOUNDED PRECEDING) AS grp
  FROM (
    SELECT *,
      CASE 
        WHEN status != LAG(status) OVER (ORDER BY id) THEN 1 
        ELSE 0 
      END AS is_new_group
    FROM order_status_log
  ) t1
) t2
GROUP BY order_id, status, grp;

注意点：

LAG() 默认返回 NULL，第一行会误判为“状态变化”，所以实际中常加 COALESCE(LAG(status), '___') 避免歧义
MySQL 5.7 不支持窗口函数，这种写法直接报错 ERROR 1064；必须升级或改用自连接模拟
如果 id 不连续（比如有删行），但 updated_at 是严格递增的，就把 ORDER BY id 换成 ORDER BY updated_at, id 防止时序错乱

遇到 NULL 状态或中间插入记录怎么办连续性判断对空值极其敏感：`LAG(status)` 返回 `NULL`，而 `NULL != 'shipped'` 为 `UNKNOWN`（不是 `TRUE`），导致 `CASE` 不触发。结果就是：含 `NULL` 的行永远被归进前一个组，或单独成组，取决于写法。

安全写法是显式处理 NULL：

把状态转成非空占位符：COALESCE(status, '__MISSING__')
比较时用 IS DISTINCT FROM（PostgreSQL）或 (a IS NULL) != (b IS NULL) OR a != b（兼容写法）
如果业务允许跳过 NULL 记录，可提前 WHERE status IS NOT NULL 过滤，但要注意这会破坏原始时间线