Back-end development - How to write sql statements for mysql statistics by time segment?

Question

My database table structure is as follows:

The fields are ID, NUM1, NUM2, TIME1, TIME2, MONEY.

I want to count the number of IDs at intervals of 15 minutes. The effect is the same as the following sql statement.

SELECT  
  sum(CASE when TIME1 >= '2014-02-27 8:00:00' and TIME1 < '2014-02-27 8:15:00' then 1 else 0 end)   AS '1',  
  sum(CASE when TIME1 >= '2014-02-27 8:15:00' AND TIME1 < '2014-02-27 8:30:00' then 1 else 0 end)   AS '2',  
  sum(CASE when TIME1 >= '2014-02-27 8:30:00' AND TIME1 < '2014-02-27 8:45:00' then 1 else 0 end)   AS '3',  
  sum(CASE when TIME1 >= '2014-02-27 8:45:00' AND TIME1 < '2014-02-27 9:00:00' then 1 else 0 end)   AS '4',  
  sum(CASE when TIME1 >= '2014-02-27 9:00:00' and TIME1 < '2014-02-27 9:15:00' then 1 else 0 end)   AS '5'  
from dealdata;

But there is a lot of repetitive code like the above, and if I change it to 30 minutes, I will have to change a lot, so I asked the SQL master to see if there is any good method.

-----------------------------------Dividing line---------- ————————————————————————

Thank you Comrade arm for your help, now I can query it

count(id) is the number of people entering the station
But in some time periods, there are no people entering the station at all, so there are no people in some time periods, such as 5:00 -- 5:15
If you want to The time slots of people who have not entered the station are completed to 0. What should we do?

Answer 1

Thank you for the invitation, you can arrange it vertically, as follows

SELECT count(id)
from dealdata
where timestampdiff(minute,'2014-02-27 9:15:00',`TIME1`)<0 and timestampdiff(minute,'2014-02-27 8:00:00',`TIME1`)>=0
group by floor(timestampdiff(minute,'2014-02-27 8:00:00',`TIME1`)/15)

Where to limit the time period, divide it by the time interval you need in group by.
I don’t know why you got an error. It may be related to the mysql version. Mine is mysql5.6

SELECT count(*),
floor(timestampdiff(minute,'2017-1-18 16:00:00',`time`)/30),
date_add('2017-1-18 16:00:00',interval 30*floor(timestampdiff(minute,'2017-1-18 16:00:00',`time`)/30) MINUTE)
FROM 我是马赛克.我是马赛克
group by floor(timestampdiff(minute,'2017-1-18 16:00:00',`time`)/30)

The result

After checking it, it ran quite fast, less than 0.3s

Answer 2

The number of columns is variable, so many variables are defined

Answer 3

Script drives MySQL and customizes a variable. Maybe there is a better way, so I answered casually.