mysql - 如何写一条SQL，分时段统计结果？

Question

比如时间是过去24小时，将这过去24小时分成10等分，一条SQL查询出10条记录，也就是每个时间段的统计结果。
一条SQL可以实现上述的需求吗？谢谢:-)

我想到一种办法，将这10条SQL用union拼接起来，请问一下还有其他的办法吗？

我的SQL是：

select sum(num) from T where time >= xx and time <= yy;

现在是要将[xx, yy]时间分成10等分，返回各个时间段的统计结果

Answer 1

One SQL by Case When

• Isn’t it possible to use case when to meet your needs? One SQL to get it done! ! ! ! case when不就是可以做到的么？一条SQL搞定！！！！

select sum(case when time >= xx and time < xx + (yy - xx) / 10 then 1 else 0 end) as time_1, sum(case when time >= xx + (yy - xx) / 10 and time < xx + 2 * (yy - xx) / 10 then 1 else 0 end) as time_2, sum(case when time >= xx + 2 * (yy - xx) / 10 and time < xx + 3 * (yy - xx) / 10 then 1 else 0 end) as time_3,
..
sum(case when time >= xx + (i -1) * (yy - xx) / 10 and time < xx + i * (yy - xx) / 10 then 1 else 0 end) as time_i
.., 
sum(case when time >= xx + 9 * (yy - xx) / 10 and time <= yy then 1 else 0 end) as time_10 from test where time >= xx and time <= yy;

Better maintain

• 注意时间的减法要做好，推荐使用存储过程封装上面的sql，接收startTime，endTime和等分数三个参数，计算各个区间的起始和结束时间(注意我的sql里面采用的是前闭后开，但是最后一个等分是要等于结束时间的！！！要不然就会丢了一个等于endTime的值未统计)然后把值传入sql执行就好了。这样每次调用只需要调用一个存储过程即可

你是不是真的需要这样做？

其实我不清楚你具体的业务场景，但是我会说分情况做选择

• 如果你在startTime和endTime之间查出来的数据不大，或者数据库里面总量就不大，那么我建议二楼的@Paul_Ding的说法。

• 但是如果你的数据库里面的记录很大，或者经常根据startTime和endTime查出几十万的记录，那么推荐你使用一条SQL，这样不用每次都把这么多的记录通过网络再传到代码里面去处理，不过这种要做简单编程和计算的SQL，建议封装到存储过程里面会更好维护一些

• 不管上面两种方式，其实只是复杂度

rrreee

Better maintain🎜 🎜🎜🎜Pay attention to the subtraction of time. It is recommended to use stored procedure to encapsulate the above sql and receive startTime, endTime and equal fraction three Parameters, calculate the start and end time of each interval (Note that my sql uses front closing and back opening, but the last equal division must be equal to the end time!!! Otherwise, one will be lost! The value equal to endTime is not counted) and then the value is passed into sql for execution. In this way, you only need to call one stored procedure for each call🎜🎜🎜 🎜Do you really need to do this? 🎜 🎜Actually, I don’t know your specific business scenario, but I will make a choice based on the situation🎜 🎜 🎜🎜If the data you find between startTime and endTime is not large, or the total amount in the database is not large, then I suggest what @Paul_Ding said on the second floor . 🎜🎜 🎜🎜But if the records in your database are very large, or you often find hundreds of thousands of records based on startTime and endTime, then it is recommended that you use a SQL, so there is no need Every time, so many records are transmitted through the network to the code for processing. However, for this kind of SQL that requires simple programming and calculation, it is recommended to encapsulate it into stored procedure for better maintenance🎜🎜 🎜🎜 Regardless of the above two methods, it is actually just a transfer of complexity. It is nothing more than using more complex code or more complex SQL to solve the problem, but you must choose the appropriate one according to your own business scenario. That’s it. 🎜🎜 🎜

Answer 2

Just group by equal time function

Answer 3

It is recommended to split the 24-hour data after checking it out. This way, the cost is smaller and there is no need to use SQL to do such things.

Answer 4

This is what I did before:
1. Create a time segment temporary table split_time_table based on this time period [xx, yy] and the equal fraction N
id start_time
1 2017-01-12 00:00:00
2 2017-01-12 02:24:00
3 2017-01-12 04:48:00

...

10 2017-01-12 21:36:00
(There are tricks to create this temporary table, remember to create a primary key for id)
2. Associate this temporary table with the table T you want to check

select t1.date_time,sum(t2.num) 
  from split_time_table t1 
    left join T t2 on t2.time>=t1.start_time and t2.time<DATE_ADD(t1.start_time,INTERVAL 144 MINUTE)
  group by t1.id

The above 144 is calculated by (yy-xx)/N. The above example is based on 10 equal parts of 24 hours a day

This is a method that can be practiced. Some variables in it can be dynamically calculated by passing in parameters

Answer 5

GROUP BY FLOOR(mod(time, 86400)/8640)

Answer 6

Let’s talk about the methods and ideas under Oracle. The difficulty of this requirement is to divide it into 10 equal parts. Even if case when is used, it still needs: starting time + equal time period 1, starting time + equal time period 2, starting time + equal time period * 3. ..
Using Oracle's level query, you can create 1, 2, 3...10 out of thin air
The code is as follows:

SELECT LEVEL num
  FROM DUAL
CONNECT BY LEVEL <= 10

Citation information
Reuse the obtained num and multiply it by the time period (interval) and calculate it in advance

select sum(case when begintime+(level-1)*interval<time and time<begintime+level*interval then num else 0 end)
from dual, table
connect by level<=10