SQL从入门到出门第7篇分组与汇总

程序员文章站 2022-07-13 16:08:22

...

- 聚合函数
- 数据分组

本篇介绍SQL:2016（ISO/IEC 9075:2016）标准中定义的对查询结果分组汇总的GROUP BY子句、HAVING子句和常见的聚合函数（aggregate function），以及六种主流数据库中的实现及差异：Oracle、MySQL、Microsoft SQL Server、PostgreSQL、Db2、SQLite。

在SQL中，函数主要分为两种类型：标量函数（scalar function）和聚合函数（aggregate function）。

标量函数针对每一行输入参数，返回一行输出结果。例如，ABS(x) 可以计算 x 的绝对值。关于标量函数，可以参考后续关于 SQL 函数的篇章。

本篇主要介绍用于分组计算的聚合函数，聚合函数针对每个分组内的数据进行操作，并且返回一个结果。例如，AVG(x) 可以按照分组计算每个组的 x 平均值。

聚合函数

最常见的聚合函数包括：AVG、COUNT、MIN、MAX、SUM。除此之外，不同的数据库还支持各种更多的函数，例如方差函数、标准差函数。使用时可以参考官方文档。

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

SELECT AVG(salary)
  FROM employees;

以上查询返回了员工的平均薪水。由于我们没有指定分组，AVG 计算的是所有员工薪水的平均值。
SQL从入门到出门第7篇分组与汇总

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

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

例如：

SELECT COUNT(*), 
       COUNT(DISTINCT salary), 
       COUNT(commission_pct)
  FROM employees;

查询结果如下：
SQL从入门到出门第7篇分组与汇总
其中，COUNT(*) 返回了所有记录的总数（107），COUNT(DISTINCT salary) 返回了薪水不相同的数量（58），COUNT(commission_pct) 返回了佣金百分比不为空值的数量（35）。

MIN 和 MAX 分别计算每个组内的最小值和最大值，SUM 计算组内所有值的和。

SELECT MIN(salary), 
       MAX(salary), 
       SUM(salary)
  FROM employees;

返回的结果如下：
SQL从入门到出门第7篇分组与汇总

数据分组

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

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

SELECT department_id,
       AVG(salary)
  FROM employees
 GROUP BY department_id;

查询结果如下：
SQL从入门到出门第7篇分组与汇总

首先，GROUP BY指定了按照部门编号进行分组，然后应用AVG函数计算每个部门的平均薪水。

如果GROUP BY后的分组字段存在 NULL 值，多个 NULL 值将被看作一个分组。

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

-- error example
SELECT department_id, 
       first_name,
       AVG(salary)
  FROM employees
 GROUP BY department_id;

以上语句的错误在于 first_name 既不是分组的条件，也不是聚合函数。查询结果按照部门进行分组显示，但是每个部门包含多个员工，每个员工存在不同的名字。数据库无法知道需要显示哪个 first_name 值。

SQLite 不会报错，而是随机显示一个first_name 值，实际上并没有价值；MySQL 在某些情况下也不会报错，但同样也是显示一个随机值。

我们不仅可以按照一个字段进行分组，也可以依据多个字段将数据分成更多的组。例如：

SELECT department_id, 
       job_id,
       AVG(salary)
  FROM employees
 GROUP BY department_id, job_id;

以上查询首选按照部门分组，然后针对一部门，再按照职位进行分组，最后计算各个组内的评价薪水。结果如下：
SQL从入门到出门第7篇分组与汇总

在SQL查询中，如果同时存在WHERE子句和GROUP BY子句，要求WHERE子句在GROUP BY子句之前。因此，WHERE子句无法对分组后的结果进行过滤，例如：

-- error example
SELECT department_id, 
       AVG(salary)
  FROM employees
 WHERE AVG(salary) > 10000
 GROUP BY department_id;

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

假设要查看员工数量大于 10 的部门编号，可以使用以下查询：

SELECT department_id, 
       COUNT(*) AS headcount
  FROM employees
 GROUP BY department_id
HAVING COUNT(*) > 10;

首先，按照部门编号进行分组，计算每个组内的员工数量；然后使用HAVING子句过滤员工数量大于 10 人的部门。查询结果如下：
SQL从入门到出门第7篇分组与汇总

MySQL 和 SQLite 允许在 HAVING 子句中使用列的别名进行过滤：
HAVING headcount > 10;

我们还可以同时使用WHERE子句进行数据行的过滤，使用HAVING进行分组结果的过滤。

假如要查看薪水大于 8000 的员工，数量大于 10 的部门编号，可以使用以下查询：

SELECT department_id, 
       COUNT(*) AS headcount
  FROM employees
 WHERE salary > 8000
 GROUP BY department_id
HAVING COUNT(*) > 10;

首先，WHERE子句过滤薪水大于 8000 的所有员工；然后，按照部门编号进行分组，计算每个组内的员工数量；最后，使用HAVING子句过滤员工数量大于 10 人的部门。查询结果如下：
SQL从入门到出门第7篇分组与汇总

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

SELECT column1,
       column2,
       aggregate_function(column3)
  FROM table1
[WHERE conditions]
[GROUP BY column1, column2]
[HAVING group_condition];

数据库	GROUP BY	描述
Oracle	OK	Oracle支持GROUP BY中的ROLLUP、CUBE、GROUPING SETS高级选项
MySQL	OK	MySQL允许在GROUP BY子句中指定一个分组顺序，同时支持WITH ROLLUP高级选项
SQL Server	OK	SQL Server支持GROUP BY中的ROLLUP、CUBE、GROUPING SETS高级选项
PostgreSQL	OK	PostgreSQL支持GROUP BY中的ROLLUP、CUBE、GROUPING SETS高级选项
Db2	OK	Db2支持GROUP BY中的ROLLUP、CUBE、GROUPING SETS高级选项
SQLite	OK

SQL从入门到出门第7篇分组与汇总

聚合函数

数据分组