【实用】Mysql 按照时间（年月周日）维度统计，不存在时间数据自动补充 0 数值

野性酷女 2023-09-24 23:27 44阅读 0赞

# **前言** #

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

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

本篇内容 ：

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

② 不存在的数据自动补充 0 （实用）

# **正文** #

不多说，开搞。

结合实例 ：

先看我们的表 student

![071afe99840441d7890839ad1accd888.png][]

建表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条数据：

![e38bcf0b10fb4714b36486d4b6cb8724.png][]

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

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

###    日 维度 ###

sql：

> SELECT date\_format(admission\_time, '%Y-%m-%d') date\_time, COUNT(\*) num  
> FROM student  
> GROUP BY date\_time ;

看看效果：

![35ceacf9f0714c1da7f11aaf9b3a85f4.png][]

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 ;

看看效果 ：

![a7da439837cf41d2955d6fe4fe214fff.png][]

### 周维度 ###

sql：

> SELECT DATE\_FORMAT(admission\_time,'%Y-%u 周') AS date\_time, COUNT(\*) num FROM student GROUP BY date\_time;

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

![46f9d0ab32b64fc697ee7307c8d02e82.png][]

看看效果 ：

![f7bdcd1c8aa24213ac09fbc674679663.png][]

###    月维度 ###

sql：

> SELECT DATE\_FORMAT(admission\_time, '%Y-%m') date\_time, COUNT(\*) num  
> FROM student  
> GROUP BY date\_time ;

看看效果 ：

![653d6438ea0b47c3b43cd83ed7b6377d.png][]

### 年维度 ###

sql：

> SELECT DATE\_FORMAT(admission\_time, '%Y') date\_time, COUNT(\*) num  
> FROM student  
> GROUP BY date\_time ;

看看效果:  
  
![fadb5970a4ed498da44ecc7e2a1f8061.png][]

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

--------------------

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

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

![8514e5ddf05843f18fe7f7aff6ff1450.png][]  
  
这里突然想到个点，很多人说那这里面混了 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'

效果：

![c64cdfa031b942c885a1b059e8ffc231.png][]

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

### **如何默认去填充 0呢 ？**    ###

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

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

接下来看看SQL怎么玩 。

思路&想法 ：

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

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

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

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

**你看看（一万个拒绝）**

![e94f35d4c4a1479c971c5b7b9aac883f.png][]

还有这种，按照目前时间拼接出来的：

**（5000个拒绝，这种now 直接切割到现在，还得写一大堆这种01，02，03，04；  
如果我是要填充 日维度的数据，那我这sql代码量不就爆炸了？**  
**）**   
![64d78ae482ce4372bf627148956507e9.png][]

这种也是：  
![3c6f91a1c4fb48d183041e41227f9403.png][]

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')

效果 ：

![495b7997f4c0478787b2f721ce5ba9f7.png][]

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

sql 作用简析 ：

简析点一

![c2977028737048ecb813e08f193b4422.png][]

简析点二

![2fc7195f4f1a428380a9db53a91f5f86.png][]

可以看到 ，这里面 我写了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 万 条。

![1f59a5713e1c4bd2bad0f619a78714c8.png][]

OK，不啰嗦，回到我们的示例 ：

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

![36203a8813844981b6302fd2ac0c9655.png][]

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

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

![13ef201d40a14fa5b28768df4f7cf07d.png][]

sql操作图析:

![c3a48e0954244de38ad9cf03325804f3.png][]

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

看看效果 ：  
  
![83ffea96c869450b968ebd3b0ec9d9e9.png][]

![935a94bceedf4e958d631ef40eff0a2b.png][]

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

文末补充 日、年、周 维度的 列出完整数据条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'

效果：

![e7af57f74bb84146b777f814eecb7d66.png][]

按周 列出范围内日期的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')

效果：  
![61c1373af6894380b5ecbb6568db979b.png][]

