小问题-用分析函数求平均时间段的统计数.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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26451536/viewspace-753261/