SQL查询如何统计每个月的新增用户数_利用分组与自连接实现-SQL-PHP中文网

正确做法是先按user_id分组求每个用户的MIN(created_at)，再按该首次时间归月统计新增数；直接按注册时间分组会误将老用户重复行为计入新增。

sql查询如何统计每个月的新增用户数_利用分组与自连接实现

怎么用 GROUP BY 提取每月首次登录的用户

新增用户数本质是「每个用户在全库中最早出现的月份」，不是简单按注册时间分组。如果直接 GROUP BY DATE_FORMAT(reg_time, '%Y-%m')，会把老用户重复登录或资料补录也计入当月新增。

正确做法是先算出每个用户的首次行为时间（比如第一次登录、第一次下单、第一次注册），再按该时间归月统计。假设用户表为 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 天 + 索引覆盖完整），否则应避免以下模式：

DATE_FORMAT()

如果只查近 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(*) ...

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