SQL数据处理的三大利器！| SQL全面教程三：实现对数据的汇总——聚合、分组及分组内过滤和排序

代码迷途
1,986 阅读15分钟

聚合函数（Aggregate Functions）

使用SQL进行某种操作或计算时要用到函数。比如计算数据行数、平均值等。

聚合函数

如下常用的函数：

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

SUM：计算表中数值列中数据的和(合计值)

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

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

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

用于汇总的函数称为聚合函数或聚集函数——Aggregate Functions。聚合，就是将多行汇总为一行。

所有的聚合函数都是输入多行输出一行。

计算数据的行数（COUNT()）

计算全部数据的行数

SELECT COUNT(*) FROM Product;

-- 输出为 8

COUNT ()中的星号为输入值，即参数(parameter)，而 输出值 称为 返回值。

只有COUNT函数可以将星号作为参数。

对于COUNT函数，参数列不同计算的结果也可能不同。当列中数据为NULL时，将不被COUNT计算在内。

即COUNT参数为列时，计算的是NULL之外的数据行数。COUNT(*)计算的是包含NULL的所有数据行数。

计算purchase_price列除NULL之外的数据行数：

SELECT COUNT(purchase_price) FROM Product;

-- 输出 6

计算合计值（SUM()）

计算sale_price和purchase_price的合计值

SELECT SUM(sale_price),SUM(purchase_price) FROM Product;    -- 输出 16780 | 12210

purchase_price有两条不明数据NULL。但是SUM函数仍能计算出结果。这与"四则运算中如果存在NULL，结果一定是NULL"并不矛盾，是因为以列名为参数的所有聚合函数，在计算之前会把NULL排除在外。

计算平均值（AVG()）

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

结果为：

          avg          |          avg
-----------------------+-----------------------
 2097.5000000000000000 | 2035.0000000000000000

进货单价purchase_price平均值的计算和SUM函数一样，会先排除NULL再计算。(500+320+2800+2800+500+790)/6=2035

将列中的NULL值作为0(或其他值)进行聚合统计

如果想将NULL作为0进行平均值计算(除数为8)，该如何实现呢？

答案是借助COALESCE函数。coalesce函数在做统计时比较有用，用来处理NULL值，参数可以有多个，coalesce返回第一个不为NULL的值。

COALESCE(expression_1, expression_2, ...,expression_n)依次参考各个参数，遇到非NULL值则停止并返回该值。如果所有表达式都是空值，则最终返回NULL。

常用COALESCE处理遇到NULL值时返回一个非空值，进行统计计算。

将purchase_price列的NULL作为0参与聚合统计计算平均值，可以使用AVG(COALESCE(purchase_price,0))的形式。

如下：

 SELECT SUM(purchase_price),AVG(purchase_price),AVG(COALESCE(purchase_price,0)),count(purchase_price),count(COALESCE(purchase_price,0)) FROM Product;

结果：

  sum  |          avg          |          avg          | count | count
-------+-----------------------+-----------------------+-------+-------
12710 | 1815.7142857142857143 | 1412.2222222222222222 |     7 |     9
(1 行记录)

COALESCE语法有多种变体，目前所知PostgreSQL、SQL Server、Oracle、MySQL、MariaDB，甚至Access，都支持COALESCE。

其他对NULL处理的函数：

  • SQL Server、Access：ISNULL(col,0)
  • MySQL、MariaDB：IFNULL(col,0)。MySQL/MariaDB中ISNULL(expr)用于判断一个值是否为null，如果为null返回1，不为null返回0。
  • Oracle：NVL(col,0)

PostgreSQL中只有COALESCE处理NULL值。

处理使用NULL值函数外，还可使用case实现对NULL的统计处理。

计算最大值和最小值

使用MAX和MIN函数(maximam/minimum)计算最大值最小值。

SELECT MAX(sale_price),MIN(purchase_price),MAX(regist_date),MIN(regist_date) FROM Product;  --  6800 | 320 | 2009-11-11 | 2008-04-28

SUM/AVG函数只能对数值类型的列使用。

MAX/MIN函数原则上可以适用于任何数据类型的列。

如上，对日期类型的列regist_date计算最大最小值。

聚合函数使用DISTINCT去除重复值

product_type有3种商品共8行数据(衣服2行、办公用品2行，厨房用具4行)。如何计算商品种类的个数呢？

SELECT COUNT(DISTINCT product_type) FROM Product;   -- 输出 3

DISTINCT必须写在括号中。这是因为必须要在计算行数之前删除product_type列中的重复数据。

聚合函数的参数中使用DISTINCT，可以计算数据种类，即去除重复数据后的聚合值。

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

