9.2 使用GROUP BY子句分组数据

上面讨论的分组函数使用组成整个表的多组行。本节讨论如何使用GROUP BY子句将一组数据划分为多个组。分组函数可以应用于这些行子集或者行群集。

9.2.1 创建数据组

表至少有一列和零行或者多行数据。在许多表中，这些数据需要进行分析，以将它转换为有用的信息。从使用不同特性划分为组的一组数据中计算统计是常见的报告需求。前面使用分组函数的示例作用于表的全部行。整个表被看作一个大的组。通常将行与公共特性关联，来创建集合中的数据组。之后，分组函数可以对每个组执行。数据组包括整个行和非特殊列。

考虑一下EMPLOYEES表，它包含11列和107行。可以创建行的组，这些行共享公共的DEPARTMENT_ID值。然后可以使用SUM函数得到每个部门的薪水总和。另一种可能的组集合可能共享公共的JOB_ID列值。然后可以使用AVG分组函数来确定支付给从事不同工作的员工的平均薪水。

组定义为整个数据集的子集，它共享一个或者多个公共特性。这些特性通常是列值，但也可能是表达式。创建的组数取决于公共特性中存在的不同值。

如图9-4所示，EMPLOYEES表中有12个不同的DEPARTMENT_ID值。如果使用公共的DEPARTMENT_ID值来分组行，就会有12个组。如果对这些组执行分组函数，就会返回12个值，因为对每个组执行一次分组函数。

9.2.2 GROUP BY子句

添加GROUP BY子句可以增强SELECT语句。这个子句有利于创建组，它出现在WHERE子句之后和ORDER BY子句之前，如下所示：

        SELECT column|expression|group_function(column|expression [alias]), ...}
        FROM table
        [WHERE condition(s)]
        [GROUP BY {col(s)|expr}]
        [ORDER BY {col(s)|expr|numeric_pos} [ASC|DESC] [NULLS FIRST|LAST]];

GROUP BY子句中指定的列或者表达式也称为分组特性(grouping attribute)，并且是分组行的组成部分。依据分组特性划分数据集。考察下面的查询：

        select department_id, max(salary), count(*)
        from employees
        group by department_id
        order by department_id;

本示例中的分组特性是DEPARTMENT_ID列。数据集(SELECT列表中的分组函数必须作用于它)划分为12个组，每个部门对应一组。对于每个组(部门)而言，返回最大薪水值和行数。结果按DEPARTMENT_ID排序，因此结果集中的第三行包含值11 000和6时，表明6名员工的DEPARTMENT_ID值为30。在这6名员工中，最高的SALARY值为11 000。这个查询说明SELECT列表中不需要包含分组特性。

经常会在SELECT列表中的分组特性的旁边看到分组函数。如果某一项(不是分组函数)出现在SELECT列表中并且没有GROUP BY子句，就会返回“ORA-00937: not a single-group group function”错误。如果有GROUP BY子句，但该项不是分组特性，那么会返回“ORA-00979:not a GROUP BY expression”错误。

如果在WHERE子句中有分组函数，就会返回“ORA-00934: group function is not allowed here”错误。本章后面将讨论使用HAVING子句实施组级条件。然而，分组函数可用作ORDER BY子句的一部分。

图9-5中的第一个查询会产生错误，因为END_DATE列在包含分组函数的SELECT列表中，并且没有GROUP BY子句。第二个查询会返回“ORA-00979”错误，因为START_DATE项列在SELECT子句中，但它不是分组特性。

第三个查询依据END_DATE列中的4位数年组成部分将JOB_HISTORY行划分为组。使用这个分组特性创建4个组，这些组表示员工终止工作的不同年份。COUNT显示在这些年里辞职的员工数量。结果依据“Number of Employees”表达式降序排列。注意，COUNT分组函数出现在ORDER BY子句中。

9.2.3 按多列分组

对GROUP BY子句的强大扩充就是使用多个分组特性。Oracle允许将数据集划分为多个组，并且允许使用不同的分组特性将这些组进一步划分为子组。考察下面两个查询：

        查询1: select department_id, sum(commission_pct)
        from employees where commission_pct is not null
        group by department_id;
        查询2: select department_id, job_id, sum(commission_pct)
        from employees where commission_pct is not null
        group by department_id, job_id;

查询1将从EMPLOYEES表返回的行限制为包含非空COMMISSION_PCT值的35行，然后依据DEPARTMENT_ID分组特性将这些行划分为两组：80和NULL。结果集包含两行，它们返回每个组COMMISSION_PCT值的和。

查询2类似于查询1，但是有其他项：SELECT和GROUP BY子句中的JOB_ID。这个第二个分组特性依据DEPARTMENT_ID将这两个组分解为作为组成部分的JOB_ID，这些JOB_ID属于每个组中的行。DEPARTMENT_ID=80的行的不同JOB_ID值是SA_REP和SA_MAN，包含空DEPARTMENT_ID的行的不同JOB_ID值是SA_REP。因此，查询2返回两个分组，一个由两个子组构成；另一个只有一个子组，如图9-6所示。

练习9-2 依据多列分组数据

分析员工的更替情况是常见的报告需求。要求创建一个报表，使其包含辞职的员工数，并依据他们辞职的年份进行分组，也需要提供他们从事的工作。结果必须依据每个组中员工的数量按降序排列。报表必须列出年、JOB_ID和在该年辞去特定工作的员工的数量。

(1) 启动SQL Developer，连接到HR模式。

(2) JOB_HISTORY表包含END_DATE和JOB_ID列，它们构成报表的源数据。

(3) 使用TO_CHAR函数提取年组成部分。使用COUNT(*)函数获取每年辞去特定工作的员工数量。

(4) 执行下列语句，按要求返回员工更替报表。

        select  to_char(end_date, 'yyyy') "Year" , job_id,
        count(*) "Number of Employees"
        from job_history
        group by to_char(end_date, 'yyyy'), job_id
        order by count(*) desc;

9.2.4 嵌套的分组函数

单行函数可以嵌套或者多层嵌套，而分组函数只能嵌套两层。下面显示了使用分组函数的三种格式：

G1(group_item) = result

G1(G2(group_item ) = result

G1(G2(G3(group_item)))这种格式是不允许的

用字母G后面紧跟一个数字的形式表示分组函数。第一种简单形式不包含嵌套函数，例如SUM (group_item)或者AVG(group_item)函数，它们为每个组返回一个结果。第二种形式支持两层嵌套的分组函数，如SUM(AVG(group_item))。这里的GROUP BY子句是强制的，因为在使用SUM函数聚集数据之前要先计算每个组中group_item的平均值。

Oracle不接受第三种形式。考察嵌套三个分组函数的表达式。如果将MAX函数应用于前面的示例，那么会形成MAX(SUM(AVG(group_item)))表达式。两个内部分组函数返回单个值，这个值表示一组平均值之和。表达式变成了MAX(single value)，这时就会出错，因为分组函数不能应用于单个值。