[PL/SQL] 求一条按时间段分组的SQL（千万级）

icecity1306 2014-09-10

展开全文

id                   date                      value
------------------------------------------------
1          2011-01-24 10:00:00                   1.2
1          2011-01-24 10:05:00                   1.5
1          2011-01-24 11:05:00                   2.0
1          2011-01-24 11:10:00                   30
.......................................
2          2011-01-24 07:00:00                   2.2
2          2011-01-24 08:05:00                   4.5
2          2011-01-24 08:10:00                   2.0
2          2011-01-24 11:10:00                   30
.................................

该表是分区表
要求按天，月，年统计平均值，数据到年底估计会过亿

最好能按ID进行行列转换，这样我后台能省不少事

我现在天的写法如下，才2千W左右就已经接近4秒了，要是按月和年平均的话估计就完蛋了，有没有更好的办法啊

SELECT id,date,
            MAX(DECODE(hh,0,value,NULL)) HH_0,
            MAX(DECODE(hh,1,value,NULL)) HH_1,
            MAX(DECODE(hh,2,value,NULL)) HH_2,
            MAX(DECODE(hh,3,value,NULL)) HH_3,
            MAX(DECODE(hh,4,value,NULL)) HH_4,
            MAX(DECODE(hh,5,value,NULL)) HH_5,
            MAX(DECODE(hh,6,value,NULL)) HH_6,
            MAX(DECODE(hh,7,value,NULL)) HH_7,
            MAX(DECODE(hh,8,value,NULL)) HH_8,
            MAX(DECODE(hh,9,value,NULL)) HH_9,
            MAX(DECODE(hh,10,value,NULL)) HH_10,
            MAX(DECODE(hh,11,value,NULL)) HH_11,
            MAX(DECODE(hh,12,value,NULL)) HH_12,
            MAX(DECODE(hh,13,value,NULL)) HH_13,
            MAX(DECODE(hh,14,value,NULL)) HH_14,
            MAX(DECODE(hh,15,value,NULL)) HH_15,
            MAX(DECODE(hh,16,value,NULL)) HH_16,
            MAX(DECODE(hh,17,value,NULL)) HH_17,
            MAX(DECODE(hh,18,value,NULL)) HH_18,
            MAX(DECODE(hh,19,value,NULL)) HH_19,
            MAX(DECODE(hh,20,value,NULL)) HH_20,
            MAX(DECODE(hh,21,value,NULL)) HH_21,
            MAX(DECODE(hh,22,value,NULL)) HH_22,
            MAX(DECODE(hh,23,value,NULL)) HH_23
            (
SELECT
  TO_CHAR(date,'yyyy-mm-dd') date_,
  TO_CHAR(date,'HH24') hh,
  id,
  round(avg(value),2)
FROM tt
where date>= to_date('2011-04-25','yyyy-mm-dd') and date < to_date('2011-04-26','yyyy-mm-dd')
GROUP BY id,TO_CHAR(date,'yyyy-mm-dd'),TO_CHAR(date,'HH24')
order by id
)

对数据的实时要求不高，建议用物化视图放到晚上去运行，可以在sql加上parallel提示。

原帖由 laoyao999 于 2011-5-31 13:41 发表
看看是不是你要的效果
select id,extract(year from cdate) year,extract(month from cdate) month,extract(day from cdate) day ,avg(value)
from tt
group by grouping sets((id,extract(year from cdate)),(id,extract(month from cdate)),(id,extract(day from cdate)));