1. 首页
  2. 自学中心
  3. 软件
  4. SQL

小胖带你学SQL(三)聚合与排序

作者胖熊酱

1 对表进行聚合查询

1.1 聚合函数

函数用来在SQL中对数据进行某种操作和计算。

以下是常用的5种函数:

COUNT: 计算表中的记录数(行数)

SUM: 计算表中数值列中数据的合计值

AVG: 计算表中数值列中数据的平均值

MAX: 求出表中任意列中数据的最大值

MIN: 求出表中任意列中数据的最小值

用于汇总的函数称为聚合函数或者聚集函数。所谓聚合,就是将多行汇总为一行。

1.2 计算表中数据的行数

计算全部数据的行数

SELECT COUNT(*)  
FROM Product;

此处的输入值称为参数或者 parameter,输出值称为返回值。

小胖带你学SQL(三)聚合与排序

1.3 计算NULL之外的数据的行数

计算NULL之外的数据行数

SELECT COUNT(purchase_price)  
FROM Product;
小胖带你学SQL(三)聚合与排序

对于 COUNT 函数来说,参数列不同计算的结果也会发生变化。

只包含NULL的表

小胖带你学SQL(三)聚合与排序

将包含NULL的列作为参数时,COUNT(*)和COUNT(<列名>)的结果并不相同

SELECT COUNT(*), COUNT(col_1)  
FROM NullTbl;
小胖带你学SQL(三)聚合与排序

COUNT函数的结果根据参数的不同而不同。COUNT(*)会得到包含NULL的数据行数,而COUNT(<列名>)会得到NULL之外的数据行数。

1.4 计算合计值

计算销售单价的合计值

SELECT SUM(sale_price)   
FROM Product;

计算销售单价和进货单价的合计值

SELECT SUM(sale_price), SUM(purchase_price)   
FROM Product;
小胖带你学SQL(三)聚合与排序

所有的聚合函数,如果以列名为参数,那么在计算之前就已经把 NULL排除在外了。但COUNT(*)例外,并不会排除NULL。

1.5 计算平均值

计算销售单价的平均值

SELECT AVG(sale_price)   
FROM Product;
小胖带你学SQL(三)聚合与排序

(值的合计)/( 值的个数)就是平均值的计算公式。

计算销售单价和进货单价的平均值

SELECT AVG(sale_price), AVG(purchase_price)  
FROM Product;
小胖带你学SQL(三)聚合与排序

1.6 计算最大值和最小值

计算销售单价的最大值和进货单价的最小值

SELECT MAX(sale_price), MIN(purchase_price)  
FROM Product;
小胖带你学SQL(三)聚合与排序

SUM/ AVG函数只能对数值类型的列使用,而 MAX/MIN 函数原则上可以适用于任何数据类型的列。

计算登记日期的最大值和最小值

SELECT MAX(regist_date), MIN(regist_date)  
FROM Product;
小胖带你学SQL(三)聚合与排序

1.7 使用聚合函数删除重复值(关键字DISTINCT)

计算去除重复数据后的数据行数

SELECT COUNT(DISTINCT product_type)  
FROM Product;
小胖带你学SQL(三)聚合与排序

DISTINCT 写在括号中,按照计算的优先级,会在计算行数之前删除 product_type 列中的重复数据。

DISTINCT 写在括号外,则反之。

先计算数据行数再删除重复数据的结果

SELECT DISTINCT COUNT(product_type)  FROM Product;
小胖带你学SQL(三)聚合与排序

所有的聚合函数都可以使用DISTINCT。

使不使用DISTINCT时的动作差异(SUM函数)

SELECT SUM(sale_price), SUM(DISTINCT sale_price)  FROM Product;
小胖带你学SQL(三)聚合与排序

2 对表进行分组

2.1 GROUP BY子句

上文聚合函数的使用方法都是针对表中所有的数据进行的汇总处理。这里,我们将利用GROUP BY 字句先把表分成几组,然后, 在进行汇总处理。

