学习SQL【4】-聚合与排序

爱吃西瓜的番茄酱

发布于 2018-04-04 11:27:43

2.7K0

发布于 2018-04-04 11:27:43

随着表中记录（数据行）的不断积累，存储数据逐渐增加，有时我们可能希望计算出这些数据的合计值或者平均值等，这个时候就需要使用SQL语句的汇总操作等方法。

一：对表进行聚合排序

1：聚合函数 通过SQL对数据进行某种操作或计算时需要使用函数。SQL有五种常用的函数： ● COUNT：计算表中数据的行数（记录数）。 ● SUM：计算表中数值列中数据的合计数。 ● AVG：计算表中数值列中数据的平均值。 ● MAX：计算表中数值列中数据的最大值。 ● MIN：计算表中数值列中数据的最小值。

如上所示，用于汇总的函数成为聚合函数或者聚集函数。接下来，我们仍然使用之前创建的Product表进行函数的学习，Product表的结构和内容如下：

 product_id | product_name | product_type | sale_price | purchase_price | regist_date------------+--------------+--------------+------------+----------------+-------------
 0001       | T衫          | 衣服         |       1000 |            500 | 2017-09-20
 0002       | 打孔器       | 办公用品     |        500 |            320 | 2017-09-11
 0003       | 运动T衫      | 衣服         |       4000 |           2800 |
 0004       | 菜刀         | 厨房用具     |       3000 |           2800 | 2017-09-20
 0005       | 高压锅       | 厨房用具     |       6800 |           5000 | 2017-01-15
 0006       | 叉子         | 厨房用具     |        500 |                | 2017-09-20
 0007       | 擦菜板       | 厨房用具     |        880 |            790 | 2016-04-28
 0008       | 圆珠笔       | 办公用品     |        100 |                | 2017-11-11
(8 行记录)

2：计算表中数据的行数 使用COUNT函数时，输入表的列，就能输出数据行数：例如，计算全部数据的行数：

 SELECT COUNT(*)      
   FROM Product;

执行结果：

 count-------
     8
(1 行记录)

COUNT（）中的星号，代表全部列的意思。函数的输入值称为参数，输出值称为返回值。

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

SELECT COUNT(purchase_price)     
  FROM Product;

执行结果：

 count-------
     6
(1 行记录)

对于COUNT函数来说，参数列不同，计算结果也会不同。COUNT(*)会得到包含NULL的数据行数，而COUNT(<列名>)会得到NULL之外的数据行数。

4：计算合计值 计算合计值需要使用SUM函数例，计算销售单价的合计值：

SELECT SUM(sale_price)     
  FROM Product;

执行结果：

  sum-------
 16780
(1 行记录)

例如，计算销售单价和进货单价的合计值：

SELECT SUM(sale_price), SUM(purchase_price)     
  FROM Product;

执行结果：

  sum  |  sum-------+-------
 16780 | 12210
(1 行记录)

注释：聚合函数会将NULL排除在外，但COUNT(*)例外，并不会排除NULL。

5：计算平均值 计算平均值需要使用AVG函数例，计算销售单价的平均值：

 SELECT AVG(sale_price)      
   FROM Product;

执行结果：

          avg-----------------------
 2097.5000000000000000
(1 行记录)

例如，计算销售单价和进货单价的平均值：

 SELECT AVG(sale_price), AVG(purchase_price)      
   FROM Product;

执行结果：

          avg          |          avg-----------------------+-----------------------
 2097.5000000000000000 | 2035.0000000000000000
(1 行记录)

6：计算最大值和最小值 想要计算出多条记录的最大值或最小值，可以分别使用MAX和MIN函数。例，计算销售单价的最大值和进货单价的最小值：

SELECT MAX(sale_price), MIN(purchase_price)     
  FROM Product;

执行结果：

 max  | min------+-----
 6800 | 320
(1 行记录)

例如，计算登记日期的最大值和最小值：

SELECT MAX(regist_date), MIN(regist_date)     
  FROM Product;

执行结果：

   max     |    min------------+------------
 2017-11-11 | 2016-04-28
(1 行记录)

MAX/MIN函数几乎适用于所有数据类型的列。而SUM/AVG函数只适用于数值类型的列。

7：使用聚合函数删除重复值（关键字DISTINCT） 例，计算除去重复数据后的数据行数：

SELECT COUNT(DISTINCT product_type)     
  FROM Product;

执行结果：

 count-------
     3
(1 行记录)

想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。在聚合函数的参数中使用DISTINCT，可以删除重复数据。

二：对表进行分组

1：GROUP BY子句 使用GROUP BY子句可以像切蛋糕那样将表分割。GROUP BY子句的语法结构如下：

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

例如，按照商品种类统计数据行数：

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

执行结果：

 product_type | count--------------+-------
 衣服         |     2
 办公用品     |     2
 厨房用具     |     4
(3 行记录)

● 在GROUP BY 子句中指定的列称为聚合键。 ● 子句的书写顺序（暂定）： SELECT→FROM→ WHERE→ GROUP BY ● SQL子句的顺序不能更改，也不能相互替换。

2：聚合键中包含NULL的情况 例，按照进货单价统计数据行数：

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

执行结果：

 purchase_price | count----------------+-------
                |     2
            320 |     1
            500 |     1
           5000 |     1
           2800 |     2
            790 |     1
(6 行记录)

聚合键中包含NULL时，在结果中会以“不确定”行（空行）的形式显示出来。

