SQL 如何对表进行聚合和分组查询并对查询结果进行排序

随着表中记录(数据行)的不断积累,存储数据逐渐增加,有时我们可能希望计算出这些数据的合计值或者平均值等。

本文介绍如何使用 SQL 语句对表进行聚合和分组的方法。此外,还介绍在汇总操作时指定条件,以及对汇总结果进行升序、降序的排序方法。

一、对表进行聚合查询

本节重点

  • 使用聚合函数对表中的列进行计算合计值或者平均值等的汇总操作。

  • 通常,聚合函数会对 NULL 以外的对象进行汇总。但是只有 COUNT 函数例外,使用 COUNT(*) 可以查出包含 NULL 在内的全部数据的行数。

  • 使用 DISTINCT 关键字删除重复值。

1.1 聚合函数

通过 SQL 对数据进行某种操作或计算时需要使用函数。例如,计算表中全部数据的行数时,可以使用 COUNT 函数。该函数就是使用 COUNT(计数)来命名的。

除此之外,SQL 中还有很多其他用于汇总的函数,请大家先记住以下 5 个常用的函数。

  • COUNT:计算表中的记录数(行数)

  • SUM:计算表中数值列中数据的合计值

  • AVG:计算表中数值列中数据的平均值

  • MAX:求出表中任意列中数据的最大值

  • MIN:求出表中任意列中数据的最小值

如上所示,用于汇总的函数称为聚合函数或者聚集函数,本文中统称为聚合函数。所谓聚合,就是将多行汇总为一行。实际上,所有的聚合函数都是这样,输入多行输出一行。

接下来,本文将继续使用在 SQL 如何对表进行创建、更新和删除操作 中创建的 Product 表(图 1)来学习聚合函数的使用方法。

Product 表的内容

图 1 Product 表的内容

1.2. 计算表中数据的行数

首先,我们以 COUNT 函数为例让大家对函数形成一个初步印象。函数这个词,与我们在学校数学课上学到的意思是一样的,就像是输入某个值就能输出相应结果的盒子一样 1

使用 COUNT 函数时,输入表的列,就能够输出数据行数。如图 2 所示,将表中的列放入名称为 COUNT 的盒子中,咔嗒咔嗒地进行计算,咕咚一下行数就出来了……就像自动售货机那样,很容易理解吧。

COUNT 函数的操作演示图

图 2 COUNT 函数的操作演示图

接下来让我们看一下 SQL 中的具体书写方法。COUNT 函数的语法本身非常简单,像代码清单 1 那样写在 SELECT 子句中就可以得到表中全部数据的行数了。

代码清单 1 计算全部数据的行数

计算全部数据的行数

执行结果:

执行结果

COUNT() 中的星号,我们在 SQL SELECT WHERE 语句如何指定一个或多个查询条件 中已经介绍过,代表全部列的意思。COUNT 函数的输入值就记述在其后的括号中。

此处的输入值称为参数或者 parameter,输出值称为返回值。这些称谓不仅本文中会使用,在多数编程语言中使用函数时都会频繁出现,请大家牢记。

1.3 计算 NULL 之外的数据的行数

想要计算表中全部数据的行数时,可以像 SELECT COUNT(*) 这样使用星号。

如果想得到 purchase_price 列(进货单价)中非空行数的话,可以像代码清单 2 那样,通过将对象列设定为参数来实现。

代码清单 2 计算 NULL 之外的数据行数

1
2
SELECT COUNT(purchase_price)
  FROM Product;

执行结果:

1
2
3
count
-------
    6

此时,如图 1 所示,purchase_price 列中有两行数据是 NULL,因此并不应该计算这两行。对于 COUNT 函数来说,参数列不同计算的结果也会发生变化,这一点请大家特别注意。

为了有助于大家理解,请看如下这个只包含 NULL 的表的极端例子。

只包含 NULL 的表

图 3 只包含 NULL 的表

我们来看一下针对上述表,将星号(*)和列名作为参数传递给 COUNT 函数时所得到的结果(代码清单 3)。

代码清单 3 将包含 NULL 的列作为参数时,COUNT(*)COUNT(<列名>) 的结果并不相同

1
2
SELECT COUNT(*), COUNT(col_1)
  FROM NullTbl;

执行结果:

执行结果

