怎么用SQL嵌套查询计算复购率和新用户留存率-SQL-PHP中文网

复购率需用嵌套查询计算：分子为至少购买2次的用户数，分母为全部付费用户数，推荐left join子查询方式并用nullif防除零；留存率须先通过子查询确定新用户首单日期，再关联判断n日内复购，注意时间字段口径、id统一及时区一致。

怎么用sql嵌套查询计算复购率和新用户留存率

复购率：用子查询统计重复购买用户占比

复购率本质是「至少买过 2 次的用户数 / 所有付费用户数」。直接在 GROUP BY user_id 后加 HAVING COUNT(*) >= 2 只能筛出复购用户，但分母需要全量用户——必须用嵌套查询分离分子和分母逻辑。

常见错误是写成单层 COUNT(DISTINCT CASE WHEN cnt >= 2 THEN user_id END) / COUNT(DISTINCT user_id)，这在 MySQL 8.0 之前不支持窗口函数时会报错，且无法处理订单时间跨月等场景。

推荐写法（兼容 MySQL/PostgreSQL）：

SELECT 
  ROUND(
    COUNT(DISTINCT t2.user_id) * 1.0 / NULLIF(COUNT(DISTINCT t1.user_id), 0), 4
  ) AS repurchase_rate
FROM orders t1
LEFT JOIN (
  SELECT user_id
  FROM orders
  GROUP BY user_id
  HAVING COUNT(*) >= 2
) t2 ON t1.user_id = t2.user_id;

关键点：

NULLIF(..., 0) 防止分母为 0 导致除零错误
外层用 t1 表确保分母是全部下单用户（不是去重后的复购用户）
若只算「指定时间段内」的复购率，两个 orders 表都要加 WHERE order_time BETWEEN ...，且子查询里不能漏掉时间过滤，否则会把历史老订单也计入

新用户留存率：用日期差 + 关联子查询定位首单用户

新用户留存率 = 「第 N 天还下单的新用户数」/「首单发生在 D 日的新用户数」。难点在于：必须先识别出每个用户的首单日期，再判断他们是否在 D+N 日再次下单。

错误做法是直接 WHERE order_time = MIN(order_time) OVER (PARTITION BY user_id) —— 这在 WHERE 子句里不合法，且无法用于关联后续行为。

正确思路：用子查询先生成「新用户首单表」，再 LEFT JOIN 原表查其 N 日后行为：

QMuse

QMuse是一款AI开发辅助工具，蚂蚁集团推出的AI无代码应用生成平台。

下载

SELECT 
  ROUND(
    COUNT(DISTINCT t2.user_id) * 1.0 / NULLIF(COUNT(DISTINCT t1.user_id), 0), 4
  ) AS retention_7d
FROM (
  -- 所有新用户首单（按自然日计算，非滚动）
  SELECT user_id, MIN(order_date) AS first_order_date
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY user_id
) t1
LEFT JOIN orders t2 
  ON t1.user_id = t2.user_id 
  AND t2.order_date = t1.first_order_date + INTERVAL '7' DAY
WHERE t1.first_order_date = '2024-01-01';

注意：

PostgreSQL 用 INTERVAL '7' DAY，MySQL 用 DATE_ADD(t1.first_order_date, INTERVAL 7 DAY)
如果要算「7日内任意一天再次下单」，把 t2.order_date = ... 改成 t2.order_date BETWEEN t1.first_order_date + INTERVAL '1' DAY AND t1.first_order_date + INTERVAL '7' DAY
首单日期必须限定范围（如 WHERE order_date >= '2024-01-01'），否则子查询可能拉取全表，性能崩盘