MySQL中按時(shí)間統(tǒng)計(jì)每個(gè)小時(shí)的記錄數(shù)的示例分析

153次閱讀

共計(jì) 2352 個(gè)字符，預(yù)計(jì)需要花費(fèi) 6 分鐘才能閱讀完成。

這期內(nèi)容當(dāng)中丸趣 TV 小編將會(huì)給大家?guī)?lái)有關(guān) MySQL 中按時(shí)間統(tǒng)計(jì)每個(gè)小時(shí)的記錄數(shù)的示例分析，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

抽空在 stack overflow 轉(zhuǎn)了一圈, 看到一個(gè)有關(guān) SQL 的提問, 大概轉(zhuǎn)述一下:

構(gòu)造語(yǔ)句:

點(diǎn)擊 (此處) 折疊或打開

CREATE TABLE `st` (

`id` varchar(8) NOT NULL,

`time` timestamp NULL DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

insert into st values(F41 , 2016-08-27 00:25:58

insert into st values(F42 , 2016-08-27 01:15:03

insert into st values(F43 , 2016-08-27 02:14:30

insert into st values(F44 , 2016-08-27 02:24:12

insert into st values(F45 , 2016-08-27 03:05:46

insert into st values(F46 , 2016-08-27 04:08:07

insert into st values(F47 , 2016-08-27 05:10:35

insert into st values(F48 , 2016-08-27 07:12:32

insert into st values(F59 , 2016-08-27 08:21:19

insert into st values(F50 , 2016-08-27 09:19:26

insert into st values(F51 , 2016-08-27 11:01:56

insert into st values(F52 , 2016-08-27 12:09:38

insert into st values(F53 , 2016-08-27 13:42:48

insert into st values(F54 , 2016-08-27 14:47:26

insert into st values(F55 , 2016-08-27 15:24:12

insert into st values(F56 , 2016-08-27 16:22:05

insert into st values(F57 , 2016-08-27 18:20:47

insert into st values(F58 , 2016-08-27 19:11:09

insert into st values(F59 , 2016-08-27 19:41:47

insert into st values(F60 , 2016-08-27 19:57:06

insert into st values(F61 , 2016-08-27 20:12:45

insert into st values(F62 , 2016-08-27 21:55:41

insert into st values(F63 , 2016-08-27 22:17:38

insert into st values(F64 , 2016-08-27 23:15:17

需要按小時(shí)來(lái)統(tǒng)計(jì)記錄的數(shù)量, 結(jié)果期望如下:

點(diǎn)擊 (此處) 折疊或打開

ID time count

——————————————

1 00:00:00 1

2 01:00:00 1

3 02:00:00 1

4 03:00:00 2

5 04:00:00 1

6 05:00:00 1

7 06:00:00 1

8 07:00:00 0

9 08:00:00 1

10 09:00:00 1

11 10:00:00 1

12 11:00:00 0

13 12:00:00 1

14 13:00:00 1

15 14:00:00 1

16 15:00:00 1

17 16:00:00 1

18 17:00:00 1

19 18:00:00 0

20 19:00:00 1

21 20:00:00 3

22 21:00:00 1

23 22:00:00 1

24 23:00:00 1

這個(gè)問題有兩個(gè) 關(guān)鍵點(diǎn) :
1. 某個(gè)時(shí)間段, 不存在的記錄, 需要輸出 0;
2. 這個(gè)計(jì)數(shù), 是 less than time 的計(jì)數(shù)方法, 即 19:xx:xx 的記錄是記到 20:00:00 下面的, 而不是 19:00:00;

首先解決時(shí)間的提取問題;

利用 date_format 和 hour, 就可以把小時(shí)數(shù)提取出來(lái)了,
同時(shí)需要用 concat 去拼接一下字符串, 展示出 20:00:00 這種效果;

為了達(dá)成關(guān)鍵點(diǎn) 2 的要求, 需要稍微處理一下 hour 的結(jié)果, 使用 case when 來(lái)加工一下,
最后的語(yǔ)句如下:

點(diǎn)擊 (此處) 折疊或打開

select @rownum := @rownum + 1 AS ID,

concat((case when t.hour = 24 then 0 else t.hour end), :00:00 ) as time, count(*) as count

from (select id, hour(date_format(time, %H:%i:%s))+1 as hour from st) t,

(SELECT @rownum := 0) r

group by time order by ID

效果如下:

可以看到這個(gè)語(yǔ)句基本滿足了關(guān)鍵點(diǎn) 2 了:

現(xiàn)在還缺少關(guān)鍵點(diǎn) 1 的解決辦法: 某個(gè)時(shí)間段, 不存在的記錄, 需要輸出 0;

目前想到的辦法, 就是在表中提前插入占位用的無(wú)效數(shù)據(jù), 然后在 count 數(shù)量上 -1;
PS: _(:з」∠)_水平有限, 感覺靠 SQL 來(lái)做的話, 沒什么太好的思路了 ….

上述就是丸趣 TV 小編為大家分享的 MySQL 中按時(shí)間統(tǒng)計(jì)每個(gè)小時(shí)的記錄數(shù)的示例分析了，如果剛好有類似的疑惑，不妨參照上述分析進(jìn)行理解。如果想知道更多相關(guān)知識(shí)，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道。

正文完