使用聚合函数

已完成

T-SQL 提供 SUM、MAX 和 AVG 等聚合函数,以执行接受多个值并返回单个结果的计算。

使用聚合函数

我们前面所看到的大多数查询都是一次对一行进行操作,使用 WHERE 子句来筛选行。 返回的每一行对应于原始数据集中的一行。

SQL Server 中提供了很多聚合函数。 本部分介绍最常见的函数,例如 SUM、MIN、MAX、AVG 和 COUNT。

使用聚合函数时,需要考虑以下几点:

  • 聚合函数返回单个(标量)值,几乎可以在能够使用单一值的任何情况下在 SELECT 语句中使用。 例如,可以在 SELECT、HAVING 和 ORDER BY 子句中使用这些函数。 但不能在 WHERE 子句中使用这些函数。
  • 聚合函数将忽略 NULL 值,但使用 COUNT(*) 时除外。
  • SELECT 列表中的聚合函数没有列标题,除非你使用 AS 提供别名。
  • SELECT 列表中的聚合函数对传递给 SELECT 操作的所有行执行操作。 如果没有 GROUP BY 子句,则会汇总满足 WHERE 子句中的任何筛选条件的所有行。 你将在下一个主题中了解有关 GROUP BY 的详细信息。
  • 除非使用 GROUP BY,否则不应将聚合函数与在同一 SELECT 列表中函数中未包含的列进行合并。

为扩展到内置函数之外,SQL Server 通过 .NET 公共语言运行时 (CLR) 为用户定义的聚合函数提供了一种机制。 该主题超出了本模块的范围。

内置聚合函数

如前所述,Transact-SQL 提供了许多内置聚合函数。 常用函数包括:

函数名

语法

说明

SUM

SUM(表达式)

对列中所有非空数值求总计。

AVG

AVG(表达式)

对列中所有非 NULL 数值求平均值(sum/count)。

MIN

MIN(表达式)

返回最小的数字、最早的日期/时间或第一个出现的字符串(根据排序规则)。

MAX

MAX(表达式)

返回最大的数字、最晚的日期/时间或最后一个出现的字符串(根据排序规则)。

COUNT 或 COUNT_BIG

COUNT(*) 或 COUNT(expression)

使用 (*) 计算所有行,包括具有 NULL 值的行。 将列指定为表达式后,将返回该列的非 NULL 行计数。 COUNT 返回 int;COUNT_BIG 返回 big_int。

要在 SELECT 子句中使用内置聚合,请在 MyStore 示例数据库中考虑下面的示例:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

此查询的结果如下所示:

AveragePrice

MinimumPrice

MaximumPrice

744.5952

2.2900

3578.2700

请注意,上面的示例汇总了 Production.Product 表中的所有行。 通过添加 WHERE 子句,可以轻松地修改查询以返回特定类别中产品的平均价格、最低价格和最高价格,如下所示:

SELECT AVG(ListPrice) AS AveragePrice,
       MIN(ListPrice) AS MinimumPrice,
       MAX(ListPrice) AS MaximumPrice
FROM Production.Product
WHERE ProductCategoryID = 15;

在 SELECT 子句中使用聚合时,SELECT 列表中引用的所有列必须用作聚合函数的输入,或在 GROUP BY 子句中引用。

请考虑以下查询,该查询尝试在聚合结果中包括 ProductCategoryID 字段:

SELECT ProductCategoryID, AVG(ListPrice) AS AveragePrice,
MIN(ListPrice) AS MinimumPrice,
MAX(ListPrice) AS MaximumPrice
FROM Production.Product;

运行此查询会导致以下错误

消息 8120、级别 16、状态 1、第 1 行

选择列表中的列 'Production.ProductCategoryID' 无效,因为该列没有包含在聚合函数或 GROUP BY 子句中。

查询将所有行视为单个聚合组。 因此,所有列都必须用作聚合函数的输入。

