DB基础第十二篇：对表进行分组

GROUP BY 子句  

    目前为止，我们看到的聚合函数的使用方法，无论是否包含 NULL ，无论是否删除重复数据，都是针对表中的所有数据进行的汇总处理。下面，我们先把表分成几组，然后再进行汇总处理。也就是按照“商品种类”“登记日期”等进行汇总。这里我们将要第一次接触到 GROUP BY 子句，其语法结构如下所示。

语法3-1　使用 GROUP BY 子句进行汇总
SELECT < 列名 1>, < 列名 2>, < 列名 3>,  ……
FROM < 表名 >
GROUP BY < 列名 1>, < 列名 2>, < 列名 3>,  …… ;

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

代码清单3-13　按照商品种类统计数据行数
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

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

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

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

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

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

子句的书写顺序（暂定）
1.  SELECT → 2.  FROM → 3.  WHERE → 4.  GROUP BY

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

聚合键中包含 NULL 的情况

     接下来我们将进货单价（ purchase _ price ）作为聚合键对表进行切分。在 GROUP BY 子句中指定进货单价的结果请参见代码清单 3-14。

代码清单3-14　按照进货单价统计数据行数
SELECT purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;

     像 790 日元或者 500 日元这样进货单价很清楚的数据行不会有什么问题，结果与之前的情况相同。问题是结果中的第一行，也就是进货单价为NULL 的组。从结果我们可以看出，当聚合键中包含 NULL 时，也会将NULL 作为一组特定的数据，如图 3-6 所示。

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

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

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

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

语法3-2　使用 WHERE 子句和 GROUP BY 子句进行汇总处理
SELECT < 列名 1>, < 列名 2>, < 列名 3>,  ……
FROM < 表名 >
WHERE
GROUP BY < 列名 1>, < 列名 2>, < 列名 3>,  …… ;

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

代码清单3-15　同时使用 WHERE 子句和 GROUP BY 子句
SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = ' 衣服 '
GROUP BY purchase_price;

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

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

GROUP BY 和 WHERE 并用时， SELECT 语句的执行顺序如下所示。

 SELECT 语句的执行顺序FROM →  WHERE →  GROUP BY →  SELECT

这是由于在 SQL 语句中，书写顺序和 DBMS 内部的执行顺序并不相同。这也是 SQL 难以理解的原因之一。

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

① ——在 SELECT 子句中书写了多余的列

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

    ● 常数

    ● 聚合函数

    ● GROUP BY 子句中指定的列名（也就是聚合键）

    常数就是像数字 123，或者字符串 ' 测试 ' 这样写在 SQL 语句中的固定值，将常数直接写在 SELECT 子句中没有任何问题。此外还可以书写聚合函数或者聚合键，这些在之前的示例代码中都已经出现过了。

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

代码清单3-16　在 SELECT 子句中书写聚合键之外的列名会发生错误
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 子句之中。

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

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

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

② ——在 GROUP BY 子句中写了列的别名

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

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

③ ——  GROUP BY 子句的结果能排序吗

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

答案是：“随机的。”

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

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

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

④ ——在 WHERE 子句中使用聚合函数  

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

代码清单3-18　按照商品种类统计数据行数
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;

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

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

代码清单3-19　在 WHERE 子句中使用聚合函数会引发错误
SELECT product_type, COUNT(*)
FROM Product
WHERE COUNT(*) = 2
GROUP BY product_type;

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

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

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

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

    ① 只能写在 SELECT 子句之中

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

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

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

                               <END>