五种主流数据库：数据汇总

汇总分析是数据报表中的基本功能，例如产品销售金额的汇总、学生的平均身高和标准差统计等。SQL 定义了聚合函数，可以实现数据的汇总分析。

本文比较五种主流数据库支持的常见聚合函数，包括 MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite。

除 LISTAGG 函数外，以上聚合函数在 5 种主流数据库中的实现一致。

我们在使用聚合函数时需要注意两点：

• 聚合函数的参数支持 DISTINCT 关键字，表示在计算之前排除重复数据。
• 聚合函数在计算时忽略数据中的 NULL 值，COUNT(*) 函数除外。

接下来我们详细介绍这些聚合函数的作用。

使用 COUNT 函数统计行数

COUNT(*) 函数用于统计查询结果或者表中的行数。例如，以下语句统计了员工的数量：

SELECT COUNT(*) AS "员工数量"
FROM employee;

查询返回的结果如下：

员工数量
------
 25

员工表中包含 25 条记录，也就是 25 名员工。

COUNT 函数也可以统计某个字段或者表达式不为空值的数量，例如：

SELECT COUNT(emp_id), COUNT(0)
FROM employee;

查询返回的结果如下：

COUNT(emp_id)|COUNT(0)
-------------|--------
           25| 25

两个 COUNT 函数分别统计了员工编号和常量 0 不为空的数量，两个结果都是 25，因为每个员工都有一个编号，而 COUNT(0) 和 COUNT(*) 的结果相同。

以下查询在 COUNT 函数中使用了 DISTINCT 关键字：

SELECT COUNT(sex) AS "所有性别", COUNT(DISTINCT sex) AS "不同性别"
FROM employee;

查询返回的结果如下：

所有性别|不同性别
-------|-------
     25| 2

员工表中的不同性别只有“男”和“女”，因此使用 DISTINCT 关键字之后的结果为 2。

提示：除了 DISTINCT 关键字之外，我们也可以使用 ALL 关键字，表示汇总时不排除重复数据。因为 ALL 是默认值，所以我们通常省略。

另外，如果参数中存在空值，COUNT 函数会忽略这些空值。以下查询统计了员工拥有奖金的情况：

SELECT COUNT(*) AS "员工数量",
       COUNT(bonus) AS "拥有奖金",
       COUNT(*) - COUNT(bonus) AS "没有奖金"
FROM employee;

查询返回的结果如下：

员工数量|拥有奖金|没有奖金
------|-------|-------
    25|      9| 16

查询结果显示 9 名员工拥有奖金，16 名员工没有奖金。

使用 AVG 函数计算平均值

AVG 函数用于计算一组数据的平均值。例如，以下查询统计了所有员工的平均月薪：

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

查询返回的结果如下：

平均月薪 
-----------
9832.000000

所有员工的平均月薪为 9832 元。

如果我们为 AVG 函数指定了 DISTINCT 关键字，则会在计算平均值之前排除重复数据。例如，1、1、2 的平均值为(1+2)/2，而不是(1+1+2)/3。例如，以下查询返回了所有不重复月薪的平均值：

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

查询返回的结果如下：

平均月薪 
-----------
9865.000000

去掉重复数据之后的平均月薪有所增加。

另外，如果参数中存在空值，AVG 函数会忽略这些空值。例如，1、2、NULL 的平均值为 (1+2)/2，而不是 (1+2+NULL)/3。以下查询返回了员工（不包括没有奖金的员工）的平均奖金：

SELECT AVG(bonus) AS "平均奖金"
FROM employee;

查询返回的结果如下：

平均奖金 
-----------
6388.888889

如果我们想要将没有奖金的员工当作奖金为零处理，可以使用 CASE 表达式：

SELECT AVG(CASE WHEN bonus IS NULL THEN 0 ELSE bonus END) AS "平均奖金"
FROM employee;

查询返回的结果如下：

平均奖金 
-----------
2300.000000

使用 SUM 函数计算总和

SUM 函数用于计算一组数值的总和。例如，以下语句返回了所有员工的月薪总和：

SELECT SUM(salary) AS "月薪总和"
FROM employee;

查询返回的结果如下：

月薪总和 
---------
245800.00

公司所有员工每个月的薪水总和为 245 800 元。

SUM 函数也可以利用 DISTINCT 关键字在计算总和之前排除重复数据，一般很少使用。另外，如果参数中存在空值，SUM 函数会忽略这些空值。以下查询返回了所有员工的平均奖金，没有奖金的员工被当作奖金为零处理：

