Oracle--7、sql统计函数与分组统计查询

1、统计函数

在之前使用过了一个COUNT()函数，这个函数的主要功能是进行表中数据量的统计。而这个函数就属于一种统计函数，那么对于统计函数常见的有5个：COUNT()个数、SUM()求和、AVG()平均值、MAX()最大值、MIN()最小值。

范例：统计所有雇员的人数，支付的总工资，平均工资，最高工资，最低工资

SELECT COUNT(*),SUM(sal),AVG(sal),MAX(sal),MIN(sal) FROM emp ;

范例：统计公司支付的总年薪与平均工资

SELELCT SUM((sal + NVL(comm,0))*12),AVG((sal + NVL(comm,0))*12) FROM emp ;

范例：求出公司最早雇佣雇员的日期和最晚雇佣雇员的日期

SELECT MAX(hiredate),MIN(hiredate) FROM emp ；

面试题：请解释“COUNT(*)”、“COUNT(字段)”、“COUNT(DISTINCT 字段)”的区别？

SELECT COUNT(*),COUNT(empno),COUNT(comm),COUNT(DISTINCT job) FROM emp ;

·COUNT(*):会实际的统计出表中的数据量；

·COUNT(字段)：

|-如果统计的字段上不包含有null，那么与COUNT(*)的结果相同；

|-如果统计字段上包含有null，null不参与统计；

·COUNT(DISTINCT 字段)：消除掉重复数据后统计；

 

2、分组统计GroupBy（难点）

在讲解具体的分组统计操作之前必须要先解决一个问题，什么情况下有可能分组？

·例如：全班，男生一组，女生一组 ；

实际上所谓的分组，指的是某些群体具备共同的特征。现在回到emp表，可以发现job和deptno中存在有重复的数据，那么就证明这两个字段上可以进行分组。但是一定要记住，实际上一条记录也可以分组，只不过这类的做法是没有意义的。如果想要实现统计查询,那么可以采用如下的语法完成。

SELECT [DISTINCT] * | 列名称[别名],列名称[别名],… | 统计函数  è 4、确定列 FROM 数据表[别名], 数据表[别名],…  è 1、数据来源 [WHERE 条件(s)]  è 2、过滤行 [GROUP BY 分组字段,分组字段,…]  è 3、分组 [ORDER BY 字段[ASC|DESC], 字段[ASC|DESC],…]  è 5、排序

范例：按照职位分组，统计出每个职位的平均工资、最高和最低工资、人数

SELECT job,AVG(sal),MAX(sal),MIN(sal),COUNT(*) FROM emp GROUP BY job ;

范例：按照部门编号分组，统计出每个部门的人数、平均工资、平均服务年限

SELECT deptno，COUNT(*)，AVG(sal),AVG(MONTHS_BETWEEN(SYSDATE,hiredate)/12) FROM emp GROUP BY deptno ;

提示：关于统计查询的几个重要说明：

以上的代码只是根据基础语法实现了统计的操作，但是在整个操作之中还会存在有三个限制：

·限制一：统计函数单独使用的时候（没有GROUP BY子句）,SELECT子句之中只能够出现统计函数，不能够出现其他字段。

正确的代码：	SELECT COUNT(empno) FROM emp ;
错误的代码：	SELECT COUNT(empno),ename FROM emp ;

错误代码分析：COUNT(*)返回一行数据，ename返回14行数据，不能对应上，所以错误。

·限制二：使用统计查询时（存在GROUP BY 子句），SELECT子句之中只允许出现统计函数与分组字段，其他的任何字段都不允许出现。

正确的代码：	SELECT deptno,COUNT(empno) FROM emp GROUP BY deptno ;
错误的代码：	SELECT deptno,COUNT(empno),ename FROM emp GROUP BY deptno ;

·限制三：统计函数在分组之中可以嵌套使用，但是嵌套之后的统计查询之中，SELECT子句中不允许再出现任何的字段，包括分组字段也不能出现。

正确的代码：	SELECT MAX(COUNT(empno)) FROM emp GROUP BY deptno ;
错误的代码：	SELECT deptno,MAX(COUNT(empno)) FROM emp GROUP BY deptno ;

在整个分组操作之中，以上的三个限制可以说是最难理解的，一定要搞清楚。

以上的操作都是针对于单张表一个字段的分组，那么通过之前给出的语法格式可以发现，实际上分组也可以同时设置多个字段，那么就要求这多个字段必须同时重复。

范例（开始）：查询出每个部门的名称，部门人数、平均工资

·确定所需要的数据表：

|-dept表：找到部门名称

|-emp表：统计部门人数、平均工资

·确定已知的关联字段：

|-雇员和部门关联：emp.deptno = dept.deptno

第一步：先不考虑分组问题，换个角度，实现查询，查询出每个部门名称、雇员编号、工资

SELECT d.dname,e.empno,e.sal FROM emp e,dept d WHERE e.deptno = d.deptno ;

第二步：，在以上的查询结果中部门名称是重复的，但是这个时候并不是直接针对于一张实体表进行的分组操作，而是针对于一张临时数据表（查询结果）进行分组操作。由于GROUP BY是在WHERE之后执行，那么可以直接分组。