使用GROUP BY子句进行汇总

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

按照商品种类统计数据行数

SELECT product_type, COUNT(*)  
FROM Product 
GROUP BY product_type;
小胖带你学SQL(三)聚合与排序

不使用GROUP BY子句时,是将表中的所有数据作为一组来对待的。而使用GROUP BY 子句时,会将表中的数据分为多个组进行处理。

按照商品种类对表进行切分

小胖带你学SQL(三)聚合与排序

GROUP BY子句就像切蛋糕那样将表进行了分组。在GROUP BY 子句中指定的列称为聚合键或者分组列。能够决定表的切分方式, 是非常重要的列。GROUP BY字句还可以通过逗号分隔指定多列。

子句的书写顺序(暂定)

1. SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

SQL子句的顺序不能改变,也不能互相替换。

2.2 聚合键中包含NULL的情况

按照进货单价统计数据行数

SELECT purchase_price, COUNT(*)  
FROM Product 
GROUP BY purchase_price;
小胖带你学SQL(三)聚合与排序

从结果我们可以看出,当聚合键中包含 NULL 时,也会将 NULL 作为一组特定的数据。

按照进货单价对表进行切分

这里的 NULL,大家可以理解为“不确定”。

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

使用WHERE子句和GROUP BY子句进行汇总处理

SELECT <列名1>, <列名2>, <列名3>, ……  
FROM <表名> WHERE  
GROUP BY <列名1>, <列名2>, <列名3>, ……;

像这样使用 WHERE 子句进行汇总处理时,会先根据 WHERE 子句指定的条件进行过滤,然后再进行汇总处理。

同时使用WHERE子句和GROUP BY子句

SELECT purchase_price, COUNT(*)  
FROM Product 
WHERE product_type = '衣服' 
GROUP BY purchase_price;
小胖带你学SQL(三)聚合与排序

GROUP BY和WHERE并用时SELECT语句的执行顺序

FROM → WHERE → GROUP BY → SELECT

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

常见错误① ——在SELECT子句中书写了多余的列

使用聚合函数时,SELECT子句中只能存在以下三种元素。

●常数 ●聚合函数 ● GROUP BY子句中指定的列名(也就是聚合键)

在SELECT子句中书写聚合键之外的列名会发生错误(只有mysql认可,介于本文使用的是mysql,不包含在内)

SELECT product_name, purchase_price, COUNT(*)  
FROM Product 
GROUP BY purchase_price;
小胖带你学SQL(三)聚合与排序

列名product_name 并没有包含在GROUP BY 子句当中。因此, 该列名也不能书写在 SELECT 子句之中。

常见错误② ——在GROUP BY子句中写了列的别名

SELECT 子句中的项目可以通过 AS 关键字来指定别名。但是,GROUP BY子句中不能使用别名的。

GROUP BY子句中使用列的别名会引发错误(mysql除外)

SELECT product_type AS pt, COUNT(*)  
FROM Product 
GROUP BY pt;
小胖带你学SQL(三)聚合与排序

常见错误③ —— GROUP BY子句的结果能排序吗

GROUP BY子句结果的显示是无序的。

常见错误④ ——在WHERE子句中使用聚合函数

按照商品种类统计数据行数

SELECT product_type, COUNT(*)  
FROM Product 
GROUP BY product_type;
小胖带你学SQL(三)聚合与排序

取出恰好包含2行数据 “办公用品”和“衣服的组时。

在WHERE子句中使用聚合函数会引发错误(mysql也会发生错误)

SELECT product_type, COUNT(*)  
FROM Product 
WHERE COUNT(*) = 2 
GROUP BY product_type;
小胖带你学SQL(三)聚合与排序

只有SELECT子句和HAVING子句(以及ORDER BY子句)中能够使用聚合函数。

DISTINCT和GROUP BY能够实现相同的功能

SELECT DISTINCT product_type  
FROM Product;

SELECT product_type  
FROM Product 
GROUP BY product_type;
小胖带你学SQL(三)聚合与排序

