3 SQL 聚合与排序

阅读量：4981 次

发布时间：2019-06-12

本文共 8071 字，大约阅读时间需要 26 分钟。

3 聚合与排序

3-1 对表进行聚合查询

聚合函数

通过SQL对数据进行操作或计算时需要使用函数。

计算表中全部数据行数时，可以使用COUNT函数。

COUNT : 计算表中的记录数（行数）。

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

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

MAX ：计算表中任意列中数据的最大值。

MIN ：计算表中任意列中数据的最小值。

如上所示，用于合计的函数称为聚合函数或者集合函数。本书中统称为聚合函数。所谓聚合，就是将多行汇总为一行。

函数这个词，与我们在学校课上学到的意思是一样的，就像是输入某个值就能输出相应结果的盒子一样。

计算表中数据的行数

COUNT 计算表中的记录数（行数）

sum 计算表中数值列的数据合计值

avg 计算表中数值列的数据平均值

max 求出表中任意列中数据的最大值

min 求出表中任意列中数据的最小值

-- 此处的输入值称为参数或者parameter，输出值称为返回值

SELECT COUNT(*) FROM shohin;

SELECT COUNT(shiire_tanka) FROM shohin;

即使对同一张表使用COUNT函数，输入的参数不同得到的结果也会不同。

星号对于COUNT函数来说是独有的，其他函数并不能将星号作为参数（如果使用星号会出错）。

计算NULL以外数据的行数

SELECT COUNT(shiire_tanka) FROM shohin;

对于COUNT函数来说，参数列不同计算的结果也会发生变化。

法则3-1

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

计算合计值

SELECT SUM(hanbai_tanka) FROM shohin;

SELECT SUM(shiire_tanka) FROM shohin;

法则3-2

聚合函数会将NULL排除在外。但COUNT（*）例外，并不会排除NULL。

计算平均值

-- （值的合计）/（值的个数）就是平均值的计算公式如果遇到空的情况会先剔除null 值的合计和值的个数都需要去除。

SELECT AVG(hanbai_tanka), AVG(shiire_tanka) FROM shohin;

计算最大值和最小值

SELECT MAX(hanbai_tanka), MIN(shiire_tanka) FROM shohin;

SELECT MAX(torokubi), MIN(torokubi) FROM shohin;

法则3-3

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

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

SELECT COUNT(DISTINCT shohin_bunrui) FROM shohin;

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

SELECT SUM(hanbai_tanka), SUM(DISTINCT hanbai_tanka) FROM shohin;

SELECT DISTINCT COUNT(shohin_bunrui) FROM shohin;

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

法则3-4

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

法则3-5

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

3-2 对表进行分组

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

GROUP BY子句

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

SELECT shohin_bunrui, COUNT(*) FROM shohin;

SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui;

这样，GROUP BY子句就像切蛋糕那样将表进行分组。在GROUP BY子句中指定的列称为聚合键或者分组列。

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

法则3-6

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

法则3-7

SQL子句的顺序不能改变，也不能互换位置。

子句的书写顺序（暂定）：SELECT > FROM > WHERE > GROUP BY

聚合键中包含NULL的情况

SELECT shiire_tanka, COUNT(*) FROM shohin GROUP BY shiire_tanka;

-- 当聚合键中包含null时，也会将null作为一组特定的数据这里的null 大家可以理解为“不确定”

SELECT shiire_tanka, COUNT(*) FROM shohin GROUP BY shiire_tanka;

从结果我们可以看出，当聚合键中包含NULL时，也会将NULL作为一组特定的数据。这里的NULL，大家可以理解为“不确定”。

法则3-8

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

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

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

SELECT shiire_tanka, COUNT(*) FROM shohin WHERE shohin_bunrui = '衣服';

SELECT shiire_tanka, COUNT(*) FROM shohin WHERE shohin_bunrui = '衣服' GROUP BY shiire_tanka;

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

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

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

-- 其他dbms可能报错

SELECT shohin_mei, shiire_tanka, COUNT(*) FROM shohin GROUP BY shiire_tanka;

SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui;

SELECT shohin_bunrui AS sb, COUNT(*) FROM shohin GROUP BY sb;

SELECT shohin_bunrui, COUNT(*) FROM shohin WHERE COUNT(2) GROUP BY shohin_bunrui;

法则3-9

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

法则3-10

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

法则3-11

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

法则3-12

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

DISTINCT和GROUP BY

-- DISTINCT和GROUP BY能够实现相同的功能

SELECT DISTINCT shohin_bunrui FROM shohin;

SELECT shohin_bunrui FROM shohin GROUP BY shohin_bunrui;

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

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

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

3-3 为聚合结果指定条件

HVING子句

SELECT <列名1>, <列名2>, ...... FROM <表名> GROUP BY <列名1>, <列名2>, ...... HAVING <分组结果对应的条件>;

SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui;

SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui HAVING COUNT(*) = 2;

SELECT shohin_bunrui, AVG(hanbai_tanka) FROM shohin GROUP BY shohin_bunrui;

SELECT shohin_bunrui, AVG(hanbai_tanka) FROM shohin GROUP BY shohin_bunrui HAVING AVG(hanbai_tanka) >= 2500;

