第一章 聚合函数概述

计算列的所有值并返回单个聚合值的函数。

支持聚合函数

聚合函数执行与单个列中的一个或多个值相关的任务，并返回单个值。
支持的功能有:

• SUM - 返回指定列的值的和。
• AVG - 返回指定列值的平均值。
• COUNT - 返回表中的行数，或指定列中非空值的个数。
• MAX - 返回指定列中使用的最大值。
• MIN - 返回指定列中使用的最小值。
• VARIANCE，VAR_SAMP, VAR_POP - 返回指定列的值的统计方差。
• STDDEV, STDDEV_SAMP, STDDEV_POP - 返回指定列值的统计标准偏差。
• LIST - 以逗号分隔的列表形式返回指定列中使用的所有值。
• %DLIST - 返回指定列中使用的所有值，作为 IRIS列表结构中的元素。
• XMLAGG - 将指定列中使用的所有值作为连接字符串返回。
• JSON_ARRAYAGG - 返回指定列中使用的所有值作为JSON格式数组。

可以使用CREATE aggregate命令定义其他用户定义的聚合函数。

聚合函数忽略为NULL的字段。
例如，LIST和%DLIST不包含指定字段为NULL的行的元素。
COUNT只计算指定字段的非空值。

聚合函数(COUNT除外)不能应用于流字段。
这样做会产生一个SQLCODE -37错误。
可以使用COUNT来计数流字段值，但有一些限制。

注意:聚合函数类似于窗口函数。
但是，聚合函数从一组行中获取列的值，并将结果作为单个值返回。
窗口函数从一组行中获取一列的值，并为每一行返回一个值。
聚合函数可以在窗口函数中指定。
不能在聚合函数中指定窗口函数。
SUM既可以用作聚合函数，也可以用作窗口函数。

使用聚合函数

聚合函数可用于:

• SELECT列表，可以作为列出的选择项，也可以作为子查询的选择项。
• HAVING 子句。
  然而，HAVING子句必须显式指定聚合函数;
  它不能使用相应的选择项列别名或选择项序列号指定聚合。
• DISTINCT BY子句。
  然而，单独指定聚合函数本身是没有意义的，而且总是返回单行。
  更有意义的是将聚合函数指定为表达式的一部分，例如DISTINCT BY(MAX(Age)-Age)。

聚合函数不能直接用于:

• ORDER BY子句。
  尝试这样做会产生一个SQLCODE -73错误。
  但是，通过指定相应的列别名或选择项序列号，可以在ORDER BY子句中使用聚合函数。
• WHERE子句。
  尝试这样做会产生一个SQLCODE -19错误。
• GROUP BY。
  尝试这样做会产生一个SQLCODE -19错误。
• TOP。
  尝试这样做会产生一个SQLCODE -1错误。
• JOIN。
  尝试在ON子句中指定聚合将产生SQLCODE -19错误。
  试图在USING子句中指定聚合将生成SQLCODE -1错误。

但是，可以使用提供列别名的子查询为这些子句提供聚合函数值(TOP子句除外)。
例如，要使用WHERE子句来选择年龄值小于平均年龄值，可以将AVG聚合函数放在一个子查询中:

SELECT Name,Age,AvgAge
FROM (SELECT Name,Age,AVG(Age) AS AvgAge FROM Sample.Person)
WHERE Age < AvgAge
ORDER BY Age

image.png

组合聚合和字段

SQL允许在查询中使用其他SELECT项指定聚合函数。诸如COUNT(*)之类的聚合不需要在单独的查询中。

SELECT TOP 5 COUNT(*),Name,AVG(Age)
FROM Sample.Person
ORDER BY Name

image.png

当指定聚合函数并且在选择列表中未指定字段选择项时，SQL将返回一行。将忽略TOP子句，除非它是TOP 0(不返回行)：

SELECT TOP 7 AVG(Age),LIST(Age)
FROM Sample.Person
WHERE Age > 75

image.png

指定聚合函数并在选择列表中指定一个或多个字段选择项时，SQL将根据字段项所需的行数返回：

SELECT DISTINCT Age,AVG(Age),LIST(Age)
FROM Sample.Person
WHERE Age > 75

image.png

列名和别名

默认情况下，分配给聚合函数结果的列名是Aggregate_n，其中n数字后缀是SELECT列表中指定的列序列号。因此，下面的示例创建列名Aggregate_2和Aggregate_5：

SELECT TOP 5 Home_State,COUNT(*),Name,Age,AVG(Age)
FROM Sample.Person
ORDER BY Name

image.png

要指定另一个列名(列别名)，请使用AS关键字：

SELECT COUNT(*) AS PersonCount
FROM Sample.Person,Sample.Employee

image.png

可以使用列别名在ORDER BY子句中指定聚合字段。以下示例按年龄与平均年龄不同的顺序列出人员：