DISTINCT必须使用列名，不能用于计算或表达式。

DISTINCT不能用于COUNT(*)；MIN()和 MAX()中使用DISTINCT没有任何价值，结果是相同的

相对DISTINCT，对所有行执行就算默认使用的是ALL参数，因为它是默认行为，所以不用指定。

Access在聚集函数中不支持DISTINCT。

对表进行分组

GROUP BY

汇总处理经常需要把表分为几组，然后再汇总。即对数据进行分组，然后在组内进行聚合统计。分组通过GROUP BY子句实现

SELECT <col1>,<col2>,······
 FROM <TableName>
 GROUP BY <col1>,<col2>,······

如下，按照商品种类分组，并求每种商品的数据行数(即商品数量)

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

结果为：

 product_type | count
--------------+-------
 厨房用具     |     4
 衣服         |     2
 办公用品     |     2

GROUP BY用于分组。GROUP BY子句中指定的列称为聚合键或分组列。

聚合键中包含NULL时，会将NULL作为一组特定的数据分组。

使用WHERE和GROUP BY子句时，会先执行WHERE过滤数据，再对过滤后的数据进行分组。

GROUP BY子句可以包含任意数目的列。

聚合函数和GROUP BY子句使用注意事项

1. 在SELECT子句中只能出现分组列、聚合函数和常数

使用聚合函数时，SELECT中的元素只能为以下三种：常数、聚合函数、GROUP BY子句中指定的列名。

SELECT子句中书写聚合键之外的列名会发生错误。

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

-- 错误:  字段 "product.product_type" 必须出现在 GROUP BY 子句中或者在聚合函数中使用
-- 第1行SELECT product_type,COUNT(*)

如上执行报错。只有MySQL默认情况下认同这种语法，所以能够执行，不会发生错误。但是MySQL以外的DBMS都不支持这样的语法，因此千万不要使用这样的写法。

MySQL/MariaDB通过设置SQL_MODE为ONLY_FULL_GROUP_BY，在分组中，可以强制SELECT子句只能出现分组列和聚合函数。

通过某个聚合键将表分组之后，结果中的一行数据就代表一组。聚合键之外的列，也就是一组之内，会有多个值，这种情况下，多个值无法与聚合键不是一对一的，就无法确定当前一行显示哪个值，这也是无法在SELECT中选择分组列之外的列的原因。

2. GROUP BY子句中不能使用列的别名

如下， GROUP BY子句中使用列的别名会引发错误。SELECT子句在GROUP BY之后执行，在执行GROUP BY时，SELECT中定义的别名，DBMS还并不知道。

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

这样的写法在PostgreSQL/MariaDB中可以正常执行。但SQL Server中会报错。

这样的写法在DBMS中并不是通用的，因此建议不要使用。

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

通常SELECT语句的执行结果的显示顺序都是随机的，想要按照某种特定顺序进行排序，需要使用ORDER BY子句。

4. WHERE子句中不能使用聚合函数

比如查询按product_type分组且包含2行数据的组，即查询商品数为2的商品种类。

如果在WHERE中判断聚合函数将会报错。

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

-- 错误:  聚合函数不允许出现在WHERE中
-- 第3行 WHERE COUNT(*)=2

==只有SELECT、HAVING、ORDER BY子句中可以使用聚合函数。==

5. 大多数SQL实现不允许GROUP BY列带有长度可变的数据类型（如文本或备注型字段）。

DISTINCT和GROUP BY

DISTINCT和GROUP BY子句都能实现去除后续列中的重复数据。

如下两条语句，返回相同的结果：

select distinct product_type from product;

select product_type from product group by product_type;

结果：

 product_type
--------------
 厨房用具
 衣服
 办公用品

并且两者也都将NULL作为一个单独的结果返回，多列使用时结果也相同。两者执行速度也差不多。

关于这两者的使用，最好参考语义化的需求。如果想要获取不重复的数据(去除结果中的重复记录)就用DISTINCT；如果需要进行分组聚合统计(对结果进行计算汇总)就用GROUP BY。

为聚合结果指定条件（过滤分组）

WHERE子句用来指定数据行的条件，HAVING子句用来指定分组的条件。

WHERE过滤行；HAVING过滤分组。

HAVING子句

HAVING子句用于指定组的条件，写在GROUP BY之后

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

查询按product_type分类后包含数据行数为2的产品类型

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

结果：

 product_type | count
--------------+-------
 衣服         |     2
 办公用品     |     2

查询按商品种类分组后，销售单价的平均值大于等于2500的组

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

------结果------
 product_type |          avg
--------------+-----------------------
 厨房用具     | 2795.0000000000000000
 衣服         | 2500.0000000000000000

