优化MySQL用户与文章统计查询：新增7日计数及差值列需求

需求概述 #

现有tbl_users用户表和tbl_articles文章表，需统计过去7天的新增用户数与新增文章数，优化原查询的冗长、执行缓慢问题，并为两个统计项添加差值列。

表结构示例 #

tbl_users表 #

[Code, Username, Createdate]
1,David,01/01/2022
2,Henry,02/01/2022

tbl_articles表 #

[Code, Header, Createdate]
1,Hello,01/01/2022
2,Goodbye,02/01/2022

优化后的查询语句 #

WITH days_range AS (
    SELECT 0 AS days_back UNION ALL
    SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL
    SELECT 7
),
article_stats AS (
    SELECT 
        dr.days_back,
        COUNT(a.Code) AS article_count
    FROM days_range dr
    LEFT JOIN tbl_articles a 
        ON DATE(a.Createdate) >= DATE_SUB(CURDATE(), INTERVAL dr.days_back + 1 DAY)
        AND DATE(a.Createdate) < DATE_SUB(CURDATE(), INTERVAL dr.days_back DAY)
    GROUP BY dr.days_back
),
user_stats AS (
    SELECT 
        dr.days_back,
        COUNT(u.Code) AS user_count
    FROM days_range dr
    LEFT JOIN tbl_users u 
        ON DATE(u.Createdate) >= DATE_SUB(CURDATE(), INTERVAL dr.days_back + 1 DAY)
        AND DATE(u.Createdate) < DATE_SUB(CURDATE(), INTERVAL dr.days_back DAY)
    GROUP BY dr.days_back
)
SELECT 
    dr.days_back,
    COALESCE(us.user_count, 0) AS 新增用户数,
    COALESCE(as_.article_count, 0) AS 新增文章数,
    COALESCE(as_.article_count, 0) - COALESCE(us.user_count, 0) AS 文章用户差值
FROM days_range dr
LEFT JOIN user_stats us ON dr.days_back = us.days_back
LEFT JOIN article_stats as_ ON dr.days_back = as_.days_back
ORDER BY dr.days_back;

优化说明 #

• 简化日期范围生成：用CTE（公共表表达式）days_range一次性生成0-7的天数区间，替代原查询中重复的UNION ALL子查询，大幅精简代码结构。
• 减少表扫描次数：对用户表和文章表各仅执行一次关联查询，通过日期范围条件筛选对应天数的新增数据，避免原查询中多次全表扫描，显著提升执行效率。
• 空值处理：使用COALESCE函数确保某天无新增数据时显示0，避免结果出现NULL值。

差值列说明 #

新增的文章用户差值列，计算逻辑为当日新增文章数 - 当日新增用户数，直观展示两者的数量差异。

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