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

随着表中记录(数据行)的不断积累,存储数据逐渐增加,有时我们可能希望计算出这些数据的合计值或者平均值等,这个时候就需要使用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 子句中不要使用列编号。

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

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

原文发布于微信公众号 - 小白客(youcoding)

原文发表时间:2017-12-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏Jackson0714

【T-SQL基础】02.联接查询

3939
来自专栏数据分析

[数据库基础]——编码标准之格式

代码就像家里的各种物品,格式化就好比对家中物品的排放。家中的物品随便怎么放,物品都不会反对,房子也不会介意,但是物品的排放合理、规整,会让家里变得更具有美感。代...

2918
来自专栏程序员宝库

MYSQL 业务上碰到的 SQL 问题整理集合

前言 身为一名前端工程师,对于 SQL了解程度并不是很深刻,盘点一些个人工作遇到的问题,给大家普及下知识,以及记录自己如何解决这些问题的。 导航 SELECT ...

3436
来自专栏一个爱吃西瓜的程序员

学习SQL【3】-查询基础

一:SELECT 语句基础 1:列的查询 基本的SELECT语句 SELECT <列名>,..... FROM <表名>; 以下举例均是对表Produc...

37310
来自专栏乐沙弥的世界

SQL基础-->分组与分组函数

使用group by column1,column2,..按columm1,column2进行分组,即column1,column2组合相同的值为一个组

1392
来自专栏Java成神之路

Oracle学习笔记_05_分组函数

group by 增强:rollup      cube     grouping      grouping set

1322
来自专栏一个爱吃西瓜的程序员

常用SQL语句和语法汇总

近几年数据库发挥了越来越重要的作用,这其中和大数据、数据科学的兴起有不可分割的联系。学习数据库,可以说是每个从事IT行业的必修课。你学或不学,它就在那里;你想或...

3778
来自专栏一个爱吃西瓜的程序员

学习SQL【9】-集合与联结

现在我们开始学习使用2张以上的表的SQL语句。通过以行方向为单位的集合运算符和以列方向为单位的联结,就可以将分散在多张表中的数据组合成期望的结果。 表的加减法 ...

30812
来自专栏battcn

MySQL - RANGE优化篇

对于单列索引,索引值区间可以方便地用WHERE语句中的相应范围条件表示。优化器在常量传播阶段,会将一些非常量值转换为常量。

2063
来自专栏LanceToBigData

MySQL(九)之数据表的查询详解(SELECT语法)一

这一篇是MySQL中的重点也是相对于MySQL中比较难得地方,个人觉得要好好的去归类,并多去练一下题目。MySQL的查询也是在笔试中必有的题目。希望我的这篇博客...

30110

扫码关注云+社区

领取腾讯云代金券