小問題-用分析函式求平均時間段的統計數.txt

在qq群裡看到一個問題，覺得很好解決。但坐下來認真分析，卻發現自己不會。

問題：
有如下的一個表
SQL> select * from test order by time;

TIME          COUNT
-------- ----------
00:00:02          6
00:00:04          9
00:00:07          7
00:00:09          2
00:00:09          3
00:00:10          5
00:00:12          1
00:00:15          8
00:00:18          4

需要統計每五秒count的平均值。(半開半閉區間，即0秒包括[0,5)秒)
結果應該為：

TIME     avg(COUNT)
-------- ----------
00:00:00 7.5
00:00:05 3.3
00:00:10 3
00:00:15 6

想了一晚上，現在只知道得出平均值的一個笨方法：
floor((to_date(time,'hh24:mi:ss')-trunc(to_date(time,'hh24:mi:ss')))*86400/5)

得到結果：
SQL> select floor((to_date(time,'hh24:mi:ss')-trunc(to_date(time,'hh24:mi:ss')))*86400/5),time from test order by

time;

FLOOR((TO_DATE(TIME,'HH24:MI:S TIME
------------------------------ --------
                             0 00:00:02
                             0 00:00:04
                             1 00:00:07
                             1 00:00:09
                             1 00:00:09
                             2 00:00:10
                             2 00:00:12
                             3 00:00:15
                             3 00:00:18

9 rows selected

 

異常艱難地得到了各個時間所在的分組：
SQL> select floor((to_date(time,'hh24:mi:ss')-trunc(to_date(time,'hh24:mi:ss')))*86400/5)/86400*5+trunc(to_date

(time,'hh24:mi:ss')),time,count from test order by time;

FLOOR((TO_DATE(TIME,'HH24:MI:S TIME          COUNT
------------------------------ -------- ----------
2012-12-1                      00:00:02          6
2012-12-1                      00:00:04          9
2012-12-1 0:00:05              00:00:07          7
2012-12-1 0:00:05              00:00:09          2
2012-12-1 0:00:05              00:00:09          3
2012-12-1 0:00:10              00:00:10          5
2012-12-1 0:00:10              00:00:12          1
2012-12-1 0:00:15              00:00:15          8
2012-12-1 0:00:15              00:00:18          4

根據以前學過的分析函式，over子句，用floor這個表示式作為分割槽條件應該是合適的，於是嘗試：
SQL> select distinct floor((to_date(time,'hh24:mi:ss')-trunc(to_date(time,'hh24:mi:ss')))*86400/5)/86400*5+trunc

(to_date(time,'hh24:mi:ss')),
avg(count) over (partition by floor((to_date(time,'hh24:mi:ss')-trunc(to_date(time,'hh24:mi:ss')))

*86400/5)/86400*5+trunc(to_date(time,'hh24:mi:ss')))
 from test;

FLOOR((TO_DATE(TIME,'HH24:MI:S AVG(COUNT)OVER(PARTITIONBYFLOO
------------------------------ ------------------------------
2012-12-1 0:00:05                                           4
2012-12-1 0:00:10                                           3
2012-12-1 0:00:15                                           6
2012-12-1                                                 7.5

看著都覺得噁心，不過總算有個結果了。以後有時間再研究。

當前存在的不足主要是時間太不靈活了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26451536/viewspace-753261/，如需轉載，請註明出處，否則將追究法律責任。