SELECT Name,Age,
    AVG(Age) AS AvgAge,
    ABS(Age - AVG(Age)) AS RelAge
FROM Sample.Person
ORDER BY RelAge

image.png

使用ORDER BY

LIST、%DLIST、XMLAGG和JSON_ARRAYAGG函数将多行中的表列的值合并为单个聚合值。由于ORDER BY子句是在计算所有聚合字段之后应用于查询结果集的，因此ORDER BY不能直接影响这些聚合中的值序列。在某些情况下，这些聚合的结果可能会按顺序显示，但不应依赖此顺序。给定聚合结果值中列出的值不能显式排序。

DISTINCT关键字子句

所有聚合函数都支持可选的DISTINCT关键字子句。此关键字将聚合操作限制为只有不同的(唯一)字段值。使用默认字段排序规则(%SQLUPPER)时，仅字母不同的字段值不被视为不同的值。如果未指定DISTINCT，则默认为对所有非空值(包括重复值)执行聚合操作。MIN和MAX聚合函数支持DISTINCT关键字，尽管它不执行任何操作。

聚合函数DISTINCT field1子句忽略为NULL的field1值。这与SELECT语句的DISTINCT子句不同：SELECT DISTINCT子句为DISTINCT NULL返回一行，就像它为每个DISTINCT字段值返回一行一样。但是，由(Field2)field1区分的聚合函数不会忽略field2的不同NULL。例如，如果FavoriteColors有50个不同的值和多个NULL，则返回的不同行数为51，计数(DISTINCT FavoriteColors)为50，计数(DISTINCT BY(FavoriteColors)%ID)为51：

SELECT DISTINCT FavoriteColors,
       COUNT(DISTINCT FavoriteColors),
       COUNT(DISTINCT BY(FavoriteColors) %ID)
 FROM Sample.Person

image.png

使用DISTINCT和GROUP BY

带有SELECT-ITEM聚合函数和GROUP BY子句的SELECT DISTINCT返回的结果与DISTINCT关键字不存在时返回的结果相同。要获得所需的结果，请将聚合函数放入子查询中。

例如，返回各州不同计数的人数(有些州有4个人，有些州有6个人，依此类推)。可以按如下方式实现此结果：

SELECT DISTINCT COUNT(*) AS PersonCounts
FROM Sample.Person
GROUP BY Home_State

image.png

相反，你会得到每个州的人数统计，就像DISTINCT关键字不存在一样:

SELECT COUNT(*) AS PersonCounts
FROM Sample.Person
GROUP BY Home_State

image.png

为了达到预期的结果，需要使用子查询，如下所示:

SELECT DISTINCT * 
FROM (SELECT COUNT(*) AS PersonCounts FROM Sample.Person
      GROUP BY Home_State)

image.png

行数

当查询返回聚合值时，%ROWCOUNT值取决于查询:

• 仅聚合函数:计算聚合值并返回%ROWCOUNT 1。
  如果只选择聚合的查询没有选择行，它仍然返回%ROWCOUNT 1: COUNT=0，其他聚合函数返回NULL.
• 只使用GROUP BY的聚合函数:返回GROUP BY子句选择的每个组的聚合值。
  %ROWCOUNT是选定的组数。
  如果查询没有选择行，则groupby不选择组，并且查询返回%ROWCOUNT 0。
• 仅使用DISTINCT的聚合函数:计算聚合值并返回%ROWCOUNT 1。
  如果查询没有选择行，DISTINCT就不会选择不同的值，并且查询返回%ROWCOUNT 0。
• 只包含TOP子句的聚合函数:对于任何非零的TOP值，计算聚合值并返回%ROWCOUNT 1。
  对于TOP=0，返回%ROWCOUNT 0，不计算聚合。
• 与字段聚合:如果查询返回字段值和聚合函数，则返回的行数就是所选择的行数。
  如果查询没有选择行，则返回%ROWCOUNT 0，并且不计算聚合。

这些结果在子查询或表达式的选择项中不受影响。

聚合、事务和锁定

在查询中包含一个聚合函数会导致查询将数据的当前状态返回给所有结果集字段，包括未提交的数据更改。
因此，对于包含聚合函数的查询，将忽略ISOLATION LEVEL READ COMMITTED设置。
未提交数据的当前状态如下:

• INSERT和UPDATE:聚合计算确实包含修改的值，即使这些修改尚未提交并可能回滚。
• DELETE和TRUNCATE TABLE:聚合计算不包括已删除的行，即使这些删除尚未提交并可能回滚。

因为聚合函数通常涉及来自大量行的数据，所以在聚合计算中涉及的所有行上发出事务锁是不可接受的。
因此，在进行聚合计算时，另一个用户可能正在执行修改数据的事务。

聚合和分片表

分片表对聚合函数的支持受到限制。
例如，切分表不支持聚合函数DISTINCT、%FOREACH和%AFTERHAVING子句。