搜索
第五章用组函数合计数据Copyright©OracleCorporation,2001.Allrightsreserved.用组函数合计数据进度表：时间主题35分钟讲演40分钟练习75分钟总共中国科学院西安网络中心©2005OracleSQL入门5-1第五章用组函数合计数据5-2Copyright©OracleCorporation,2001.Allrightsreserved.目标完成本课后,您应当能够执行下列操作：•识别可用的组函数•描述组函数的使用•用GROUPBY子句分组数据•用HAVING子句包含或排除分组的行课程目标本课进一步讲解函数。本课的重点是关于获得行聚集的概要信息，例如平均值。课程中将讨论怎样聚合表中的行到较小的集合中，怎样指定用于行聚合的搜索条件。中国科学院西安网络中心©2005OracleSQL入门5-2第五章用组函数合计数据5-3Copyright©OracleCorporation,2001.Allrightsreserved.什么是组函数?组函数操作行集，给出每组的结果EMPLOYEES在EMPLOYEES表中的最高薪水…组函数不象单行函数，组函数对行的集合进行操作，对每组给出一个结果。这些集合可能是整个表或者是表分成的组。中国科学院西安网络中心©2005OracleSQL入门5-3第五章用组函数合计数据5-4Copyright©OracleCorporation,2001.Allrightsreserved.组函数的类型•AVG平均值•COUNT计数•MAX最大值•MIN最小值•STDDEV标准差•SUM合计•VARIANCE方差组函数(续)每个函数接收一个参数，下面的表确定你可以在语法中使用的选项：函数说明AVG([DISTINCT|ALL]n)n的平均值，忽略空值COUNT({*|[DISTINCT|ALL]expr})行数，expr求除了空计算(用*计数所有行，包括重复和带空值的行)MAX([DISTINCT|ALL]expr)expr的昀大值，忽略空值MIN([DISTINCT|ALL]expr)expr的昀小值，忽略空值STDDEV([DISTINCT|ALL]x)n的标准差，忽略空值SUM([DISTINCT|ALL]n)合计n的值，忽略空值VARIANCE([DISTINCT|ALL]x)n的方差，忽略空值中国科学院西安网络中心©2005OracleSQL入门5-4第五章用组函数合计数据5-5Copyright©OracleCorporation,2001.Allrightsreserved.SELECT[column,]group_function(column),...FROMtable[WHEREcondition][GROUPBYcolumn][ORDERBYcolumn];组函数的语法使用组函数的原则DISTINCT使得函数只考虑不重复的值；ALL使得函数考虑每个值，包括重复值。默认值是ALL，因此不需要指定。用于函数的参数的数据类型可以是CHAR、VARCHAR2、NUMBER或DATE。所有组函数忽略空值。为了用一个值代替空值，用NVL、NVL2或COALESCE函数。当使用GROUPBY子句时，Oracle服务器隐式以升序排序结果集。为了覆盖该默认顺序，DESC可以被用于ORDERBY子句。教师注释强调用DISTINCT和组函数忽略空值。ALL是默认。中国科学院西安网络中心©2005OracleSQL入门5-5第五章用组函数合计数据5-6Copyright©OracleCorporation,2001.Allrightsreserved.SELECTAVG(salary),MAX(salary),MIN(salary),SUM(salary)FROMemployeesWHEREjob_idLIKE'%REP%';使用AVG和SUM函数你可以使用AVG和SUM用于数字数据组函数你可以使用AVG、SUM、MIN和MAX函数用于数字数据，幻灯片中例子显示平均、昀高、昀低和月薪水的和。中国科学院西安网络中心©2005OracleSQL入门5-6第五章用组函数合计数据5-7Copyright©OracleCorporation,2001.Allrightsreserved.使用MIN和MAX函数你可以使用MIN和MAX用于任何数据类型SELECTMIN(hire_date),MAX(hire_date)FROMemployees;组函数(续)你可以用MAX和MIN函数于任意数据类型。幻灯片中的例子显示昀年少的和做年老的雇员。下面的例子显示按字母排序，雇员的排在昀前面和昀后面的名字。SELECTMIN(last_name),MAX(last_name)FROMemployees;注：AVG、SUM、VARIANCE和STDDEV函数只能被用于数字数据类型。中国科学院西安网络中心©2005OracleSQL入门5-7第五章用组函数合计数据5-8Copyright©OracleCorporation,2001.Allrightsreserved.SELECTCOUNT(*)FROMemployeesWHEREdepartment_id=50;使用COUNT函数COUNT(*)返回一个表中的行数COUNT函数COUNT函数有三中格式：COUNT(*)COUNT(expr)COUNT(DISTINCTexpr)COUNT(*)返回表中满足SELECT语句标准的行数，包括重复行，包括有空值列的行。如果WHERE子句包括在SELECT语句中，COUNT(*)返回满足WHERE子句条件的行数。对比，COUNT(expr)返回在列中的由expr指定的非空值的数。COUNT(DISTINCTexpr)返回在列中的由expr指定的唯一的非空值的数。幻灯片的例子显示部门50中的雇员人数。中国科学院西安网络中心©2005OracleSQL入门5-8第五章用组函数合计数据5-9Copyright©OracleCorporation,2001.Allrightsreserved.使用COUNT函数•COUNT(expr)返回对于表达式expr非空值的行数•显示EMPLOYEES表中部门数的值，不包括空值SELECTCOUNT(commission_pct)FROMemployeesWHEREdepartment_id=80;COUNT函数(续)幻灯片的离子显示部门80中有佣金的雇员人数。例显示EMPLOYEES表中的部门数。SELECTCOUNT(department_id)FROMemployees;中国科学院西安网络中心©2005OracleSQL入门5-9第五章用组函数合计数据5-10Copyright©OracleCorporation,2001.Allrightsreserved.SELECTCOUNT(DISTINCTdepartment_id)FROMemployees;使用DISTINCT关键字•COUNT(DISTINCTexpr)返回对于表达式expr非空并且值不相同的行数•显示EMPLOYEES表中不同部门数的值DISTINCT关键字使用DISTINCT关键字禁止计算在一列中的重复值。幻灯片中的例子显示EMPLOYEES表中不重复的部门数。中国科学院西安网络中心©2005OracleSQL入门5-10第五章用组函数合计数据5-11Copyright©OracleCorporation,2001.Allrightsreserved.SELECTAVG(commission_pct)FROMemployees;组函数和Null值组函数忽略列中的空值组函数和空值所有组函数忽略列中的空值。在幻灯片的例子中，平均值只基于表中的那些COMMISSION_PCT列的值有效的行的计算。平均值计算是用付给所有雇员的总佣金除以接受佣金的雇员数(4)。中国科学院西安网络中心©2005OracleSQL入门5-11第五章用组函数合计数据5-12Copyright©OracleCorporation,2001.Allrightsreserved.SELECTAVG(NVL(commission_pct,0))FROMemployees;在组函数中使用NVL函数NVL函数强制组函数包含空值组函数和空值(续)NVL函数强制组函数包括空值。在幻灯片的例子中，平均值被基于所有表中的行来计算，不管COMMISSION_PCT列是否为空。平均值的计算是用付给所有雇员的总佣金除以公司的雇员总数(20)。中国科学院西安网络中心©2005OracleSQL入门5-12第五章用组函数合计数据5-13Copyright©OracleCorporation,2001.Allrightsreserved.创建数据组EMPLOYEES在EMPLOYEES表中每个部门的平均薪水4400…95003500640010033创建数据组直到现在，所有组函数是将表作为一个大的信息组进行处理，有时，你需要将表的信息划分为较小的组，可以用GROUPBY子句实现。中国科学院西安网络中心©2005OracleSQL入门5-13第五章用组函数合计数据5-14Copyright©OracleCorporation,2001.Allrightsreserved.SELECTcolumn,group_function(column)FROMtable[WHEREcondition][GROUPBYgroup_by_expression][ORDERBYcolumn];创建数据组:GROUPBY子句语法用GROUPBY子句划分表中的行到较小的组中GROUPBY子句你可以用GROUPBY子句把表中的行划分为组。然后你可以用组函数返回每一组的摘要信息。在语法中：group_by_expression指定那些用于将行分组的列，这些列的值作为行分组的依据。原则如果在SELECT子句中包含了组函数，就不能选择单独的结果，除非单独的列出现在GROUPBY子句中。如果你未能在GROUPBY子句中包含一个字段列表，你会收到一个错误信息。使用WHERE子句，你可以在划分行成组以前过滤行。在GROUPBY子句中必须包含列。在GROUPBY子句中你不能用列别名。默认情况下，行以包含在GROUPBY列表中的字段的升序排序。你可以用ORDERBY子句覆盖这个默认值。中国科学院西安网络中心©2005OracleSQL入门5-14第五章用组函数合计数据5-15Copyright©OracleCorporation,2001.Allrightsreserved.SELECTdepartment_id,AVG(salary)FROMemployeesGROUPBYdepartment_id;使用GROUPBY子句在SELECT列表中的不在组函数中的所有列必须在GROUPBY子句中GROUPBY子句(续)当使用GROUPBY子句时，确保在SELECT列表中的所有没有包括在组函数中的列必须在GROUPBY子句中。幻灯片中的例子显示每个部门的部门号和薪水的平均值。下面是包含一个GROUPBY子句SELECT语句的求值过程：SELECT子句指定要返回的列：在EMPLOYEES表中的部门号−你在GROUPBY子句中指定分组的所有薪水的平均值−FROM子句指定数据库必须访问的表：EMPLOYEES表。WHERE子句指定被返回的行。因为无WHERE子句默认情况下所有行被返回。GROUPBY子句指定行怎样被分组。行用部门号分组，所以AVG函数被应用于薪水列，以计算每个部门的平均薪水。教师注释分组结果被以分组列隐式排序，可以用ORDERBY指定不同的排序顺序，但只能用组函数或分组列。中国科学院西安网络中心©2005OracleSQL入门5-15第五章用组函数合计数据5-16Copyright©OracleCorporation,2001.Allrightsreserved.使用GROUPBY子句G