SELECT d.dname,COUNT(e.empno),AVG(e.sal) FROM emp e,dept d WHERE e.deptno = d.deptno GROUP BY d.dname ;

第三步：现在既然已经引入了dept表，那么就表示存在有4个部门的数据，而此时只有3个部门的数据，所以需要进行外连接进行40部门的数据显示。

SELECT d.dname,COUNT(e.empno),AVG(e.sal) FROM emp e,dept d WHERE e.deptno(+) = d.deptno GROUP BY d.dname ;

范例（完结）：查询出每个部门的编号，名称，位置，部门人数、平均工资

·确定所需要的数据表：

|-dept表：找到部门名称，编号，位置

|-emp表：统计部门人数、平均工资

·确定已知的关联字段：

|-雇员和部门关联：emp.deptno = dept.deptno

第一步：首先不去考虑分组，先实现多表查询，查询部门编号、名称、位置，雇员编号、工资。

SELECT d.deptno,d.dname,d.loc,e.empno,e.sal FROM emp e,dept d WHERE e.deptno = d.deptno ;

第二步：现在发现部门的数据三个字段都是在整体重复的，那么直接针对于三个字段实现分组。

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal) FROM emp e,dept d WHERE e.deptno = d.deptno GROUP BY d.deptno,d.dname,d.loc ;

一个部门对应一个编号，对应一个部门名称，对应一个位置

加上外连接，显示40部门数据。

SELECT d.deptno,d.dname,d.loc,COUNT(e.empno),AVG(e.sal) FROM emp e,dept d WHERE e.deptno(+) = d.deptno GROUP BY d.deptno,d.dname,d.loc ;

3、Having字句

范例：按照职位分组，统计每个职位的平均工资，要求显示的是平均工资高于2000的职位信息。

现在唯一可以想到的条件过滤只有WHERE子句一个。于是编写出如下代码：

SELECT job,AVG(sal) FROM emp e WHERE AVG(sal)>2000 GROUP BY job ;

WHERE AVG(sal)>2000       * 第 3 行出现错误: ORA-00934: 此处不允许使用分组函数

但是在执行之后发现了错误的提示信息，意思是WHERE子句里面不能够使用统计函数。而且通过之前的分析也应该知道，WHERE是在GROUP BY之前使用的，而此时的条件明显是在GROUP BY之后的过滤，所以此时只能够使用HAVING子句完成：语法如下：

SELECT [DISTINCT] * | 列名称[别名],列名称[别名],… | 统计函数è 5、确定列 FROM 数据表[别名], 数据表[别名],…    è 1、数据来源 [WHERE 条件(s)]    è 2、过滤行 [GROUP BY 分组字段,分组字段,…]    è 3、分组 [HAVING 分组后过滤]    è 4、分组后过滤 [ORDER BY 字段[ASC|DESC], 字段[ASC|DESC],…]    è 6、排序

范例：使用HAVING修改程序

SELECT job,AVG(sal) FROM emp e GROUP BY job HAVING AVG(sal)>2000 ;

解释：关于WHERE和HAVING的区别？

·WHERE子句：是在分组前使用，而且不能够使用统计函数进行验证，经过WHERE筛选后的数据才可以参加分组；

·HAVING子句：必须结合GROUP BY子句一起出现，是在分组后的过滤，可以使用统计函数。

思考题一：统计公司每个工资等级的人数、平均工资

·确定要使用的数据表：

|-salgrade表：工资等级

|-emp表：统计人数、平均工资

·确定已知的关联字段：

|-工资与工资等级：emp.sal BETWEEN salgrade.losal AND salgrade.hisal

第一步：查询出工资等级，以及每个雇员的编号、工资，直接实现多表查询

SELECT s.grade,e.empno,e.sal FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal ;

第二步：针对于临时数据表之中的数据进行分组

SELECT s.grade,COUNT(e.empno),AVG(sal) FROM emp e,salgrade s WHERE e.sal BETWEEN s.losal AND s.hisal GROUP BY s.grade ;

思考题二：统计出公司领取佣金和不领取佣金的人数、平均工资

现在很容易联想到，直接针对于comm字段分组，意识编写出如下语句；

SELECT comm,COUNT(empno),AVG(sal) FROM emp GROUP BY comm ;

所以此时这个查询结果发现并不理想，那么不如换个思路。

第一步：统计出所有领取佣金的人数、平均工资

SELECT ‘领取佣金’,COUNT(empno),AVG(sal) FROM emp WHERE comm IS NOT NULL ;

第二步：统计出所有不领取佣金的人数、平均工资

SELECT ‘不领取佣金’,COUNT(empno),AVG(sal) FROM emp WHERE comm IS NULL ;

第三步：以上两个查询返回的结构完全相同，那么就将其变为一个结果，使用UNION

SELECT ‘领取佣金’,COUNT(empno),AVG(sal) FROM emp WHERE comm IS NOT NULL UNION SELECT ‘不领取佣金’,COUNT(empno),AVG(sal) FROM emp WHERE comm IS NULL ;

很多时候都可以直接进行分组，但是在进行分组的时候必须保证数据列上有重复，但是如果是针对于一些条件（如18岁以上，18岁以下），那么只能够利用以上的方式完成。

---

---