3聚合与排序
3-1对表进行聚合查询
聚合函数
COUNT：计算表中的记录数（行数）
SUM：计算表中数值列中数据的合计数
AVG：计算表中数值列中数据的平均值
MAX：求出表中任意列中数据的最大值
MIN：求出表中任意列中数据的最小值
用于汇总的函数称为聚合函数；聚合：将多行汇总为一行
计算表中数据的行数
SELECT COUNT(*)
FROM Product;
计算NULL之外的数据的行数
SELECT COUNT(purchase_price)
FROM Product;
COUNT函数的结果会根据参数的不同而不同：
COUNT(*) 会得到包含NULL的数据行数；COUNT(<列名>)会得到NULL之外的数据行数。
计算合计值
SELECT SUM(sale_price), SUM(purchase_price)
FROM Product;
3-1.png
SUM函数应用于包含NULL的列时，会无视其中的NULL。
聚合函数会将NULL排除在外，但COUNT(*)例外，不会排除NULL。
计算平均值
SELECT AVG(sale_price), AVG(purchase_price)
FROM Product;
3-2.png
purchase_price一列总共有8个数据，其中2个为NULL值，计算平均值时，忽略NULL，求平均值时分母是6而不是8。
计算最大值和最小值
SELECT MAX(regist_date), MIN(regist_date)
FROM Product;
MAX/MIN函数几乎适用于所有数据类型的列；SUM/AVG函数只适用于数值类型的列。
使用聚合函数删除重复值（关键字DISTINCT）
计算值的种类，可以在COUNT函数的参数里使用DISTINCT
--计算去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
FROM Product;
3-2对表进行分组
GROUP BY子句
按照商品种类对表进行划分
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
3-3.png
GROUP BY子句的书写位置有严格要求，目前已经出现的各子句的暂定顺序如下：
1.SELECT->2.FROM->3.WHERE->4.GROUP BY
聚合键包含NULL的情况
SELECT purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;
3-4.png
当聚合键包含NULL时，也会将NULL作为一组特定的数据。
使用WHERE子句时GROUP BY的执行结果
SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;
3-5.png
GROUP BY和WHERE并用时SELECT语句的执行顺序：
FROM->WHERE->GROUP BY->SELECT
需要注意的是：SQL书写顺序与实际执行顺序不同
与聚合函数和GROUP BY子句有关的常见错误
- 1.在SELECT子句中书写的多余的列
在使用聚合函数时，SELECT子句只能出现3种元素：
常数
聚合函数
GROUP BY子句中指定的列名（聚合键）
--发生错误
SELECT product_name, purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;
原因：通过purchase_price将表分组以后，结果中的一行数据就代表一组取不同价格的商品。聚合键和商品名不是一对一的。
- 2.在GROUP BY子句中写了列的别名
--发生错误
SELECT product_type AS pt, COUNT(*)
FROM product
GROUP BY pt;
错误原因：SELECT子句在GROUP BY子句之后执行，pt别名还没有被定义
- 3GROUP BY子句的结果能排序吗
不能，顺序随机 - 4在WHERE子句中使用聚合函数
首先我们按照商品种类统计数据行数：
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
3-5.png
假设我们想取出总个数为2的商品类别，也就是前两行，能不能用WHERE子句来选择呢？
SELECT product_type, COUNT(*)
FROM Product
WHERE COUNT(*) = 2
GROUP BY product_type;
3-6.png
执行错误，这是由于在SQL中聚合函数（COUNT等）只能在SELECT子句和HAVING子句（以及ORDER BY子句）中存在。
3-3为聚合结果指定条件
HAVING子句
前面所讨论问题的正确解法
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;
3-7.png
使用HAVING子句时SELECT语句的顺序（书写顺序）：
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type;
--按照商品种类分组，条件是“销售单价的平均值大于等于2500日元”
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price) >= 2500;
HAVING子句的构成要素
HAVING子句同包含GROUP BY子句时的SELECT子句一样，只能够使用下列3种元素：
常数
聚合函数
GROUP BY子句中指定的列名（即聚合键）
--错误示例
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING product_name = '圆珠笔';
3-8.png
相对于HAVING子句，更适合写在WHERE子句中的条件
聚合键所对应的条件，既可以写在HAVING子句中，也可以写在WHERE子句中
--将条件写在HAVING子句里
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING product_type = '衣服';
--将条件写在WHERE子句里
SELECT product_type, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY product_type;
WHERE子句=指定行所对应的条件
HAVING子句=指定组所对应的条件
作者建议写在WHERE子句中，节省运行时间（WHERE子句先进行筛选）
3-4对查询结果进行排序
ORDER BY子句
--查询结果按照sale_price升序排列
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price;
3-9.png
ORDER BY子句中书写的列名称是排序键
子句的书写顺序：
1.SELECT子句 -> 2.FROM子句 -> 3.WHERE子句 -> 4.GROUP BY 子句 -> 5.HAVING子句 -> 6.ORDER BY子句
指定升序或降序
--按照销售单价由高到低降序排列
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price DESC;
指定多个排序键
--按照销售单价和商品编号的升序进行排序
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price, product_id;
3-10.png
需要注意第2、3行，当价格相同时按照商品编号的升序排列
NULL的顺序
--按照进货单价的升序进行排列
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY purchase_price;
3-11.png
排序键中包含NULL时，NULL值会集中在开头或者结尾
在排序键中使用显示用的别名
SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
FROM Product
ORDER BY sp, id;
3-12.png
之前在GROUP BY子句中不可以使用别名，但是在ORDER BY子句中允许使用别名，这是由于SQL语句在DBMS内部的执行顺序造成的：
FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> 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;
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
ORDER BY COUNT(*);
3-13.png
不要使用列编号
--两段代码作用相同
--通过列名指定
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;
不推荐方式2
习题3
3.2
--不确定正确性
SELECT product_type, SUM(sale_price), SUM(purchase_price)
FROM Product
GROUP BY product_type
HAVING SUM(sale_price) > 1.5 * SUM(purchase_price)
3.3
SELECT *
FROM Product
ORDER BY regist_date DESC, sale_price;
网友评论