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字符。