lead函数需配合partition by user_id和order by order_time使用，否则跨用户取值错误；必须过滤无效订单（如order_status='paid'），并处理null值及日期计算兼容性问题。
LEAD函数怎么写才能拿到下一次复购时间
直接用 LEAD(order_time) 就行，但必须按用户分组、按时间升序排序，否则结果完全错乱。很多人漏掉 PARTITION BY user_id 和 ORDER BY order_time，导致跨用户取值，比如把张三的下次订单当成李四的。
典型写法：
SELECT user_id, order_time, LEAD(order_time) OVER (PARTITION BY user_id ORDER BY order_time) AS next_order_time FROM orders WHERE order_status = 'paid';
- 必须过滤掉无效订单（如取消、退款），否则
next_order_time可能指向一个作废记录 -
LEAD()默认取下一行（OFFSET = 1），不需要显式写，写成LEAD(order_time, 1)多余 - 如果某用户只有一次订单，
next_order_time是NULL，后续计算间隔时得用COALESCE或WHERE next_order_time IS NOT NULL过滤
算时间间隔用DATEDIFF还是直接减
MySQL 和 PostgreSQL 支持直接用 next_order_time - order_time 得到天数（结果是 interval 或 numeric），但 SQL Server 必须用 DATEDIFF(day, order_time, next_order_time)；SQLite 没原生日期减法，得转儒略日。别硬套一种写法。
更稳妥的通用做法（适配多数引擎）：
SELECT
user_id,
order_time,
next_order_time,
DATEDIFF('day', order_time, next_order_time) AS days_to_next
FROM (
SELECT
user_id,
order_time,
LEAD(order_time) OVER (PARTITION BY user_id ORDER BY order_time) AS next_order_time
FROM orders
WHERE order_status = 'paid'
) t
WHERE next_order_time IS NOT NULL;
- PostgreSQL 里
DATEDIFF不存在，得用next_order_time - order_time，结果单位是“天”（interval类型），再EXTRACT(DAY FROM ...) - BigQuery 用
DATE_DIFF(next_order_time, order_time, DAY)，参数顺序和 MySQL 相反 - 别用
UNIX_TIMESTAMP手动算秒数再除 86400——浮点误差和时区问题会悄悄污染结果
预测间隔不是简单取平均值
用户复购行为有强周期性或衰减性，直接对 days_to_next 取 AVG() 会掩盖关键分布。比如 80% 用户 7 天内复购，20% 却要 90 天，均值是 25 天，但实际业务中“7 天”才是有效信号。
- 先看分位数：
PERCENTILE_CONT(0.5)（中位数）比均值更能代表典型行为 - 按用户聚合后再统计：每个用户算自己历史平均间隔，再对这些用户级均值取中位数，避免高频用户刷高全局均值
- 加时间衰减权重：近 3 个月的
days_to_next权重更高，可用CASE WHEN next_order_time > DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY) THEN 2 ELSE 1 END做简易加权
容易被忽略的边界情况
真实数据里，order_time 字段经常不干净：存在重复下单（同一秒多个订单）、时区未归一（UTC 存储但业务按本地时区理解）、测试订单混入生产表。这些都会让 LEAD 输出错误的“下一次”。
- 去重必须做：
ROW_NUMBER() OVER (PARTITION BY user_id, order_time ORDER BY created_at)留第一个 - 所有时间字段统一转为
TIMESTAMP WITHOUT TIME ZONE（PostgreSQL）或CONVERT_TZ(..., '+00:00', @@session.time_zone)（MySQL）再排序 - 上线前用小样本验证：挑 3 个用户，手工查他们的订单时间线，和 SQL 输出逐行比对
next_order_time是否真为“下一个有效订单”
LEAD 本身很稳，出问题几乎全在数据质量或窗口定义上。别急着建模型，先盯住那几行 PARTITION BY 和 WHERE 条件。
