oracle统计函数

统计函数

count(*) --- 对于count()函数而言 可以传递三类内容: *, 字段, distinct字段

面试题:

请问count(*) count(字段),count(distinct 字段)有什么区别

如果列上存在null,那么null不统计,如果使用distinct , 那么列上如果有重复,重复的记录也不统计

使用count(*)是最方便的, 最好用count(字段)

在所有统计函数, 只有count()函数可以在表中没有任何数据的时候,依然返回内容 . count()永远会有数据返回

sum()

avg()

max()

min()

median() -----返回中间值 例如,现在有三个数字 2000,3000,2500,所谓的中间值就是2500----select median(sal) from emp

variance() -----返回方差 

stddev() -------返回标准差 select stddev(sal),variance(sal) from emp;

select max(sal) ,min(sal),avg(sal),round(avg(sal),2) from emp

oracel 中的函数是可以进行数据类型的互相转换,最早雇佣hiredate值一定是最小的,最晚雇佣的 hiredate 是最大的

select min(hiredate) ,max(hiredate) from emp

单字段分组查询

对于统计函数而言,单独使用的情况一定是有的

本章目标

掌握group by 字句的使用

掌握分组操作的使用限制

 范例: 统计出 每个部门 的人数

select deptno,count(empno) from emp group by deptno

范例 :统计出 每种职位 的最低和最高工资

    select job, min(sal) ,max(sal) from emp group by job

注意事项一:

    如果在一个查询之中不存在group by 字句 那么在select 字句之中只允许 出现统计函数 ,其他的任何字段不允许出现

 错误的程序:

select deptno ,count(empno) from emp

正确的程序:

select count(empno) from emp

注意事项二

在统计查询之中,存在了group by 字句 ,select字句之中只允许出现分组字段(group by之后定义的字段) 和统计函数,其他的

任何字段都不允许出现

范例:错误的程序:

select deptno , ename ,count(empno) from emp group by deptno

正确的程序:

select deptno ,count(empno) from emp group by deptno

注意事项三:

所有统计函数允许嵌套使用,但是一旦使用了嵌套的统计函数之后, select字句之中不允许在出现任何字段 ,包括分组字段

范例:求出每个部门平均工资最高的工资

错误代码:

select deptno max(avg(sal)) from emp group by deptno 此时因为select 字句之中存在了deptno的字段,所以这个时候出现了错误

正确代码

select max(avg(sal)) from emp group by deptno

范例 :查询每个部门的名称,部门人数,平均工资,平均服务年限

select d.dname, count (e.empno), round ( avg (e.sal)),  round ( avg (months_between( sysdate ,e.hiredate)/ 12 ), 2 ) avgyear from emp e,dept d where e.deptno = d.deptno group by d.dname

select d.dname, count (e.empno), round ( avg (e.sal)),  round ( avg (months_between( sysdate ,e.hiredate)/ 12 ), 2 ) avgyear from emp e,dept d where e.deptno (+)= d.deptno group by d.dname

字句顺序:

from --- where --- group by --- select ---- order by

范例 :查询公司各个工资等级雇员的数量,平均工资

select s.group, count(e.empno),avg(e.sal) from emp e, salgrade s  where emp.sal between s.losal and s.hisal  group by s.group

范例:统计出领取佣金与不领取佣金的雇员的平均工资,平均服务年限,雇员人数

错误代码:select comm round(avg(sal),2) avgsal, round(avg(months_between(sysdate,hiredate/12),2) avgyear,count(empno) count

from emp group by comm

正确代码:

select '领取佣金' , round ( avg (sal), 2 ) avgsal, round ( avg (months_between( sysdate ,hiredate)/ 12 ), 2 ) avgyear, count (empno) count

from emp where comm is not null

union

select '不领取佣金' , round ( avg (sal), 2 ) avgsal, round ( avg (months_between( sysdate ,hiredate)/ 12 ), 2 ) avgyear, count (empno) count

from emp where comm is   null

多字段统计查询

本章目标

在group by 中使用多个字段进行分组实现

既然可以在group by字句中出现多个分组字段,那么在select 字句里面也一定可以

定义多个分组字段的查询显示

范例: 现在要求查询出每个部门的详细信息(部门编号,名称,位置,平均工资,总工资,最高工资,最低工资,部门人数)

步骤一:

select d.deptno,d.dname,d.loc,e.empno,e.ename 

from emp e,dept d 

where e.deptno = d.deptno

三个列上的数据整体都在重复,既然列上的信息重复,就一定 具备了分组的条件

步骤二 使用多字段分组,同时实现统计信息

select d.deptno,d.dname,d.loc,count(e.empno) count , round(avg(e.sal),2) avg ,sum(e.sal) sum, max(e.sal) max ,min(e.sal) min

from emp e,dept d 

where e.deptno (+)= d.deptno

group by d.deptno,d.dname,d.loc

HAVING字句

本章目标

掌握having字句的使用

理解having与where字句的区别

having字句一定要与group by 子句一起使用

范例:查询出所有 

select ..

from ...

where  ...=... 

group by ...

having ...

order by...