【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值
作者:mmseoamin日期:2023-12-25

前言

ps: 网上看了一大堆文章, 介绍的东西真的是很够呛, 就没一个能真正用起来的, 各个都是自动补,然后很多都是不好用的。

我自己整理一篇,这是真能用。

本篇内容 :

① 按照 日 、周、月 、年  的维度 去对数据 做分组统计 

② 不存在的数据自动补充 0  (实用)

正文

不多说,开搞。

结合实例 :

先看我们的表 student 

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第1张

 建表sql:

CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'id',
    `name` VARCHAR(50) NULL DEFAULT NULL COMMENT '名字' COLLATE 'utf8_general_ci',
    `admission_time` DATETIME NULL DEFAULT NULL COMMENT '入学时间',
    PRIMARY KEY (`id`) USING BTREE
)

现在我们就举个简单的业务场景:

根据admission_time 入学时间,按照年月日周这些维度 去统计 学生数量。

我们来做一些模拟数据,8条数据:

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第2张

第一部分 , 按照时间维度统计的SQL 写法

我们先简单看看 按照 日 、周  、月 、年 的统计 sql怎么写 :

日 维度 

sql:

SELECT date_format(admission_time, '%Y-%m-%d') date_time, COUNT(*) num

FROM student

GROUP BY date_time ;

 看看效果:


【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第3张

 

ps : 注意了 这些 ‘-’ 是我们定义的格式, 我们不要 ‘-’ 换成其他的也是可以的,但是 不能不要 ymd 这些关键字。

举个小栗子:

 

 

比如 ,date_format(admission_time, '收藏%Y点%m赞%d')

SELECT date_format(admission_time, '收藏%Y点%m赞%d') date_time, COUNT(*) num

FROM student

GROUP BY date_time ;

看看效果 :

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第4张

周维度 

 sql:

SELECT DATE_FORMAT(admission_time,'%Y-%u 周')  AS  date_time, COUNT(*) num FROM student GROUP BY date_time;

ps : 为了让你们知道 这个周的概念, 我故意加了个中文。 而且特意把一条数据 时间改成 22年的最后一周的一天。

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第5张

 

看看效果 :

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第6张

月维度 

sql:

SELECT DATE_FORMAT(admission_time, '%Y-%m') date_time, COUNT(*) num

FROM student

GROUP BY date_time ;

看看效果 :

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第7张

年维度

sql:

SELECT DATE_FORMAT(admission_time, '%Y') date_time, COUNT(*) num

FROM student

GROUP BY date_time ;

看看效果:

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第8张

好了 知道这些基础的 时间分组sql 用法之后, 那么我们接下来就 来 玩下怎么 解决自动补 0 的这个问题。


第二部分 , 自动填充不存在的数据 数值 0

首先,如果说 sql查出来的数据没有, 我们拿月维度的来 做个示例 ,就像这样 :

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第9张 

这里突然想到个点,很多人说那这里面混了 2022年, 2023年的数据, 我想要指定查询某段时间的怎么搞?

其实一样的,就根据时间查询就行:

比如像这样传入我们的筛选时间范围,按照月就这样 :

sql:

SELECT * FROM (

select date_format(admission_time, '%Y-%m') date_time, count(*) num

from student

group by date_format(admission_time, '%Y-%m')

)t WHERE t.date_time BETWEEN '2023-01' AND   '2023-03'

效果:


【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第10张

 

回到刚才,可以看到统计出来的数据, 比如说23年的,有1月的,2月的, 3月的, 那么 4,5,6,7后面这些月份,没数据,那怎么办?

如何默认去填充 0呢 ?

 

写代码填充,后端拿到查数据库返回的数据,for循环遍历,检测时间段内的日期, 比如说 12个月,看看哪个月没有,就填充。

确实 这是可以的, 但是今天这一篇介绍的是通过sql返回 , 不考虑代码上面的填补。

接下来看看SQL怎么玩 。

思路&想法 : 

 

我们能查出来 student 现有的日期数据, 那么缺少的数据 我也得给整出来 。

那我们肯定不能去改 student表的数据呀, 现在就是单纯少了一些 空白月份的数据 。

所以我们选择 临时数据表的思维。

ps : 网上一大堆文章,都让咱们去跑个存储过程  强行生成一个表..

