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

這期內(nèi)容當(dāng)中小編將會(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的提問(wèn), 大概轉(zhuǎn)述一下:

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

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

1. CREATE TABLE `st` (
   

2.   `id` varchar(8) NOT NULL,
   

3.   `time` timestamp NULL DEFAULT NULL,
   

4.   PRIMARY KEY (`id`)
   

5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
   

6. 
   

7. insert into st values('F41','2016-08-27 00:25:58');
   

8. insert into st values('F42','2016-08-27 01:15:03');
   

9. insert into st values('F43','2016-08-27 02:14:30');
   

10. insert into st values('F44','2016-08-27 02:24:12');
    

11. insert into st values('F45','2016-08-27 03:05:46');
    

12. insert into st values('F46','2016-08-27 04:08:07');
    

13. insert into st values('F47','2016-08-27 05:10:35');
    

14. insert into st values('F48','2016-08-27 07:12:32');
    

15. insert into st values('F59','2016-08-27 08:21:19');
    

16. insert into st values('F50','2016-08-27 09:19:26');
    

17. insert into st values('F51','2016-08-27 11:01:56');
    

18. insert into st values('F52','2016-08-27 12:09:38');
    

19. insert into st values('F53','2016-08-27 13:42:48');
    

20. insert into st values('F54','2016-08-27 14:47:26');
    

21. insert into st values('F55','2016-08-27 15:24:12');
    

22. insert into st values('F56','2016-08-27 16:22:05');
    

23. insert into st values('F57','2016-08-27 18:20:47');
    

24. insert into st values('F58','2016-08-27 19:11:09');
    

25. insert into st values('F59','2016-08-27 19:41:47');
    

26. insert into st values('F60','2016-08-27 19:57:06');
    

27. insert into st values('F61','2016-08-27 20:12:45');
    

28. insert into st values('F62','2016-08-27 21:55:41');
    

29. insert into st values('F63','2016-08-27 22:17:38');
    

30. insert into st values('F64','2016-08-27 23:15:17');

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

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

1. ID   time   count
   

2. ------------------------------------------
   

3. 1  00:00:00  1
   

4. 2  01:00:00  1
   

5. 3  02:00:00  1
   

6. 4  03:00:00  2
   

7. 5  04:00:00  1
   

8. 6  05:00:00  1
   

9. 7  06:00:00  1
   

10. 8  07:00:00  0
    

11. 9  08:00:00  1
    

12. 10      09:00:00  1
    

13. 11      10:00:00  1
    

14. 12      11:00:00  0
    

15. 13      12:00:00  1
    

16. 14      13:00:00  1
    

17. 15      14:00:00  1
    

18. 16      15:00:00  1
    

19. 17      16:00:00  1
    

20. 18      17:00:00  1
    

21. 19      18:00:00  0
    

22. 20      19:00:00  1
    

23. 21      20:00:00  3
    

24. 22      21:00:00  1
    

25. 23      22:00:00  1
    

26. 24      23:00:00  1

這個(gè)問(wèn)題有兩個(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í)間的提取問(wèn)題;

利用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)擊(此處)折疊或打開(kāi)

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

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

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

4.      (SELECT @rownum := 0) r
   

5. 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: _(:з」∠)_水平有限, 感覺(jué)靠SQL來(lái)做的話, 沒(méi)什么太好的思路了....

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