SQL 对表进行分组(GROUP BY)

学习重点

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

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

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

    ① 只能写在 SELECT 子句之中

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

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

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

一、GROUP BY 子句#

目前为止,我们看到的聚合函数的使用方法,无论是否包含 NULL,无论是否删除重复数据,都是针对表中的所有数据进行的汇总处理。下面,我们先把表分成几组,然后再进行汇总处理。也就是按照“商品种类”“登记日期”等进行汇总。

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

KEYWORD

  • 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;

执行结果

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

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

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

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

这样,GROUP BY 子句就像切蛋糕那样将表进行了分组。在 GROUP BY 子句中指定的列称为聚合键或者分组列。由于能够决定表的切分方式,所以是非常重要的列。当然,GROUP BY 子句也和 SELECT 子句一样,可以通过逗号分隔指定多列。

KEYWORD

  • 聚合键

  • 分组列

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

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

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

法则 6

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

此外,GROUP BY 子句的书写位置也有严格要求,一定要写在 FROM 语句之后(如果有 WHERE 子句的话需要写在 WHERE 子句之后)。如果无视子句的书写顺序,SQL 就一定会无法正常执行而出错。目前 SQL 的子句还没有全部登场,已经出现的各子句的暂定顺序如下所示。

▶ 子句的书写顺序(暂定)

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

法则 7

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

二、聚合键中包含 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 时,在结果中会以“不确定”行(空行)的形式表现出来。

三、使用 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 恤衫 0001 1000 500 2009-09-20
衣服 运动 T 恤 0003 4000 2800

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

执行结果

purchase_price  | count
----------------+------
            500 |     1
           2800 |     1

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

GROUP BYWHERE 并用时 SELECT 语句的执行顺序

FROMWHEREGROUP BYSELECT

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

四、与聚合函数和 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 的情况)

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

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

    不支持这种语法的原因,大家仔细想一想应该就明白了。通过某个聚合键将表分组之后,结果中的一行数据就代表一组。例如,使用进货单价将表进行分组之后,一行就代表了一个进货单价。问题就出在这里,聚合键和商品名并不一定是一对一的

    例如,进货单价是 2800 日元的商品有“运动 T 恤”和“菜刀”两种,但是 2800 日元这一行应该对应哪个商品名呢(图 7)?如果规定了哪种商品优先表示的话则另当别论,但其实并没有这样的规则。

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

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

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

    法则 9

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

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

    这也是一个非常常见的错误。在 SELECT 语句基础 中我们学过,SELECT 子句中的项目可以通过 AS 关键字来指定别名。但是,在 GROUP BY 子句中是不能使用别名的。代码清单 17 中的 SELECT 语句会发生错误 2

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

    GROUP BY 子句中使用列的别名会引发错误

    上述语句发生错误的原因之前已经介绍过了,是 SQL 语句在 DBMS 内部的执行顺序造成的——SELECT 子句在 GROUP BY 子句之后执行。在执行 GROUP BY 子句时,SELECT 子句中定义的别名,DBMS 还并不知道。

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

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

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

    法则 10

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

  • 常见错误 ③ —— GROUP BY 子句的结果能排序吗

    GROUP BY 子句的结果通常都包含多行,有时可能还会是成百上千行。那么,这些结果究竟是按照什么顺序排列的呢?

    答案是:“随机的。”

    我们完全不知道结果记录是按照什么规则进行排序的。可能乍一看是按照行数的降序或者聚合键的升序进行排列的,但其实这些全都是偶然的。当你再次执行同样的 SELECT 语句时,得到的结果可能会按照完全不同的顺序进行排列。

    通常 SELECT 语句的执行结果的显示顺序都是随机的,因此想要按照某种特定顺序进行排序的话,需要在 SELECT 语句中进行指定。

    KEYWORD

    • 排序

    法则 11

    GROUP BY 子句结果的显示是无序的。

  • 常见错误 ④ ——在 WHERE 子句中使用聚合函数

    最后要介绍的是初学者非常容易犯的一个错误。我们还是先来看一下之前提到的按照商品种类(product_type 列)对表进行分组,计算每种商品数据行数的例子吧。SELECT 语句如代码清单 18 所示。

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

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

    执行结果

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

    如果我们想要取出恰好包含 2 行数据的组该怎么办呢?满足要求的是“办公用品”和“衣服”。

    想要指定选择条件时就要用到 WHERE 子句,初学者通常会想到使用代码清单 19 中的 SELECT 语句吧。

    代码清单 19 在 WHERE 子句中使用聚合函数会引发错误

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

    遗憾的是,这样的 SELECT 语句在执行时会发生错误。

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

    ERROR: 不能在WHERE子句中使用聚合
    行 3: WHERE COUNT(*) = 2
    

    实际上,只有 SELECT 子句和 HAVING 子句(以及之后将要学到的 ORDER BY 子句)中能够使用 COUNT 等聚合函数。并且,HAVING 子句可以非常方便地实现上述要求。

    法则 12

    只有 SELECT 子句和 HAVING 子句(以及 ORDER BY 子句)中能够使用聚合函数。

专栏

DISTINCTGROUP BY

细心的读者可能会发现,对表进行聚合查询 中介绍的 DISTINCT 和本文介绍的 GROUP BY 子句,都能够删除后续列中的重复数据。例如,代码清单 A 中的 2 条 SELECT 语句会返回相同的结果。

代码清单 A DISTINCTGROUP BY 能够实现相同的功能

1
2
3
4
5
6
SELECT DISTINCT product_type
 FROM Product;

SELECT product_type
 FROM Product
GROUP BY product_type;

执行结果

product_type
--------------
衣服
办公用品
厨房用具

除此之外,它们还都会把 NULL 作为一个独立的结果返回,对多列使用时也会得到完全相同的结果。其实不仅处理结果相同,执行速度也基本上差不多 3,那么到底应该使用哪一个呢?

但其实这个问题本身就是本末倒置的,我们应该考虑的是该 SELECT 语句是否满足需求。选择的标准其实非常简单,在“想要删除选择结果中的重复记录”时使用 DISTINCT,在“想要计算汇总结果”时使用 GROUP BY

不使用 COUNT 等聚合函数,而只使用 GROUP BY 子句的 SELECT 语句,会让人觉得非常奇怪,使人产生“到底为什么要对表进行分组呢?这样做有必要吗?”等疑问。

SQL 语句的语法与英语十分相似,理解起来非常容易,如果大家浪费了这一优势,编写出一些难以理解的 SQL 语句,那就太可惜了。

请参阅#

(完)


  1. 不过,只有 MySQL 认同这种语法,所以能够执行,不会发生错误(在多列候补中只要有一列满足要求就可以了)。但是 MySQL 以外 的 DBMS 都不支持这样的语法,因此请不要使用这样的写法。 ↩︎

  2. 需要注意的是,虽然这样的写法在 PostgreSQL 和 MySQL 都不会发生执行错误,但是这并不是通常的使用方法 ↩︎

  3. 它们都是数据的内部处理,都是通过排序处理来实现的。 ↩︎

comments powered by Disqus