怎样用SQL窗口函数计算相邻订单时间差-SQL-PHP中文网

lag()需配合partition by和order by获取相邻订单时间差，mysql用timestampdiff()、postgresql用extract(epoch from ...)计算，首单用coalesce或nullif处理空值，order_time字段必须建索引以避免文件排序。

怎样用sql窗口函数计算相邻订单时间差

用 `LAG()` 获取上一笔订单时间

窗口函数计算相邻订单时间差，核心是拿到当前行和上一行的时间字段。最直接的方式是用 LAG()，它能按指定排序取前 N 行的值。注意必须配合 ORDER BY 使用，否则结果不可靠——比如按 order_id 排序不等于按真实下单时间排序，得用 created_at 或 order_time。

常见错误：漏写 PARTITION BY 导致跨用户混算。比如用户 A 的最后一单和用户 B 的第一单被当成“相邻”，时间差毫无业务意义。实际中多数要按用户分组：

SELECT
  user_id,
  order_time,
  LAG(order_time) OVER (PARTITION BY user_id ORDER BY order_time) AS prev_order_time
FROM orders;

`TIMESTAMPDIFF()` 或减法算时间差（MySQL / PostgreSQL 差异）

拿到当前时间和前序时间后，差值计算方式因数据库而异。MySQL 常用 TIMESTAMPDIFF() 指定单位（秒、分钟、小时），避免直接相减出小数天：

TIMESTAMPDIFF(SECOND, prev_order_time, order_time) → 返回整型秒数
直接 order_time - prev_order_time 在 PostgreSQL 中返回 interval 类型，需 EXTRACT(EPOCH FROM ...) 转秒
SQL Server 用 DATEDIFF(second, prev_order_time, order_time)

别用 ROUND(UNIX_TIMESTAMP(...) - UNIX_TIMESTAMP(...))，时区处理易出错，且 MySQL 8.0+ 已倾向用 TIMESTAMPDIFF。

处理首单或空值：用 `COALESCE()` 设默认值

LAG() 对每个分组第一行返回 NULL，直接参与减法会把整行结果变 NULL。业务上通常希望首单时间差为 0 或 NULL 明确标识，而不是让下游误判：

Writecream

一款集AI写作、营销文案和内容创作于一体的在线工具，可辅助生成文章、广告、邮件等多种类型的文本内容。

下载

设为 0：COALESCE(TIMESTAMPDIFF(SECOND, LAG(order_time) OVER (...), order_time), 0)
保持空（更推荐）：NULLIF(TIMESTAMPDIFF(...), 0) 配合 LAG 原始 NULL，能区分“无前序订单”和“间隔为 0 秒”的异常情况

注意 COALESCE 不能提前包裹 LAG，否则会掩盖真实的空值逻辑——比如把 LAG 返回的 NULL 强制转成某个时间点再相减，差值就失真了。

性能提醒：ORDER BY 字段必须有索引

窗口函数的 ORDER BY 不是装饰，它决定数据扫描顺序。如果 order_time 没索引，大表上执行会触发文件排序（Using filesort），速度断崖式下降。尤其当加了 PARTITION BY user_id 后，复合索引 (user_id, order_time) 是刚需。

验证方式：用 EXPLAIN 看执行计划，确认 key 列命中索引，且 Extra 不含 Using temporary; Using filesort。没索引时，10 万行可能从毫秒级拖到秒级。

真正麻烦的是时间字段精度——如果存的是 DATETIME(6)（微秒级），但业务只关心分钟级间隔，提前用 DATE_SUB(order_time, INTERVAL SECOND(order_time) SECOND) 截断，既能减少排序比较开销，也避免因毫秒差异导致同分钟内订单被误判为“非相邻”。