3：使用WHERE子句和GROUP BY 子句的执行结果 例，同时使用WHERE子句和GROUP BY子句：

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

执行结果：

 purchase_price | count----------------+-------
            500 |     1
           2800 |     1
(2 行记录)

4：与聚合函数和GROUP BY 子句有关的常见错误 ● 使用GROUP BY 子句时，SELECT 子句中不能出现聚合键之外的列名。 ● 在GROUP BY 子句中不能使用SELECT子句中定义的别名。 ● GROUP BY 子句结果的显示是无序的。 ● 只有SLEECT子句、GROUP BY 子句和HAVING子句中能够使用聚合函数，WHERE 子句中不能使用聚合函数。

三：为聚合结果指定条件

1：HAVING子句 对集合指定条件可以使用HAVING子句。HAVING子句的语法如下：

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

注释：HAVING子句要写在GROUP BY 子句之后。例，从按照商品种类进行分组后的结果中，取出“包含的数据行数为2行”的组：

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

执行结果：

product_type | count--------------+-------
 衣服         |     2
 办公用品     |     2
(2 行记录)

不使用HAVING子句的情况

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

执行结果：

product_type | count--------------+-------
 衣服         |     2
 办公用品     |     2
 厨房用具     |     4
(3 行记录)

2：HAVING子句的构成要素 HAVING子句中能够使用的3中要素如下： ● 常数 ● 聚合函数 ● GROUP BY 子句中指定的列名（聚合键）

3：相比于HAVING子句，更适合于写在WHERE子句中的条件 我们会发现，有些条件既可以写在HAVING子句中，也可以写在WHERE 子句中，而且结果是一样的。这些条件就是聚合键所对应的条件。例，将条件写在HAVING 子句中：

SELECT product_type, COUNT(*)    
  FROM Product  
 GROUP BY product_type  
 HAVING product_type = '衣服';

执行结果：

product_type | count--------------+-------
 衣服         |     2
(1 行记录)

将条件书写在WHERE子句中的情况

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

执行结果：

 product_type | count--------------+-------
 衣服         |     2
(1 行记录)

两者结果完全相同，但是，从执行速度来讲，将条件写在WHERE 子句中要比写在HAVING子句中的处理速度要快。所以，聚合键所对应的条件应该书写在WHERE 子句中。

四：对查询结果进行排序

1：ORDER BY子句 使用ORDER BY 子句可对查询结果进行排序，ORDER BY子句的语法：

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

例，按照销售单价由低到高（升序）进行排序：

SELECT product_id, product_name, sale_price, purchase_price     
  FROM Product  
 ORDER BY sale_price;

执行结果：

 product_id | product_name | sale_price | purchase_price------------+--------------+------------+----------------
 0008       | 圆珠笔       |        100 |
 0006       | 叉子         |        500 |
 0002       | 打孔器       |        500 |            320
 0007       | 擦菜板       |        880 |            790
 0001       | T衫          |       1000 |            500
 0004       | 菜刀         |       3000 |           2800
 0003       | 运动T衫      |       4000 |           2800
 0005       | 高压锅       |       6800 |           5000
(8 行记录)

● ORDER BY子句中书写的列名称为排序键。 ● ORDER BY 子句通常写在SELECT语句的末尾。

2：指定升序或降序 降序排列时，可使用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 |            790
 0002       | 打孔器       |        500 |            320
 0006       | 叉子         |        500 |
 0008       | 圆珠笔       |        100 |
(8 行记录)

注释：未指定ORDER BY 子句中排列顺序时会默认以升序排列。

3：指定多个排序键 可以在ORDER BY 子句中指定多个排序键，规则是优先使用左侧的键，如果该列存在相同的值，再接着参考右侧的键。例，按照销售单价和商品编号的升序进行排序：

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 |
 0002       | 打孔器       |        500 |            320
 0006       | 叉子         |        500 |
 0007       | 擦菜板       |        880 |            790
 0001       | T衫          |       1000 |            500
 0004       | 菜刀         |       3000 |           2800
 0003       | 运动T衫      |       4000 |           2800
 0005       | 高压锅       |       6800 |           5000
(8 行记录)

4：NULL 的排序 例，按照进货单价的升序进行排序：

SELECT product_id, product_name, sale_price, purchase_price      
  FROM Product  
 ORDER BY purchase_price;

执行结果：

product_id | product_name | sale_price | purchase_price------------+--------------+------------+----------------
 0002       | 打孔器       |        500 |            320
 0001       | T衫          |       1000 |            500
 0007       | 擦菜板       |        880 |            790
 0003       | 运动T衫      |       4000 |           2800
 0004       | 菜刀         |       3000 |           2800
 0005       | 高压锅       |       6800 |           5000
 0006       | 叉子         |        500 |
 0008       | 圆珠笔       |        100 |
(8 行记录)

如上所示，排序键包含NULL时，会在开头或者末尾进行汇总。

5：几点关于ORDER BY子句的事项 ● 在ORDER BY 子句中可以使用SELECT子句中定义的别名。 ● 在ORDER BY 子句中可以使用SLEECT子句中为使用的列和聚合函数。 ● 在ORDER BY 子句中不要使用列编号。

路漫漫其修远兮，吾将上下而求索。

每天学习一点点，每天进步一点点。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2017-12-03，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

数据库

本文分享自小白客微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

sql

数据库

登录后参与评论

0 条评论

热度

学习SQL【4】-聚合与排序

学习SQL【4】-聚合与排序

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