Mysql日期分组无数据查询填充0

时间:2021-07-06 04:10:58

前言

这篇文章标题不好取。。。(主要是生成连续的日期),本文关键点有:Mysql 获取指定时间段内的所有日期列表,
Mysql 按照日期分组查询没有数据的日期也一并查询出来。

本文原文链接地址:http://nullpointer.pw/Mysql日期分组无数据查询填充0.html

问题

产品提出一个需求,需要展示这样的一张折线图,用来反映指定时间段内网站注册用户的增加趋势,于是需要后端的 JSON 工程师给出对应的接口。

Mysql日期分组无数据查询填充0

疏忽大意

具体的表结构和数据是这样的

Mysql日期分组无数据查询填充0

JSON 工程师不加思索,展开了 CRUD 大法,顺手写下了一个 SQL,不到5分钟,接口完活,测都没测试直接给到了前端开发。

select date(t.create_time) as `date`,
       count(t.id)  as num
from t_user t
group by `date`;

前端拿到数据后开始绘图,结果画的图完全不对啊,因为时间不是连续的。于是反馈到了 JSON 工程师这里。

JSON 工程师一想,哎哟,没考虑掉某天没有数据的情况,分组查询的话,肯定缺少这一天的数据的。

 ------------ ----- 
|    date    | num |
 ------------ ----- 
| 2019-05-06 |  2  |
 ------------ ----- 
| 2019-05-08 |  2  |
 ------------ ----- 
| 2019-05-09 |  9  |
 ------------ ----- 

正确的做法应该是即使某天没有数据,也填充一个 0 作为记录值。

修正查询数据

找到了问题就好办了,捋了一下逻辑分成了两步

一、拿到所有日期

2019年09月12日更新

感谢评论区中 @一个小可爱(感觉是个有趣的人哈哈)提出的方法,于是我就来更新一下本文了。最好的方式是在 Java 代码中处理生成连续的日期,然后创建一个 map 对象,初始化所有的键值对的值为 0,然后遍历查询出来的按照日期作为 key,之后进行 put 覆盖默认值即可。

提供一个生成连续日期的方法

public static Set<String> getBetweenDate(String start, String end) {
  LocalDate startDate = LocalDate.parse(start);
  LocalDate endDate = LocalDate.parse(end);
  long between = ChronoUnit.DAYS.between(startDate, endDate);
  if (between < 1) {
    return Stream.of(start, end).collect(Collectors.toSet());
  }

  return Stream.iterate(startDate, e -> e.plusDays(1))
    .limit(between   1)
    .map(LocalDate::toString)
    .collect(Collectors.toSet());
}


public static void main(String[] args) {
  Set<String> days = BaseTest.getBetweenDate("2019-08-29", "2019-09-02");
  log.info("{}", days);
}

二、按照日期分组查询

原来的文章可能会造成误解,已更新掉
按照日期分组查询,遍历查询结果,覆盖第一步里的时间map的值即可。

结果如下:

 ------------ ----- 
|    date    | num |
 ------------ ----- 
| 2019-05-03 |  0  |
 ------------ ----- 
| 2019-05-04 |  0  |
 ------------ ----- 
| 2019-05-05 |  0  |
 ------------ ----- 
| 2019-05-06 |  2  |
 ------------ ----- 
| 2019-05-07 |  0  |
 ------------ ----- 
| 2019-05-08 |  2  |
 ------------ ----- 
| 2019-05-09 |  9  |
 ------------ ----- 

顺利达到了目的,收工!

参考

  • https://www.cnblogs.com/zhengguangpan/p/10308886.html

  • https://blog.csdn.net/Dai_Aixy/article/details/83144619

  • https://www.cnblogs.com/dennyzhangdd/p/8073181.html