如上所示,即使对同一个表使用 COUNT 函数,输入的参数不同得到的结果也会不同。由于将列名作为参数时会得到 NULL 之外的数据行数,所以得到的结果是 0 行。

该特性是 COUNT 函数所特有的,其他函数并不能将星号作为参数(如果使用星号会出错)。

法则 1

COUNT 函数的结果根据参数的不同而不同。COUNT(*) 会得到包含 NULL 的数据行数,而 COUNT(<列名>) 会得到 NULL 之外的数据行数。

1.4 计算合计值

接下来我们学习其他 4 个聚合函数的使用方法。这些函数的语法基本上与 COUNT 函数相同,但就像我们此前所说的那样,在这些函数中不能使用星号作为参数。

首先,我们使用计算合计值的 SUM 函数,求出销售单价的合计值(代码清单 4)。

代码清单 4 计算销售单价的合计值

1
2
SELECT SUM(sale_price)
  FROM Product;

执行结果:

1
2
3
  sum
------
 16780

得到的结果 16780 元,是所有销售单价(sale_price 列)的合计,与下述计算式的结果相同。

计算式

接下来,我们将销售单价和进货单价(purchase_price 列)的合计值一起计算出来(代码清单 5)。

代码清单 5 计算销售单价和进货单价的合计值

1
2
SELECT SUM(sale_price), SUM(purchase_price)
  FROM Product;

执行结果:

执行结果

这次我们通过 SUM(purchase_price) 将进货单价的合计值也一起计算出来了,但有一点需要大家注意。具体的计算过程如下所示。

计算过程

大家都已经注意到了吧,与销售单价不同,进货单价中有两条不明数据 NULL。对于 SUM 函数来说,即使包含 NULL,也可以计算出合计值。

还记得 SQL SELECT WHERE 语句如何指定一个或多个查询条件 内容的读者可能会产生如下疑问。

“四则运算中如果存在 NULL,结果一定是 NULL,那此时进货单价的合计值会不会也是 NULL 呢?”

有这样疑问的读者思维很敏锐,但实际上这两者并不矛盾。从结果上说,所有的聚合函数,如果以列名为参数,那么在计算之前就已经把 NULL 排除在外了。

因此,无论有多少个 NULL 都会被无视。这与“等价为 0”并不相同 2

因此,上述进货单价的计算表达式,实际上应该如下所示。

计算表达式

法则 2

聚合函数会将 NULL 排除在外。但 COUNT(*) 例外,并不会排除 NULL

1.5 计算平均值

接下来,我们练习一下计算多行数据的平均值。为此,我们需要使用 AVG 函数,其语法和 SUM 函数完全相同(代码清单 6)。

代码清单 6 计算销售单价的平均值

1
2
SELECT AVG(sale_price)
  FROM Product;

执行结果:

1
2
3
         avg
----------------------
2097.5000000000000000

平均值的计算式如下所示。

平均值的计算式

(值的合计)/(值的个数) 就是平均值的计算公式了。下面我们也像使用 SUM 函数那样,计算一下包含 NULL 的进货单价的平均值(代码清单 7)。

代码清单 7 计算销售单价和进货单价的平均值

1
2
SELECT AVG(sale_price), AVG(purchase_price)
  FROM Product;

执行结果:

执行结果

计算进货单价平均值的情况与 SUM 函数相同,会事先删除 NULL 再进行计算,因此计算式如下所示。

计算式

需要注意的是分母是 6 而不是 8,减少的两个也就是那两条 NULL 的数据。

但是有时也想将 NULL 作为 0 进行计算,具体的实现方式请参考 SQL 常用的函数

将 NULL 作为 0

1.6 计算最大值和最小值

想要计算出多条记录中的最大值或最小值,可以分别使用 MAXMIN 函数,它们是英语 maximam(最大值)和 minimum(最小值)的缩写,很容易记住。

这两个函数的语法与 SUM 的语法相同,使用时需要将列作为参数(代码清单 8)。

代码清单 8 计算销售单价的最大值和进货单价的最小值

1
2
SELECT MAX(sale_price), MIN(purchase_price)
  FROM Product;

执行结果:

执行结果

如图 1 所示,我们取得了相应的最大值和最小值。

但是,MAX/MIN 函数和 SUM/AVG 函数有一点不同,那就是 SUM/AVG 函数只能对数值类型的列使用,而 MAX/MIN 函数原则上可以适用于任何数据类型的列。

