mysql 语句自动补全脚本java mysql补全日期

转载

mob64ca13fc220d 2023-11-09 10:28:50

文章标签 mysql 语句自动补全脚本java MySQL 日期记录补全结果集 文章分类 MySQL 数据库

开始

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

连续时间查询的结果集 （此次有误！！！）
下面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 和 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`