你看看(一万个拒绝) 

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第11张

 还有这种,按照目前时间拼接出来的:

(5000个拒绝,这种now 直接切割到现在,还得写一大堆这种01,02,03,04;

   如果我是要填充 日维度的数据,那我这sql代码量不就爆炸了?
) 
【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第12张

 这种也是:
【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第13张

OK, 我们来看看我们的SQL :

 

select DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') date_time from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
    (SELECT @row:=-1) r
 ) se
 where DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') <= DATE_FORMAT('2023-12-01','%Y-%m')

效果 :

 

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第14张

ps: 这里用了月举例, 要弄年或周或者日的 在文末有补充。

sql 作用简析 :

简析点一 


【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第15张

 

 简析点二

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第16张

 可以看到 ,这里面 我写了2行这个玩意  。

(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t

 

简单说下:

 

写一行这个玩意, 代表能搞出 10 条 符合 范围时间内日期格式时间数据 ;

当写多一行呢(2行的时候),代表 10 * 10 =100 条 

当再写多一行呢(3行的时候)?  10*10*10 =1000条

所以我们上面的sql,我写了2行,

代表 在我传入的时间范围 2023-01-01 到 2323-12-01  内

我写的时间格式是取月, 一共其实就12 个月数据,也就是12条, 但是只写一次10条不够用,我也就用了2次(100条)。

言下之意, 其实你跟我这样写2次,100条, 什么概念, 1年12个月 相当于12条,这样 100条相当于可以查跨度 8年的时间了 (8*12-96)

如果你不是要查日, 业务需求一般不会让咱们写跨度这么大的。

当然了,如果就是有, 那么我们大不了直接写 4条, 相当于 10*10*10*10 =1 万  条。

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第17张

OK,不啰嗦,回到我们的示例 :

我们现在 如果说是查跨度 2年的数据, 比如现在按照我们part的sql 查出来是这样子的 。

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第18张

可以看到结果集, 22年数据 缺了很多 需要补0的, 23年也缺了很多。

所以我们这时候需要做一个 left join 即可解决自动补 0 的事情。

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第19张

sql操作图析:

 

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第20张

sql:

SELECT  A.date_time, COALESCE(B.num, 0) as num  FROM 
(
SELECT DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') date_time FROM
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
	  
    (SELECT @row:=-1) r
 ) se
WHERE DATE_FORMAT(date_add('2023-01-01', interval row MONTH),'%Y-%m') <= DATE_FORMAT('2023-12-01','%Y-%m')
) A
LEFT JOIN 
(
SELECT DATE_FORMAT(admission_time, '%Y-%m') date_time, COUNT(*) num
FROM student
GROUP BY DATE_FORMAT(admission_time, '%Y-%m')
) B
ON    A.date_time= B.date_time

看看效果 :

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第21张

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第22张

这盛世如我所愿, 好了, 该篇就到这。 

文末补充 日、年、周 维度的 列出完整数据条sql :

按日 列出范围内日期的sql :

 

select date_add('2023-01-01', interval row DAY) date from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	  (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	   (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,   
    (SELECT @row:=-1) r
 ) se
 where date_add('2023-01-01', interval row DAY) <= '2023-01-20'

 效果:

【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第23张

按周 列出范围内日期的sql :

按照周要注意一点,当传入每年的01-01这一天的时候 会出现0周 ,可以做一下处理。

 select DATE_FORMAT(date_add('2023-01-01', interval row WEEK),'%Y-%u') date_time from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
	  
    (SELECT @row:=-1) r
 ) se
 where DATE_FORMAT(date_add('2023-01-01', interval row WEEK),'%Y-%u') <= DATE_FORMAT('2023-01-06','%Y-%u')

按年 列出范围内日期的sql :

select DATE_FORMAT(date_add('2020-01-01', interval row YEAR),'%Y') date_time from
 ( 
    SELECT @row := @row + 1 as row FROM 
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
    (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
	 (select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
	  
    (SELECT @row:=-1) r
 ) se
 where DATE_FORMAT(date_add('2020-01-01', interval row YEAR),'%Y') <= DATE_FORMAT('2023-12-01','%Y')
 

效果:
【实用】Mysql 按照时间(年月周日)维度统计,不存在时间数据 自动补充 0 数值,第24张