例如,对图 1 中日期类型的列 regist_date 使用 MAX/MIN 函数进行计算的结果如下所示(代码清单 9)。

代码清单 9 计算登记日期的最大值和最小值

1
2
SELECT MAX(regist_date), MIN(regist_date)
  FROM Product;

执行结果:

执行结果

刚刚我们说过 MAX/MIN 函数适用于任何数据类型的列,也就是说,只要是能够排序的数据,就肯定有最大值和最小值,也就能够使用这两个函数。

对日期来说,平均值和合计值并没有什么实际意义,因此不能使用 SUM/AVG 函数。

这点对于字符串类型的数据也适用,字符串类型的数据能够使用 MAX/MIN 函数,但不能使用 SUM/AVG 函数。

法则 3

MAX/MIN 函数几乎适用于所有数据类型的列。SUM/AVG 函数只适用于数值类型的列。

1.7 使用聚合函数删除重复值(关键字 DISTINCT)

接下来我们考虑一下下面这种情况。

在图 1 中我们可以看到,商品种类(product_type 列)和销售单价(sale_price 列)的数据中,存在多行数据相同的情况。

例如,拿商品种类来说,表中总共有 3 种商品共 8 行数据,其中衣服 2 行,办公用品 2 行,厨房用具 4 行。

如果想要计算出商品种类的个数,怎么做比较好呢?删除重复数据然后再计算数据行数似乎是个不错的办法。

实际上,在使用 COUNT 函数时,将 SQL 如何对表进行创建、更新和删除操作 中介绍过的 DISTINCT 关键字作为参数,就能得到我们想要的结果了(代码清单 10)。

代码清单 10 计算去除重复数据后的数据行数

1
2
SELECT COUNT(DISTINCT product_type)
  FROM Product;

执行结果:

1
2
3
 count
-------
     3

请注意,这时 DISTINCT 必须写在括号中。这是因为必须要在计算行数之前删除 product_type 列中的重复数据。

如果像代码清单 11 那样写在括号外的话,就会先计算出数据行数,然后再删除重复数据,结果就得到了 product_type 列的所有行数(也就是 8)。

代码清单 11 先计算数据行数再删除重复数据的结果

1
2
SELECT DISTINCT COUNT(product_type)
  FROM Product;

执行结果:

1
2
3
 count
-------
     8

法则 4

想要计算值的种类时,可以在 COUNT 函数的参数中使用 DISTINCT

不仅限于 COUNT 函数,所有的聚合函数都可以使用 DISTINCT

下面我们来看一下使用 DISTINCT 和不使用 DISTINCTSUM 函数的执行结果(代码清单 12)。

代码清单 12 使不使用 DISTINCT 时的动作差异(SUM 函数)

1
2
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
  FROM Product;

执行结果:

执行结果

左侧是未使用 DISTINCT 时的合计值,和我们之前计算的结果相同,都是 16780 元。

右侧是使用 DISTINCT 后的合计值,比之前的结果少了 500 元。

这是因为表中销售单价为 500 元的商品有两种——“打孔器”和“叉子”,在删除重复数据之后,计算对象就只剩下一条记录了。

法则 5

在聚合函数的参数中使用 DISTINCT,可以删除重复数据。

二、对表进行分组

本节重点

  • 使用 GROUP BY 子句可以像切蛋糕那样将表分割。通过使用聚合函数和 GROUP BY 子句,可以根据“商品种类”或者“登记日期”等将表分割后再进行汇总。

  • 聚合键中包含 NULL 时,在结果中会以“不确定”行(空行)的形式表现出来。

  • 使用聚合函数和 GROUP BY 子句时需要注意以下 4 点。

    1. 只能写在 SELECT 子句之中

    2. GROUP BY 子句中不能使用 SELECT 子句中列的别名

    3. GROUP BY 子句的聚合结果是无序的

    4. WHERE 子句中不能使用聚合函数

2.1 GROUP BY 子句

目前为止,我们看到的聚合函数的使用方法,无论是否包含 NULL,无论是否删除重复数据,都是针对表中的所有数据进行的汇总处理。

下面,我们先把表分成几组,然后再进行汇总处理。也就是按照“商品种类”“登记日期”等进行汇总。

这里我们将要第一次接触到 GROUP BY 子句,其语法结构如下所示。