3 为聚合结果指定条件

3.1 HAVING子句

使用 GROUP BY 子句,可以得到将表分组后的结果。而通过HAVING子句指定条件,可以选取特定组。

取出符合指定条件的组

小胖带你学SQL(三)聚合与排序

WHERE 子句只能指定记录(行)的条件,而HAVING子句 可以用来指定组的条件。

HAVING子句

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

HAVING 子句必须写在 GROUP BY 子句之后。

使用 HAVING 子句时 SELECT 语句的顺序

SELECT → FROM → WHERE → GROUP BY → HAVING

从按照商品种类进行分组后的结果中,取出“包含的数据行数为2 行”的组

SELECT product_type, COUNT(*)  
FROM Product 
GROUP BY product_type 
HAVING COUNT(*) = 2;
小胖带你学SQL(三)聚合与排序

不使用HAVING子句的情况

SELECT product_type, COUNT(*)  
FROM Product 
GROUP BY product_type;
小胖带你学SQL(三)聚合与排序

当条件变成“销售单价的平均值大于等于 2500日元”。

不使用HAVING子句的情况

SELECT product_type, AVG(sale_price)  
FROM Product 
GROUP BY product_type;
小胖带你学SQL(三)聚合与排序

使用HAVING子句设定条件的情况

SELECT product_type, AVG(sale_price)  
FROM Product 
GROUP BY product_type 
HAVING AVG(sale_price) >= 2500;

3.2 HAVING子句的构成要素

HAVING 子句中 能够使用的 3种要素:

●常数 ●聚合函数 ● GROUP BY子句中指定的列名(即聚合键)

HAVING子句的不正确使用方法

SELECT product_type, COUNT(*)  
FROM Product 
GROUP BY product_type 
HAVING product_name = '圆珠笔';

product_name 列并不包含在 GROUP BY 子句中,因此不允许 写在 HAVING 子句里。

3.3 相对于HAVING子句, 更适合写在WHERE子句中的条件

聚合键所对应的条件既可以写在 HAVING 子句当中, 又可以写在 WHERE子句当中。

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

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

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

SELECT product_type, COUNT(*)  
FROM Product 
WHERE product_type = '衣服' 
GROUP BY product_type;
小胖带你学SQL(三)聚合与排序

但是,聚合键所对应的条件最好书写在 WHERE 子句之中。 理由有两个。 首先,根本原因是WHERE 子句和HAVING 子句的作用不同。写在WHERE 子句之中,书写出的 SELECT 语句不但可以分清两者各自的功能,理解起来也更加容易。

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

其次,WHERE 子句 对比HAVING 子句的执行速度更快,返回结果所需的时间更短 。

4 聚合与排序对查询结果进行排序

4.1 ORDER BY子句

简单的 SELECT 语句回顾。

显示商品编号、商品名称、销售单价和进货单价的SELECT语句

SELECT product_id, product_name, sale_price, purchase_price  
FROM Product;
小胖带你学SQL(三)聚合与排序

以上结果乍一看,貌似是按照商品编号从小到大的顺序(升序)排列的。其实,排列顺序是随机的,这只是个偶然。

通常,从表中抽取数据时,如果没有特别指定顺序,最终排列顺序便无从得知。。即使是同一条 SELECT 语句,每次执行时排列顺序很可能发生改变。

但是不进行排序,很可能出现结果混乱的情况。这时,便需要通过在 SELECT 语句末尾添加 ORDER BY 子句来明确指定排列顺序。

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;
小胖带你学SQL(三)聚合与排序

不论何种情况,ORDER BY 子句都需要写在 SELECT 语句的末尾。这 是因为对数据行进行排序的操作必须在结果即将返回时执行。ORDER BY 子句中书写的列名称为排序键。

子句的书写顺序

1.SELECT 子句 →2.FROM 子句→3.WHERE 子句→4.GROUP BY 子句→5. HAVING 子句→ 6.ORDER BY 子句

4.2 指定升序或降序

