如题,现有一个表A (recordtime date,
hostname varchar2(20),
指标a number);A中的记录是每分钟一条的,如:
2011-11-19 12:01:02 WAP01 3
2011-11-19 12:02:02 WAP01 2
2011-11-19 12:03:02 WAP01 1
2011-11-19 12:04:02 WAP01 3
2011-11-19 12:05:02 WAP01 2
2011-11-19 12:06:02 WAP01 1
2011-11-19 12:07:02 WAP01 3
2011-11-19 12:08:02 WAP01 2
2011-11-19 12:09:02 WAP01 1
2011-11-19 12:10:02 WAP01 3
2011-11-19 12:11:02 WAP01 2
2011-11-19 12:12:02 WAP01 1
现要求,将这些记录每五分钟间隔汇总成一条记录,期望:
2011-11-19 12:05:00 WAP01 11
2011-11-19 12:10:00 WAP01 10
2011-11-19 12:15:00 WAP01 sum(11分-15分的指标a)
求助SQL或什么方法
hostname varchar2(20),
指标a number);A中的记录是每分钟一条的,如:
2011-11-19 12:01:02 WAP01 3
2011-11-19 12:02:02 WAP01 2
2011-11-19 12:03:02 WAP01 1
2011-11-19 12:04:02 WAP01 3
2011-11-19 12:05:02 WAP01 2
2011-11-19 12:06:02 WAP01 1
2011-11-19 12:07:02 WAP01 3
2011-11-19 12:08:02 WAP01 2
2011-11-19 12:09:02 WAP01 1
2011-11-19 12:10:02 WAP01 3
2011-11-19 12:11:02 WAP01 2
2011-11-19 12:12:02 WAP01 1
现要求,将这些记录每五分钟间隔汇总成一条记录,期望:
2011-11-19 12:05:00 WAP01 11
2011-11-19 12:10:00 WAP01 10
2011-11-19 12:15:00 WAP01 sum(11分-15分的指标a)
求助SQL或什么方法
,hostname
,sum(a)
from tb
group by to_date(to_char(recordtime,'yyyy-mm-dd hh24')||':'||ceil(to_char(recordtime,'mi')/5)*5,'yyyy-mm-dd hh24:mi')
;