HAVING支持所有WHERE操作符。

HAVING子句的限制

HAVING子句中能使用的要素和包含GROUP BY子句的SELECT一样，只能有三种：常数、聚合函数、GROUP BY子句中指定的列名(聚合键/分组列)。

聚合键对应的条件应写在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;

 product_type | count
--------------+-------
 衣服         |     3

聚合键所对应的条件不应该写在HAVING子句中，而应写在WHERE子句当中。

主要原因是WHERE子句和HAVING子句的作用不同。

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

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

其次是性能问题，通常将条件写在WHERE子句中要比写在HAVING子句中的处理速度更快，时间更短。where会先过滤后再排序聚合，排序聚合时处理的数据量小，且where中的列可以先创建索引，大幅提高处理速度。但HAVING子句是在排序之后才对数据进行分组的，然后执行过滤的。

使用COUNT函数等对表中的数据进行聚合操作时，DBMS内部就会进行排序处理。排序处理是会大大增加机器负担的高负荷的处理(有的数据库，如Oracle会使用散列hash而不是排序，但同样加重负担)。因此，只有尽可能减少排序的行数，才能提高处理速度。

关于统计行数：

通常COUNT(*)和COUNT(1)实现是一样的，没有性能差异。这两者是最优方案。

COUNT(*)是SQL92的标准用法。

COUNT(col)要看列是否是主键列、唯一索引列、索引列等，会比非键列查询快。

COUNT(col)如果列的前后位置不同，则可能导致查询速度不同。具体看DBMS实现。

排序

默认查询时，检索出来的数据是无序的。

如果不排序，数据通常以它在底层表中出现的顺序显示。但实际上，由于数据的更新删除，这个顺序也会出现变更。也就是，不明确指定的某种顺序的话，查询结果的顺序并不能确定。

关系数据库设计理论认为，如果不明确 规定排序顺序，则不应该假定检索出的数据的顺序有任何意义。

ORDER BY

从表中查询数据时，要想明确指定排列顺序，需要指定ORDER BY子句。

SELECT <列名1>,<列名2>,······
 FROM <表名>
 ORDER BY <排序基准列1>,<排序基准列2>,······

按照sale_price由低到高(升序)排列：

SELECT * FROM Product ORDER BY sale_price;

ORDER BY子句中的列名称为排序键

在ORDER BY的列名后使用DESC实现降序排序。升序ASC是默认值，可以不写。

ascendent——上升的；descendent——下降的

SELECT * 
  FROM Product 
  ORDER BY product_type DESC,sale_price;

SQL子句的书写顺序

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

ORDER BY子句应该总是写在SQL语句的最后，因为只有对最后查询的结果进行排序才有意义，中间的处理过程中没有排序的必要。大多数DBMS都会对此进行限制。

比如SQL Server中，如果在子查询、cte、视图、派生表、inline functions等中使用Order by就会报错：The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

多列排序

对于多列排序，如上面的ORDER BY product_type DESC,sale_price，排序方式是先按product_type降序排列，然后(product_type值相同的行再)按sale_price升序排列。

多个排序键的规则是优先使用左侧的键，如果该列存在相同值的话，再接着参考右侧的键。

NULL的顺序

使用含有NULL的列作为排序键时，NULL会在结果的开头或末尾汇总显示。不同的DBMS可实现指定NULL在开头还是末尾。

==在 SQL Server/MySQL/MariaDB 中 null 值默认为最小值。==

因此SQLServer中排序是，升序时默认NULL在开头。在SQL Server升序排序中，要想使NULL排在末尾，可在ORDER BY子句中使用表达式实现。如下所示。

MySQL/MariaDB升序时默认NULL也是在开头。

NULL的默认顺序及修改排序时NULL的位置：

-- SQL Server | MySQL/MariaDB  升序 NULL默认排在开头
SELECT purchase_price
 FROM Product 
 ORDER BY purchase_price;

-- SQL Server 升序  让NULL排在末尾
-- 通过ISNULL，实现任何非空数都将变为0，并且空值将变为1(升序时排在最后)
SELECT purchase_price
 FROM Product 
 ORDER BY ISNULL(purchase_price*0,1),purchase_price;

-- MySQL/MariaDB 升序  让NULL排在末尾
-- 借助类似的IFNULL函数或COALESCE函数实现
SELECT purchase_price
 FROM Product 
 ORDER BY IFNULL(purchase_price*0,1),purchase_price;

-- SQL Server | MySQL/MariaDB  升序 让NULL排在末尾
select purchase_price 
 from Product 
 order by case when purchase_price is null then 1 else 0 end asc,purchase_price asc;

