MySQL如何按每3小时统计一天的数据?

我参考了很多代码,没有发现能够兼顾效率和准确率的代码,于是自己手写了一个能用的代码。虽然 sql 语句很长,但是能够查询的非常快,在给关键字段加了索引的情况下,查询两百万条数据不超过 2 秒;如果有大佬能有更简洁的代码,请写在评论区吧,我会在本篇文章后面加上的。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
SELECT NAME, IFNULL(count + count1, 0) AS `count`
FROM (
SELECT t1.NAME, t.count, t1.count1
FROM (
SELECT CASE
WHEN HOUR(DISCOVERTIME) BETWEEN 0 AND 3 THEN '00'
WHEN HOUR(DISCOVERTIME) BETWEEN 3 AND 6 THEN '03'
WHEN HOUR(DISCOVERTIME) BETWEEN 6 AND 9 THEN '06'
WHEN HOUR(DISCOVERTIME) BETWEEN 9 AND 12 THEN '09'
WHEN HOUR(DISCOVERTIME) BETWEEN 12 AND 15 THEN '12'
WHEN HOUR(DISCOVERTIME) BETWEEN 15 AND 18 THEN '15'
WHEN HOUR(DISCOVERTIME) BETWEEN 18 AND 21 THEN '18'
WHEN HOUR(DISCOVERTIME) BETWEEN 21 AND 23 THEN '21'
END AS `name`, IFNULL(COUNT(1), 0) AS `count`
FROM 表名
WHERE 1 = 1
-- 这里可以添加其他条件 比如 and flag=1 and type = 2 and isdelete = 0
-- AND to_Days(DISCOVERTIME) = to_days(now()) 这里不建议这么写,有to_days()这种运算的逻辑可以放到程序中去处理,没必要在数据库中去做运算,我们在开发过程中尽量避免这一点,可以参考下面这种写法,这个时间的处理可以参考我的另外一篇博客 http://www.fujiatian.com/post/24365.html
and DISCOVERTIME >= '2019-11-11 00:00:00' AND DISCOVERTIME < '2019-11-12 00:00:00'
GROUP BY CASE
WHEN HOUR(DISCOVERTIME) BETWEEN 0 AND 3 THEN 1
WHEN HOUR(DISCOVERTIME) BETWEEN 3 AND 6 THEN 2
WHEN HOUR(DISCOVERTIME) BETWEEN 6 AND 9 THEN 3
WHEN HOUR(DISCOVERTIME) BETWEEN 9 AND 12 THEN 4
WHEN HOUR(DISCOVERTIME) BETWEEN 12 AND 15 THEN 5
WHEN HOUR(DISCOVERTIME) BETWEEN 15 AND 18 THEN 6
WHEN HOUR(DISCOVERTIME) BETWEEN 18 AND 21 THEN 7
WHEN HOUR(DISCOVERTIME) BETWEEN 21 AND 23 THEN 8
END
) t
RIGHT JOIN (
SELECT '00' AS NAME, 0 AS count1
UNION
SELECT '03' AS NAME, 0 AS count1
UNION
SELECT '06' AS NAME, 0 AS count1
UNION
SELECT '09' AS NAME, 0 AS count1
UNION
SELECT '12' AS NAME, 0 AS count1
UNION
SELECT '15' AS NAME, 0 AS count1
UNION
SELECT '18' AS NAME, 0 AS count1
UNION
SELECT '21' AS NAME, 0 AS count1
) t1
ON t.NAME = t1.NAME
) t

本片完结!

您的打赏,是我创作的动力,谢谢支持。
0%