复购定义是同一用户在不同日期的有效购买行为，而非单纯订单次数；必须先按user_id+DATE(created_at)去重，再统计购买天数，COUNT(DISTINCT DATE(created_at))≥2才视为复购，且需过滤退款、取消等无效订单。
复购定义必须先明确
复购不是“买了两次”，而是同一用户在不同时间完成的多次有效购买。关键在于排除同一订单拆单、同一天多笔支付等干扰。实践中建议以 user_id + order_date（或 created_at 截断到日）为唯一购买行为单位。
常见错误是直接对 user_id 计数，结果把当天下的 3 单全算作 3 次复购——这其实是首购行为的重复，不是复购。
- 正确做法：先按
user_id和日期去重，再统计每人有多少个不同购买日 - 若业务要求“间隔 ≥7 天才算复购”，就得用窗口函数查上一笔订单日期，不能只看去重天数
- 注意
NULL订单状态（如已取消）必须提前过滤，否则会虚增购买日
用 GROUP BY + COUNT(DISTINCT) 最简实现
适用于“只要买过 ≥2 天就算复购，不关心间隔”的场景。这是 80% 的运营报表需求。
SELECT user_id, COUNT(DISTINCT DATE(created_at)) AS purchase_days FROM orders WHERE status = 'paid' -- 只算成功支付 GROUP BY user_id HAVING COUNT(DISTINCT DATE(created_at)) >= 2;
这里 COUNT(DISTINCT DATE(created_at)) 是核心：它数的是每人有几个不同的购买日。HAVING 过滤出至少有两天购买记录的用户。
-
DATE(created_at)在 MySQL/PostgreSQL 中可用；SQLite 用DATE(created_at)，SQL Server 用CAST(created_at AS DATE) - 别在 WHERE 里写
DATE(created_at) >= '2024-01-01'—— 这会让索引失效，应改用created_at >= '2024-01-01' - 如果表没建
(user_id, created_at)联合索引，这个查询可能变慢，尤其数据量超百万后
要算“第几次复购”就得用窗口函数
当需要知道某用户“第二次购买是什么时候”“第三次购买金额多少”，就必须给每次购买行为按时间排序。这时 ROW_NUMBER() 或 RANK() 是刚需。
WITH user_orders AS (
SELECT user_id,
DATE(created_at) AS buy_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
FROM orders
WHERE status = 'paid'
)
SELECT user_id, buy_date, rn
FROM user_orders
WHERE rn >= 2; -- 所有复购行为（第2次及以后）注意 ROW_NUMBER() 和 RANK() 的区别：ROW_NUMBER() 绝对连续编号（哪怕同一天两单也编成 1、2），RANK() 会并列（同一天两单都编 1，下一单编 3）。多数复购分析用 ROW_NUMBER() 更合理。
- PostgreSQL/MySQL 8.0+/SQL Server 2017+ 支持标准窗口函数；旧版 MySQL 必须用变量模拟，极易出错
- ORDER BY 里必须包含能区分先后的字段，比如
created_at，不能只靠id（自增 ID 不一定等于下单顺序） - 如果要算“复购间隔天数”，就在 CTE 里加
LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at)
漏掉退款订单会导致复购率虚高
很多团队只过滤 status = 'paid'，但没处理后续退款。用户 A 1月1日下单付款，1月5日全额退款，这笔订单就不该计入任何购买日。
- 理想方案是维护一张
order_status_log表，取每个订单最终状态，而非原始订单表的快照状态 - 简单方案是在子查询中排除所有
refund_amount > 0或final_status = 'refunded'的订单 - 特别注意：有些系统把“部分退款”记为新订单号，这种需关联原订单 ID 才能识别，否则会漏判
复购统计真正的难点不在 SQL 写法，而在业务状态定义是否闭环。一个没对齐的“已支付”状态，能让整个复购率指标失去参考价值。
