SQL基础之——聚合与排序

1. 聚合查询

1.1 聚合函数(COUNT,SUM,AVG,MAX,MIN)

通过SQL对数据进行某种操作或计算时需要使用函数，用于汇总的函数叫做聚合函数（聚集函数）。

COUNT：计算查询出的行数。
SUM：计算表中数值列中数据的合计值。
AVG：计算表中数值列中数据的平均值。
MAX：求出表中任意列中数据的最大值。
MIN：求出表中任意列中数据的最小值。

-- 计算order_info表中用户数以及用户消费的总金额、平均金额、最大值、最小值
SELECT COUNT(final_total_amount) AS "用户数",
	   SUM(final_total_amount) AS "用户消费总金额",
       AVG(final_total_amount) AS "用户消费平均金额",
	   MAX(final_total_amount) AS "用户消费最大值",
	   MIN(final_total_amount) AS "用户消费最小值"
FROM `order_info` ;

图1 聚合函数查询结果

1.2 使用聚合函数删除重复值(DISTINCT)

-- 查询order_info表中的用户来自于几个省
SELECT COUNT(DISTINCT province_id)
FROM `order_info` ;

图2 order_info表中的用户来自于几个省查询结果

2. 对表进行分组

2.1 GROUP BY子句

-- 查询order_info表中各个省份的用户数分别有多少
SELECT province_id AS "省份编码" , COUNT(id) AS "该省用户数"
FROM `order_info` 
GROUP BY province_id;

图3 各省用户数

GROUP BY子句的书写位置一定要在FROM子句之后，如果有WHERE子句则在WHERE子句之后。
如果GROUP BY子句的分组条件中包含空行，那么将以“不确定行”(空行)的形式表现出来。

2.2 WHERE子句和GROUP BY 一起使用

WHERE和GROUP BY一起使用时，会将表中数据根据WHERE中的条件先进行过滤然后再根据GROUP BY进行分组。

-- 查询order_info表中省份编号在10-20之间的各省分别有多少用户
SELECT province_id AS "省份编码" , COUNT(id) AS "该省用户数"
FROM `order_info` 
WHERE province_id <= 20 AND province_id >= 10
GROUP BY province_id;

图4 order_info表中省份编号在10-20之间的各省的用户数

GROUP BY与WHERE并用时，SQL语句的执行顺序：

FROM → WHERE → GROUP BY → SELECT

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

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

在使用聚合函数时，SELECT子句中只能存在以下三种元素：

常数
聚合函数
GROUP BY子句中指定的列名(聚合键)

-- 错误示例
SELECT id AS id ,
			 province_id AS "省份编码" , 
			 COUNT(id) AS "该省用户数"
FROM `order_info` 
GROUP BY province_id;

该语句出现了id字段，但该字段的行数不一定与GROUP BY分组后的行数相同，即使相同，一一对应的可能性也非常小，所以通常情况下会报错。

图5 行数不一致报错信息

2.3.2 在GROUP BY子句中书写了列的别名

SELECT子句中是可以通过AS关键字来为列指定别名的，但在GROUP BY子句中是不能使用别名的。

-- 错误示例
SELECT id AS id ,
			 province_id AS pid , 
			 COUNT(id) AS "该省用户数"
FROM `order_info` 
GROUP BY pid;

该语句的执行顺序应该是FROM → WHERE → GROUP BY → SELECT，GROUP BY在SELECT前边执行，所以GROUP BY无法找到pid是哪一列，语句报错。

图6 GROUP BY中指定别名报错信息

2.3.3 GROUP BY执行结果能排序？

GROUP BY子句的执行结果通常包括多行，他们的输出结果有顺序吗？
答案是否定的，输出结果的顺序完全不可控，当再次执行同样的语句时，完全有可能输出不同顺序的结果，若想对结果进行排序则需要用到ORDER BY子句。

2.3.4 在WHERE子句中使用聚合函数

若想取出对某个品类中商品的种类数为2的品类名称，将使用到把聚合函数的结果当做条件来完成SQL，但是

-- 错误示例
SELECT product_type , COUNT(*)
FROM product
WHERE COUNT(*) = 2
GROUP BY product_type ;

这种写法是错误的，实际上COUNT等聚合函数只能在SELECT子句和HAVING子句以及ORDER BY子句中使用。并且HAVING子句可以非常方便的实现该需求。

3.为聚合结果指定条件

前文说到对数据进行汇总操作时，将该汇总作为条件不可以用WHERE子句，应该用HAVING子句。

3.1 HAVING子句

3.1.1 基本用法

指定查询条件一般使用WHERE子句，但WHERE只能用于指定记录(行)的条件，并不能指定组的条件，不能使用聚合函数作为条件进行查询。
因此对集合指定条件就需要用到HAVING子句。

HAVING子句必须在GROUP BY子句之后，使用HAVING子句时语句的执行顺序如下：