在以上示例中,我们聚合了数值数据,如上一个示例中的价格和数量。 某些聚合函数还可用于汇总日期、时间和字符数据。 以下示例显示了对日期和字符使用聚合:

此查询使用 MIN 和 MAX 按名称返回第一个和最后一个公司:

SELECT MIN(CompanyName) AS MinCustomer, 
       MAX(CompanyName) AS MaxCustomer
FROM SalesLT.Customer;

此查询将按照数据库排序规则序列(在这种情况下,按字母顺序排序)返回 CompanyName 的第一个值和最后一个值:

MinCustomer

MaxCustomer

自行车商店

Yellow Bicycle Company

其他函数可能与聚合函数嵌套。

例如,在下面的示例中使用了 YEAR 标量函数,仅返回在对 MIN 和 MAX 求值之前的订单日期的年份部分:

SELECT MIN(YEAR(OrderDate)) AS Earliest,
       MAX(YEAR(OrderDate)) AS Latest
FROM Sales.SalesOrderHeader;

最早

最晚

2008

2021

MIN 和 MAX 函数也可以与日期数据一起使用,以返回最早和最晚的时间值。 但 AVG 和 SUM 只能用于数字数据,包含整数、货币、浮点和十进制数据类型。

将 DISTINCT 与聚合函数一起使用

应该注意,在 SELECT 子句中使用 DISTINCT 是为了删除重复的行。 与聚合函数一起使用时,在计算汇总值之前,DISTINCT 会从输入列中删除重复值。 当汇总值的唯一匹配项(如订单表中的客户)时,DISTINCT 非常有用。

下面的示例将返回已下订单的客户的数量,而无论他们下了多少个订单:

SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers
FROM Sales.SalesOrderHeader;

COUNT(<some_column>) 仅计算列中有多少行具有某个值。 如果没有 NULL 值,则 COUNT(<some_column>) 将与 COUNT(*) 相同。 COUNT (DISTINCT <some_column>) 计算列中有多少个不同的值。

将聚合函数与 NULL 一起使用

请务必注意,数据中存在 NULL 值,以及 NULL 与 T-SQL 查询组件(包括聚合函数)交互的方式。 有以下几点需要注意:

  • 除了与 (*) 选项一起使用的 COUNT 之外,T-SQL 聚合函数会忽略 NULL 值。 例如,SUM 函数将只对非 NULL 值进行累加。 NULL 值的计算结果不为零。 COUNT(*) 对所有行进行计数,不考虑任何列中的值或非值。
  • 如果列中存在 NULL 值,则可能会导致 AVG 计算不准确,因为只会对填充的行求和,并将总和除以非 NULL 行的数目。 AVG(<column>) 与 (SUM(<column>)/COUNT(*)) 的结果可能有区别。

例如,请考虑以下名为 t1 的表:

C1

C2

1

Null

2

10

3

20

4

30

5

40

6

50

此查询说明了 AVG 处理 NULL 值的方式与使用 SUM/COUNT(*) 计算所得列计算平均值的方式之间的区别:

SELECT SUM(c2) AS sum_nonnulls, 
    COUNT(*) AS count_all_rows, 
    COUNT(c2) AS count_nonnulls, 
    AVG(c2) AS average, 
    (SUM(c2)/COUNT(*)) AS arith_average
FROM t1;

结果将为:

sum_nonnulls

count_all_rows

count_nonnulls

average

arith_average

150

6

5

30

25

在此结果集内,名为 average 的列是一个聚合,该聚合在内部获取总和 150,除以 c2 列中非 null 值的数量。 计算结果为 150/5 或 30。 名为 arith_average 的列将总和显式除以所有行的计数,因此计算结果为 150/6 或 25。

如果需要汇总所有行(无论是否为 NULL),请考虑将 NULL 替换为聚合函数不会忽略的另一个值。 可以使用 COALESCE 函数来实现此目的。