SQL基础教程笔记：第三章 聚合与排序

目录

对表进行聚合查询

聚合函数

  • COUNT： 计算表中的记录数（行数），
  • SUM： 计算表中数值列中数据的合计值，
  • AVG： 计算表中数值列中数据的平均值，
  • MAX： 求出表中任意列中数据的最大值，
  • MIN： 求出表中任意列中数据的最小值，

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

计算表中数据的行数COUNT(*)

-- 计算全部数据的行数
SELECT COUNT(*)
FROM Product;

image

计算NULL之外的数据的行数COUNT(col_name)

想要计算表中全部数据的行数时，可以像 SELECT COUNT(*)~ 这样使用星号。如果想得到 purchase_price 列（进货单价）中非空行
数的话，可以通过将对象列设定为参数来实现。

-- 计算NULL之外的数据行数
SELECT COUNT(purchase_price)
FROM Product;

image

对于COUNT函数来说，参数列不同计算的结果也会发生变化，这一点请大家特别注意。为了有助于大家理解，请看如下这个只包含NULL 的表的极端例子。
image
我们来看一下针对上述表，将星号（*）和列名作为参数传递给COUNT 函数时所得到的结果.

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

image
COUNT函数的结果根据参数的不同而不同。 COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。该特性是 COUNT 函数所特有的，其他函数并不能将星号作为参数（如果使用星号会出错）.

计算合计值的 SUM 函数

-- 计算销售单价和进货单价的合计值
SELECT SUM(sale_price), SUM(purchase_price)
FROM Product;

image
所有的聚合函数，如果以列名为参数，那么在计算之前就已经把NULL排除在外了。

计算平均值的AVG 函数

-- 计算销售单价和进货单价的平均值
SELECT AVG(sale_price), AVG(purchase_price)
FROM Product;

image
计算进货单价平均值的情况与SUM函数相同，会事先删除NULL再进行计算，因此计算式如下所示。
image
需要注意的是分母是6而不是8，减少的两个也就是那两条 NULL的数据。

计算最大值的MAX函数和最小值的MIN函数

这两个函数的语法与 SUM 的语法相同，使用时需要将列作为参数。

-- 计算销售单价的最大值和进货单价的最小值
SELECT MAX(sale_price), MIN(purchase_price)
FROM Product;

image

但是，MAX/MIN函数和SUM/AVG函数有一点不同，那就是SUM/AVG函数只能对数值类型的列使用，而 MAX/MIN 函数原则上可以适用于任何数据类型的列。也就是说，只要是能够排序的数据，就肯定有最大值和最小值，也就能够使用这两个函数。对日期来说，平均值和合计值并没有什么实际意义，因此不能使用SUM/AVG 函数。这点对于字符串类型的数据也适用，字符串类型的数据能够使用 MAX/MIN 函数，但不能使用 SUM/AVG 函数。

-- 计算登记日期的最大值和最小值
SELECT MAX(regist_date), MIN(regist_date)
FROM Product;

image

使用聚合函数删除重复值（关键字DISTINCT）

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

-- 计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
FROM Product;

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

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

-- 使不使用DISTINCT时的动作差异（SUM函数）
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
FROM Product;

image

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

对表进行分组

GROUP BY子句

目前为止，我们看到的聚合函数的使用方法，无论是否包含NULL，无论是否删除重复数据，都是针对表中的所有数据进行的汇总处理。下面，我们先把表分成几组，然后再进行汇总处理。
使用GROUP BY子句进行汇总，

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

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

image
如上所示，未使用 GROUP BY 子句时，结果只有 1 行，而这次的结果却是多行。这是因为,

  • 不使用 GROUP BY 子句时，是将表中的所有数据作为一组来对待的。
  • 而使用 GROUP BY 子句时，会将表中的数据分为多个组进行处理。

如图 3-4 所示， GROUP BY 子句对表进行了切分。
image

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

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

此外，GROUP BY子句的书写位置也有严格要求，一定要写在FROM语句之后（如果有 WHERE 子句的话需要写在 WHERE 子句之后）。如果无视子句的书写顺序， SQL 就一定会无法正常执行而出错。

