MySql 根据日期 group by 统计数据并将时间不存在的记录补全(纯 sql ,不建表)

开始

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

思考

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

实践

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

  1. 根据时间聚合查出结果集
SELECT
	DATE( u.create_time ) AS create_time,
	count( u.user_id ) AS total
FROM
	`user` u 
GROUP BY
	DATE( u.create_time )
  1. 连续时间查询的结果集 (此次有误!!!)
    下面sql有误
SET @i :=- 1;
SELECT
	date_format( DATE_SUB( NOW( ), INTERVAL ( @i := @i + 1 ) DAY ), '%Y-%m-%d' ) AS `time` 
FROM
	`user` 
WHERE
	@i <= 20

上面 sql 查出来的连续时间局限于user表的记录长度,当 user 表记录少于 20,那么连续日期为记录数而不是 20,所以要么把 FROM user 改为 FROM 一个你认为一个大于管理员所要查询的连续天数的记录表,要么修改为一下方式

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. 联合 1 和 2 两个结果集的数据
    下面sql有误
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
		`user` 
	WHERE
		@i <= 20 
	) 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`

修正版

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`

扩展

在最终语句中:

  1. NOW() 可以替换成你想要的任意时间;
  2. DATE_SUB 表示从 NOW() 时间起往前多少天的连续日期,如果需要往后的连续日期,改为 DATE_ADD 即可。
  3. 在获得连续日期的语句中,WHERE 条件中的 @i <= 20 表示连续 20 天的日期,数值可自定义,选择自己想要查询的连续时间范围。

总结

确定好自己想要的结果形式,然后慢慢补全,直至获得最终的结果,利用分治法的思想,把一个大问题分解成几个小问题,一步一步解决,最终得出大问题的解。

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页