说到指定条件，估计大家都会首先想到WHERE子句。但是，WHERE子句只能指定记录（行）的条件，而不能用来指定组的条件（例如，“数据行数为2行”或者“平均值为500等”）。

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

使用HAVING子句时SELECT语句的顺序：SELECT > FROM > WHERE > GROUP BY > HAVING

法则3-13

HAVING子句要写在GROUP BY子句之后。

HAVING子句的构成要素

-- HAVING子句中能够使用的3种要素：常数/聚合函数/GROUP BY子句中指定的列名（即聚合键）

SELECT shohin_mei, shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_mei HAVING shohin_mei = '圆珠笔';

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

SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui HAVING shohin_bunrui = '衣服';

SELECT shohin_bunrui, COUNT(*) FROM shohin WHERE shohin_bunrui = '衣服' GROUP BY shohin_bunrui;

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

如果仅从结果来看的话，确实如此。但笔者却认为，聚合键所对应的条件还是应该书写在WHERE子句之中。

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

Where子句 = 指定行所对应的条件

Having子句 = 指定组所对应的条件

其次where执行速度快一点

通常情况下，为了得到相同的结果，将条件写在WHERE子句中要比写在HAVING子句中的处理速度更快，返回结果所需时间更短。

为了理解其中原因，就要从DBMS的内部运行机制来考虑。使用COUNT函数等对表中的数据进行聚合操作时，DBMS内部就会进行排序处理。排序处理会大大增加机器的负担，此即所谓高负荷的处理。因此，只有尽可能减少排序的行数，才能增加处理速度。

通过WHERE子句指定条件时，由于排序之前就对数据进行了过滤，所以能够减少排序的数据量。但HAVING子句是在排序之后才对数据进行分组的，因此与在WHERE子句中指定条件比起来，需要排序的数据量就会多得多。虽然DBMS的内部处理不尽相同，但是对于排序处理来说，基本上都是一样的。

此外，WHERE子句更具速度优势的另一个理由是，可以对WHERE子句指定条件所对应的列创建索引，这样也可以大幅度提高处理速度。创建索引是一种非常普遍的提高DBMS性能的方法，效果也十分明显，这对WHERE子句来说也十分有利。

法则3-14

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

3-4 对查询结果进行排序

通常，从表中抽取数据时，如果没有特别指定顺序，最终排列顺序便无从得知。即使是同一条SELECT语句，每次执行时排列顺序很可能发生改变。

但是不进行排序，很可能出现结果混乱的情况。这时，便需要通过在SELECT语句末尾添加ORDER BY子句来明确指定排列顺序。

ORDER BY子句

-- SELECT <列名1>, <列名2>, ...... FROM <表名> ORDER BY <排序基准列1>, <排序基准列2>, ...;

SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin;

SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY hanbai_tanka;

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

子句的书写顺序：SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY

法则3-15

ORDER BY子句通常写在SELECT语句的末尾。

指定升序（ASC）和降序（DESC）

SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY hanbai_tanka DESC;

SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY hanbai_tanka ASC;

由于ASC和DESC这两个关键字是以列为单位指定的，所以可以同时指定一个列为升序，指定其他列为降序。

法则3-16

未指定ORDER BY子句中排列顺序时会默认使用升序进行排列。

指定多个排序键

SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY hanbai_tanka, shohin_id;

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

NULL的顺序

SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY shiire_tanka;

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

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

法则3-17

排序键中包含NULL时，会在开头或末尾进行汇总。

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

SELECT shohin_id AS id, shohin_mei, hanbai_tanka AS ht, shiire_tanka FROM shohin ORDER BY ht, id;

不能在GROUP BY子句中使用的别名，为什么可以在ORDER BY子句中使用呢？这是因为SQL语句在DBMS内部的执行顺序被掩盖起来了。

使用HAVING子句时SELECT语句执行顺序：FROM > WHERE >GROUP BY > HAVING > SELECT > ORDER BY

这只是一个粗略的总结，虽然具体的执行顺序根据DBMS的不同而不同，但是大家有这样一个大致的印象就可以了。一定要记住SELECT子句的执行顺序在GROUP BY子句之后，ORDER BY子句之前。因此，在执行GROUP BY子句时，SELECT语句中定义的别名无法被识别。对于在SELECT子句之后执行的ORDER BY子句来说，就没有这样的问题了。

法则3-18

在ORDER BY子句中可以使用SELECT子句中定义的别名。

ORDER BY 子句中可以使用存在于表中，但并不包含在SELECT子句中的列。除此之外，还可以使用聚合函数。

ORDER BY子句中可以使用的列

SELECT shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY shohin_id;

SELECT shohin_bunrui, COUNT(*) FROM shohin GROUP BY shohin_bunrui ORDER BY COUNT(*);

法则3-19

在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。

不要使用列编号

-- ORDER BY 子句中可以使用列的编号，列编号是指SELECT子句中的列按照从左到右的顺序进行排列时所对应的编号（1，2，3，…）。不建议使用编号代码阅读起来比较难该排序功能将来会被删除

-- 使用列名

SELECT shohin_id, shohin_mei, hanbai_tanka, shiire_tanka FROM shohin ORDER BY hanbai_tanka DESC, shohin_id;