17,078
社区成员
发帖
与我相关
我的任务
分享
select distinct d1+level-1 d1,d2+level-1 d2
from
(
select to_date('2013-05-20 '||lpad(8-8+level,2,'0')||':00:00','yyyy-mm-dd hh24:mi:ss') d1,
to_date('2013-05-20 '||lpad(8+level-1,2,'0')||':00:00','yyyy-mm-dd hh24:mi:ss') d2
from dual
connect by level <= 12- 8 + 1 --8和12为时间范围
)
connect by level <= 3 --3为天数
with t1 as
(
select 1 c1,to_date('2013-05-20 01:01:01','yyyy-mm-dd hh24:mi:ss') c2,100 c3 from dual union all
select 2 c1,to_date('2013-05-20 02:01:01','yyyy-mm-dd hh24:mi:ss') c2,200 c3 from dual union all
select 3 c1,to_date('2013-05-20 03:01:01','yyyy-mm-dd hh24:mi:ss') c2,300 c3 from dual union all
select 4 c1,to_date('2013-05-20 04:01:01','yyyy-mm-dd hh24:mi:ss') c2,400 c3 from dual union all
select 5 c1,to_date('2013-05-20 05:01:01','yyyy-mm-dd hh24:mi:ss') c2,500 c3 from dual union all
select 6 c1,to_date('2013-05-20 06:01:01','yyyy-mm-dd hh24:mi:ss') c2,600 c3 from dual union all
select 7 c1,to_date('2013-05-20 07:01:01','yyyy-mm-dd hh24:mi:ss') c2,700 c3 from dual union all
select 8 c1,to_date('2013-05-20 08:01:01','yyyy-mm-dd hh24:mi:ss') c2,800 c3 from dual union all
select 9 c1,to_date('2013-05-20 09:01:01','yyyy-mm-dd hh24:mi:ss') c2,900 c3 from dual union all
select 10 c1,to_date('2013-05-20 10:01:01','yyyy-mm-dd hh24:mi:ss') c2,1000 c3 from dual union all
select 11 c1,to_date('2013-05-20 11:01:01','yyyy-mm-dd hh24:mi:ss') c2,1100 c3 from dual union all
select 12 c1,to_date('2013-05-20 12:01:01','yyyy-mm-dd hh24:mi:ss') c2,1200 c3 from dual
)
select to_char(d1,'yyyy-mm-dd') dt,to_char(d1,'hh24')||'-'||to_char(d2,'hh24') d_scope,avg(c3) c3
from
(
select to_date('2013-05-20 '||lpad(8-8+level,2,'0')||':00:00','yyyy-mm-dd hh24:mi:ss') d1,
to_date('2013-05-20 '||lpad(8+level-1,2,'0')||':00:00','yyyy-mm-dd hh24:mi:ss') d2
from dual
connect by level <= 12- 8 + 1 --8为起始小时 12为结束小时 都可以作为参数
) t left join t1 on c2 between d1 and d2
group by to_char(d1,'yyyy-mm-dd'),to_char(d1,'hh24')||'-'||to_char(d2,'hh24')
order by to_char(d1,'yyyy-mm-dd'),to_char(d1,'hh24')||'-'||to_char(d2,'hh24')
dt d_scope c3
------------------------------------------
1 2013-05-20 01-08 400
2 2013-05-20 02-09 500
3 2013-05-20 03-10 600
4 2013-05-20 04-11 700
5 2013-05-20 05-12 800
with t1 as
(
select 1 c1,to_date('2013-05-20 01:01:01','yyyy-mm-dd hh24:mi:ss') c2,100 c3 from dual union all
select 2 c1,to_date('2013-05-20 02:01:01','yyyy-mm-dd hh24:mi:ss') c2,200 c3 from dual union all
select 3 c1,to_date('2013-05-20 03:01:01','yyyy-mm-dd hh24:mi:ss') c2,300 c3 from dual union all
select 4 c1,to_date('2013-05-20 04:01:01','yyyy-mm-dd hh24:mi:ss') c2,400 c3 from dual union all
select 5 c1,to_date('2013-05-20 05:01:01','yyyy-mm-dd hh24:mi:ss') c2,500 c3 from dual union all
select 6 c1,to_date('2013-05-20 06:01:01','yyyy-mm-dd hh24:mi:ss') c2,600 c3 from dual union all
select 7 c1,to_date('2013-05-20 07:01:01','yyyy-mm-dd hh24:mi:ss') c2,700 c3 from dual union all
select 8 c1,to_date('2013-05-20 08:01:01','yyyy-mm-dd hh24:mi:ss') c2,800 c3 from dual union all
select 9 c1,to_date('2013-05-20 09:01:01','yyyy-mm-dd hh24:mi:ss') c2,900 c3 from dual union all
select 10 c1,to_date('2013-05-20 10:01:01','yyyy-mm-dd hh24:mi:ss') c2,1000 c3 from dual
)