聚合函数
- 聚合函数
- 通过 SQL 对数据进行某种操作或计算时需要使用函数。
- 用于汇总的函数称为聚合函数或者聚集函数
- 聚合，就是将多行汇总为一行
- 5 个常用的函数
- COUNT：计算表中的记录数（行数）
- SUM：计算表中数值列中数据的合计值
- AVG：计算表中数值列中数据的平均值
- MAX：求出表中任意列中数据的最大值
- MIN：求出表中任意列中数据的最小值
COUNT 函数
COUNT 函数的结果根据参数的不同而不同。COUNT(*) 会得到包含 NULL 的数据行数，而 COUNT(<列名>) 会得到NULL之外的数据行数。
-- 计算 purchase_price 不为 NULL 的数据条数
SELECT COUNT(purchase_price)
FROM Product;
-- 计算总的数据条数
SELECT COUNT(*)
FROM Product;
SUM 函数
聚合函数会将NULL排除在外。但COUNT(*)例外，并不会排除NULL
-- 计算合计值
SELECT SUM(sale_price), SUM(purchase_price)
FROM Product;
AVG 函数
- 平均值的计算公式: ( 值的合计 )/( 值的个数 )
- 数据中含有 NULL 值得时候，会事先排除 NULL 再进行计算。
-- 计算平均值
SELECT AVG(sale_price)
FROM Product;
SELECT AVG(sale_price), AVG(purchase_price)
FROM Product;
MAX 函数 和 MIN 函数
MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
-- 计算最大值和最小值
SELECT MAX(regist_date), MIN(regist_date)
FROM Product;
SELECT MAX(sale_price), MIN(purchase_price)
FROM Product;
使用聚合函数删除重复值（关键字 DISTINCT）
1. 在聚合函数的参数中使用DISTINCT，可以删除重复数据。
2. DISTINCT 必须写在括号中。这是因为必须要在计算行数之前删除 product_type 列中的重复数据。
-- 使用聚合函数去重
SELECT COUNT(DISTINCT product_type)
FROM Product;
SELECT SUM(sale_price), SUM(DISTINCT sale_price)
FROM Product;
对表进行分组
GROUP BY 子句
- 语法
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……;
- 示例：
-- 按照商品种类统计数据行数
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type;
1. 在 GROUP BY 子句中指定的列称为聚合键或者分组列。
2. GROUP BY 子句也和 SELECT 子句一样，可以通过逗号分隔指定多列。
- 子句的书写顺序:
1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY
注：SQL 子句的顺序不能改变，也不能互相替换。
聚合键中包含 NULL 的情况
SELECT purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;
聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式表现出来。
同时使用 WHERE 子句和 GROUP BY 子句
- 语法
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
WHERE
GROUP BY <列名1>, <列名2>, <列名3>, ……;
- 示例：
SELECT purchase_price, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY purchase_price;
- GROUP BY 和 WHERE 并用时 SELECT 语句的执行顺序:
FROM → WHERE → GROUP BY → SELECT
与聚合函数和 GROUP BY 子句有关的常见错误
- 在 SELECT 子句中书写了多余的列
-- 错误示例：
SELECT product_name, purchase_price, COUNT(*)
FROM Product
GROUP BY purchase_price;
-- 正确：
SELECT product_name, purchase_price, COUNT(*)
FROM Product
GROUP BY product_name,purchase_price;
使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名。
- 在 GROUP BY 子句中写了列的别名
SQL 语句在 DBMS 内部的执行顺序造成了这个错误 —— SELECT 子句在 GROUP BY 子句之后执行。在执行 GROUP BY 子句时，SELECT 子句中定义的别名，DBMS 还并不知道。
- 在 WHERE 子句中使用聚合函数
-- 错误示例：
SELECT product_type, COUNT(*)
FROM Product
WHERE COUNT(*) = 2
GROUP BY product_type;
-- 正确示例：
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING COUNT(*) = 2;
只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数。
为聚合结果指定条件
HAVING 子句
- 语法：
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
GROUP BY <列名1>, <列名2>, <列名3>, ……
HAVING <分组结果对应的条件>
- 使用 HAVING 子句时 SELECT 语句的顺序:
SELECT → FROM → WHERE → GROUP BY → HAVING
- 示例:
-- 销售单价的平均值大于等于 2500 日元
SELECT product_type, AVG(sale_price)
FROM Product
GROUP BY product_type
HAVING AVG(sale_price) >= 2500;
HAVING 子句的构成要素
- HAVING 子句中能够使用 3 种要素:
- 常数
- 聚合函数
- GROUP BY子句中指定的列名（即聚合键）
HAVING 子句 与 WHERE 子句
聚合键所对应的条件既可以写在 HAVING 子句当中，又可以写在 WHERE 子句当中.
- 代码:
SELECT product_type, COUNT(*)
FROM Product
GROUP BY product_type
HAVING product_type = '衣服';
SELECT product_type, COUNT(*)
FROM Product
WHERE product_type = '衣服'
GROUP BY product_type;
以上两句 SQL 语句返回的结果是相同的.
- HAVING 子句 与 WHERE 子句中条件的区别:
- WHERE 子句 = 指定行所对应的条件.
- HAVING 子句 = 指定组所对应的条件
- 聚合键所对应的条件不应该书写在HAVING子句当中，而应该书写在WHERE子句当中。
- WHERE子句 和 HAVING子句的执行速度
- 通过 WHERE 子句指定条件时，由于排序之前就对数据进行了过滤，因此能够减少排序的数据量。但 HAVING 子句是在排序之后才对数据进行分组的，因此与在 WHERE 子句中指定条件比起来，需要排序的数据量就会多得多。虽然 DBMS 的内部处理不尽相同，但是对于排序处理来说，基本上都是一样的。
- WHERE 子句更具速度优势的另一个理由是，可以对 WHERE 子句指定条件所对应的列创建索引，这样也可以大幅提高处理速度。
对查询结果进行排序
ORDER BY 子句
- 语法:
SELECT <列名1>, <列名2>, <列名3>, ……
FROM <表名>
ORDER BY <排序基准列1>, <排序基准列2>, ……
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price;
ORDER BY子句通常写在SELECT语句的末尾。
DESC 降序
ASC 升序
未指定ORDER BY子句中排列顺序时会默认使用升序进行排列
- 子句的书写顺序:
- 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;
指定多个排序键
1. 如果想要对该顺序的商品进行更细致的排序的话，就需要再添加一个排序键。
2. 规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键。当然，也可以同时使用 3 个以上的排序键。
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY sale_price, product_id;
NULL 的顺序
排序键中包含NULL时，会在开头或末尾进行汇总。
SELECT product_id, product_name, sale_price, purchase_price
FROM Product
ORDER BY purchase_price;
在排序键中使用显示用的别名
在 ORDER BY子句中可以使用SELECT子句中定义的别名。
SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
FROM Product
ORDER BY sp, id;
ORDER BY 子句中可以使用的列
在ORDER BY子句中可以使用SELECT子句中未使用的列和聚合函数。
SELECT product_name, sale_price, purchase_price
FROM Product
ORDER BY product_id;
不要使用列编号
在 ORDER BY 子句中,可以使用在 SELECT 子句中出现的列所对应的编号
-- 通过列名指定
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;
注意:
1.使用列编号,会导致代码阅读起来比较难
2.在 SQL-92{9[1992 年制定的 SQL 标准。]} 中已经明确指出该排序功能将来会被删除
