本篇我们介绍 Oracle 中的数据分组汇总和分组后的数据过滤。

8.1 聚合函数

聚合函数（aggregate function）可以针对一组数据进行汇总并返回一个结果。例如，AVG(x) 可以计算一组 x 的平均值。常见的聚合函数包括：

• AVG - 计算一组值的平均值。
• COUNT - 统计一组值的数量。
• MAX - 计算一组值的最大值。
• MIN - 计算一组值的最小值。
• SUM - 计算一组值的和值。
• LISTAGG - 连接一组字符串。

我们首先来看一个 AVG 函数的例子：

SELECT AVG(salary) AS "平均月薪"
FROM employee;

平均月薪|
-------|
   9832|

以上查询返回了全体员工的平均月薪。

关于聚合函数，我们需要注意两点：

• 在参数中使用DISTINCT选项可以在计算之前排除重复数据；
• 聚合函数在计算时，忽略输入为 NULL 值的行。

例如：

SELECT COUNT(*) AS "员工总数", 
       COUNT(DISTINCT sex) AS "不同性别", 
       COUNT(bonus) AS "拥有奖金"
FROM employee;

员工总数|不同性别|拥有奖金|
-------|-------|--------|
     25|      2|       9|

其中，COUNT(*) 返回了所有的记录数（员工总数），COUNT(DISTINCT sex) 返回了性别的种类，COUNT(bonus) 返回了拥有奖金的员工数量。

MIN 和 MAX 函数分别返回数据中的最小值和最大值，SUM 函数计算所有数据的和值。例如：

SELECT MIN(salary) AS "最低月薪", 
       MAX(salary) AS "最高月薪", 
       SUM(salary) AS "月薪总计"
FROM employee;

最低月薪|最高月薪 |月薪总计  |
-------|--------|----------|
   4000|   30000|    245800|

LISTAGG 函数可以将一组字符串通过指定分隔符进行合并，例如：

SELECT LISTAGG(emp_name, ';') AS "员工列表"
FROM employee
WHERE dept_id = 1;

员工列表      |
-------------|
刘备;关羽;张飞|

LISTAGG 函数还提供了排序、去重、合并结果超长处理等功能，具体可以参考这篇文章。

📝除了以上常见的聚合函数之外，Oracle 还支持例如方差函数、标准差函数等更多的聚合函数，需要时可以参考官方文档。

8.2 数据分组

在前面的示例中，我们将所有的数据作为一个整体（分组），聚合函数只返回了一个结果。接下来，我们结合GROUP BY子句，将数据分成不同的组，然后分别计算各个组内的数据汇总。

假如我们想要知道每个部门的平均薪水，而不是所有员工的平均值，可以使用以下语句：

SELECT dept_id AS "部门编号",
       AVG(salary) AS "平均月薪"
FROM employee
GROUP BY dept_id;

部门编号|平均月薪                                 |
-------|-----------------------------------------|
      1| 26666.6666666666666666666666666666666667|
      2| 13166.6666666666666666666666666666666667|
      3|                                     9000|
      4|7577.777777777777777777777777777777777778|
      5|                                   5012.5|

其中，GROUP BY子句指定了按照部门进行分组，然后通过 AVG 函数计算每个部门的平均薪水。

如果GROUP BY分组字段存在 NULL 值，多个 NULL 值将被分为一个组。例如，以下语句按照不同奖金值统计员工的数量：

SELECT bonus AS "奖金",
       COUNT(*) AS "员工数量"
FROM employee
GROUP BY bonus;

奖金 |员工数量|
-----|-------|
 8000|      1|
 1500|      1|
 5000|      2|
     |     16|
 6000|      1|
10000|      3|
 2000|      1|

从查询结果可以看出，16 个员工没有奖金；但他们都被分组同一个组中，而不是多个不同的组。

在使用分组汇总时，一个常见的错误就是SELECT列表中包含了既不是聚合函数、也不属于GROUP BY子句的字段，例如：

-- 错误示例
SELECT dept_id, 
       emp_name,
       AVG(salary)
FROM employee
GROUP BY dept_id;

SQL 错误 [979] [42000]: ORA-00979: 不是 GROUP BY 表达式

以上语句的错误在于 emp_name 既不是分组字段，也不是聚合函数。查询按照部门进行分组，但是每个部门包含多个员工，数据库无法知道需要显示哪个员工的姓名。这是一个逻辑错误。

我们不仅可以基于一个字段进行分组，也可以依据多个字段将数据分成更多的组。例如，以下语句同时按照部门和性别统计员工的数量：

SELECT dept_id AS "部门编号", 
       sex AS "性别",
       COUNT(*) AS "员工数量"
FROM employee
GROUP BY dept_id, sex;

部门编号|性别|员工数量|
-------|----|-------|
      1|男  |      3|
      2|男  |      3|
      4|女  |      1|
      4|男  |      8|
      5|男  |      8|
      3|女  |      2|

在 SQL 查询中，如果同时存在WHERE子句和GROUP BY子句，要求WHERE子句出现在GROUP BY子句之前。因此，WHERE子句无法对分组后的结果进行过滤。例如，以下错误示例想要查找员工数量大于 5 人的部门：

-- 错误示例
SELECT dept_id, 
       COUNT(*)
FROM employee
WHERE COUNT(*) > 5
GROUP BY dept_id;

SQL 错误 [934] [42000]: ORA-00934: 此处不允许使用分组函数

错误原因在于WHERE子句执行时还没有进行分组计算，它只能基于分组之前的数据行进行过滤。如果需要对分组后的结果进行过滤，需要使用HAVING子句。

8.3 分组过滤

如果要查看员工数量大于 10 的部门编号，可以使用HAVING子句实现：

SELECT dept_id, 
       COUNT(*) AS headcount
FROM employee
GROUP BY dept_id
HAVING COUNT(*) > 5;

DEPT_ID|HEADCOUNT|
-------|---------|
      4|        9|
      5|        8|

查询首先按照部门进行分组，计算每个部门的员工数量；然后使用HAVING子句过滤员工数量大于 5 人的部门。

我们可以同时使用WHERE子句进行数据行的过滤，使用HAVING进行分组结果的过滤。例如想要查看薪水大于 10000 的员工数量大于 2 人的部门，可以使用以下查询：

SELECT dept_id, 
       COUNT(*)
FROM employee
WHERE salary > 10000
GROUP BY dept_id
HAVING COUNT(*) > 2;

DEPT_ID|COUNT(*)|
-------|--------|
      1|       3|

首先，WHERE子句过滤薪水大于 10000 的所有员工；然后按照部门进行分组，计算每个部门的员工数量；最后，使用HAVING子句过滤这类员工数量大于 2 人的部门。

最后，我们来看一下 SQL 标准的语法：

SELECT column1,
       column2,
       aggregate_function(column3)
FROM table_name
[WHERE conditions]
[GROUP BY column1, column2
[HAVING conditions] ]
[ORDER BY ...]
[OFFSET m {ROW | ROWS}]
[FETCH { FIRST | NEXT } [ num_rows | n PERCENT ] { ROW | ROWS } { ONLY | WITH TIES }];

对于以上各个子句，Oracle 的逻辑执行顺序为 FROM、WHERE、SELECT、GROUP BY、HAVING、ORDER BY 以及 OFFSET/FECTH。

Oracle 中的 GROUP BY 子句还支持一些高级分组选项，我们将会在第 13 篇中进行介绍。