09 Oracle基础 - 分组统计

分组统计本节知识点统计函数使用分组统计查询的实现对分组的数据过滤统计函数（分组函数）之前学过一个COUNT()函数，这个函数的主要作用是统计一张数据表中的数据量的个数，但与之功能类似的常用函数一共有五个：COUNT()统计个数：根据表中的实际数量返回结果SUM()求和：针对数字的统计AVG()平均值：针对数字的统计MIN()最小值：各种数据类型都支持MAX()最大值：各种数据类型都支持这五个函数所有数据库都支持。测试5个函数SELECTCOUNT(*)人数,AVG(SAL)员工平均工资,SUM(SAL)每月总支出,MAX(sal)最高工资,MIN(sal)最低工资FROMemp;这些统计函数是允许和其它函数嵌套使用的。范例：统计出公司的平均雇佣年限范例：求出最早和最晚的雇佣日期统计函数以上的几个操作函数，在表中没有数据的时候，只有COUNT函数会返回数据，其它函数都返回为null范例：统计bonus中的记录COUNT函数该函数有三种操作形式：COUNT(*)：可以准确的返回表中的全部记录数COUNT(字段)：统计不为null的所有数据量COUNT(DISTINCT字段)：消除重复数据之后的统计结果COUNT函数范例统计查询一Selectcount(*),count(empno),count(comm)fromemp;统计查询二Selectcount(distinctjob)fromemp;面试题：请解释COUNT(*)、COUNT(字段)、COUNT(DISTINCT字段)的区别？？？分组统计什么情况下可能分组？例如：拔河、上厕所、玩LOL游戏、打CF等，这就是分组。分组的前提是存在有重复但是允许单独一行记录进行分组。如果要进行分组则使用GROUPBY子句，分组统计语法格式为：④SELECT[DISTINCT]*|列[别名],列[别名],……①FROM表名称[别名],表名称[别名],……②[WHERE限定条件]③[GROUPBY分组字段,分组字段,分组字段……]⑤[ORDERBY排序列名[ASC|DESC],排序列名[ASC|DESC],……]写多表也可以使用分组，所以分组在where之后分组统计范例：根据部门编号分组，查询出每个部门的编号、人数、平均工资。Selectdeptno,count(*),avg(sal)FromempGroupbydeptno范例：根据职位分组，统计出每个职位的人数，最低工资与最高工资Selectjob,count(*),min(sal),max(sal)FromempGroupbyjob分组统计约定条件实际上GROUPBY子句之所以使用麻烦，是因为分组的时候有三个约定条件：1、如果查询不使用GROUPBY子句，那么SELECT子句中只允许出现统计函数，其它任何字段不允许出现。错误代码：selectempno,count(*)fromemp;正确代码：selectcount(*)fromemp;分组统计约定条件2、如果查询中使用GROUPBY子句，那么SELECT子句中只允许出现分组字段、统计函数，其它任何字段都不允许出现。错误代码：selectempno,job,count(*)fromempgroupbyjob;正确代码：selectjob,count(*)fromempgroupbyjob;分组统计约定条件3、统计函数允许嵌套，但是嵌套之后的SELECT子句里面只允许出现嵌套函数，而不允许出现任何字段包括分组字段。错误代码：selectdeptno,max(count(*))fromempgroupbydeptno;正确代码：selectmax(count(*))fromempgroupbydeptno;说明：嵌套其实是每一次查询出来的值形成一个临时表，再在这个临时表上进行统计；第二次统计时，没有groupby，因此字段不能出现在统计字段中。多表查询与分组统计（重点）对于GROUPBY子句而言是在WHERE子句之后执行的，所以在使用时可以进行限定查询，也可以进行多表查询。多表查询与分组统计范例：查询出每个部门的名称、部门人数、平均工资【分析】确定要使用的数据表：Dept表：部门名称Emp表：统计数据确定已知的关联字段：雇员与部门：emp.deptno=dept.deptno确定统计字段：count(emp.empno)、avg(emp.sal)确定分组字段：部门名称多表查询与分组统计第一步：查询出每个部门的名称、雇员编号(count(empno))、基本工资(avg(sal))。SELECTd.dname,e.empno,e.salFROMempe,deptdWHEREe.deptno=d.deptno多表查询与分组统计第二步：此时的查询结果中对于部门名称部分出了重复的内容，按照分组来讲，只要出了数据的重复就可以进行分组，只是此时的分组是针对临时表（查询结果）。已经确定了dname上存在有重复记录，那么就直接针对dname进行分组。SELECTd.dname,count(e.empno),avg(e.sal)FROMempe,deptdWHEREe.deptno=d.deptnoGROUPBYd.dname会发现有问题？？？？多表查询与分组统计第三步：在dept表中实际上存在有四个部门信息，而此时的要求也是统计所有的总计名称，如果发现在数据不完整，立刻使用外连接。SELECTd.dname,count(e.empno),avg(e.sal)FROMempe,deptdWHEREe.deptno(+)=d.deptnoGROUPBYd.dname;如果觉得平均值为空不好看，可以使用处理空函数(NVL函数)多表查询与分组统计范例：查询每个部门编号、名称、位置、部门人数、平均工资。确定要使用的数据表：Dept表：编号、名称、位置Emp表：统计信息确定已知的关联字段：雇员与部门：emp.deptno=dept.deptno多表查询与分组统计第一步：查询每个部门的编号、名称、位置、雇员编号count(*)、工资avg(sal)SELECTd.deptno,d.dname,d.loc,e.empno,e.salFROMempe,deptdWHEREe.deptno(+)=d.deptno;多表查询与分组统计第二步：发现有三个列(DEPT表)同时发生重复，那么就可以进行多字段分组。SELECTd.deptno,d.dname,d.loc,count(e.empno),avg(e.sal)FROMempe,deptdWHEREe.deptno(+)=d.deptno;GROUPBYd.deptno,d.dname,d.loc;多字段分组：指多个字段的数据都重复。要注意分组的约定条件。HAVING子句引例：查询每个职位的名称、职位的平均工资，但职位的平均工资高于2000的记录。分析：第一步、按照职位先进行分组，同时统计出每个职位的平均工资第二步、只显示平均工资高于2000的职位信息HAVING子句这样大家首先想到的是WHERE子句进行筛选行。因此有以下代码：Selectjob,avg(sal)FromempWhereavg(sal)>2000Groupbyjob;提示错误：Whereavg(sal)>2000，此处不允许使用分组函数。这是为什么？？？？HAVING子句这个错误，我们要从语句的执行顺序开始分析：因为GROUPBY子句是在WHERE子句之后执行，此时执行WHERE子句时还没有进行分组了，就自然无法进行统计。所以在这样的情况下，就必须使用另外一个子句完成，HAVING子句。可以知道：WHERE子句不能使用统计函数HAVING子句分组过滤语法格式：⑤SELECT[DISTINCT]*|列[别名],列[别名],……①FROM表名称[别名],表名称[别名],……②[WHERE限定条件]③[GROUPBY分组字段,分组字段,分组字段……]④[HAVING分组过滤]⑥[ORDERBY排序列名[ASC|DESC],排序列名[ASC|DESC],……]HAVING子句修改引例：查询每个职位的名称、职位的平均工资，但职位的平均工资高于2000的记录。Selectjob,avg(sal)FromempGroupbyjobHavingavg(sal)>2000HAVING是在GROUPBY分组之后才执行的筛选，在HAVING里可以直接使用统计函数，必须与GROUPBY子句一起使用。HAVING和WHERE子句区别WHERE子句是在GROUPBY分组之前进行记录筛选，指的是选出那些可以参与分组的数据，并且WHERE子句中不允许使用统计函数（分组函数）。HAVING子句是在GROUPBY分组之后执行，可以使用统计函数（分组函数），必须与GROUPBY子句一起使用。分组案例(拆分问题)通过两个具体的安全进行分组统计的操作总结。范例：显示所有非销售人员的工作名称以及从事同一工作的雇员的月工资的总和，并且要求满足从事同一工作雇员的工资的合计大于5000，显示的结果按照月工资的合计的升序排列。分组案例(拆分问题)【分析】第一步：查询所有非销售人员的信息，WHERE进行限定查询SELECT*FORMempWHEREjob<>’SALESMAN’分组案例(拆分问题)第二步：按照职位进行分组，再求出月工资的总支出SELECTjob,SUM(sal)FORMempWHEREjob<>’SALESMAN’GROUPBYjob;分组案例(拆分问题)第三步：分组后的数据进行再次的筛选，使用HAVING子句。SELECTjob,SUM(sal)FORMempWHEREjob<>’SALESMAN’GROUPBYjobHAVINGSUM(sal)>5000;分组案例(拆分问题)第四步：按照月工资的合计升序排列。SELECTjob,SUM(sal)sumFORMempWHEREjob<>’SALESMAN’GROUPBYjobHAVINGSUM(sal)>5000;ORDERBYsum;复杂的把题拆开写，这样就可以解决问题。分组案例(拆分问题)范例：统计所有领取佣金和不领取佣金人员的人数、平均工资。【误区：自然会想到，按照comm分组】错误代码：SELECTcomm,count(*),avg(sal)FROMempGROUPBYcomm;发现问题：每一种值都进行了统计。结论：使用GROUPBY的时候会把每一个种子值当做一个分组。分组案例（有null值分组）现在找个思路，把问题拆分：【分析】第一步，查询出所有领取佣金的雇员的人数、平均工资。直接使用WHERE子句，都不会GROUPBY。SELECT‘领取佣金’info,COUNT(*),AVG(sal)FROMempWHEREcommISNOTNULL;分组案例（有null值分组）第二步，查询出所有不领取佣金的雇员的人数、平均工资。直接使用WHERE子句，都不会GROUPBY。SEL