前言

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

问题

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

疏忽大意

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

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

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

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

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

1
2
3
4
5
6
7
8
9
+------------+-----+
| date | num |
+------------+-----+
| 2019-05-06 | 2 |
+------------+-----+
| 2019-05-08 | 2 |
+------------+-----+
| 2019-05-09 | 9 |
+------------+-----+

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

修正查询数据

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

一、拿到所有日期

2019年09月12日更新

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

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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);
}

因为入参有两个,分别是起始日期与结束日期,需要拿到两个日期之间的所有日期,从而按照日期分组。

1
2
3
4
5
6
7
8
select * from (SELECT DATE_FORMAT(DATE_SUB('2019-05-09', INTERVAL xc day), '%Y-%m-%d') as day
FROM (SELECT @xi := @xi + 1 as xc
from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc3,
(SELECT @xi := -1) xc0
) as t) as t2
where t2.`day` > '2019-05-02' and t2.`day` <= '2019-05-09'

结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
+------------+
| DAY |
+------------+
| |
| 2019-05-09 |
| |
| 2019-05-08 |
| |
| 2019-05-07 |
| |
| 2019-05-06 |
| |
| 2019-05-05 |
| |
| 2019-05-04 |
| |
| 2019-05-03 |
+------------+

二、联合用户表查询

拿到所有日期后作为一张左表,join 连接用户表,再进行分组查询

1
2
3
4
5
6
7
8
9
10
11
12
select t2.`day` as `date`, count(t.id) as num
from (SELECT DATE_FORMAT(DATE_SUB('2019-05-09', INTERVAL xc day), '%Y-%m-%d') as day
FROM (
SELECT @xi := @xi + 1 as xc
from (SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc1,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc2,
(SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5) xc3,
(SELECT @xi := -1) xc0
) as x1x2x) t2
left join t_user t on date(t.create_time) = t2.day
where t2.`day` > '2019-05-02' and t2.`day` <= '2019-05-09'
group by date ORDER BY date asc;

结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
+------------+-----+
| 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 |
+------------+-----+

顺利达到了目的,收工!

参考