我要汇总一个端口在五分钟内的数据流入流量,请问一下汇总语句怎么写?数据库里有端口的原始数据
解决方案 »
- 貌似有些难度,关于通过生成的数字进行排序的
- sql高手进来看看如何用sql实现舍位平衡?(带测试数据)
- table block number与TABLE ACCESS FULL实际时间相差很大
- 帮忙求一个SQL语句
- 为什么删除纪录后,性能反而下降?
- 这个 sql语句怎么写(急)
- 请说明FLOOR、ROUND、CEIL、TRUNC的区别
- 基于oracle 数据库的程序客户端的问题?
- oracle sql developer如何远程连接数据库,如何设置外网跳转
- kettle IO错误 connetion reset怎么破,求教,急。
- Connect By ..不指定Start With,结果这样怎么办??
- 请教各位PL/SQL中单步调试ORACLE存储过程的问题,多谢
with t1 as
(
select to_date('2012-07-01 10:00:00','yyyy-mm-dd hh24:mi:ss') c1,100 c2 from dual
union all
select to_date('2012-07-01 10:00:30','yyyy-mm-dd hh24:mi:ss') c1,100 c2 from dual
union all
select to_date('2012-07-01 10:05:05','yyyy-mm-dd hh24:mi:ss') c1,100 c2 from dual
union all
select to_date('2012-07-01 10:08:00','yyyy-mm-dd hh24:mi:ss') c1,100 c2 from dual
union all
select to_date('2012-07-01 10:10:11','yyyy-mm-dd hh24:mi:ss') c1,100 c2 from dual
union all
select to_date('2012-07-01 10:22:00','yyyy-mm-dd hh24:mi:ss') c1,100 c2 from dual
union all
select to_date('2012-07-01 10:26:00','yyyy-mm-dd hh24:mi:ss') c1,100 c2 from dual
)select t2.c1,t2.c2,nvl(sum(t1.c2),0) c3
from
(select distinct m+(level-1)/288 c1, m+(level)/288 c2
from t1,(select min(c1) m,max(c1) n from t1) t2
connect by level <= round((n-m)*288,0)+1
order by c1
) t2 left join t1 on t1.c1 between t2.c1 and t2.c2
group by t2.c1,t2.c2
order by t2.c1
c1 c2 c3
------------------------------------------------------------------
1 2012/7/1 10:00:00 2012/7/1 10:05:00 200
2 2012/7/1 10:05:00 2012/7/1 10:10:00 200
3 2012/7/1 10:10:00 2012/7/1 10:15:00 100
4 2012/7/1 10:15:00 2012/7/1 10:20:00 0
5 2012/7/1 10:20:00 2012/7/1 10:25:00 100
6 2012/7/1 10:25:00 2012/7/1 10:30:00 100