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;

 

-- 使用列编号

select shohin_id, shohin_mei, hanbai_tanka, shiire_tanka from shohin order by 3 desc, 1;

 

虽然列编号使用起来非常方便，但我们并不推荐使用，原因有以下两点 ：第一，代码阅读起来比较难；第二，这也是根本问题。实际上，在sql-92中已经明确指出该排序功能将来会被删除。

 

法则3-20

在order by子句中不要使用列编号。