SQL窗口函数如何计算每月新增与留存用户-SQL-PHP中文网

“每月新增用户”指每个用户在其生命周期中首次行为发生的月份，需先按用户粒度取min(event_time)或row_number()=1标记首次行为，再按该时间归入对应月份，不可重复计入后续月份。

sql窗口函数如何计算每月新增与留存用户

什么是“每月新增用户”的正确计算逻辑

新增用户不是简单按注册时间分月统计，而是指首次出现的用户。如果一个用户在 2024-01 注册，2024-03 又登录了一次，他只算作 2024-01 的新增，不能重复计入 2024-03。

关键在于：先用 MIN(event_date) 或 MIN(registration_time) 找出每个用户的首次行为时间，再按该时间归入对应月份。

必须基于用户粒度去重后再取最小时间，不能直接对原始日志表 GROUP BY user_id, DATE_TRUNC('month', event_time)
如果原始表没有注册时间字段，只能用首次登录/下单/访问时间替代，此时要明确业务定义：“新增”到底指注册还是首次活跃
注意时区问题：DATE_TRUNC('month', event_time AT TIME ZONE 'UTC') 和 AT TIME ZONE 'Asia/Shanghai' 结果可能差一天

用 `ROW_NUMBER()` 标记首次行为并过滤

这是最通用、兼容性最好的写法，适用于 PostgreSQL、MySQL 8.0+、SQL Server、BigQuery 等主流引擎：

SELECT 
  user_id,
  DATE_TRUNC('month', event_time) AS month_start
FROM (
  SELECT 
    user_id,
    event_time,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time) AS rn
  FROM events
  WHERE event_type = 'login'  -- 或 'register'
) t
WHERE rn = 1

ROW_NUMBER() 比 MIN() 更灵活：可加条件（如只看 App 登录、排除测试账号）、可结合多个时间字段（如优先用 register_time，为空则用 first_login_time）
不要写成 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time ASC) —— ASC 是默认值，冗余且部分方言不支持
如果数据量大，务必在 (user_id, event_time) 上建联合索引，否则 OVER 排序会很慢

留存率必须用“分母锚定”，不能用 `LAG()` 直接算

常见错误：用 LAG(user_id) OVER (PARTITION BY user_id ORDER BY month) 判断是否连续出现——这算的是“连续活跃率”，不是留存。

真正留存（比如次月留存）是：上月新增用户中，有多少人在本月又出现了。

正确做法是两步：

先生成“新增用户月表”（上一节结果）
再和全量行为表做左连接或存在性判断

WITH new_users AS (
  SELECT user_id, DATE_TRUNC('month', event_time) AS first_month
  FROM (...)
),
monthly_active AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_time) AS act_month
  FROM events
  WHERE event_type = 'login'
)
SELECT 
  nu.first_month,
  COUNT(DISTINCT nu.user_id) AS new_cnt,
  COUNT(DISTINCT ma.user_id) AS retained_cnt,
  ROUND(COUNT(DISTINCT ma.user_id)::FLOAT / NULLIF(COUNT(DISTINCT nu.user_id), 0), 3) AS retention_rate
FROM new_users nu
LEFT JOIN monthly_active ma 
  ON nu.user_id = ma.user_id 
  AND ma.act_month = nu.first_month + INTERVAL '1 month'
GROUP BY nu.first_month

NULLIF(COUNT(...), 0) 防止除零错误，比 CASE WHEN 更简洁
INTERVAL '1 month' 在不同数据库写法不同：PostgreSQL 支持，MySQL 要用 DATE_ADD(nu.first_month, INTERVAL 1 MONTH)，ClickHouse 用 addMonths(nu.first_month, 1)
留存计算本身不依赖窗口函数，强行套 LEAD() 或 LAG() 只会让逻辑变难懂、性能变差

为什么不用 `PERCENT_RANK()` 或 `NTILE()`？

这两个函数解决的是分位数、分桶问题，和新增/留存完全无关。

PERCENT_RANK() 返回某行在其分组内的相对排名（0~1），常用于“Top 10% 用户消费额”这类场景
NTILE(4) 把数据平均分成 4 组，适合做用户价值分层（高/中/低/流失风险），但无法告诉你“这个用户是不是上月新增的”

如果看到有人用它们算留存，基本是混淆了“用户分群”和“跨期行为追踪”两个维度。真正的难点从来不在函数选型，而在于：
你是否清楚地定义了“用户唯一标识”（是 device_id？还是 login_id？是否做了 ID 映射？）
以及“行为有效性”（一次埋点失败的静默登录，该不该计入？）
这些细节不厘清，再漂亮的窗口函数也输出垃圾结果。