Les05-使用分组函数来对数据进行聚集.ppt

五.使用分组函数来对数据进行聚集,目标,完成本节课的学习后，应当完成下面的目标: 识记常用的分组函数。 描述分组函数的用处。 使用GROUP BY 子句对元组进行分组 使用HAVING 子句来筛选分组,什么是分组函数?,分组函数对一组数据行进行操作，并对每个组得出一个结果.,EMP,“EMP 表中最 大的SAL值”,DEPTNO SAL - - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250,MAX(SAL) - 5000,分组函数的类型,AVG COUNT MAX MIN STDDEV SUM VARIANCE,使用分组函数,SELECT column, group_function(column) FROM table WHERE condition GROUP BY column ORDER BY column;,使用AVG和SUM 函数,AVG(SAL) MAX(SAL) MIN(SAL) SUM(SAL) - - - - 1400 1600 1250 5600,对于数字类型的数据，可以使用 AVG和SUM 函数.,SQL SELECT AVG(sal), MAX(sal), 2 MIN(sal), SUM(sal) 3 FROM emp 4 WHERE job LIKE SALES%;,使用 MIN和MAX 函数,可以对许多类型的数据使用MIN和MAX函数.,SQL SELECT MIN(hiredate), MAX(hiredate) 2 FROM emp;,MIN(HIRED MAX(HIRED - - 17-DEC-80 12-JAN-83,使用 COUNT函数,COUNT(*) - 6,SQL SELECT COUNT(*) 2 FROM emp 3 WHERE deptno = 30;,COUNT(*) 返回查询出的总行数.,使用 COUNT函数,COUNT(expr) 返回expr值非空的行的数.,SQL SELECT COUNT(comm) 2 FROM emp 3 WHERE deptno = 30;,COUNT(COMM) - 4,分组函数和空值,分组函数忽略了行中所有的空行.,SQL SELECT AVG(comm) 2 FROM emp;,AVG(COMM) - 550,分组函数和NVL函数的结合,NVL函数使得分组函数可以处理空值.,SQL SELECT AVG(NVL(comm,0) 2 FROM emp;,AVG(NVL(COMM,0) - 157.14286,创建聚组数据,EMP,“EMP表中 每个部门 的平均 工资”,2916.6667,2175,1566.6667,DEPTNO SAL - - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250,DEPTNO AVG(SAL) - - 10 2916.6667 20 2175 30 1566.6667,创建分组数据: GROUP BY 子句,SELECT column, group_function(column) FROM table WHERE condition GROUP BY group_by_expression ORDER BY column;,将表中的数据行用GROUP 语句分为几个组.,使用 GROUP BY子句,在SELECT语句中，没有使用分组函数的列必须在GROUP By子句中.,SQL SELECT deptno, AVG(sal) 2 FROM emp 3 GROUP BY deptno;,DEPTNO AVG(SAL) - - 10 2916.6667 20 2175 30 1566.6667,使用 GROUP BY子句,GROUP BY后面的列可以不出现在 SELECT链中.,SQL SELECT AVG(sal) 2 FROM emp 3 GROUP BY deptno;,AVG(SAL) - 2916.6667 2175 1566.6667,根据多个列进行分组,EMP,“每个部门中， 每种工作的 工资总和”,DEPTNO JOB SAL - - - 10 MANAGER 2450 10 PRESIDENT 5000 10 CLERK 1300 20 CLERK 800 20 CLERK 1100 20 ANALYST 3000 20 ANALYST 3000 20 MANAGER 2975 30 SALESMAN 1600 30 MANAGER 2850 30 SALESMAN 1250 30 CLERK 950 30 SALESMAN 1500 30 SALESMAN 1250,JOB SUM(SAL) - - CLERK 1300 MANAGER 2450 PRESIDENT 5000 ANALYST 6000 CLERK 1900 MANAGER 2975 CLERK 950 MANAGER 2850 SALESMAN 5600,DEPTNO - 10 10 10 20 20 20 30 30 30,在多个列上使用 GROUP BY 子句,SQL SELECT deptno, job, sum(sal) 2 FROM emp 3 GROUP BY deptno, job;,DEPTNO JOB SUM(SAL) - - - 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 20 ANALYST 6000 20 CLERK 1900 . 9 rows selected.,分组函数的误用,在SELECT 语句中，任何不在聚组函数中出现的列，必须在 GROUP BY 子句中.,SQL SELECT deptno, COUNT(ename) 2 FROM emp;,SELECT deptno, COUNT(ename) * ERROR at line 1: ORA-00937: not a single-group group function,Deptno列没有在 GROUP BY 子句中出现,分组函数的误用,不能在 WHERE子句中对列做出限定. 使用 HAVING 子句来限定分组.,SQL SELECT deptno, AVG(sal) 2 FROM emp 3 WHERE AVG(sal) 2000 4 GROUP BY deptno;,WHERE AVG(sal) 2000 * ERROR at line 3: ORA-00934: group function is not allowed here,不能用 WHERE 子句对 分组做出限定,排除聚组结果,组中最高薪水大 于$2900的部门号”,EMP,DEPTNO SAL - - 10 2450 10 5000 10 1300 20 800 20 1100 20 3000 20 3000 20 2975 30 1600 30 2850 30 1250 30 950 30 1500 30 1250,DEPTNO MAX(SAL) - - 10 5000 20 3000,用 HAVING 子句排除分组结果,用 HAVING 子句来限定分组 行被分组了. 使用了分组函数. 符合 HAVING子句条件的组将被显示出来.,SELECT column, group_function FROM table WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BY column;,使用 HAVING 子句,SQL SELECT deptno, max(sal) 2 FROM emp 3 GROUP BY deptno 4 HAVING max(sal)2900;,DEPTNO MAX(SAL) - - 10 5000 20 3000,使用 HAVING子句,SQL SELECT job, SUM(sal) PAYROLL 2 FROM emp 3 WHERE job NOT LIKE SALES% 4 GROUP BY job 6 ORDER BY SUM(sal);,JOB PAYROLL - - ANALYST 6000 MANAGER 8275,5 HAVING SUM(sal)5000,分组函数的嵌套使用,SQL SELECT max(avg(sal) 2 FROM emp 3 GROUP BY deptno;,MAX(AVG(SAL) - 2916.6667,显示最大的平均工资.,总结,SELECT column, group_function(column) FROM table WHERE condition GROUP BY group_by_expression HAVING group_condition ORDER BY column;,子聚的顺序: WHERE 子句 GROUP BY 子句 HAVING 子句,select sum(sum(L2) Total ,sum(sum(decode(L1,80,L2,0) “1980“ ,sum(sum(decode(