SELECT → FROM → WHERE → GROUP BY → HAVING

例1：查询用户数大于40的省份的编码及其用户数

-- 查询order_info表中各省份的用户数
SELECT  province_id , COUNT(province_id)
FROM order_info
GROUP BY province_id ;

图7 各省份及其用户数查询结果

-- 查询用户数大于40的省份的编码及其用户数
SELECT  province_id , COUNT(province_id)
FROM order_info
GROUP BY province_id
HAVING COUNT(province_id) > 40 ;

图8 用户数大于40的省份的编码及其用户数

例2：查询省份编码大于25且用户数大于45的省份及其用户数

SELECT  province_id , COUNT(province_id)
FROM order_info
WHERE province_id > 25
GROUP BY province_id
HAVING COUNT(province_id) > 40 ;

图9 省份编码大于25且用户数大于45的省份及其用户数

3.1.2 HAVING子句的构成要素

HAVING子句中能使用的三种要素如下：

常数
聚合函数
GROUP BY子句中指定的列名(聚合键)

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

有些条件既可写在WHERE中又可写在HAVING中，例如聚合键(GROUP BY 的分组)所对应的条件，例如：

-- 查询省份编码为27的省份及其用户数
SELECT  province_id , COUNT(province_id)
FROM order_info
WHERE province_id = 27
GROUP BY province_id ;
---------------分割线---------------
SELECT  province_id , COUNT(province_id)
FROM order_info
WHERE province_id = 27
HAVING province_id ;

其运行结果均为：

图10 省份编码为27的省份及其用户数

对于这种情况，建议将条件写在WHERE子句中，因为WHERE是指定“行”所对应的条件的，而HAVING是指定组条件的。写在WHERE中不但可以分清各自功能，还更易于理解。

4.对查询结果进行排序

4.1 基本用法

SQL语句查询出的结果是无序的，每一次的顺序都可能不同，从而导致结果混乱的情况。此时便需要用到ORDER BY来指定排列顺序。

-- 将order_info表中用户按照消费金额升序排列
SELECT  id ，consignee , final_total_amount
FROM order_info
ORDER BY final_total_amount ; -- ORDER BY子句需要写在语句末尾。

图11 order_info表中用户按照消费金额升序排列

子句的书写顺序：

1.SELECT子句 → 2.FROM子句 → 3.WHERE子句 → 4.GROUP BY子句 → 5.HAVING子句 → 6.ORDER BY子句

4.2 指定升序或降序

-- 将order_info表中用户按照消费金额降序排列
SELECT id , consignee , final_total_amount
FROM order_info
ORDER BY final_total_amount DESC;

图12 order_info表中用户按照消费金额降序排列

未指定升降序时默认使用ASC(升序)进行排列
降序使用DESC来指定
ASC：ascendent
DESC：descendent

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

4.3 指定多个排序键

-- 查询order_info表中用户及其消费金额并按照消费金额降序排列，若消费金额一致的用户则按照订单创建时间升序排列
SELECT  id  , consignee , final_total_amount , create_time
FROM order_info
ORDER BY final_total_amount DESC , create_time;

图13 查询结果

在该语句中，先按照 “final_total_amount DESC”进行排序，如果该列相同无法排序，则按照“create_time (ASC)”排序。

4.4 NULL的顺序

对于排序键列为空的记录，通常会集中显示在开头或末尾。

4.5 在排序键中使用列的别名

列的别名在GROUP BY中是不能使用的，但在ORDER BY中可以，因为ORDER BY的执行顺序在最后，所有子句执行完成后最后才会执行ORDER BY语句进行排序,所以ORDER BY子句是可以识别SELECT语句为列起的别名的。

语句执行顺序：FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

4.6 ORDER BY子句中可以使用的列

ORDER BY可以使用存在于表中但不在SELECT语句中的列。

SELECT id , consignee , create_time
FROM order_info
ORDER BY final_total_amount DESC , create_time;

图14 使用存在于表中但不在SELECT语句中的列

除此之外，还可使用聚合函数

-- 查询各个省份的用户数并按照用户数降序排列
SELECT province_id , COUNT(*)
FROM order_info
GROUP BY province_id
ORDER BY COUNT(*) DESC;

图15 查询各个省份的用户数并按照用户数降序排列

4.7 不要使用列编号

在ORDER BY子句中，除使用列名之外，还可以使用列编号进行排序，如下列两语句运行结果是一样的：

SELECT *
FROM order_info
ORDER BY final_total_amount DESC;
-------------分割线-------------
SELECT *
FROM order_info
ORDER BY 4 DESC;

图16 原表

图17 查询后结果(上述两语句运行结果一致)

虽然可以指定列编号进行排序，但不建议这么做

1.代码阅读起来比较困难，ORDER BY中只有一个列编号，只看ORDER > BY子句无法判断排序依据是什么
2.在SQL-92(1992年制定的SQL标准)中已经明确指出该功能在未来会删除