个人中心
退出
《Oracle 入门教程》第 08 篇 分组汇总
作者: 不剪发的Tony老师
毕业于北京航空航天大学，十多年数据库管理与开发经验，目前在一家全球性的金融公司从事数据库架构设计。CSDN学院签约讲师以及GitChat专栏作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目录
8.1 聚合函数
8.2 数据分组
8.3 分组过滤
本篇我们介绍 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 篇中进行介绍。
分类导航