SELECT SUM(bonus)/COUNT(*) AS "平均奖金"
FROM employee;

查询返回的结果和前面的 CASE 表达式示例相同。

使用 MAX 函数返回最大值

MAX 函数用于返回一组数据中的最大值。例如，以下查询返回了最晚入职的员工的入职时间：

SELECT MAX(hire_date) AS "入职时间"
FROM employee;

查询返回的结果如下：

入职时间 
----------
2019-05-11

最后一位员工的入职时间是 2019 年 5 月 11 日。

MAX 函数支持 DISTINCT 关键字，但是没有实际意义，因为它对结果没有影响。另外，如果参数中存在空值，MAX 函数会忽略这些空值。

使用 MIN 函数返回最小值

MIN 函数用于返回一组数据中的最小值。例如，以下查询返回了第一位员工的入职时间：

SELECT MIN(hire_date) AS "入职时间"
FROM employee;

查询返回的结果如下：

入职时间 
----------
2000-01-01

第一位员工的入职时间是 2000 年 1 月 1 日。

MIN 函数支持 DISTINCT 关键字，但是没有实际意义，因为它对结果没有影响。另外，如果参数中存在空值，MIN 函数会忽略这些空值。

使用 LISTAGG 函数连接字符串

LISTAGG 函数用于对字符串进行聚合，可以将多行字符串合并成单个字符串。例如，以下查询返回了行政管理部门中所有员工的电子邮箱：

-- Oracle
SELECT LISTAGG(email, ';') AS "收件人"
FROM employee
WHERE dept_id = 1;

目前只有 Oracle 实现了该函数，函数中的第二个参数用于指定连接字符串的分隔符，默认为空。查询返回的结果如下：

收件人 
-------------------------------------------------------
liubei@shuguo.com;guanyu@shuguo.com;zhangfei@shuguo.com

LISTAGG 函数支持 WITHIN GROUP 选项，可以在合并之前对数据进行排序。例如：

-- Oracle
SELECT LISTAGG(email, ';') WITHIN GROUP (ORDER BY email) AS "收件人"
FROM employee
WHERE dept_id = 1;

其中 ORDER BY 表示对邮箱地址进行排序，查询返回的结果如下：

收件人
-------------------------------------------------------
guanyu@shuguo.com;liubei@shuguo.com;zhangfei@shuguo.com

MySQL 提供了执行字符串聚合操作的 GROUP_CONCAT 函数，例如：

-- MySQL
SELECT GROUP_CONCAT(email ORDER BY email SEPARATOR ';') AS "收件人"
FROM employee
WHERE dept_id = 1;

其中，ORDER BY 表示对邮箱地址进行排序，SEPARATOR 指定了连接字符串的分隔符，默认为逗号。查询返回的结果和上面的示例相同。

SQLite 提供了和 MySQL 类似的 GROUP_CONCAT 函数，但是调用参数不同。例如：

-- SQLite
SELECT GROUP_CONCAT(email, ';') AS "收件人"
FROM employee
WHERE dept_id = 1;

第 2 个参数指定了连接字符串的分隔符，默认为逗号。SQLite 中的 GROUP_CONCAT 函数不支持数据排序，查询返回的结果和上面的第 1 个 Oracle 示例相同。

Microsoft SQL Server 提供了执行字符串聚合操作的 STRING_AGG 函数，例如：

-- Microsoft SQL Server
SELECT STRING_AGG(email, ';') WITHIN GROUP (ORDER BY email) AS "收件人"
FROM employee
WHERE dept_id = 1;

第 2 个参数指定了连接字符串的分隔符，WITHIN GROUP 选项用于在合并之前对数据进行排序。查询返回的结果和上面的第 2 个 Oracle 示例相同。

PostgreSQL 提供了和 Microsoft SQL Server 类似的 STRING_AGG 函数，但是调用参数不同。
例如：

-- PostgreSQL
SELECT STRING_AGG(email, ';' ORDER BY email) AS "收件人"
FROM employee
WHERE dept_id = 1;

第 2 个参数同时指定了连接字符串的分隔符和数据的排序。查询返回的结果和上面的第 2 个 Oracle 示例相同。

以上字符串聚合函数都可以使用 DISTINCT 关键字在合并之前排除重复数据，同时还会忽略数据中的 NULL 值。