统计查询

统计查询

  Oracle提供了一些函数来完成统计工作，这些函数称为组函数，组函数不同于前面介绍和使用的函数(单行函数)。组函数可以对分组的数据进行求和、求平均值等运算。组函数只能应用于SELECT子句、HAVING子句或ORDER BY子句中。组函数也可以称为统计函数。

分组函数中SUM和AVG只应用于数值型的列，MAX、MIN和COUNT可以应用于字符、数值和日期类型的列。组函数忽略列的空值。

  使用GROUPBY 从句可以对数据进行分组。所谓分组，就是按照列的相同内容，将记录划分成组，对组可以应用组函数。

  如果不使用分组，将对整个表或满足条件的记录应用组函数。

  在组函数中可使用DISTINCT或ALL关键字。ALL表示对所有非NULL值(可重复)进行运算(COUNT除外)。DISTINCT表示对每一个非NULL值，如果存在重复值，则组函数只运算一次。如果不指明上述关键字，默认为ALL。

1．统计查询 

  SELECTCOUNT(*) FROM emp;

  SELECTAVG(sal) FROM emp WHERE deptno=10;

  SELECTMAX(hiredate),MIN(hiredate) FROM emp;

  SELECTCOUNT( DISTINCT job) FROM emp;

2．分组统计

  SELECTSUM(sal) FROM emp GROUP BY job;

  SELECTjob,SUM(sal) FROM emp GROUP BY job;

  SELECTename,job,SUM(sal) FROM emp GROUP BY job;

3．多列分组统计

  SELECT   deptno, job, sum(sal)FROM empGROUP BY deptno, job;

4．分组统计结果限定

  对分组查询的结果进行过滤，要使用HAVING从句。HAVING从句过滤分组后的结果，它只能出现在GROUP BY从句之后，而WHERE从句要出现在GROUP BY从句之前。

SELECT   deptno, max(sal)FROM empGROUP BY deptno

  HAVING   max(sal)>=3000;

HAVING从句的限定条件中要出现组函数。如果同时使用WHERE条件，则WHERE条件在分组之前执行，HAVING条件在分组后执行。

5．分组统计结果排序

  可以使用ORDER BY从句对统计的结果进行排序，ORDER BY从句要出现在语句的最后。

  SELECT job 职务, SUM(sal)工资总和FROM emp GROUPBY job ORDERBY SUM(sal);

6．组函数的嵌套使用

  SELECTmax(avg(sal))FROM empGROUP BY deptno;

虽然在查询中有分组列，但在查询字段中不能出现分组列。如下的查询是错误的：

SELECTdeptno,max(avg(sal)) FROM emp GROUP BY deptno;

2.4.4分析函数OVER ()

Oracle 从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是:对于每个组返回多行，而聚合函数对于每个组只返回一行。

select t.empno,t.ename,sum(t.sal)from emp t ;

selectt.empno,t.ename,sum(t.sal)over() sum from emp t ;

over()、over(order by...)与over(partition by...)之间的区别

1.分析函数over()用在聚合函数（max(),sun()....）后面，可返回多行所有结果的聚合值；

2.over(order by...)用在聚合函数（max(),sun()....）后面，可返回根据排序结果进行统计到当前行的聚合值(即“连续”统计)； 

select t.empno,  t.deptno, t.ename,  t.sal, 

sum(t.sal) over(order by t.ename)sum  from emp t ;

3.over(partition by...)用在聚合函数（max(),sun()....）后面，可根据pratition by里指定的某一列来统计聚合值。

select t.empno,  t.deptno,  t.ename,   t.sal,  sum(t.sal) over(partition by t.deptno) sum  from emp t  

select t.empno,   t.deptno,  t.ename,  t.sal,  sum(t.sal) over(partition by t.deptno order by t.ename) sum  from emp t