聚合键中包含NULL的情况

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

image
问题是结果中的第一行，也就是进货单价为NULL的组。从结果我们可以看出，当聚合键中包含 NULL 时，也会将NULL 作为一组特定的数据，如图 3-6 所示。
image
这里的 NULL，大家可以理解为“不确定”。聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。

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

使用WHERE子句和GROUP BY子句进行汇总处理,

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

像这样使用WHERE子句进行汇总处理时，会先根据WHERE子句指定的条件进行过滤，然后再进行汇总处理。

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

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

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

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

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

常见错误一，在SELECT子句中书写了多余的列

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

  • 常数
  • 聚合函数
  • GROUP BY子句中指定的列名（也就是聚合键）

这里经常会出现的错误就是把聚合键之外的列名书写在SELECT子句之中。

-- 在SELECT子句中书写聚合键之外的列名会发生错误
SELECT product_name, purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;

image

列名product_name并没有包含在 GROUP BY 子句当中。因此，该列名也不能书写在 SELECT 子句之中.不过，只有 MySQL 认同这种语法，所以能够执行，不会发生错误（在多列候补中只要有一列满足要求就 可 以 了 ）。但 是 MySQL 以 外 的DBMS 都不支持这样的语法，因此请不要使用这样的写法。

不支持这种语法的原因，大家仔细想一想应该就明白了。像这样与聚合键相对应的、同时存在多个值的列出现在 SELECT 子句中的情况，理论上是不可能的。
image

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

常见错误二，在GROUP BY子句中写了列的别名

这也是一个非常常见的错误。在2-2节中我们学过，SELECT子句中的项目可以通过AS关键字来指定别名。但是，在GROUP BY子句中是不能使用别名的。
image
上述语句发生错误的原因之前已经介绍过了，是SQL语句在DBMS内部的执行顺序造成的——SELECT 子句在 GROUP BY 子句之后执行。在执行 GROUP BY 子句时， SELECT 子句中定义的别名， DBMS 还并不知道。

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

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

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

常见错误三， GROUP BY子句的结果能排序吗

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

答案是：“随机的。”

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

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

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

常见错误四，在WHERE子句中使用聚合函数

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

image

如果我们想要取出恰好包含2行数据的组该怎么办呢？满足要求的是“办公用品”和“衣服”。想要指定选择条件时就要用到 WHERE 子句，初学者通常会想到使用代码清单 3-19 中的 SELECT 语句吧
image

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

image

为聚合结果指定条件

学习重点

  • 使用COUNT函数等对表中数据进行汇总操作时，为其指定条件的不是WHERE子句，而是HAVING子句。
  • 聚合函数可以在SELECT子句、 HAVING子句和ORDER BY子句中使用。
  • HAVING子句要写在GROUP BY子句之后。
  • WHERE子句用来指定数据行的条件， HAVING子句用来指定分组的条件。

HAVING子句

image
说到指定条件，估计大家都会首先想到 WHERE 子句。但是， WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件（例如，“数据行数为2行”或者“平均值为500”等）。因此，对集合指定条件就需要使用其他的子句了，此时便可以用HAVING 子句(HAVING 是HAVE（拥有）的现在分词，并不是通常使用的英语单词)。
image

HAVING子句必须写在GROUP BY子句之后，其在 DBMS 内部的执行顺序也排在 GROUP BY 子句之后。
image

-- 从按照商品种类进行分组后的结果中，取出“包含的数据行数为2行”的组
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;

image

SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price) >= 2500;

image

HAVING子句的构成要素

HAVING子句和包含GROUP BY子句时的SELECT子句一样，能够使用的要素有一定的限制，限制内容也是完全相同的。 HAVING 子句中能够使用的 3 种要素如下所示。

  • 常数
  • 聚合函数
  • GROUP BY子句中指定的列名（即聚合键）