==在PostgreSQL/Oracle中，null值默认最大。==

PostgreSQL中，升序排序时默认NULL在尾部。除了ORDER BY使用表达式实现NULL排在开头，还可以通过nulls last或者nulls first关键字指定NULL在开头还是结尾。

也可借助COALESCE函数实现。

-- PostgreSQL 升序 让NULL排在开头
SELECT purchase_price
 FROM Product 
 ORDER BY purchase_price nulls first;

-- 或
SELECT purchase_price
 FROM Product 
 ORDER BY COALESCE(purchase_price*0,-1),purchase_price;

-- 或
select purchase_price
 from Product
 order by case when purchase_price is null then 0 else 1 end asc,purchase_price asc;

ORDER BY排序键可以使用别名

SELECT子句的执行顺序在GROUP BY子句之后，ORDER BY子句之前。因此ORDER BY可以使用SELECT中指定的别名。

SELECT product_id AS id,product_name,sale_price sp,purchase_price
FROM Product
ORDER BY sp,id;

ORDER BY子句中可以使用的列或聚合函数

在表中的列或聚合函数都可以作为排序键。

SELECT product_name
  FROM Product
ORDER BY product_id;

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

存在分组GROUP BY子句时，ORDER BY子句只能使用聚合函数或分组列。

ORDER BY是对分组后的每组组内进行统计后来排序，或将分组后的分组列排序，即对分组后的结果进行排序。

如下执行将会报错

SELECT product_type,COUNT(*)
  FROM Product
  GROUP BY product_type
ORDER BY product_id;
-- 错误:  字段 "product.product_id" 必须出现在 GROUP BY 子句中或者在聚合函数中使用

另外，只要涉及使用聚合函数，则ORDER BY中就要使用聚合函数，或分组列。

如下，两个SQL都会报错

 SELECT COUNT(*)
  FROM Product
 ORDER BY product_id;
-- 错误:  字段 "product.product_id" 必须出现在 GROUP BY 子句中或者在聚合函数中使用

这和在有聚合函数时，不能单独使用非分组列或非聚合函数是一样的。

SELECT product_type,COUNT(*)
  FROM Product;
-- 错误:  字段 "product.product_type" 必须出现在 GROUP BY 子句中或者在聚合函数中使用

ORDER BY中建议不要使用列编号

ORDER BY子句中，可以使用在SELECT中出现的列所对应的编号。

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

如下，两者是相同的

-- 列名
SELECT product_id,product_name,sale_price
  FROM Product
ORDER BY sale_price DESC,product_id;

-- 列编号
SELECT product_id,product_name,sale_price
  FROM Product
ORDER BY 3 DESC,1;

虽然列编号很方便，但是不推荐使用:

  1. 代码阅读起来比较难。
  2. SQL-92中已经明确指出该排序功能将来会被删除(根本原因)。

GROUP BY子句中使用列编号。和别名一样

目前所知，在PostgreSQL/MySQL/MariaDB中支持，SQL Server中不支持。

其他DBMS未测试。

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

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

区分大小写和排序顺序

对文本数据排序时，在字典(dictionary)排序顺序中，A被视为与a相同，这是大多数DBMS的默认行为，因此文本数据的排序需要特别注意。

如有必要，需要设置排序或比较的规则，实现区分。

如下，演示PostgreSQL中，设置使用排序规则(Collation)，实现对字母大小写的区分排序。

关于Collation排序规则，以后会专门介绍。

创建表并插入BbAa字母数据：

CREATE TABLE OrderTest(
  letter char(1) NOT NULL
);
INSERT INTO OrderTest values('B'),('b'),('A'),('a');

默认排序规则，A/a相同，B/b相同

shop=# select * from OrderTest order by letter;
 letter
--------
 a
 A
 b
 B
(4 行记录)

shop=# select * from OrderTest order by letter collate "default";
 letter
--------
 a
 A
 b
 B
(4 行记录)

shop=# select * from OrderTest order by letter desc;
 letter
--------
 B
 b
 A
 a
(4 行记录)

指定collate为'C'或'POSIX'，将会严格按照字符代码字节值的顺序排序。ucs_basic是用于UTF8编码的collation，等同于C，按照Unicode码点排序。

shop=# select * from OrderTest order by letter collate "ucs_basic";
 letter
--------
 A
 B
 a
 b
(4 行记录)


shop=# select * from OrderTest order by letter collate "C";
 letter
--------
 A
 B
 a
 b
(4 行记录)

shop=# select * from OrderTest order by letter collate "C" desc;
 letter
--------
 b
 a
 B
 A
(4 行记录)
avatar
文章
阅读
粉丝