现有视图如下:
cwar trdt num
A 2015-03-01 08:05:27 3
A 2015-03-01 08:25:37 2
A 2015-03-01 08:33:15 3
A 2015-03-01 09:04:09 5
A 2015-03-01 10:17:06 4
A 2015-03-01 10:22:09 1
A 2015-03-01 11:39:00 3
请问大侠,同一日期下每两小时对num进行分组统计要如何写,先行谢过!
...全文
8062打赏收藏
oracle 怎样实现每2小时分组对数据统计
现有视图如下: cwar trdt num A 2015-03-01 08:05:27 3 A 2015-03-01 08:25:37 2 A 2015-03-01 08:33:15 3 A 2015-03-01 09:04:09 5 A 2015-03-01 10:17:06 4 A 2015-03-01 10:22:09 1 A 2015-03-01 11:39:00 3 请问大侠,同一日期下每两小时对num进行分组统计要如何写,先行谢过!
with t as
( select 'A' as cwar, to_date('2015-03-01 08:05:27','yyyy-mm-dd hh24:mi:ss') as trdt, 3 as num from dual
union all select 'A', to_date('2015-03-01 08:25:37','yyyy-mm-dd hh24:mi:ss'), 2 from dual
union all select 'A', to_date('2015-03-01 08:33:15','yyyy-mm-dd hh24:mi:ss'), 3 from dual
union all select 'A', to_date('2015-03-01 09:04:09','yyyy-mm-dd hh24:mi:ss'), 5 from dual
union all select 'A', to_date('2015-03-01 10:17:06','yyyy-mm-dd hh24:mi:ss'), 4 from dual
union all select 'A', to_date('2015-03-01 10:22:09','yyyy-mm-dd hh24:mi:ss'), 1 from dual
union all select 'A', to_date('2015-03-01 11:39:00','yyyy-mm-dd hh24:mi:ss'), 3 from dual )
select t.cwar, b.trdt, sum(t.num) as num
from t,
( select level as rn, to_date('2015-03-01','yyyy-mm-dd') + (level - 1)/ 12 as trdt from dual connect by level <= 12 ) a,
( select level as rn, to_date('2015-03-01','yyyy-mm-dd') + (level)/ 12 as trdt from dual connect by level <= 12 ) b
where b.rn = a.rn
and t.trdt >= a.trdt
and t.trdt < b.trdt
group by t.cwar, b.trdt
order by 1,2