Shulou(Shulou.com)05/31 Report--

In this issue, the editor will bring you an example analysis of the number of records per hour in MySQL. The article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

When I took the time to go around stack overflow, I saw a question about SQL and roughly paraphrased it:

Construction statement:

Click (here) to collapse or open

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 ('F41century Magazine' 2016-08-27 0015-27 0014 2514 58')

Insert into st values ('F42F4214012016-08-27 01VOL15 VOL03')

Insert into st values ('F43 recording, 2016-08-27 02-14-30')

Insert into st values ('F44 recording, 2016-08-27 02, 24-12')

Insert into st values ('F45 minute Journal 2016-08-27 03 05purl 46')

Insert into st values ('F46 minute Journal 2016-08-27 04bloc 08purl 07')

Insert into st values ('F47 recording, 2016-08-27 05-10-15-35')

Insert into st values ('F48 recording and recording 2016-08-27 07-12-12-32')

Insert into st values ('F59 academic Journal 2016-08-27 08-27 08-21-19')

Insert into st values ('F50 recording, 2016-08-27 09, 1914, 26')

Insert into st values ('F51handwriting Journal 2016-08-27 1114 01purl 56')

Insert into st values ('F52parallel Journal 2016-08-27 12-12-14 09-14 38')

Insert into st values ('F53 recording, 2016-08-27 13-12-42-48')

Insert into st values ('F54 handsome Journal 2016-08-27 14-14-47-14-26')

Insert into st values ('F55 recording, 2016-08-27 15-15-24-12')

Insert into st values ('F56 recording, 2016-08-27 16-22-22-05')

Insert into st values ('F57 minute Journal 2016-08-27 18-18-20-47')

Insert into st values ('F58 academic Journal 2016-08-27 1914 1114 09')

Insert into st values ('F59 academic Journal 2016-08-27 1914 41 purl 47')

Insert into st values ('F60 handsome, 2016-08-27, 1914, 57, 14, 06')

Insert into st values ('F61Chinese Magazine' 2016-08-27 20-12-14-45')

Insert into st values ('F62recording Journal 2016-August-27, 2016-08-27, 21-15, 55-14, 41')

Insert into st values ('F63 recording, 2016-08-27 22-22, 17-14, 38')

Insert into st values ('F64 recording Journal 2016-08-27 23-15-15-17')

The number of records needs to be counted by hour, and the results are expected to be as follows:

Click (here) to collapse or open

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

There are two "key points" to this question:

1. For a certain period of time, a record that does not exist needs to be output 0

two。 This count is the less than time counting method, that is, the 19:xx:xx record is recorded below 20:00:00, not 19:00:00.

First of all, solve the problem of time extraction.

With date_format and hour, the hours can be extracted.

At the same time, you need to use concat to concatenate the strings to show the effect of 20:00:00

In order to meet the requirements of key point 2, you need to process the results of hour a little bit and use case when to process them.

The final statement is as follows:

Click (here) to collapse or open

Select @ rownum: = @ rownum + 1 AS ID

Concat ((case when t.hour = 24 then 0 else t.hour end),': 00then 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

The effect is as follows:

You can see that this statement basically satisfies key point 2:

There is still a lack of a solution to key point 1: for a certain period of time, records that do not exist need to output 0

At present, the solution is to insert invalid data for placeholder in the table in advance, and then-1 on the number of count

PS: (: "∠") _ level is limited. I feel that if I rely on SQL to do it, I don't have any good ideas.

The above is the analysis of the example of counting records per hour by time in MySQL shared by Xiaobian. If you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.