聚合函数

聚合函数对跨行的值进行操作，以执行总和、平均值、计数、最小值/最大值、标准差和估计值等数学计算，以及执行一些非数学运算。

聚合函数将多行（实际上是零行、一行或多行）作为输入，并生成单个输出。相比之下，标量函数将一行作为输入，并生成一行（一个值）作为输出。

聚合函数始终只返回一行， 即使输入包含零行 也是如此。通常，如果输入包含零行，则输出为 NULL。但是，聚合函数在传递零行时可以返回 0、空字符串或一些其他值。

函数列表（按子类别）

函数名称

备注

一般聚合
ANY_VALUE
AVG
CORR
COUNT
COUNT_IF
COVAR_POP
COVAR_SAMP
LISTAGG
MAX
MAX_BY
MEDIAN
MIN
MIN_BY
MODE
PERCENTILE_CONT

使用与其他聚合函数不同的语法。
PERCENTILE_DISC

使用与其他聚合函数不同的语法。
STDDEV、STDDEV_SAMP

STDDEV 和 STDDEV_SAMP 为别名。
STDDEV_POP
SUM
VAR_POP
VAR_SAMP
VARIANCE_POP

VAR_POP 的别名。
VARIANCE、VARIANCE_SAMP

VAR_SAMP 的别名。

按位聚合
BITAND_AGG
BITOR_AGG
BITXOR_AGG

布尔聚合
BOOLAND_AGG
BOOLOR_AGG
BOOLXOR_AGG

哈希
HASH_AGG

半结构化数据聚合
ARRAY_AGG
OBJECT_AGG

线性回归
REGR_AVGX
REGR_AVGY
REGR_COUNT
REGR_INTERCEPT
REGR_R2
REGR_SLOPE
REGR_SXX
REGR_SXY
REGR_SYY

统计与概率
KURTOSIS
SKEW

计算非重复值
ARRAY_UNION_AGG
ARRAY_UNIQUE_AGG
BITMAP_BIT_POSITION
BITMAP_BUCKET_NUMBER
BITMAP_COUNT
BITMAP_CONSTRUCT_AGG
BITMAP_OR_AGG

基数估算 . （使用 HyperLogLog）
APPROX_COUNT_DISTINCT

HLL 的别名。
DATASKETCHES_HLL
DATASKETCHES_HLL_ACCUMULATE
DATASKETCHES_HLL_COMBINE
DATASKETCHES_HLL_ESTIMATE

非聚合函数；使用来自 DATASKETCHES_HLL_ACCUMULATE 或 DATASKETCHES_HLL_COMBINE 的标量输入。
HLL
HLL_ACCUMULATE
HLL_COMBINE
HLL_ESTIMATE

非聚合函数；使用来自 HLL_ACCUMULATE 或 HLL_COMBINE 的标量输入。
HLL_EXPORT
HLL_IMPORT

相似性估算 . （使用 MinHash）
APPROXIMATE_JACCARD_INDEX

APPROXIMATE_SIMILARITY 的别名。
APPROXIMATE_SIMILARITY
MINHASH
MINHASH_COMBINE

频率估算 . （使用 Space-Saving）
APPROX_TOP_K
APPROX_TOP_K_ACCUMULATE
APPROX_TOP_K_COMBINE
APPROX_TOP_K_ESTIMATE

非聚合函数；使用来自 APPROX_TOP_K_ACCUMULATE 或 APPROX_TOP_K_COMBINE 的标量输入。

百分位估算 . （使用 T-Digest）
APPROX_PERCENTILE
APPROX_PERCENTILE_ACCUMULATE
APPROX_PERCENTILE_COMBINE
APPROX_PERCENTILE_ESTIMATE

非聚合函数；使用来自 APPROX_PERCENTILE_ACCUMULATE 或 APPROX_PERCENTILE_COMBINE 的标量输入。

聚合实用程序
GROUPING

非聚合函数，但可以与聚合函数结合使用，以确定由 GROUP BY 查询生成的行的聚合级别。
GROUPING_ID

GROUPING 的别名。

AI 函数
AI_AGG
AI_SUMMARIZE_AGG

向量聚合
VECTOR_AVG
VECTOR_MAX
VECTOR_MIN
VECTOR_SUM

语义视图
AGG

介绍性示例

以下示例说明了聚合函数 (AVG) 和标量函数 (COS) 之间的区别。标量函数为每个输入行返回一个输出行，而聚合函数为多个输入行返回一个输出行：

创建一个表并用值填充它：

CREATE TABLE simple (x INTEGER, y INTEGER);
INSERT INTO simple (x, y) VALUES
    (10, 20),
    (20, 44),
    (30, 70);

查询表：

SELECT x, y 
    FROM simple
    ORDER BY x,y;

+----+----+
|  X |  Y |
|----+----|
| 10 | 20 |
| 20 | 44 |
| 30 | 70 |
+----+----+

标量函数为每个输入行返回一个输出行。

SELECT COS(x)
    FROM simple
    ORDER BY x;

+---------------+
|        COS(X) |
|---------------|
| -0.8390715291 |
|  0.4080820618 |
|  0.1542514499 |
+---------------+

聚合函数为多个输入行返回一个输出行：

SELECT SUM(x)
    FROM simple;

+--------+
| SUM(X) |
|--------|
|     60 |
+--------+

聚合函数和 NULL 值

某些聚合函数会忽略 NULL 值。例如，AVG 根据以下公式将 1、5 和 NULL 值的平均值计算为 3：

(1 + 5) / 2 = 3

在分子和分母中，仅使用两个非 NULL 值。

如果传递给聚合函数的所有值都是 NULL，则聚合函数返回 NULL。

可以将多个列传递给某些聚合函数。例如：

SELECT COUNT(col1, col2) FROM table1;

在这些实例中，如果任何单个列为 NULL，则聚合函数将忽略某一行。

例如，在以下查询中，COUNT 返回 1，而不是 4，因为四行中有三行在所选列中至少包含一个 NULL 值：

创建一个表并用值填充它：

CREATE OR REPLACE TABLE test_null_aggregate_functions (x INT, y INT);
INSERT INTO test_null_aggregate_functions (x, y) VALUES
  (1, 2),         -- No NULLs.
  (3, NULL),      -- One but not all columns are NULL.
  (NULL, 6),      -- One but not all columns are NULL.
  (NULL, NULL);   -- All columns are NULL.

查询表：

SELECT COUNT(x, y) FROM test_null_aggregate_functions;

+-------------+
| COUNT(X, Y) |
|-------------|
|           1 |
+-------------+

如果使用引用两列或多列的表达式调用 SUM，并且如果其中一列或多列是 NULL，则该表达式的计算结果为 NULL，并且忽略该行：

SELECT SUM(x + y) FROM test_null_aggregate_functions;

+------------+
| SUM(X + Y) |
|------------|
|          3 |
+------------+

此行为与 GROUP BY 的行为不同，后者在某些列为 NULL 时不会丢弃行：

SELECT x AS X_COL, y AS Y_COL 
  FROM test_null_aggregate_functions 
  GROUP BY x, y;

+-------+-------+
| X_COL | Y_COL |
|-------+-------|
|     1 |     2 |
|     3 |  NULL |
|  NULL |     6 |
|  NULL |  NULL |
+-------+-------+