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(*)的结果相同;

|-如果统计字段上包含有nullnull不参与统计;

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

 



2、分组统计GroupBy难点

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

·例如:全班,男生一组,女生一组 ;

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

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 deptnoCOUNT(*)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 ;

??12.png


第二步:,在以上的查询结果中部门名称是重复的,但是这个时候并不是直接针对于一张实体表进行的分组操作,而是针对于一张临时数据表(查询结果)进行分组操作。由于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 ;

??13.png

第三步:现在既然已经引入了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 ;

??14.png

范例(完结):查询出每个部门的编号,名称,位置,部门人数、平均工资

·确定所需要的数据表:

|-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 ;

??15.png


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

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 ;

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

??16.png

加上外连接,显示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 ;

??17.png





3Having字句

范例:按照职位分组,统计每个职位的平均工资,要求显示的是平均工资高于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 ;

解释:关于WHEREHAVING的区别?

??18.png


·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 ;



??19.png

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

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 ;

??20.png

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

现在很容易联想到,直接针对于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岁以下),那么只能够利用以上的方式完成。





oracle

2020.11.18 20:51

https://www.meihaocloud.com.com/227.html , 欢迎转载,请在文章页标出原文连接 !


Copyright © 2020 千夕网 联系站长

粤公网安备 44030302001408号 粤ICP备19099833号-1