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搞定！ ！ ！ ！ 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🎜 🎜🎜🎜注意時間的減法要做好，推薦使用預存程序封裝上面的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

依樓層分組（mod（時間，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