编写SQL查询实现月度新增用户量环比变化计算

Hey there! Let's work through this problem to get accurate monthly new user counts and their month-over-month differences, skipping the first month that has no prior data.

先明确需求 #

我们需要从userlog表（含ID和DateJoined字段，格式YYYY-MM-DD）实现：

• 统计每个月的新增用户数（需区分年份，避免跨年同月份混淆）
• 计算当前月与前一个月的用户数差值（当前月 - 前月）
• 跳过没有前置月份的第一个月

原查询的问题 #

你的原始查询存在几个关键问题：

1. 仅用MONTH()函数提取月份数字，会把不同年份的同月份（比如2023-12和2024-12）合并，导致统计错误
2. 子查询SELECT MIN(MONTH(joindate)) FROM userlog WHERE MONTH(joindate) < MONTH(L.joindate)逻辑有误——它会关联到所有比当前月份小的月份里最早的那个，而不是直接前一个月份，如果中间有月份缺失，结果会完全偏离预期
3. 分组方式和JOIN逻辑过于复杂，容易产生重复或错误的结果

推荐解决方案 #

根据你的MySQL版本，有两种简洁可靠的写法：

方案1：MySQL 8.0+（支持窗口函数和CTE）

这是最直观高效的写法，用LAG()窗口函数直接获取前一个月的用户数：

WITH monthly_new_users AS (
    -- 第一步：统计每个年月的新增用户数
    SELECT
        DATE_FORMAT(DateJoined, '%Y-%m') AS month_year, -- 用YYYY-MM格式区分年月
        COUNT(ID) AS new_users_count
    FROM userlog
    GROUP BY month_year
    ORDER BY month_year
)
-- 第二步：计算环比差值，过滤首月
SELECT
    month_year,
    new_users_count,
    new_users_count - LAG(new_users_count) OVER (ORDER BY month_year) AS month_over_month_diff
FROM monthly_new_users
WHERE LAG(new_users_count) OVER (ORDER BY month_year) IS NOT NULL; -- 跳过无前置月份的首月

方案2：MySQL 5.x（不支持窗口函数/CTE）

用自连接的方式关联当前月份和前一个月份：

SELECT
    curr.month_year,
    curr.new_users_count,
    curr.new_users_count - prev.new_users_count AS month_over_month_diff
FROM (
    -- 统计每个年月的新增用户数
    SELECT
        DATE_FORMAT(DateJoined, '%Y-%m') AS month_year,
        COUNT(ID) AS new_users_count
    FROM userlog
    GROUP BY month_year
) curr
-- 关联前一个月份的数据
JOIN (
    SELECT
        DATE_FORMAT(DateJoined, '%Y-%m') AS month_year,
        COUNT(ID) AS new_users_count
    FROM userlog
    GROUP BY month_year
) prev 
ON STR_TO_DATE(curr.month_year, '%Y-%m') = DATE_ADD(STR_TO_DATE(prev.month_year, '%Y-%m'), INTERVAL 1 MONTH)
ORDER BY curr.month_year;

代码说明 #

• DATE_FORMAT(DateJoined, '%Y-%m')：将日期转换为YYYY-MM格式，确保不同年份的同月份不会被混淆
• LAG()窗口函数：在有序的结果集中，获取当前行的上一行数据（即前一个月的用户数）
• 自连接方案中，通过DATE_ADD(..., INTERVAL 1 MONTH)精准关联当前月份的前一个自然月，自动过滤掉没有前置月份的首月

内容的提问来源于stack exchange，提问作者Mistry Jignesh