MySQL实现按月统计每日数据量（含无数据日期补0）

嘿，我来帮你搞定这个需求！你手头有个数据表，结构是date_upload|url_upload|status_upload，数据示例如下：

2017-11-01 |www.com |verified
2017-12-01 |www.com |verified
2017-13-01 |www.com |verified
2017-11-01 |www.com |verified

你需要统计该月份每一天的记录数量，哪怕当天没有任何记录也要返回0，最终结果要覆盖当月所有日期，比如2017-11-01计数为2，2017-12-01计数为1，2017-13-01计数为1，其余日期都是0。

核心思路 #

要实现这个需求，关键是先生成目标月份的完整日期序列，然后把这个日期序列和你的数据表做左连接，最后按日期统计记录数——左连接会保留所有日期行，没有匹配记录时统计结果就是0。

下面分不同数据库给出具体实现代码：

MySQL 实现方案 #

方案1：用递归CTE（MySQL 8.0及以上版本支持） #

递归CTE可以很方便地生成连续日期：

WITH RECURSIVE date_range AS (
    -- 定义起始日期：目标月份的第一天
    SELECT DATE('2017-11-01') AS date_day
    UNION ALL
    -- 逐天加1，直到当月最后一天
    SELECT DATE_ADD(date_day, INTERVAL 1 DAY)
    FROM date_range
    WHERE date_day < LAST_DAY('2017-11-01')
)
SELECT 
    dr.date_day,
    COUNT(t.date_upload) AS record_count
FROM date_range dr
-- 左连接原表，匹配日期
LEFT JOIN your_table t ON dr.date_day = t.date_upload
GROUP BY dr.date_day
ORDER BY dr.date_day;

• 替换your_table为你的实际表名
• LAST_DAY()函数会自动获取目标月份的最后一天，不用手动算天数

方案2：用数字表生成日期（MySQL 5.x版本兼容） #

如果你的MySQL版本不支持CTE，可以用交叉连接生成数字序列，再转换成日期：

SELECT 
    date_day,
    COUNT(t.date_upload) AS record_count
FROM (
    -- 生成足够多的数字，转换成日期
    SELECT DATE_ADD('2017-11-01', INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY) AS date_day
    FROM (SELECT 0 AS a 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 UNION ALL SELECT 8 UNION ALL SELECT 9) AS a
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3) AS b
    CROSS JOIN (SELECT 0 AS a UNION ALL SELECT 1) AS c
    -- 筛选出当月的日期
    HAVING date_day <= LAST_DAY('2017-11-01')
) dr
LEFT JOIN your_table t ON dr.date_day = t.date_upload
GROUP BY dr.date_day
ORDER BY dr.date_day;

PostgreSQL 实现方案 #

PostgreSQL自带generate_series函数，生成日期序列超简单：

SELECT 
    dr.date_day::DATE,
    COUNT(t.date_upload) AS record_count
FROM generate_series(
    -- 起始日期
    DATE '2017-11-01',
    -- 结束日期（当月最后一天）
    DATE '2017-11-30',
    -- 步长：每天
    INTERVAL '1 day'
) AS dr(date_day)
-- 左连接原表，注意转换日期类型匹配
LEFT JOIN your_table t ON dr.date_day::DATE = t.date_upload::DATE
GROUP BY dr.date_day
ORDER BY dr.date_day;

• 如果你不知道当月最后一天，可以用(DATE '2017-11-01' + INTERVAL '1 month' - INTERVAL '1 day')来替代DATE '2017-11-30'

额外注意点 #

1. 如果date_upload是字符串类型，需要先转换成日期格式：
   • MySQL：用STR_TO_DATE(t.date_upload, '%Y-%m-%d')
   • PostgreSQL：用t.date_upload::DATE
2. 可以动态生成目标月份的起始日期，比如获取当前月第一天：
   • MySQL：DATE_FORMAT(NOW(), '%Y-%m-01')
   • PostgreSQL：DATE_TRUNC('month', CURRENT_DATE)::DATE

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