五种主流数据库：分组统计

如果将数据按照某种规则进行分组，然后分别进行汇总，通常能够得到更详细的分析结果。例如，按照不同性别计算员工的平均月薪，按照不同的产品和渠道统计销售金额等。为了实现这种分组统计的效果，我们可以将聚合函数与分组操作（GROUP BY）结合使用。

本文比较五种主流数据库实现的分组统计功能，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

功能	MySQL	Oracle	SQL Server	PostgreSQL	SQLite
GROUP BY	✔️	✔️	✔️	✔️	✔️
HAVING	✔️	✔️	✔️	✔️	✔️

创建数据分组

GROUP BY 子句可以将数据按照某种规则进行分组。例如，以下查询使将员工按照性别进行分组：

SELECT sex AS "性别"
FROM employee
GROUP BY sex;

其中，GROUP BY 表示将性别的每个不同取值分为一组，每个组返回一条记录。查询返回的结果如下：

性别
---
男
女

员工表中只存在 2 种不同的性别，因此返回了 2 条记录。我们也可以通过 DISTINCT 运算符实现相同的结果：

SELECT DISTINCT sex AS "性别"
FROM employee;

其中，DISTINCT 表示返回不重复的数据，查询结果和上面的示例相同。

我们也可以基于多个字段或表达式进行分组，从而创建更详细的分组。例如，以下语句按照不同的部门和性别进行分组：

SELECT dept_id AS "部门编号", sex AS "性别"
FROM employee
GROUP BY dept_id, sex;

查询返回的结果如下：

部门编号|性别
------|---
     1|男
     2|男
     3|女
     4|男
     4|女
     5|男

研发部（部门编号为 4）既有男性员工，又有女性员工，因此分为 2 个组。

进行组内汇总

我们可以结合使用 GROUP BY 子句与聚合函数，将数据进行分组，并在每个组内进行一次数据汇总。例如，以下语句按照不同的性别统计员工数量和平均月薪：

SELECT sex AS "性别", 
       COUNT(*) AS "员工数量",
       AVG(salary) AS "平均月薪"
FROM employee
GROUP BY sex;

其中 GROUP BY 用于将员工按照性别分为男、女两个组，然后利用 COUNT 和 AVG 函数分别计算男性员工和女性员工的总数和平均月薪。查询返回的结果如下：

性别|员工数量|平均月薪 
---|-------|------------
男 |     22|10054.545455
女 |      3| 8200.000000

男性员工有 22 人，平均月薪约为 10055 元；女性员工有 3 人，平均月薪为 8200 元。以下查询统计了每年入职的员工数量：

-- Oracle、MySQL 以及 PostgreSQL
SELECT EXTRACT(YEAR FROM hire_date) AS "入职年份",
 COUNT(*) AS "员工数量"
FROM employee
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY COUNT(*) DESC;

-- Microsoft SQL Server
SELECT DATEPART(YEAR, hire_date) AS "入职年份",
 COUNT(*) AS "员工数量"
FROM employee
GROUP BY DATEPART(YEAR, hire_date)
ORDER BY COUNT(*) DESC;

-- SQLite
SELECT STRFTIME('%Y', hire_date) AS "入职年份",
 COUNT(*) AS "员工数量"
FROM employee
GROUP BY STRFTIME('%Y', hire_date)
ORDER BY COUNT(*) DESC;

我们在以上查询中使用了基于表达式的分组汇总，其中 EXTRACT、DATEPART 以及 STRFTIME 是不同数据库中用于提取日期信息的函数。查询返回的结果如下：

入职年份|员工数量
-------|-------
   2018| 5
   2000| 3
   2011| 3
   2002| 2
   2012| 2
   2017| 2
...

我们在使用 GROUP BY 子句进行分组时，如果分组字段中存在多个空值（NULL），它们将被分为一个组，而不是多个组。例如，以下查询按照不同奖金额统计员工的数量：

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

查询返回的结果如下：

奖金    |员工数量
--------|-------
10000.00| 3
 8000.00| 1
        | 16
 5000.00| 2
 6000.00| 1
 2000.00| 1
 1500.00| 1

查询结果显示 16 位员工没有奖金，他们都被分到了同一个组。

提示：虽然 SQL 中的 NULL 和 NULL 不同，但是 GROUP BY 子句会将多个 NULL 值分为一组，也就是执行分组操作的时候认为它们相等。

常见语法问题

初学者在使用分组汇总操作时经常会犯的一个错误就是，在 SELECT 列表中使用了 GROUP BY 子句之外的字段。例如：

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

以上语句在大多数数据库中都会返回一个类似的错误：emp_name 字段没有出现在 GROUP BY 子句或者聚合函数中。

这个错误的原因在于，我们想要按照部门进行分组，但是每个部门包含多名员工，数据库无法确定显示哪个员工的姓名。这是一个逻辑上的错误，而不是数据库实现的问题。

注意：MySQL 通过 sql_mode 参数 ONLY_FULL_GROUP_BY 控制该行为，默认遵循 SQL 标准；但是如果禁用该参数，以上示例将不会报错，而是随机返回一个员工姓名。以上示例在 SQLite 中也不会报错，而是随机返回一个员工姓名。

再次过滤数据

我们通常使用 WHERE 子句进行数据过滤，但是如果需要对分组汇总的结果进行过滤，是不是也可以使用 WHERE 子句实现呢？以下语句统计了每个部门的平均月薪，然后返回平均月薪大于 10 000 元的部门：

-- 使用 WHERE 子句进行数据过滤的错误示例
SELECT dept_id, AVG(salary)
FROM employee
WHERE AVG(salary) > 10000
GROUP BY dept_id;

以上语句在 5 种数据库中都返回了类似的错误信息：WHERE 子句中不允许使用聚合函数。

这个错误的原因在于，WHERE 子句会针对 FROM 子句中的数据行进行过滤，在 WHERE子句执行时还没有进行分组汇总操作，还没有计算出 AVG(salary)函数的值，因此不允许使用聚合函数。

为了对分组汇总后的数据再次进行过滤，SQL 提供了另一个过滤数据的子句：HAVING。我们可以使用 HAVING 子句将上面的错误示例修改如下：

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

其中，HAVING 子句必须与 GROUP BY 子句一起使用，并且位于 GROUP BY 子句之后，表示对 AVG(salary)函数的结果进行过滤。查询返回的结果如下：

部门编号|平均月薪 
------|------------
     1|26666.666667
     2|13166.666667

我们可以使用 WHERE 子句对表进行数据过滤，同时使用 HAVING 子句对分组结果进行过滤。例如，以下语句查询拥有 2 名以上女性员工的部门：

SELECT dept_id AS "部门编号", COUNT(*) AS "员工数量"
FROM employee
WHERE sex = '女'
GROUP BY dept_id
HAVING COUNT(*) >= 2;

其中，WHERE 子句用于检索女性员工，GROUP BY 子句按照部门统计女性员工的数量，HAVING 子句选择数量大于或等于 2 的部门。查询返回的结果如下：

部门编号|员工数量
------|-------
 3| 2

只有财务部（dept_id=3）中有 2 名女性员工。

提示：从性能的角度来说，我们应该尽量使用 WHERE 子句过滤掉更多的数据，而不是等到分组之后再通过 HAVING 子句进行过滤。但是如果业务需求只能基于汇总之后的结果进行过滤，那就另当别论了。