代码清单 3-20 中的例文指定了 HAVING COUNT（）= 2 这样的条件，其中 COUNT（）是聚合函数， 2 是常数，全都满足上述要求。反之，如果写成了下面这个样子就会发生错误（代码清单 3-24）
image

相对于HAVING子句，更适合写在WHERE子句中的条件

也许有的读者已经发现了，有些条件既可以写在 HAVING 子句当中，又可以写在 WHERE 子句当中。这些条件就是聚合键所对应的条件。原表中作为聚合键的列也可以在 HAVING 子句中使用。

-- 将条件书写在HAVING子句中的情况
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING product_type = '衣服';

image

-- 将条件书写在WHERE子句中的情况
SELECT product_type, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY product_type;

image

虽然条件分别写在 WHERE 子句和 HAVING 子句当中，但是条件的内容以及返回的结果都完全相同。因此，大家可能会觉得两种书写方式都没问题。如果仅从结果来看的话，确实如此。但笔者却认为，聚合键所对应的条件还是应该书写在 WHERE 子句之中。理由有两个:

  • 首先，根本原因是 WHERE 子句和 HAVING 子句的作用不同。如前所述， HAVING 子句是用来指定“组”的条件的。因此，“行”所对应的条件还是应该写在 WHERE 子句当中。这样一来，书写出的 SELECT 语句不但可以分清两者各自的功能，理解起来也更加容易。
    image

  • 其次，WHERE 子句和 HAVING 子句的执行速度.

聚合键所对应的条件不应该书写在HAVING子句当中，而应该书写在WHERE子句当中。

image

对查询结果进行排序

ORDER BY子句

通常，从表中抽取数据时，如果没有特别指定顺序，最终排列顺序便无从得知。即使是同一条 SELECT 语句，每次执行时排列顺序很可能发生改变。但是不进行排序，很可能出现结果混乱的情况。这时，便需要通过在SELECT 语句末尾添加 ORDER BY 子句来明确指定排列顺序。
image

-- 按照销售单价由低到高（升序）进行排列
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price;

image

不论何种情况， ORDER BY 子句都需要写在 SELECT 语句的末尾。这是因为对数据行进行排序的操作必须在结果即将返回时执行。 ORDER BY子句中书写的列名称为排序键。 该子句与其他子句的顺序关系如下所示。
image

指定升序或降序

-- 按照销售单价由高到低（降序）进行排列
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price DESC;

image
其实，使用升序进行排列时，正式的书写方式应该是使用关键字 ASC，但是省略该关键字时会默认使用升序进行排序。这可能是因为实际应用中按照升序排序的情况更多吧。 ASC 和 DESC 是 ascendent（上升的）和descendent（下降的）这两个单词的缩写.由于 ASC 和 DESC 这两个关键字是以列为单位指定的，因此可以同时指定一个列为升序，指定其他列为降序。

指定多个排序键

SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price, product_id;

image
这样一来，就可以在ORDER BY子句中同时指定多个排序键了。规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键。当然，也可以同时使用 3 个以上的排序键。

NULL的顺序

不能对NULL 使用比较运算符，也就是说，不能对 NULL 和数字进行排序，也不能与字符串和日期比较大小。因此，使用含有 NULL 的列作为排序键时，NULL 会在结果的开头或末尾汇总显示.

SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY purchase_price;

image
究竟是在开头显示还是在末尾显示，并没有特殊规定。某些 DBMS中可以指定 NULL 在开头或末尾显示，希望大家对自己使用的 DBMS 的功能研究一下。

在排序键中使用显示用的别名

-- ORDER BY子句中可以使用列的别名
SELECT product_id AS id, product_name, sale_price AS sp, purchase 
_price
FROM Product
ORDER BY sp, id;

image

不能在 GROUP BY 子句中使用的别名，为什么可以在 ORDER BY子句中使用呢？这是因为 SQL 语句在 DBMS 内部的执行顺序被掩盖起来了。 SELECT 语句按照子句为单位的执行顺序如下所示。
image
这只是一个粗略的总结，虽然具体的执行顺序根据 DBMS 的不同而不同，但是大家有这样一个大致的印象就可以了。一定要记住 SELECT 子句的执行顺序在 GROUP BY 子句之后， ORDER BY 子句之前。因此，在执行 GROUP BY 子句时， SELECT 语句中定义的别名无法被识别 。对于在 SELECT 子句之后执行的 ORDER BY 子句来说，就没有这样的问题了。

