上一篇我们介绍了如何利用 CASE 表达式为 SQL 语句增加逻辑处理功能。
在上一篇行转列的示例中，我们使用了 SUM 函数；它是一个聚合函数，可以对数据进行汇总求和。SQL 提供了许多这类函数，本篇我们就来学习如何利用聚合函数实现数据报表中的汇总分析。

聚合函数

汇总分析是数据报表中的基本功能，例如销售额度的汇总统计、计算学生的平均身高以及标准差等。为此，SQL 提供了许多具有汇总功能的聚合函数。
在 SQL 中，聚合函数（Aggregate Function）用于对一组数据进行汇总计算，并且返回单个分析结果。常见的聚合函数包括：

  • COUNT，统计查询结果的行数；
  • AVG，计算一组数值的平均值；
  • SUM，计算一组数值的总和；
  • MAX ，计算一组数据中的最大值；
  • MIN，计算一组数据中的最小值；
  • VAR_SAMP、STDDEV_SAMP，计算一组数据的方差和标准差。

接下来我们分别演示这些函数的作用。

使用 COUNT 函数统计数量

COUNT(*) 函数用于统计行数。以下示例统计员工的数量：

  1. SELECT COUNT(*) AS "员工数量"   FROM employee;
  2. 员工数量 ------|
  3. 25|

查询的结果显示员工表包含 25 条记录，也就是 25 名员工。
COUNT 函数也可以统计某个字段或者表达式的数量。以下示例统计员工编号和常量 0 的个数：

  1. SELECT COUNT(emp_id), COUNT(0)   FROM employee;
  2. COUNT(emp_id)|COUNT(0)|
  3. -------------|--------|
  4.             25|      25|

两个统计结果都是 25，因为每个员工都有一个编号；COUNT(0) 和 COUNT(*) 的效果相同。
使用聚合函数时需要注意两点：

  • 在聚合函数的参数中加上 DISTINCT 关键字，可以在计算之前排除重复值；
  • 聚合函数在计算时，忽略输入值为 NULL 的数据行；COUNT(*) 除外。

我们先来看一下 COUNT 函数加上 DISTINCT 之后的效果：

  1. SELECT COUNT(sex), COUNT(DISTINCT sex)   FROM employee;
  2. COUNT(sex)|COUNT(DISTINCT sex)|
  3. ----------|-------------------|
  4.         25|                  2|

由于员工表中的性别只有“男”和“女”，加上 DISTINCT 之后统计的结果变成了 2。
如果参数中存在空值，COUNT 函数通常会忽略这些空值。以下示例基于奖金字段进行统计：

  1. SELECT COUNT(*), COUNT(bonus), COUNT(*) - COUNT(bonus)   FROM employee;
  2. COUNT(*)|COUNT(bonus)|COUNT(*) - COUNT(bonus)|
  3. --------|------------|-----------------------|
  4.       25|           9|                     16|

从结果可以看出只有 9 名员工有奖金，其他的 16 名员工没有奖金。COUNT(*) 总是返回所有满足查询条件的行数。
最后，我们给出 COUNT 函数的完整语法：

  1. COUNT(*) COUNT( [ALL | DISTINCT] expression)

ALL 表示统计时不排除重复值。这是默认行为，通常省略。

使用 AVG 函数计算平均值

AVG 函数用于计算一组数据的平均值。以下示例用于计算所有员工的平均月薪：

  1. SELECT AVG(salary)   FROM employee;
  2. AVG(salary)|
  3. -----------|
  4. 9912.000000|

所有员工的平均月薪为 9912。
当 AVG 函数中包含 DISTINCT 参数时，在计算平均值之前会排除掉重复值。例如，（1、1、2）的平均值为 (1 + 2) / 2 = 1.5，而不是 (1 + 1 + 2) / 3 = 1.33。以下语句返回所有不重复的月薪的平均值：

  1. SELECT AVG(DISTINCT salary)   FROM employee;
  2. AVG(DISTINCT salary)|
  3. --------------------|
  4.          9865.000000|

去掉重复值之后的平均月薪为 9865，有所增加。
当 AVG 函数中存在空值时，计算之前会忽略这些空值。例如，（1，2，NULL）的平均值为 (1 + 2) / 2 = 1.5，而不是 (1 + 2) / 3 = 1。以下示例返回所有拥有奖金的员工的平均奖金：

  1. SELECT AVG(bonus)   FROM employee;
  2. AVG(bonus) |
  3. -----------|
  4. 6388.888889|

去除掉没有奖金的员工之后，平均奖金约为 6389。
最后，我们给出 AVG 函数的完整语法：

  1. AVG( [ALL | DISTINCT] expression)

