MySQL 根据日期 group by 统计数据并将时间不存在的记录补全（纯 sql ，不建表）

VioletKiss

MySQL

发布于：2020年6月12日

字数：885字

时长：3分钟

当需要统计连续时间的结果集的时候，比如说注册时间有一天没有用户注册，那么数据中便缺少了那一天的统计数据，这一天的数据可以通过代码解决，也可以使用建时间表的方法解决，当然也可以使用我下面的这个方法解决。

开始

最近同事需要一个根据时间聚合统计数据的查询结果集，并且最好是可以把没有记录的日期也可以补全，我搜索了一下只找到说建立一个日期表然后联表查询方法，但是多一张没用的表，太不优雅了，所以我打算自己试试写一条sql尝试将结果查询出来。

思考

由于需要连续的日期，我们想到可以通过MySQL的DATE_ADD或DATE_SUB来获得。那么函数中的type固定用DAY，但是expr我们怎么生成呢？这时候我们就可以设置一个变量不断递增来获取我们需要天数的日期，这样连续的日期就完成了，然后我们联合根据时间 group by 查询出来的结果集查询即可。

实践

有一张 user 表，有 user_id 和 create_time 两个字段，模拟查询每天注册人数，暂定查询今天起往前20天（示例简单为主，具体需求可各自扩展）

根据时间聚合查出结果集

SELECT
	DATE( u.create_time ) AS create_time,
	count( u.user_id ) AS total
FROM
	`user` u 
GROUP BY
	DATE( u.create_time )

连续时间查询的结果集

SET @i :=- 1;
SELECT
	date_format( DATE_SUB( NOW( ), INTERVAL ( @i := @i + 1 ) DAY ), '%Y-%m-%d' ) AS `time` 
FROM
	(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1, 
		(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2
WHERE
		@i <= 30

sql 解释：(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) 这里表示 6 次，FROM 两遍根据笛卡尔积生成 6 X 6 次，所以最大会连续到36，注意设置的 @i 查询的天数要小于等于 36 ，如果要查询40，则根据数量在后面其中一条后 UNION 7，则为 6 X 7 = 42 次，即可，其他数量自行修改。

联合 1 和 2 两个结果集的数据

SET @i :=- 1;
SELECT
	x.`time`,
-- 	IFNULL( d.create_time, x.`time` ) AS `create_time`,
	IFNULL( d.total, 0 ) AS total
FROM
	(
	SELECT
		date_format( DATE_SUB( NOW( ), INTERVAL ( @i := @i + 1 ) DAY ), '%Y-%m-%d' ) AS `time` 
	FROM
		(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc1, 
			(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) xc2
	WHERE
		@i <= 30 
	) x
	LEFT JOIN 
	(
	SELECT
		DATE( u.create_time ) AS create_time,
		count( u.user_id ) AS total
	FROM
		`user` u 
	GROUP BY
		DATE( u.create_time ) 
	) d ON TO_DAYS( x.`time` ) = TO_DAYS( DATE( d.create_time ) ) 
ORDER BY
	x.`time`