语法 1 使用 GROUP BY 子句进行汇总

1
2
3
SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

下面我们就按照商品种类来统计一下数据行数(= 商品数量)(代码清单 13)。

代码清单 13 按照商品种类统计数据行数

1
2
3
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;

执行结果:

1
2
3
4
5
 product_type | count
--------------+------
 衣服         |     2
 办公用品     |     2
 厨房用具     |     4

如上所示,未使用 GROUP BY 子句时,结果只有 1 行,而这次的结果却是多行。这是因为不使用 GROUP BY 子句时,是将表中的所有数据作为一组来对待的。

而使用 GROUP BY 子句时,会将表中的数据分为多个组进行处理。如图 4 所示,GROUP BY 子句对表进行了切分。

按照商品种类对表进行切分

图 4 按照商品种类对表进行切分

这样,GROUP BY 子句就像切蛋糕那样将表进行了分组。在 GROUP BY 子句中指定的列称为聚合键或者分组列。由于能够决定表的切分方式,所以是非常重要的列。

当然,GROUP BY 子句也和 SELECT 子句一样,可以通过逗号分隔指定多列。

如果用画线的方式来切分表中数据的话,就会得到图 5 那样以商品种类为界线的三组数据。然后再计算每种商品的数据行数,就能得到相应的结果了。

按照商品种类对表进行切分

图 5 按照商品种类对表进行切分

法则 6

GROUP BY 就像是切分表的一把刀。

此外,GROUP BY 子句的书写位置也有严格要求,一定要写在 FROM 语句之后(如果有 WHERE 子句的话需要写在 WHERE 子句之后)。

如果无视子句的书写顺序,SQL 就一定会无法正常执行而出错。目前 SQL 的子句还没有全部登场,已经出现的各子句的暂定顺序如下所示。

子句的书写顺序(暂定)

1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

法则 7

SQL 子句的顺序不能改变,也不能互相替换。

2.2 聚合键中包含 NULL 的情况

接下来我们将进货单价(purchase_price)作为聚合键对表进行切分。在 GROUP BY 子句中指定进货单价的结果请参见代码清单 14。

代码清单 14 按照进货单价统计数据行数

1
2
3
SELECT purchase_price, COUNT(*)
  FROM Product
 GROUP BY purchase_price;

上述 SELECT 语句的结果如下所示:

执行结果

790 元或者 500 元这样进货单价很清楚的数据行不会有什么问题,结果与之前的情况相同。

问题是结果中的第一行,也就是进货单价为 NULL 的组。

从结果我们可以看出,当聚合键中包含 NULL 时,也会将 NULL 作为一组特定的数据,如图 6 所示。

按照进货单价对表进行切分

图 6 按照进货单价对表进行切分

这里的 NULL,大家可以理解为“不确定”。

法则 8

聚合键中包含 NULL 时,在结果中会以“不确定”行(空行)的形式表现出来。

2.3 使用 WHERE 子句时 GROUP BY 的执行结果

在使用了 GROUP BY 子句的 SELECT 语句中,也可以正常使用 WHERE 子句。子句的排列顺序如前所述,语法结果如下所示。

语法 2 使用 WHERE 子句和 GROUP BY 子句进行汇总处理

1
2
3
4
SELECT <列名1>, <列名2>, <列名3>, ……
  FROM <表名>
 WHERE
 GROUP BY <列名1>, <列名2>, <列名3>, ……;

像这样使用 WHERE 子句进行汇总处理时,会先根据 WHERE 子句指定的条件进行过滤,然后再进行汇总处理。请看代码清单 15。

代码清单 15 同时使用 WHERE 子句和 GROUP BY 子句

1
2
3
4
SELECT purchase_price, COUNT(*)
  FROM Product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;

因为上述 SELECT 语句首先使用了 WHERE 子句对记录进行过滤,所以实际上作为聚合对象的记录只有 2 行,如表 1 所示。

表 1 WHERE 子句过滤的结果

product_type(商品种类)product_name(商品名称)product_id(商品编号)sale_price(销售单价)purchase_price(进货单价)regist_date(登记日期)
衣服T 恤衫000110005002009-09-20
衣服运动 T 恤000340002800

使用进货单价对这 2 条记录进行分组,就得到了如下的执行结果:

1
2
3
4
purchase_price  | count
----------------+------
            500 |     1
           2800 |     1

