聚合函数

• 聚合函数

1. 通过 SQL 对数据进行某种操作或计算时需要使用函数。
2. 用于汇总的函数称为聚合函数或者聚集函数
3. 聚合，就是将多行汇总为一行

• 5 个常用的函数

1. COUNT：计算表中的记录数（行数）
2. SUM：计算表中数值列中数据的合计值
3. AVG：计算表中数值列中数据的平均值
4. MAX：求出表中任意列中数据的最大值
5. 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 函数

1. 平均值的计算公式: ( 值的合计 )/( 值的个数 )
2. 数据中含有 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 种要素:

1. 常数
2. 聚合函数
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 子句中条件的区别:

1. WHERE 子句 = 指定行所对应的条件.
2. HAVING 子句 = 指定组所对应的条件
3. 聚合键所对应的条件不应该书写在HAVING子句当中，而应该书写在WHERE子句当中。

---

• WHERE子句 和 HAVING子句的执行速度

1. 通过 WHERE 子句指定条件时，由于排序之前就对数据进行了过滤，因此能够减少排序的数据量。但 HAVING 子句是在排序之后才对数据进行分组的，因此与在 WHERE 子句中指定条件比起来，需要排序的数据量就会多得多。虽然 DBMS 的内部处理不尽相同，但是对于排序处理来说，基本上都是一样的。
2. 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子句中排列顺序时会默认使用升序进行排列

• 子句的书写顺序:

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;

---

指定多个排序键

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 标准。]} 中已经明确指出该排序功能将来会被删除