Chapter 3 – 聚合与排序

• 生活家

文章目录

      • Part 1. 对表进行聚合查询
        • 1. 聚合函数
        • 2. 计算表中数据的行数
        • 3. 计算 NULL之外的数据的行数
        • 4. 计算合计值
        • 5. 计算平均值
        • 6. 计算最大值和最小值
        • 7. 使用聚合函数删除重复值
      • Part 2. 对表进行分组
        • 1. GROUP BY子句
        • 2. 聚合键中包含NULL的情况
        • 3. 使用WHERE子句时GROUP BY的执行结果
        • 4. 与聚合函数与GROUP BY子句有关的常见错误
      • Part 3. 为聚合结果指定条件
        • 1. HAVING子句
        • 2. HAVING子句的构成要素
        • 3. WHERE和HAVING子句
      • Part 4. 对查询结果进行排序
        • 1. ORDER BY子句
        • 2. 指定升序或降序
        • 3. 指定多个排序键
        • 4. NULL的顺序
        • 5. 在排序键中使用显示用的别名
        • 6. ORDER BY子句中可以使用的列

Part 1. 对表进行聚合查询

1. 聚合函数

  • 通过SQL对数据进行某种操作或计算时需要使用函数
常用函数
COUNT 计算表中的记录数
SUM 计算表中数据列中数据的合计值
AVG 计算表中数据列中数据的平均数
MAX 求出表中数据列中数据的最大值
MIN 求出表中数据列中数据的最小值
  • 用于汇总的函数称为：聚合函数或者聚集函数（聚合）

2. 计算表中数据的行数

# 使用COUNT(*) 计算表中的全部行数
SELECT COUNT(*) # COUNT(参数)
  FROM Product;
  
# 输出值为返回值
+-------------------+
| COUNT(sale_price) |
+-------------------+
|                 8 |
+-------------------+

3. 计算 NULL之外的数据的行数

# 将具体的对象作为参数，传给COUNT()
SELECT COUNT(purchase_price)
  FROM Product;
  • 对于COUNT函数来说，参数列不同计算结果也会发生变化
SELECT COUNT(*), COUNT(purchase_price)
  FROM Product;
 
# 输出结果
+----------+-----------------------+
| COUNT(*) | COUNT(purchase_price) |
+----------+-----------------------+
|        8 |                     5 |
+----------+-----------------------+
  • 【总结】
    • COUNT函数的结果根据参数的不同而不同
    • COUNT(*)会得到包含NULL的数据行数
    • COUNT(<列名>)会得到NULL之外的数据行数
    • 其他函数不能将星号作为参数

4. 计算合计值

SELECT SUM(sale_price) 
  FROM Product;
  
+-----------------+
| SUM(sale_price) |
+-----------------+
|           16780 |
+-----------------+
  • 对于SUM函数来说，即使包含NULL，也可以计算出合计值
  • 聚合函数如果以列名为参数，在计算之前已经把NULL排除在外；COUNT(*)例外

5. 计算平均值

SELECT AVG(sale_price)
  FROM Product;
  
+-----------------+
| AVG(sale_price) |
+-----------------+
|       2097.5000 |
+-----------------+
  • 计算时除去NULL行，值的个数 = 总行数 - NULL的行数

6. 计算最大值和最小值

SELECT MAX(sale_price)
  FROM Product;
  
+------------------+
| MAX(sale_price ) |
+------------------+
|             6800 |
+------------------+
  • 【区别】
  • SUM/AVG函数只能对数值类型的列使用
  • MAX/MIN函数原则上可以适用于任何数据类型的列
SELECT MAX(product_name)
  FROM Product;
  
+-------------------+
| MAX(product_name) |
+-------------------+
| 高压锅            |
+-------------------+

7. 使用聚合函数删除重复值

# 去除重复数据后的数据行数
SELECT COUNT(DISTINCT product_type)
  FROM Product;
  
+------------------------------+
| COUNT(DISTINCT product_type) |
+------------------------------+
|                            3 |
+------------------------------+

# 先计算数据行数再删除重复数据的结果
SELECT DISTINCT COUNT(product_type)
  FROM Product;
  • 不限于COUNT，所有聚合函数都可以使用DISTINCT

Part 2. 对表进行分组

1. GROUP BY子句

# 语法
SELECT <列名1>, <列名2>, <列名3>, ...
  FROM <表名>
 GROUP BY <列名1>, <列名2>, ...; # 组合键 / 分组列
  • 示例
SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type;
 
+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 办公用品     |        2 |
| 厨房用具     |        4 |
+--------------+----------+
  • 子句书写顺序（暂定）
SELECT -> FROM -> WHERE -> GROUP BY

2. 聚合键中包含NULL的情况

SELECT purchase_price, COUNT(*)
  FROM Product
 GROUP BY purchase_price;
 
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
|            500 |        1 |
|            320 |        1 |
|           2800 |        2 |
|           5000 |        1 |
|           NULL |        3 |
+----------------+----------+
  • 聚合键中包含NULL时，在结果中会以”不确定”行的形式表现出来

3. 使用WHERE子句时GROUP BY的执行结果

SELECT purchase_price, COUNT(*)
  FROM Product
 WHERE product_type = '衣服'
 GROUP BY purchase_price;
+----------------+----------+
| purchase_price | COUNT(*) |
+----------------+----------+
|            500 |        1 |
|           2800 |        1 |
+----------------+----------+

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

  • 法则1：使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名

  • 法则2：在GROUP BY子句中不能使用SELECT子句中定义的别名

  • 法则3：GROUP BY子句结果的显示是无序的

  • 法则4：只有SELECT子句和HAVING子句（以及ORDER BY子句）中能够使用聚合函数

  • 【说明】：

    • 想要删除结果中的重复记录，使用DISTINCT
    • 想要计算汇总结果，使用GROUP BY

