统计查询
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