LAG()是计算复购间隔最直接的方法,需PARTITION BY user_id和ORDER BY created_at确保同用户按时间序取上笔订单;必须用TIMESTAMPDIFF()而非减法,并注意参数顺序、单位选择及NULL处理。

用 LAG() 按用户分组排序取上一笔订单时间
复购间隔本质是同一用户两次下单的时间差,核心在于“找到前一次下单时间”。LAG() 是最直接的解法:它能在窗口内向前偏移指定行数,天然适配“当前订单减去上一笔订单”的逻辑。注意必须显式指定 ORDER BY created_at,否则顺序不可控;同时 PARTITION BY user_id 是强制项,漏掉就会跨用户错乱。
常见错误是写成 LAG(created_at) 却没加 OVER 子句,报错 Window function requires OVER clause;或者忘记 PARTITION BY,导致 A 用户的订单减了 B 用户的订单时间,结果全错。
示例片段:
SELECT order_id, user_id, created_at, LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_time FROM orders;
计算间隔时优先用 TIMESTAMPDIFF() 而非减法
MySQL 中直接用 created_at - prev_order_time 会得到一个奇怪的整数(如 20240315103000 - 20240310092000 = 5011000),这不是天数也不是秒数,毫无业务意义。必须用 TIMESTAMPDIFF() 显式指定单位。
单位选 DAY 最常用,但要注意:如果需要精确到小时或分钟复购(比如判断 2 小时内加购又下单),就得改用 HOUR 或 MINUTE;另外该函数返回的是整数截断值(TIMESTAMPDIFF(DAY, '2024-03-10 23:59:59', '2024-03-11 00:00:01') 返回 0),不四舍五入。
关键参数顺序别颠倒:TIMESTAMPDIFF(unit, earlier_time, later_time),反着写结果是负数,容易误判为“时间倒流”。
过滤首单、处理 NULL 和空间隔
LAG() 对每个用户的首笔订单返回 NULL,这是正常现象,但直接参与减法会导致整行结果为 NULL。不能靠 WHERE prev_order_time IS NOT NULL 粗暴过滤——这会丢掉所有首单用户的数据,而你可能想标记“这是新客”。
更稳妥的做法是用 COALESCE() 或条件表达式:
- 标首次购买:
CASE WHEN prev_order_time IS NULL THEN 'first_order' ELSE 'rebuy' END - 设默认间隔(如 9999 天表示无前序):
COALESCE(TIMESTAMPDIFF(DAY, prev_order_time, created_at), 9999) - 排除测试数据干扰:加
WHERE status = 'paid',避免把取消单、测试单计入复购链路
大表跑不动?给 (user_id, created_at) 加联合索引
当订单表超千万行,PARTITION BY user_id ORDER BY created_at 的窗口计算会变慢,尤其在没索引时 MySQL 要全表扫描再排序。这时联合索引能显著提速——它让每个 user_id 下的 created_at 天然有序,窗口函数可流式读取。
建索引语句:CREATE INDEX idx_user_created ON orders(user_id, created_at);。注意顺序不能反,(created_at, user_id) 对此查询无效。
另一个隐藏坑:如果 created_at 有大量 NULL 值,索引效率会下降,建议清洗或补默认值(如 created_at DEFAULT CURRENT_TIMESTAMP)。
真正卡住的往往不是语法,而是没意识到窗口函数背后依赖物理排序——索引不是可选项,是必要前提。

