[071afe99840441d7890839ad1accd888.png]: https://img-blog.csdnimg.cn/071afe99840441d7890839ad1accd888.png
[e38bcf0b10fb4714b36486d4b6cb8724.png]: https://img-blog.csdnimg.cn/e38bcf0b10fb4714b36486d4b6cb8724.png
[35ceacf9f0714c1da7f11aaf9b3a85f4.png]: https://img-blog.csdnimg.cn/35ceacf9f0714c1da7f11aaf9b3a85f4.png
[a7da439837cf41d2955d6fe4fe214fff.png]: https://img-blog.csdnimg.cn/a7da439837cf41d2955d6fe4fe214fff.png
[46f9d0ab32b64fc697ee7307c8d02e82.png]: https://img-blog.csdnimg.cn/46f9d0ab32b64fc697ee7307c8d02e82.png
[f7bdcd1c8aa24213ac09fbc674679663.png]: https://img-blog.csdnimg.cn/f7bdcd1c8aa24213ac09fbc674679663.png
[653d6438ea0b47c3b43cd83ed7b6377d.png]: https://img-blog.csdnimg.cn/653d6438ea0b47c3b43cd83ed7b6377d.png
[fadb5970a4ed498da44ecc7e2a1f8061.png]: https://img-blog.csdnimg.cn/fadb5970a4ed498da44ecc7e2a1f8061.png
[8514e5ddf05843f18fe7f7aff6ff1450.png]: https://img-blog.csdnimg.cn/8514e5ddf05843f18fe7f7aff6ff1450.png
[c64cdfa031b942c885a1b059e8ffc231.png]: https://img-blog.csdnimg.cn/c64cdfa031b942c885a1b059e8ffc231.png
[e94f35d4c4a1479c971c5b7b9aac883f.png]: https://img-blog.csdnimg.cn/e94f35d4c4a1479c971c5b7b9aac883f.png
[64d78ae482ce4372bf627148956507e9.png]: https://img-blog.csdnimg.cn/64d78ae482ce4372bf627148956507e9.png
[3c6f91a1c4fb48d183041e41227f9403.png]: https://img-blog.csdnimg.cn/3c6f91a1c4fb48d183041e41227f9403.png
[495b7997f4c0478787b2f721ce5ba9f7.png]: https://img-blog.csdnimg.cn/495b7997f4c0478787b2f721ce5ba9f7.png
[c2977028737048ecb813e08f193b4422.png]: https://img-blog.csdnimg.cn/c2977028737048ecb813e08f193b4422.png
[2fc7195f4f1a428380a9db53a91f5f86.png]: https://img-blog.csdnimg.cn/2fc7195f4f1a428380a9db53a91f5f86.png
[1f59a5713e1c4bd2bad0f619a78714c8.png]: https://img-blog.csdnimg.cn/1f59a5713e1c4bd2bad0f619a78714c8.png
[36203a8813844981b6302fd2ac0c9655.png]: https://img-blog.csdnimg.cn/36203a8813844981b6302fd2ac0c9655.png
[13ef201d40a14fa5b28768df4f7cf07d.png]: https://img-blog.csdnimg.cn/13ef201d40a14fa5b28768df4f7cf07d.png
[c3a48e0954244de38ad9cf03325804f3.png]: https://img-blog.csdnimg.cn/c3a48e0954244de38ad9cf03325804f3.png
[83ffea96c869450b968ebd3b0ec9d9e9.png]: https://img-blog.csdnimg.cn/83ffea96c869450b968ebd3b0ec9d9e9.png
[935a94bceedf4e958d631ef40eff0a2b.png]: https://img-blog.csdnimg.cn/935a94bceedf4e958d631ef40eff0a2b.png
[e7af57f74bb84146b777f814eecb7d66.png]: https://img-blog.csdnimg.cn/e7af57f74bb84146b777f814eecb7d66.png
[61c1373af6894380b5ecbb6568db979b.png]: https://img-blog.csdnimg.cn/61c1373af6894380b5ecbb6568db979b.png