MySQL按日期统计用户数：实现缺失日期返回0的方案

问题概述 #

你有一个user表，其中addtime字段为timestamp类型，希望按年、月、日分组统计每日新增用户数量。当前的查询仅返回有用户注册的日期数据，但你需要指定日期范围内的所有日期都显示出来，没有新增用户的日期返回0。

表结构 #

CREATE TABLE `user` ( 
  `id` int(11) NOT NULL AUTO_INCREMENT, 
  `username` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 
  `addtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 
  PRIMARY KEY (`id`), 
  UNIQUE KEY `username` (`username`) 
);

示例数据 #

INSERT INTO `user` VALUES 
(1,'user1','2018-01-02 10:01:01'),
(2,'user2','2018-01-02 10:11:01'),
(3,'user3','2018-01-03 10:01:01'),
(4,'user4','2018-01-03 10:11:01'),
(5,'user5','2018-01-03 10:21:01'),
(6,'user6','2018-01-03 10:41:01'),
(7,'user7','2018-01-05 10:01:01'),
(8,'user8','2018-01-05 10:11:01'),
(9,'user9','2018-01-05 10:21:01');

当前查询与结果 #

你现在使用的查询语句：

SELECT DATE_FORMAT(addtime, '%Y-%m-%d') AS Date, COUNT(id) AS total 
FROM user 
WHERE addtime BETWEEN '2018-01-01 00:00:00' AND '2018-01-07 00:00:00' 
GROUP BY YEAR(addtime), MONTH(addtime), DAY(addtime);

返回的结果只包含有注册用户的日期：

2018-01-02, 2
2018-01-03, 4
2018-01-05, 3

期望结果 #

希望得到指定范围内的所有日期，无新增用户的日期返回0：

2018-01-01, 0
2018-01-02, 2
2018-01-03, 4
2018-01-04, 0
2018-01-05, 3
2018-01-06, 0

解决方案 #

要实现这个需求，核心是先构建一个包含指定范围内所有连续日期的序列，再将这个序列和你的用户统计数据做左连接，这样空日期的统计值就会被填充为0。下面分两种MySQL版本给出方案：

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

递归CTE是生成日期序列最简洁的方式，代码如下：

WITH RECURSIVE date_range AS (
    -- 定义起始日期
    SELECT '2018-01-01' AS date_val
    UNION ALL
    -- 递归生成后续日期，直到结束日期的前一天（匹配你的查询范围）
    SELECT DATE_ADD(date_val, INTERVAL 1 DAY)
    FROM date_range
    WHERE date_val < '2018-01-06'
)
SELECT 
    dr.date_val AS Date,
    -- 使用COALESCE将NULL替换为0
    COALESCE(u.total, 0) AS total
FROM date_range dr
LEFT JOIN (
    -- 原统计逻辑，按日期分组计算新增数
    SELECT DATE_FORMAT(addtime, '%Y-%m-%d') AS user_date, COUNT(id) AS total
    FROM user
    WHERE addtime BETWEEN '2018-01-01 00:00:00' AND '2018-01-07 00:00:00'
    GROUP BY user_date
) u ON dr.date_val = u.user_date
ORDER BY dr.date_val;

方案2：MySQL 5.x（不支持CTE） #

如果你的MySQL版本较低，无法使用CTE，可以借助数字辅助表生成日期序列：

-- 创建临时数字表，这里生成0-6的数字，对应7天的日期范围
CREATE TEMPORARY TABLE nums (n INT);
INSERT INTO nums VALUES (0),(1),(2),(3),(4),(5),(6);

SELECT 
    DATE('2018-01-01') + INTERVAL n DAY AS Date,
    COALESCE(u.total, 0) AS total
FROM nums
LEFT JOIN (
    SELECT DATE_FORMAT(addtime, '%Y-%m-%d') AS user_date, COUNT(id) AS total
    FROM user
    WHERE addtime BETWEEN '2018-01-01 00:00:00' AND '2018-01-07 00:00:00'
    GROUP BY user_date
) u ON DATE('2018-01-01') + INTERVAL n DAY = u.user_date
-- 过滤出指定范围内的日期
WHERE DATE('2018-01-01') + INTERVAL n DAY <= '2018-01-06'
ORDER BY Date;

结果验证 #

以上两种方案都会返回你期望的结果：包含2018-01-01到2018-01-06的所有日期，没有新增用户的日期total字段显示为0。

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