运行环境: postgreSQL。

第三章:聚合与排序

对表进行聚合查询

聚合函数

通过 SQL 对数据进行操作或者计算时需要使用 函数,以下为5个常用 聚合函数(聚合即将多行汇总为一行):

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

计算表中数据的行数

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

计算 NULL 之外数据的行数,即将列明作为参数传入 COUNT 函数即可

1
SELECT COUNT(purchase_price) FROM Product;

计算合计值

示例:

1
2
3
4
SELECT SUM(sale_price), SUM(purchase_price) FROM Product;

// 在聚合函数参数重使用 DISTINCT, 可以删除重复数据
SELECT SUM(sale_price), SUM(DISTINCT sale_price) FROM Product;

注意:处理 NULL 的方式是将其排除在外

对表进行分组

GROUP BY

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

示例:按照商品种类统计行数(=商品种类数量)

1
SELECT product_type, COUNT(*) FROM Product GROUP BY product_type;

GROUP BY 子句重指定的列称为 聚合键 或者 分组列

聚合键可以为 NULL,其将 NULL 作为一组特定的数据:

1
SELECT purchase_price, COUNT(*) FROM Product GROUP BY purchase_price;

当使用 WHERE 子句时,先根据 WHERE 子句指定的条件进行过滤,再进行汇总处理,示例:

1
2
SELECT purchase_price, COUNT(*) FROM Product
	WHERE product_type = '衣服' GROUP BY purchase_price;

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

  • SELECT 子句中只能存在以下三种元素:

    1. 常数
    2. 聚合函数
    3. ** GROUP BY 子句中指定的列明 **(即聚合键)

当把聚合键之外的列名写在 SELECT 子句时,将无法正常执行,其原因是当出现列相同的项归为一组时,会出现歧义,数据库不知道对应哪一行,即聚合键和其他列不一定是一对一的。

  • GROUP BY 使用类的别名

GROUP BY 是在 SELECT 之前执行的,因此 SELECT 子句定义的别名,DBMS 并不能在 GROUP BY 解析。

  • GROUP BY 子句结果能排序吗?

答案是:“随机的”。

  • WHERE 子句不能使用聚合函数

只有 SELECT 子句和 HAVING 子句能使用 COUNT 等聚合函数。

为聚合结果指定条件

HAVING 子句

WHERE 子句只能指定记录(行)的条件,而指定组条件使用 HAVING ,以下为 HAVING 子句的语法:

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

示例:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
--按照商品种类进行分组后,将包含数据行为 2 的条件筛选出来
SELECT product_type, COUNT(*)
	FROM Product
	GROUP BY product_type
 HAVING COUNT(*) = 2;
 
--按照商品种类进行分组后,筛选销售单价平均值大于等于 2500 的记录
SELECT product_type, AVG(sale_price)
	FROM Product
	GROUP BY product_type
 HAVING AVG(sale_price) >= 2500

HAVING 子句限制的内容和 SELECT 相同,包括常数聚合函数GROUP BY 子句中指定的列名(即聚合键)

聚合键所对应的条件数据在 HAVING子句亦可,但考虑语义和性能的因素,应将聚合键所对应的条件书写在 WHERE 子句中,而指定组所对应的条件写在 HAVING 中。

对查询结果进行排序

通常 SELECT. 语句选择的数据是随机的,通过 ORDER BY 子句指定排列顺序,其语法如下:

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

示例,按照销售单价由低到高,即升序排列:

1
2
3
SELECT product_id, product_name, sale_price, purchase_price
	FROM Product
 ORDER BY sale_price;

ORDER BY 子句书写的列名称为排序键

指定升序或降序

当需要降序排列时,只要在列名后面添加 DESC 关键字 即可。

1
2
3
SELECT product_id, product_name, sale_price, purchase_price
	FROM Product
 ORDER BY sale_price DESC;

升序为关键字 **AES*,默认排序方式为升序。

ASC 和 DESC 是 ascendent 和 descendent 的缩写,可以同时指定一个列为升序,一个列为降序。

指定多个排序键

ORDER BY 规则是优先使用左侧的键,如果存在相同值时,再参考右侧的键,依次类推。

NULL 的顺序

NULL 不能进行排序,因此,将含有 NULL 的列作为排序键时, NULL 会在结果开头或末尾处汇总显示。

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

ORDER BY 子句不同于 GROUP BY 子句是允许使用别名的,因为 ORDER BY 的执行顺序在 SELECT 后面,而 GROUP BY 在 SELECT 前面

ORDER BY 子句可以使用的列

ORDER BY 可以使用存在于表中,但未被 SELECT 选中的列,如下通过 product_id 对产品信息进行排序:

1
2
3
SELECT product_name, sale_price, purchase_price
	FROM Product
 ORDER BY product_id;

ORDER BY 也被允许使用聚合函数,如下对商品类型总数进行排序:

1
2
3
4
SELECT product_type, COUNT(*)
	FROM Product
	GROUP BY product_type
 ORDER BY COUNT(*);

不要使用列编号

ORDER BY 可以通过 列编号 指定进行排序的列,如下语句语义是相同的:

1
2
3
4
5
6
7
8
9
-- 通过列名指定
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;

之所以不推荐使用列编号,其一是代码阅读困难,其二是 SQL-92 指定该排序功能将被删除。