ALL 表示统计时不排除重复值。这是默认行为，通常省略。

使用 SUM 函数进行求和

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

  1. SELECT SUM(salary)   FROM employee;
  2. SUM(salary)|
  3. -----------|
  4.   247800.00|

该公司所有员工的月薪总计为 247800。
SUM 函数也可以使用 DISTINCT 参数，在计算总和之前排除重复的值，但是很少使用。另外，SUM 函数在求和时自动忽略 NULL 值。
SUM 函数的完整语法如下：

  1. SUM( [ALL | DISTINCT] expression)

ALL 表示计算时不排除重复值。这是默认行为，通常省略。

使用 MAX 函数计算最大值

MAX 函数用于计算一组数据中的最大值。以下示例返回最后一个入职员工的入职时间：

  1. SELECT MAX(hire_date)   FROM employee; 
  2. MAX(hire_date)|
  3. --------------|
  4.     2019-05-11|

最后一个员工的入职时间是 2019 年 5 月 11 日。
MAX 函数也可以使用 DISTINCT 参数，但是没有意义，因为结果不变。另外，MAX 函数不会考虑 NULL 值。
MAX 函数的完整语法如下：

  1. MAX( [ALL | DISTINCT] expression)

ALL 表示统计时不排除重复值。这是默认行为，通常省略。

使用 MIN 函数计算最小值

MIN 函数用于计算一组数据中的最小值。以下示例返回第一个员工的入职时间：

  1. SELECT MIN(hire_date)   FROM employee;
  2. MIN(hire_date)|
  3. --------------|
  4.     2000-01-01|

第一个员工的入职时间是 2000 年 1 月 1 日。
MIN 函数也可以使用 DISTINCT 参数，但是没有意义，因为结果不变。另外，MIN 函数不会考虑 NULL 值。
MIN 函数的完整语法如下：

  1. MIN( [ALL | DISTINCT] expression)

ALL 表示统计时不排除重复值。这是默认行为，通常省略。

计算方差和标准差

统计学中通常使用方差（Variance）和标准差（Standard Deviation）衡量数据的离散程度，结果越小表示数据越集中。例如在打靶比赛中，两人的平均分都是 9 环，此时方差/标准差越小说明发挥越稳定。
SQL 定义了计算样本方差/标准差和总体方差/样本差的函数，它们在各种数据库中的实现如下：

函数 描述 Oracle MySQL SQL Server PostgreSQL
STDDEV_POP 总体标准差 支持 支持 STDEVP 支持
STDDEV_SAMP 样本标准差 支持 支持 STDEV 支持
VAR_POP 总体方差 支持 支持 VARP 支持
VAR_SAMP 样本方差 支持 支持 VAR 支持

在 Oracle 和 PostgreSQL 中，STDDEV 函数也可用于计算样本标准差，VARIANCE 函数也可用于计算样本方差。 在 MySQL 中，STDDEV、STD 函数也可用于计算总体标准差，VARIANCE 函数也用于计算样本方差。
Oracle、MySQL 以及 PostgreSQL 都与标准一致，SQL Server 使用专有的函数名。以下语句分别计算员工月薪的总体标准差、样本标准差、总体方差以及样本方差：

  1. -- Oracle、MySQL 以及 PostgreSQL 实现
  2. SELECT STDDEV_POP(salary),STDDEV_SAMP(salary),VAR_POP(salary),VAR_SAMP(salary)   FROM employee;
  3. -- SQL Server 实现
  4. SELECT STDEVP(salary),STDEV(salary),VARP(salary),VAR(salary)   FROM employee;

该查询的结果如下，不同数据库中的精度可能存在一些细微差异：

  1. STDDEV_POP(salary)|STDDEV_SAMP(salary)|VAR_POP(salary)|VAR_SAMP(salary)|
  2. ------------------|-------------------|---------------|----------------|
  3.           7546.004|           7701.608|       56942176|    59314766.667|

Oracle 和 MySQL 中的这些统计函数不支持 DISTINCT 参数；SQL Server 和 PostgreSQL 中的这些统计函数支持 DISTINCT 参数，在计算之前排除重复的值，但是这么做通常没有什么意义。另外，这些函数在计算时自动忽略 NULL 值。

小结

聚合函数可以用于数据的汇总分析，本篇介绍了如何使用 SQL 聚合函数统计行数、平均值、总和、最大值、最小值以及统计学中的方差和标准差。
练习题：编写 SQL 语句统计每个部门中的人数，一个部门显示为一列，返回下面的结果：
image.png