使用聚合函数

3 分钟

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 的表：

Null

此查询说明了 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

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

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

继续

使用聚合函数