如何通过分组和自连接统计每月新增用户数？

本文共计935个文字，预计阅读时间需要4分钟。

新增用户数本质是每个用户在全库中最早出现的月份，而不是简单按注册时间分组。如果直接使用以下代码：

正确做法是先算出每个用户的首次行为时间（比如第一次登录、第一次下单、第一次注册），再按该时间归月统计。假设用户表为 users，注册时间为 created_at：

SELECT DATE_FORMAT(MIN(created_at), '%Y-%m') AS month, COUNT(*) AS new_users FROM users GROUP BY user_id

但这只是中间结果，还需再套一层统计每月人数。更直接的写法是：

SELECT DATE_FORMAT(first_seen, '%Y-%m') AS month, COUNT(*) AS new_users FROM ( SELECT user_id, MIN(created_at) AS first_seen FROM users GROUP BY user_id ) t GROUP BY DATE_FORMAT(first_seen, '%Y-%m') ORDER BY month;

为什么不能用自连接来算新增用户

自连接（比如 users u1 LEFT JOIN users u2 ON u1.user_id = u2.user_id AND u1.created_at > u2.created_at）理论上能找出“不存在更早记录”的用户，但实际极不推荐——它会产生大量冗余笛卡尔积，数据量稍大（比如 10 万用户）就会卡死或 OOM。

常见错误现象：Query execution was interrupted, max_execution_time exceeded 或 MySQL 直接拒绝执行。

除非你明确限制了小范围数据（如最近 7 天 + 索引覆盖完整），否则应避免以下模式：

SELECT DATE_FORMAT(u1.created_at, '%Y-%m') AS month, COUNT(*) AS new_users FROM users u1 WHERE NOT EXISTS ( SELECT 1 FROM users u2 WHERE u2.user_id = u1.user_id AND u2.created_at < u1.created_at ) GROUP BY month;

这个写法逻辑正确，但性能差、不可扩展，线上环境慎用。

日期函数在不同数据库中的写法差异

DATE_FORMAT() 是 MySQL 特有；PostgreSQL 要用 TO_CHAR(created_at, 'YYYY-MM')；SQLite 用 STRFTIME('%Y-%m', created_at)；SQL Server 则是 FORMAT(created_at, 'yyyy-MM') 或 CONVERT(CHAR(7), created_at, 120)。

关键点在于：必须确保用于分组的字段是「确定性且可索引表达」的。例如在 MySQL 中，DATE_FORMAT(created_at, '%Y-%m') 无法走 created_at 索引；若查询高频，建议提前物化一个 first_month 字段并建索引。

使用场景提示：

• 如果只查近 12 个月，加 WHERE created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH) 能显著提速
• 若 user_id 不是主键或存在空值，GROUP BY user_id 前需确认去重逻辑（如是否要排除测试账号）
• 业务上“新增”可能定义为首次支付而非注册，此时应换用订单表的 pay_time 并关联用户维度

容易被忽略的时间精度与时区问题

很多团队没意识到：数据库服务器时区、应用写入时区、前端展示时区三者不一致，会导致某天凌晨 00:03 的注册被算进前一天的“新增”。例如服务器在 UTC+8，但 created_at 存的是 UTC 时间，DATE_FORMAT(created_at, '%Y-%m') 就会错位。

验证方法：查几个用户原始 created_at 值，手动转成本地时间再比对月份。必要时统一转换：

-- MySQL 示例：假设存的是 UTC，要按东八区统计 SELECT DATE_FORMAT(CONVERT_TZ(MIN(created_at), '+00:00', '+08:00'), '%Y-%m') AS month, COUNT(*) ...

这个细节在跨区域业务或迁移旧系统时几乎必踩，但日志里不会报错，只会让月度报表对不上运营数据。