要获取相邻订单时间差，LAG()得配合PARTITION BY和ORDER BY一起用。至于时间差的计算，MySQL用TIMESTAMPDIFF()，PostgreSQL则用EXTRACT(EPOCH FROM ...)。对于首单的空值处理，COALESCE或NULLIF都是不错的选择。这里要特别注意，order_time字段务必建索引，不然可就容易出现文件排序的问题了。

用 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 明确标识，而不是让下游误判：

• 设为 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) 截断，既能减少排序比较开销，也避免因毫秒差异导致同分钟内订单被误判为“非相邻”。

《夸克》非常好用的免费AI浏览器

下载APP查看