ORDER BY子句中可以使用的列

-- SELECT子句中未包含的列也可以在ORDER BY子句中使用
SELECT product_name, sale_price, purchase_price
FROM Product
ORDER BY product_id;

image

-- ORDER BY子句中也可以使用聚合函数
SELECT product_type, COUNT(*),COUNT(regist_date)
FROM Product
GROUP BY product_type
ORDER BY COUNT(regist_date) desc;

image

-- ORDER BY子句中也可以使用聚合函数
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
ORDER BY COUNT(regist_date) desc;

image

不要使用列编号

在 ORDER BY 子句中，还可以使用在 SELECT 子句中出现的列所对应的编号，列编号是指 SELECT 子句中的列按照从左到右的顺序进行排列时所对应的编号（1, 2, 3, …）。

-- 通过列名指定
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price DESC, product_id;

-- 通过列编号指定
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY 3 DESC, 1;

虽然列编号使用起来非常方便，但我们并不推荐使用，原因有以下两点。

  • 第一， 代码阅读起来比较难。使用列编号时，如果只看 ORDER BY子句是无法知道当前是按照哪一列进行排序的，只能去 SELECT 子句的列表中按照列编号进行确认。上述示例中 SELECT 子句的列数比较少，因此可能并没有什么明显的感觉。但是在实际应用中往往会出现列数很多的情况，而且 SELECT 子句和 ORDER BY 子句之间，还可能包含很复杂的 WHERE 子句和 HAVING 子句，直接人工确认实在太麻烦了。
  • 第二，这也是最根本的问题，实际上，在 SQL-92 中已经明确指出该排序功能将来会被删除。因此，虽然现在使用起来没有问题，但是将来随着 DBMS 的版本升级，可能原本能够正常执行的 SQL 突然就会出错。不光是这种单独使用的 SQL 语句，对于那些在系统中混合使用的 SQL 来说，更要极力避免。

练习题

3.1 请指出下述 SELECT 语句中所有的语法错误。
image
存在以下 3 个错误。

  • 使用了字符类型的列（product_name）作为 SUM 函数的参数。

解答
SUM 函数只能使用数值类型的列作为参数。

  • WHERE 子句写在了 GROUP BY 子句之后。

解答
WHERE 子句必须写在 GROUP BY 子句之前。

  • SELECT 子句中存在 GROUP BY 子句中未指定的列（product_id）。

解答
使用 GROUP BY 子句时，书写在 SELECT 子句中的列有很多限制。 GROUP BY子句中未指定的列不能书写在 SELECT 子句之中。此外，虽然在 SELECT 子句和 FROM 子句之间添加注释在语法上没有问题，但因为这样会使 SQL 语句难以阅读，所以请不要这样书写。在 WHERE 子句中指定 regist_date 的大小关系作为条件并没有什么问题。

3.2 请编写一条 SELECT 语句，求出销售单价（sale_price 列）合计值是进货单价（purchase_price 列）合计值 1.5 倍的商品种类。执行结果如下所示。
image

SELECT product_type, SUM(sale_price), SUM(purchase_price)
FROM Product
GROUP BY product_type
HAVING SUM(sale_price) > SUM(purchase_price) * 1.5;

3.3 此前我们曾经使用 SELECT 语句选取出了 Product（商品）表中的全部记录。当时我们使用了 ORDER BY 子句来指定排列顺序，但现在已经无法记起当时如何指定的了。请根据下列执行结果，思考 ORDER BY 子句的内容。
image

SELECT *
FROM Product
ORDER BY regist_date DESC, sale_price;

posted on 2022-10-27 17:54  朴素贝叶斯  阅读(156)  评论(0)  编辑  收藏  举报

导航