Oracle -- 分组

如果按照时间段分组显示，首先要了解一下level,connectby,oracle时间的加减。

1.level

select level from dual connect by level <=10;

2.关于connect by可以看

http://www.cnblogs.com/johnnyking39/articles/1155497.html

3.Oracle时间的加减

----结果减一天,也就24小时  
select sysdate -1 from dual;
-----结果减去半天，也就12小时
select sysdate-(1/2) from dual;
-----一天按每6个小时分组，结果即减去6小时
select sysdate-(1/4) from dual; 
-----结果减去1 小时  
select sysdate-(1/24) from dual; 
----结果减去5分钟  
select sysdate-((1/24)/12) from dual; 
---结果是10间隔1天的时间  
select sydate-(level-1) from dual connect by level<=10;

例：

select dTimes
  from (select sysdate - (level - 1) * 5 dTimes from dual connect by level <= 10) d
 group by d.dTimes;




其中sysdate - (level - 1) * 5得到的是相隔5天的时间

group by d.dTimes相隔时间分组查询
4.带有rollup和cube的group by
select deptno,job ,sum(sal) from emp group by deptno,job;
rollup 按分组的第一个列进行统计和最后的小计    
cube 按分组的所有列的进行统计和最后的小计
select deptno,job ,sum(sal) from emp group by rollup(deptno,job); 
select deptno,job ,sum(sal) from emp group by cube(deptno,job);
5.stddev：返回一组值的标准偏差

select deptno,stddev(sal) from empgroup by deptno;

variance
返回一组值的方差差
select deptno,variance(sal) fromemp group by deptno;