Mysql日期统计查询时部分日期缺失的问题

·  阅读 511

通过用sql语句对表进行日期统计 12月10号到12月14号的数据,想知道每天的数据有多少

但是有一个问题是:如果我的数据中没有12月13号、12号的话,返回的结果中就会缺少这两天的记录。

如果没有日期的话应该给12号、13号补一条为0的记录

下面我来还原整个过程

一、查询数据库中的基础数据

首先通过最原始的方式查询一下数据库的数据信息

在执行结果中可以看到数据里面是没有2021-12-122021-12-13日期的数据

SELECT
	id, 
	issued_time AS create_time,
	standard_cn_name
FROM standard 
WHERE issued_status = 1;
复制代码

执行结果

id	create_time		standard_cn_name
10	2021-12-10 09:42:32	汽运四级分类标准
14	2021-12-14 18:36:44	订单工艺标准
15	2021-12-08 10:30:27	订单工艺标准-1638879781693
28	2021-12-09 17:00:16	订单工艺路线0cp
56	2021-12-14 15:20:55	物料组222
91	2021-12-14 15:17:11	物料组333
93	2021-12-11 15:37:22	物料组444
复制代码

二、统计每天的数量,日期进行date_formatgroup by

统计的结果中发现统计后没有12号和13号的数据,但是我们想要mysql中能够补全这些缺失的日期,并且count为0,希望的预期结果展示在下面

SELECT
	date_format( issued_time, '%Y-%m-%d' ) AS create_time,
	count(*) AS count 
FROM standard 
WHERE issued_status = 1 
GROUP BY date_format( issued_time, '%Y-%m-%d' )
复制代码

执行结果

create_time	count
2021-12-08	1
2021-12-09	1
2021-12-10	1
2021-12-11	1
2021-12-14	3
复制代码

希望的预期结果

create_time	count
2021-12-08	1
2021-12-09	1
2021-12-10	1
2021-12-11	1
2021-12-12	0
2021-12-13	0
2021-12-14	3
复制代码

那我们有什么方式可以统计缺失的日期呢?

三、使用mysql.help_topic表查询出连续的日期

mysql.help_topic表中的 help_topic_id字段是一个递增的值,最大值为659,做统计查询的时候需要注意如果你的连续日期超过了659的话是不适合使用这种方法哟(一般统计查询都是 7天、30天..很少会超出659)

利用help_topic_id自增的特性我们可以生成一段连续的日期

假如我们想生成 2021-12-08到2021-12-14的连续日期

SELECT
	date_format( date_add( '2021-12-08', INTERVAL + t.help_topic_id DAY ), '%Y-%m-%d' ) AS 'date' 
FROM
	mysql.help_topic t 
WHERE
	t.help_topic_id <= timestampdiff(DAY, '2021-12-08', '2021-12-14')
复制代码

执行结果

date
2021-12-08
2021-12-09
2021-12-10
2021-12-11
2021-12-12
2021-12-13
2021-12-14
复制代码

假如想生成连续的月份

SELECT
	date_format( date_add( '2021-12-01', INTERVAL + t.help_topic_id month ), '%Y-%m') AS 'date' 
FROM
	mysql.help_topic t 
WHERE
	t.help_topic_id <= timestampdiff(month, '2021-12-01', '2022-5-01')
复制代码

执行结果

date
2021-12
2022-01
2022-02
2022-03
2022-04
2022-05
复制代码

二、通过mysql.help_topic生成的结果再关联我们的业务数据

使用mysql.help_topic生成连续日期

SELECT
	date_format( date_add( '2021-12-08', INTERVAL + t.help_topic_id DAY ), '%Y-%m-%d' ) AS 'date' 
FROM
	mysql.help_topic t 
WHERE
	t.help_topic_id <= timestampdiff(DAY, '2021-12-08', '2021-12-14')
复制代码

执行结果

date
2021-12-08
2021-12-09
2021-12-10
2021-12-11
2021-12-12
2021-12-13
2021-12-14
复制代码

使用分组查询业务数据

SELECT
	date_format( issued_time, '%Y-%m-%d' ) AS create_time,
	count(*) AS count 
FROM standard 
WHERE issued_status = 1 
GROUP BY date_format( issued_time, '%Y-%m-%d' )
复制代码

执行结果

create_time	count
2021-12-08	1
2021-12-09	1
2021-12-10	1
2021-12-11	1
2021-12-14	3
复制代码

现在是需要将 mysql.help_topic生成的连续日期和业务数据join关联结合

整合后的sql如下, t1表就是生成的连续日期,t2表是业务数据表, 通过格式化后的日期进行join关联

SELECT
	date,
	ifnull( count, 0 ) AS count 
FROM
	(
		SELECT
			date_format( date_add( '2021-12-08', INTERVAL + t.help_topic_id DAY ), '%Y-%m-%d' ) AS 'date' 
		FROM
			mysql.help_topic t 
		WHERE
			t.help_topic_id <= timestampdiff( DAY, '2021-12-08', '2021-12-14' ) 
	) t1
	LEFT JOIN (
		SELECT
			date_format( issued_time, '%Y-%m-%d' ) AS create_time,
			count(*) AS count 
		FROM
			standard 
		WHERE
			issued_status = 1 
		GROUP BY
		date_format( issued_time, '%Y-%m-%d' ) 
	) t2 ON t1.date = t2.create_time
复制代码

执行结果

date		count
2021-12-08	1
2021-12-09	1
2021-12-10	1
2021-12-11	1
2021-12-12	0
2021-12-13	0
2021-12-14	3
复制代码
分类:
后端
标签:
收藏成功!
已添加到「」, 点击更改