Part 3. 为聚合结果指定条件

1. HAVING子句

  • WHERE子句只能指定（行）的条件，而不能用来指定组的条件
  • HAVING子句的语法
SELECT <列名1>, <列名2>, <列名3>, ...
  FROM <表名>
 GROUP BY <列名1>, <列名2>, <列名3>, ...
HAVING <分组结果对应的条件>;
  • 使用HAVING子句时SELECT语句的顺序
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING

SELECT product_type, COUNT(*)
  FROM Product
 GROUP BY product_type
HAVING COUNT(*) = 2;

+--------------+----------+
| product_type | COUNT(*) |
+--------------+----------+
| 衣服         |        2 |
| 办公用品     |        2 |
+--------------+----------+

2. HAVING子句的构成要素

  • HAVING子句中能够使用的3中要素
    • 常数
    • 聚合函数
    • GROUP BY子句中指定的列名（即聚合键）

3. WHERE和HAVING子句

  • WHERE子句 = 指定行所对应的条件
  • HAVING子句 = 指定组所对应的条件

Part 4. 对查询结果进行排序

1. ORDER BY子句

SELECT <列名1>, <列名2>, ...
  FROM <表名>
 ORDER BY <排序基准列1>, <排序基准列2>,... # 排序键
  • 使用ORDER BY子句时SELECT语句的顺序
SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY

SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
 ORDER BY sale_price;
 
# 按照 sale_price进行升序排列
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0008       | 圆珠笔       |        100 |           NULL |
| 0002       | 打孔器       |        500 |            320 |
| 0006       | 叉子         |        500 |           NULL |
| 0007       | 擦彩板       |        880 |           NULL |
| 0001       | T恤衫        |       1000 |            500 |
| 0004       | 菜刀         |       3000 |           2800 |
| 0003       | 运动T恤      |       4000 |           2800 |
| 0005       | 高压锅       |       6800 |           5000 |
+------------+--------------+------------+----------------+

2. 指定升序或降序

# 默认指定为升序，正式书写可用关键字 ASC
# 在列名后使用 DESC关键字，指定降序排列
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
 ORDER BY sale_price DESC;
 
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0005       | 高压锅       |       6800 |           5000 |
| 0003       | 运动T恤      |       4000 |           2800 |
| 0004       | 菜刀         |       3000 |           2800 |
| 0001       | T恤衫        |       1000 |            500 |
| 0007       | 擦彩板       |        880 |           NULL |
| 0002       | 打孔器       |        500 |            320 |
| 0006       | 叉子         |        500 |           NULL |
| 0008       | 圆珠笔       |        100 |           NULL |
+------------+--------------+------------+----------------+

3. 指定多个排序键

  • 可在ORDER BY子句中同时指定多个排序键；规则是优先使用左侧的键
# 优先按照sale_price进行升序排列
# 在sale_price相同的情况下，按照product_id排序
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
 ORDER BY sale_price, product_id;
 
+------------+--------------+------------+----------------+
| product_id | product_name | sale_price | purchase_price |
+------------+--------------+------------+----------------+
| 0008       | 圆珠笔       |        100 |           NULL |
| 0002       | 打孔器       |        500 |            320 |
| 0006       | 叉子         |        500 |           NULL |
| 0007       | 擦彩板       |        880 |           NULL |
| 0001       | T恤衫        |       1000 |            500 |
| 0004       | 菜刀         |       3000 |           2800 |
| 0003       | 运动T恤      |       4000 |           2800 |
| 0005       | 高压锅       |       6800 |           5000 |
+------------+--------------+------------+----------------+

4. NULL的顺序

  • 不能对NULL和数字进行排序；也不能与字符串和日期比较大小
  • 使用含有NULL的列作为排序键时，NULL会在结果的开头或末尾汇总显示
SELECT product_id, product_name, sale_price, purchase_price
  FROM Product
 ORDER BY purchase_price;

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

SELECT product_id AS id, product_name, sale_price AS sp, purchase_price
  FROM Product
 ORDER BY sp, id;
 
+------+--------------+------+----------------+
| id   | product_name | sp   | purchase_price |
+------+--------------+------+----------------+
| 0008 | 圆珠笔       |  100 |           NULL |
| 0002 | 打孔器       |  500 |            320 |
| 0006 | 叉子         |  500 |           NULL |
| 0007 | 擦彩板       |  880 |           NULL |
| 0001 | T恤衫        | 1000 |            500 |
| 0004 | 菜刀         | 3000 |           2800 |
| 0003 | 运动T恤      | 4000 |           2800 |
| 0005 | 高压锅       | 6800 |           5000 |
+------+--------------+------+----------------+

6. ORDER BY子句中可以使用的列

SELECT product_name, sale_price, purchase_price
  FROM Product
 ORDER BY product_id; # 可以使用 SELECT中未包含的列
 
SELECT product_name, sale_price, purchase_price
  FROM Product
 GROUP BY product_type
 ORDER BY COUNT(*); # 可以使用 聚合函数
  • 【总结】在ORDER BY子句中可使用SELECT子句中未使用的列和聚合函数
  • 在ORDER BY子句中不要使用列编号
SELECT product_id, SUM(product_name)
-- 本SELECT语句中存在错误
  FROM Product
 GROUP BY product_type;
 WHERE regist_date > '2009-09-01'
/* 
	1. WHERE 应该在 GROUP之前
  2. SELECT中不能出现 非聚合键
 */
 
 SELECT sale_price, purchase_price
   FROM Product
  GROUP BY product_type
 HAVING SUM(sale_price) >= 1.5 * SUM(purchase_price);

本文地址：https://blog.csdn.net/GrayD1419/article/details/107170736

赞 (0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