想要按照销售单价由高到低,也就是降序排列时,在列名后面使用 DESC 关键字。

按照销售单价由高到低(降序)进行排列

SELECT product_id, product_name, sale_price, purchase_price  
FROM Product 
ORDER BY sale_price DESC;
小胖带你学SQL(三)聚合与排序

其实,使用升序进行排列时,正式的书写方式应该是使用关键字ASC, 但是省略该关键字时会默认使用升序进行排序。未指定ORDER BY子句中排列顺序时会默认使用升序进。

4.3 指定多个排序键

由于 ASC 和 DESC 这两个关键字是以列为单位指定的,因此可以 同时指定一个列为升序,指定其他列为降序。

按照销售单价和商品编号的升序进行排序

SELECT product_id, product_name, sale_price, purchase_price  
FROM Product 
ORDER BY sale_price, product_id;
小胖带你学SQL(三)聚合与排序

在 ORDER BY 子句中同时指定多个排序键的规则是优先使用左侧的键,如果该列存在相同值的话,再接着参考右侧的键。

4.4 NULL的顺序

前面我们学过不能对 NULL 使用比较运算符,也就是说,不能对 NULL 和数字进行排序,也不能与字符串和日期比较大小。因此,使用含有 NULL 的列作为排序键时, NULL 会在结果的开头或末尾汇总显示。究竟是在开头显示还是在末尾显示,并没有特殊规定。

按照进货单价的升序进行排列

SELECT product_id, product_name, sale_price, purchase_price  
FROM Product 
ORDER BY purchase_price;
小胖带你学SQL(三)聚合与排序

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

前面内容提到过,在 GROUP BY 子句中不能使用 SELECT 子句中定义的别名(mysql中可以),但是在 ORDER BY 子句中却是允许使用别名的。

ORDER BY子句中可以使用列的别名

SELECT product_id AS id, product_name, sale_price AS sp, purchase_price  
FROM Product 
ORDER BY sp, id;
小胖带你学SQL(三)聚合与排序

SELECT 子 句的执行顺序在GROUP BY 子句之后,ORDER BY 子句之前。因此,在执 行GROUP BY 子句时,SELECT 语句中定义的别名无法被识别 。对于 在SELECT 子句之后执行的 ORDER BY 子句来说,就没有这样的问题了。

使用 HAVING 子句时 SELECT 语句的顺序

FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY

4.6 ORDER BY子句中可以使用的列

SELECT子句中未包含的列也可以在ORDER BY子句中使用

SELECT product_name, sale_price, purchase_price  
FROM Product 
ORDER BY product_id;
小胖带你学SQL(三)聚合与排序

ORDER BY子句中也可以使用聚合函数

SELECT product_type, COUNT(*)  
FROM Product 
GROUP BY product_type 
ORDER BY COUNT(*);
小胖带你学SQL(三)聚合与排序

4.7 不要使用列编号

列编号是指SELECT 子句中的列按照从左到 右的顺序进行排列时所对应的编号(1, 2, 3, …)。

ORDER BY子句中可以使用列的编号

-- 通过列名指定 
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;
小胖带你学SQL(三)聚合与排序

上述两条语句结果相同,列编号使用起来非常方便,但我们并不推荐使用,原因如下。

第一,代码阅读起来比较难。使用列编号时,如果只看 ORDER BY 子句是无法知道当前是按照哪一列进行排序的,只能去SELECT 子句的列表中按照列编号进行确认。

第二,这也是最根本的问题。将来随着DBMS 的版本升级,可能原本能够正常执行的SQL 突然就会出错。 不光是这种单独使用的SQL 语句,对于那些在系统中混合使用的SQL 来 说,更要极力避免。

 

 

 

 

 

 

 

 

 

 

 

本文来自知乎,观点不代表一起大数据-技术文章心得立场,如若转载,请注明出处:https://zhuanlan.zhihu.com/p/144566504

联系我们

在线咨询:点击这里给我发消息

邮件:23683716@qq.com

跳至工具栏