- OCA/OCP认证考试指南全册(第3版) Oracle Database 12c(1Z0-061,1Z0-062,1Z0-063) (计算机与信息)
- (美)John Watson等
- 3257字
- 2021-03-26 13:10:31
9.1 分组函数
本节定义SQL分组函数和它们的不同变体,介绍选择的分组函数的语法和示例,并讨论了它们的数据类型以及DISTINCT关键字和NULL值的影响。
9.1.1 分组函数的定义
分组函数作用于聚合数据,并且针对每个组返回单个结果。这些组通常由一个或者多个数据行组成。使用如下公式定义单行函数:F(x, y, z, …) = result,其中x, y, z…是输入参数。函数F每次对数据集的一行执行操作,对每一行都返回一个结果。可以使用下面的公式定义分组函数:
F(g1, g2, g3, . . . , gn) = result1, result2, result2, . . . , resultn;
分组函数对每个行群集执行一次,并且针对每个组返回一个结果。这些组中的行通过公共值或特性相关联。如果表中所有的行作为分组函数的一个组,那么返回一个结果。SELECT列表中可以出现一个或者多个分组函数,如下所示:
SELECT group_function(column or expression), ... FROM table [WHERE ...] [ORDER BY...]
请考察EMPLOYEES表。表中有107行,可以依据这些行共享的公共值创建组。例如,共享相同DEPARTMENT_ID值的行可以聚集在一起。然后,对每个特定组单独执行分组函数。
如图9-1所示,在EMPLOYEES表中有12个不同的DEPARTMENT_ID值,包括一个空值。依据公共DEPARTMENT_ID值将这些行划分为12个组。COUNT函数执行12次,针对每个组执行一次。注意,不同的组包含不同数量的行。
图9-1 作用于12个组的分组函数
注意:
分组函数将多个行中的许多值聚集到单个结果中。它们广泛用于报告目的,提供总计、平均值和计数,也称为汇总或聚合函数。
9.1.2 使用分组函数
AVG、SUM、MIN、MAX和COUNT函数用来说明分组函数的实际应用。这些分组函数都返回数字结果。另外,MIN和MAX函数还可能返回字符和日期结果。这5个函数作用于非空值,但是和其他函数不一样,在特定条件下COUNT函数调用也可以计算包含空值的行。
1.COUNT函数
COUNT函数计算组中的行数,其语法如下所示:
COUNT({*|[DISTINCT|ALL] expr})
该语法可以分解为下面的形式:
● COUNT(*)
● COUNT(DISTINCT expr)
● COUNT(ALL expr)
● COUNT(expr)
调用COUNT(*)时,组中的所有行(包括具有空值和重复值的行)都计算在内。执行COUNT (DISTINCT expr)时,只计算每个组中expr唯一出现的次数。ALL关键字是默认语法的一部分,因此COUNT(ALL expr)和COUNT(expr)是等效的表达式。如果expr基于命名的列,就忽略空值,如果expr基于各种情况的列,将为每一行进行计算,而不管行中是否有空值。expr的数据类型可以是NUMBER、DATE、CHAR或者VARCHAR2。考察下面的查询:
查询1: select count(*) from employees; 查询2: select count(commission_pct), count(1) from employees; 查询3: select count(distinct salary) from employees; 查询4: select count(hire_date), count(manager_id) from employees;
查询1计算EMPLOYEES表中的行,返回整数107。查询2计算包含非空COMMISSION_PCT值的行,返回36。它还计算字面表达式1,该表达式不基于命名的列,因此对每一行进行计算,返回107。查询3考察107个非空行,确定唯一SALARY值的数量,返回58。查询4演示了如何在DATE列和NUMBER列上使用COUNT函数。该查询返回整数107和106,因为组中有107个非空HIRE_DATE值和106个非空MANAGER_ID值。
考点:
对字面量使用COUNT函数,例如COUNT(1),在内部重写为COUNT(*),给每一行计数,而不论是否出现空值。
2.SUM函数
SUM函数返回组中非空数字表达式值的总和,其语法如下所示:
SUM([DISTINCT|ALL] expr);
该语法可分解为下面的形式:
● SUM(DISTINCT expr)
● SUM(ALL expr)
● SUM(expr)
SUM(DISTINCT expr)提供在对组中每一行计算 expr 之后返回的唯一值进行相加得到的和。SUM (expr)和SUM(ALL expr)提供组中各行的expr相加的和,并且忽略空值。expr参数的数据类型必须是NUMBER。考察下面的查询:
查询1: select sum(2) from employees; 查询2: select sum(salary) from employees; 查询3: select sum(distinct salary) from employees; 查询4: select sum(commission_pct) from employees;
EMPLOYEES表有107行。查询1把107行的数字2加起来,返回214。查询2获得组(整个表)中各行的SALARY列值,返回薪水总额721166。查询3返回总和417158,因为许多员工获得相同的薪水,DISTINCT关键字只是将列中的唯一值添加到总和之中。在相加非空COMMISSION_PCT值之后,查询4返回7.9。
3.AVG函数
列或者表达式的平均值就是用和除以组中的非空行数。AVG函数的语法如下所示:
AVG([DISTINCT|ALL] expr);
该语法可以分解为下面的形式:
● AVG(DISTINCT expr)
● AVG(ALL expr)
● AVG(expr)
调用AVG(DISTINCT expr)时,将 expr 的不同值相加,并除以 expr 唯一出现的次数。AVG(ALL expr) 和AVG(expr)将各行中expr的非空值相加,用求得的和除以组中的非空行数。expr参数的数据类型是NUMBER。考察下面的查询:
查询1: select avg(2) from employees; 查询2: select avg(salary) from employees; 查询3: select avg(distinct salary) from employees; 查询4: select avg(commission_pct) from employees;
EMPLOYEES表有107行。查询1把107行的数字2加起来,用总和除以行数,返回数字2。原封不动地返回提交给AVG函数的数字字面值。查询2相加各行的SALARY值,得到薪水总数721166。用它除以包含非空SALARY值的107行,返回平均值6739.86916。对于查询3来说,有58个唯一的薪水值,相加之后,得到总和为417158。用417158除以58,返回7192.37931作为不同薪水值的平均值。查询4相加非空COMMISSION_PCT值,生成总和7.9。有36条员工记录包含非空COMMISSION_PCT值。用7.9除以36,生成的平均值为0.21944444。
4.STDDEV和VARIANCE函数
标准偏差衡量值在一列或表达式中的分散程度。计算方差的平方根,就得到了标准偏差。方差是指实际值和平均值的平方差之和,再除以N-1或N;根据方差是为一个样本建立的,还是为所有数据建立的,来选择除以N-1还是N。VARIANCE函数为一个样本计算方差,因此应计算实际值和平均值的平方差之和,再除以N-1。更多的信息可参阅VAR_POP和VAR_SAMP函数,它们超出了考试范围。
计算方差时,先计算集合的平均值。接着给每个数字减去平均值,再计算结果的平方。这就是平方差。把所有的平方差加起来,再除以N-1,就得到了方差。STDDEV和VARIANCE函数有两种形式:汇集形式和分析形式,这超出了考试范围。汇集形式有如下语法:
STDDEV([DISTINCT|ALL] expr) VARIANCE([DISTINCT|ALL] expr)
考虑图9-2中的查询。
图9-2 VARIANCE和STDDEV函数
有3个员工属于部门90,其薪水分别是24000、17000和17000。查询2返回平均薪水19333.3333,并显示了方差和标准偏差。从统计上看,查询3和4提供了计算方差和标准偏差的另一种方式。这些不在考试范围内,这里提供它们,是为了通过例子解释这些函数。查询3使用power函数,计算(薪水-平均薪水)的2次方,得到平方差,再使用sum函数求和,最后除以N-1。它还使用sqrt函数计算标准偏差。记住N是3,因为在样本集合中有3个薪水值。平均薪水19333.3333在查询3中硬编码了,而查询4使用一个嵌套的子查询(参见第11章),动态计算平均薪水。
5.MAX和MIN函数
MAX和MIN函数返回组中expr的最大和最小值。MAX和MIN函数作用于NUMBER、DATE、CHAR和VARCHAR2数据类型。它们返回与输入实参相同的数据类型的值,这个值要么是组中的最大值,要么是最小值。当应用于DATE项时,MAX返回最近日期,MIN返回最早日期。依据数据库中的NLS设置,将字符串转换为其组成字符的数字表示。当将MIN函数应用于一组字符串时,返回按字母顺序第一个出现的单词,而MAX返回最后出现的单词。MAX和MIN函数的语法如下所示:
MAX([DISTINCT|ALL] expr); MIN([DISTINCT|ALL] expr)
该语法可以分解为下面的形式:
● MAX(DISTINCT expr); MIN(DISTINCT expr)
● MAX(ALL expr); MIN(ALL expr)
● MAX(expr); MIN(expr);
MAX(expr)、MAX(ALL expr)和MAX(DISTINCT expr)检查一组行中expr的值,返回其中的最大值,并且忽略空值。MIN(expr)、MIN(ALL expr)和MIN(DISTINCT expr)检查一组行中expr的值,返回其中的最小值。考察下面的查询:
查询1: select min(commission_pct), max(commission_pct) from employees; 查询2: select min(start_date), max(end_date) from job_history; 查询3: select min(job_id), max(job_id) from employees;
查询1返回数值0.1和0.4,它们就是EMPLOYEES表中COMMISSION_PCT值的最小值和最大值。注意,忽略了COMMISSION_PCT的空值。查询2计算DATE列,并且表明JOB_HISTORY表中最早的START_DATE是17-SEP-1995,最近的END_DATE是07-JAN-2015。查询3返回AC_ ACCOUNT和ST_MAN作为EMPLOYEES表中按字母顺序最早和最后出现的JOB_ID值。
6.LISTAGG函数
LISTAGG函数返回列值的字符串汇总。如果有ORDER BY子句,值就排序后连接起来。这个函数运行为单集合汇集函数,它作用于所有行,返回一个输出行,或者执行为分组集合汇集函数,为GROUP BY子句中的每个组返回一个输出行(参见下一节)。LISTAGG函数的语法如下:
LISTAGG(expr, ['delimiter']) WITHIN GROUP (ORDER_BY_CLAUSE)
expr参数可以是任何有效的表达式。空值会忽略。delimiter指定分隔expr的可选字符串。ORDER BY子句确定连接好的值的返回顺序。考虑图9-3中的查询。
图9-3 LISTAGG函数
查询1返回熟悉的输出格式,列出按REGION_ID排序的国家,以及按COUNTRY_NAME字母倒序排序的国家。查询2演示了LISTAGG函数的单集合汇总形式,返回一行数据,它在一个用逗号分隔的列表中连接了COUNTRY_NAME值。查询3显示了分组集合汇总形式,为按REGION_ID分组的每一组返回一个用逗号分隔的COUNTRY_NAME值列表,并计算出每一组的项数。
考点:
学习分组函数时,需要记住两条基本规则。第一,它们每次总是作用于一组行。该组可能是数据集划分的许多组的其中之一,或者可能是整个表。分组函数对每个组都执行一次。第二,除了COUNT函数的COUNT(*)和COUNT(literal)形式外,所有的分组函数都忽略分组列或者表达式中包含空值的行。
练习9-1 使用分组函数
COUNTRIES表存储COUNTRY_NAME值列表。要求计算所有国家名称的平均长度。所有小数部分必须舍入到最接近的整数。
(1) 启动SQL*Plus或SQL Developer,连接到HR模式。
(2) 使用LENGTH函数计算各行中国家名称值的长度。可以使用AVG函数确定平均长度,并且使用ROUND函数将它舍入为最接近的整数。可能的解决方案是:
select round(avg(length(country_name))) average_country_name_length from countries;
(3) 执行该语句后,结果显示COUNTRIES表中所有国家名称的平均长度为8字符。