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

• 你的需求用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，建议封装到存储过程里面会更好维护一些

• 不管上面两种方式，其实只是复杂度的转移，无非就是用更复杂的代码，或者更复杂的SQL去解决问题，但是要根据自己的业务场景选择的合适的就行。

Answer 2

按等分时间函数分组就好了

Answer 3

建议把24小时的查出来之后再进行分割，这样开销小一点，不必用sql做等分这种事情。

Answer 4

我之前的做法是这样：
1、根据这个时间段[xx, yy]和等分数N去创建一个时间区段临时表split_time_table
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
(创建这个临时表也是有技巧的，记得给id建主键)
2、将这个临时表去关联你要查的表T

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

上面的144是通过(yy-xx)/N计算得来的，上面的例子是按一天24小时10等分为例

这是一种方法，可以实践下，里面的一些变量是可以通过传入的参数动态计算的

Answer 5

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

Answer 6

说下oracle下的做法与思路吧。这个需求的难点在于等分10份，即使使用case when 依旧要：起始时间+等份时间段1，起始时间+等份时间段2，起始时间+等份时间段*3...
利用oracle的层级查询可以凭空造出1，2，3...10
代码如下：

SELECT LEVEL num
  FROM DUAL
CONNECT BY LEVEL <= 10

引用资料
再利用得到num进行乘以时间段(interval)事先算好

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