第1个回答 推荐于2017-09-20
SQL> select to_char(dtime,'yyyymmdd hh24:mi:ss') dtime,v1,v2 from test order by 1;
DTIME V1 V2
----------------- ------ ------
20100929 10:22:13 200 200
20100929 10:32:13 200 200
20100929 15:32:06 200 200
20100929 15:42:06 200 200
20100929 16:22:00 300 300
20100929 16:32:00 200 300
20100929 17:01:51 100 100
20100929 17:11:51 100 100
20100929 17:51:45 100 100
20100929 18:01:45 100 100
20100929 18:21:33 0 100
20100929 18:31:33 100 100
12 rows selected
SQL> select to_char((trunc(sysdate)+trunc((dtime-trunc(sysdate))*24*60/30)*30/60/24),'hh24:mi')||'-'||to_char((trunc(sysdate)+trunc((dtime-trunc(sysdate))*24*60/30+1)*30/60/24),'hh24:mi') period, avg(v1),sum(v2),count(*) "ROWS" from test where trunc(dtime)=to_date(&day,'yyyymmdd') group by to_char((trunc(sysdate)+trunc((dtime-trunc(sysdate))*24*60/30)*30/60/24),'hh24:mi')||'-'||to_char((trunc(sysdate)+trunc((dtime-trunc(sysdate))*24*60/30+1)*30/60/24),'hh24:mi') order by 1;
PERIOD AVG(V1) SUM(V2) ROWS
----------- ---------- ---------- ----------
10:00-10:30 200 200 1
10:30-11:00 200 200 1
15:30-16:00 200 400 2
16:00-16:30 300 300 1
16:30-17:00 200 300 1
17:00-17:30 100 200 2
17:30-18:00 100 100 1
18:00-18:30 50 200 2
18:30-19:00 100 100 1
9 rows selected
SQL>本回答被提问者采纳