GROUP BYWHERE 并用时,SELECT 语句的执行顺序如下所示。

GROUP BY 和 WHERE 并用时 SELECT 语句的执行顺序

FROM → WHERE → GROUP BY → SELECT

这与之前语法 2 中的说明顺序有些不同,这是由于在 SQL 语句中,书写顺序和 DBMS 内部的执行顺序并不相同。这也是 SQL 难以理解的原因之一。

2.4 与聚合函数和 GROUP BY 子句有关的常见错误

截至目前,我们已经介绍了聚合函数和 GROUP BY 子句的基本使用方法。虽然由于使用方便而经常被使用,但是书写 SQL 时却很容易出错,希望大家特别小心。

  • 常见错误 ① ——在 SELECT 子句中书写了多余的列

    在使用 COUNT 这样的聚合函数时,SELECT 子句中的元素有严格的限制。实际上,使用聚合函数时,SELECT 子句中只能存在以下三种元素。

    • 常数

    • 聚合函数

    • GROUP BY 子句中指定的列名(也就是聚合键)

    数据库和 SQL 是什么关系 中我们介绍过,常数就是像数字 123,或者字符串 '测试' 这样写在 SQL 语句中的固定值,将常数直接写在 SELECT 子句中没有任何问题。

    此外还可以书写聚合函数或者聚合键,这些在之前的示例代码中都已经出现过了。

    这里经常会出现的错误就是把聚合键之外的列名书写在 SELECT 子句之中。例如代码清单 16 中的 SELECT 语句就会发生错误,无法正常执行。

    代码清单 16 在 SELECT 子句中书写聚合键之外的列名会发生错误

    1
    2
    3
    
    SELECT product_name, purchase_price, COUNT(*)
      FROM Product
    GROUP BY purchase_price;
    

    执行结果(使用 PostgreSQL 的情况):

    1
    2
    
    ERROR:列"product,product_name"必须包含在GROUP BY子句之中,或者必须在聚合函数内使用
    行 1: SELECT product_name, purchase_price, COUNT(*)
    

    列名 product_name 并没有包含在 GROUP BY 子句当中。因此,该列名也不能书写在 SELECT 子句之中 3

    不支持这种语法的原因,大家仔细想一想应该就明白了。通过某个聚合键将表分组之后,结果中的一行数据就代表一组。

    例如,使用进货单价将表进行分组之后,一行就代表了一个进货单价。问题就出在这里,聚合键和商品名并不一定是一对一的

    例如,进货单价是 2800 元的商品有“运动 T 恤”和“菜刀”两种,但是 2800 元这一行应该对应哪个商品名呢(图 7)?

    如果规定了哪种商品优先表示的话则另当别论,但其实并没有这样的规则。

    聚合键和商品名不是一对一的情况

    图 7 聚合键和商品名不是一对一的情况

    像这样与聚合键相对应的、同时存在多个值的列出现在 SELECT 子句中的情况,理论上是不可能的。

    法则 9

    使用 GROUP BY 子句时,SELECT 子句中不能出现聚合键之外的列名。

  • 常见错误 ② ——在 GROUP BY 子句中写了列的别名

    这也是一个非常常见的错误。在 为列设定别名 中我们学过,SELECT 子句中的项目可以通过 AS 关键字来指定别名。

    但是,在 GROUP BY 子句中是不能使用别名的。代码清单 17 中的 SELECT 语句会发生错误 4

    代码清单 17 GROUP BY 子句中使用列的别名会引发错误

    代码清单 17 GROUP BY 子句中使用列的别名会引发错误

    上述语句发生错误的原因之前已经介绍过了,是 SQL 语句在 DBMS 内部的执行顺序造成的——SELECT 子句在 GROUP BY 子句之后执行。

    在执行 GROUP BY 子句时,SELECT 子句中定义的别名,DBMS 还并不知道。

    使用 PostgreSQL 执行上述 SQL 语句并不会发生错误,而会得到如下结果。但是这样的写法在其他 DBMS 中并不是通用的,因此请大家不要使用

    执行结果(使用 PostgreSQL 的情况):

    1
    2
    3
    4
    5
    
          pt     | count
    -------------+------
    衣服         |     2
    办公用品     |     2
    厨房用具     |     4
    

    法则 10

    GROUP BY 子句中不能使用 SELECT 子句中定义的别名。