近似聚合函数在内存使用量和时间方面具有可扩展的特点,但其生成的结果为近似结果,而非准确结果。这些函数通常所需的内存少于精确聚合函数(例如 COUNT(DISTINCT ...)
),但也会引入统计不确定性。因此,近似聚合适用于无法使用线性内存的大数据流以及已经取近似值的数据。
本部分的近似聚合函数直接作用于输入数据,而不是数据的中间估算值。这些函数不允许用户使用草图为估算值指定精度。如果您想使用草图指定精度,请参阅:
- HyperLogLog++ 函数来估算基数。
APPROX_COUNT_DISTINCT
APPROX_COUNT_DISTINCT(expression)
说明
返回 COUNT(DISTINCT expression)
的近似结果。返回值为统计预计值,不一定是实际值。
此函数的准确性低于 COUNT(DISTINCT expression)
,但在处理海量输入时性能较为出众。
支持的参数类型
任意数据类型(ARRAY
和 STRUCT
除外)
返回的数据类型
INT64
示例
SELECT APPROX_COUNT_DISTINCT(x) as approx_distinct
FROM UNNEST([0, 1, 1, 2, 3, 5]) as x;
+-----------------+
| approx_distinct |
+-----------------+
| 5 |
+-----------------+
APPROX_QUANTILES
APPROX_QUANTILES([DISTINCT] expression, number [{IGNORE|RESPECT} NULLS])
说明
返回一组 expression
值的近似边界,其中 number
表示要创建的分位数的数量。此函数返回一个数组,该数组中包含 number
+ 1 个元素,其中第一个元素是近似最小值,最后一个元素是近似最大值。
支持的参数类型
expression
可以是任意受支持的数据类型(ARRAY
和 STRUCT
除外)
number
必须是 INT64。
可选子句
子句会按照以下顺序应用:
DISTINCT
:expression
的每个不同值仅聚合到结果中一次。IGNORE NULLS
或RESPECT NULLS
:如果指定了IGNORE NULLS
或者未指定任一项,则从结果中排除NULL
值。如果指定了RESPECT NULLS
,则结果中会包含NULL
值。如果最终查询结果中的数组包含NULL
元素,则会引发错误。
返回的数据类型
由 expression
参数指定的类型组成的 ARRAY。
如果不存在任何输入行或所有行的 expression
计算结果均为 NULL,则返回 NULL
。
示例
SELECT APPROX_QUANTILES(x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [1, 5, 10] |
+------------------+
SELECT APPROX_QUANTILES(x, 100)[OFFSET(90)] AS percentile_90
FROM UNNEST([1, 2, 3, 4, 5, 6, 7, 8, 9, 10]) AS x;
+---------------+
| percentile_90 |
+---------------+
| 9 |
+---------------+
SELECT APPROX_QUANTILES(DISTINCT x, 2) AS approx_quantiles
FROM UNNEST([1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [1, 6, 10] |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [NULL, 4, 10] |
+------------------+
SELECT FORMAT("%T", APPROX_QUANTILES(DISTINCT x, 2 RESPECT NULLS)) AS approx_quantiles
FROM UNNEST([NULL, NULL, 1, 1, 1, 4, 5, 6, 7, 8, 9, 10]) AS x;
+------------------+
| approx_quantiles |
+------------------+
| [NULL, 6, 10] |
+------------------+
APPROX_TOP_COUNT
APPROX_TOP_COUNT(expression, number)
说明
返回 expression
的近似顶级元素。number
参数指定返回的元素数量。
支持的参数类型
expression
可以是 GROUP BY
子句支持的任意数据类型。
number
必须是 INT64。
返回的数据类型
返回 ARRAY 类型的结果,其元素为 STRUCT 类型。该 STRUCT 包含两个字段。第一个字段(名为 value
)包含一个输入值。第二个字段(名为 count
)包含一个 INT64 值,指定 value 返回的次数。
如果不存在任何输入行,则返回 NULL
。
示例
SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST(["apple", "apple", "pear", "pear", "pear", "banana"]) as x;
+-------------------------+
| approx_top_count |
+-------------------------+
| [{pear, 3}, {apple, 2}] |
+-------------------------+
NULL 处理
APPROX_TOP_COUNT 不会忽略输入中的 NULL。例如:
SELECT APPROX_TOP_COUNT(x, 2) as approx_top_count
FROM UNNEST([NULL, "pear", "pear", "pear", "apple", NULL]) as x;
+------------------------+
| approx_top_count |
+------------------------+
| [{pear, 3}, {NULL, 2}] |
+------------------------+
APPROX_TOP_SUM
APPROX_TOP_SUM(expression, weight, number)
说明
根据指定 weight
的总和,返回 expression
的近似顶级元素。number
参数指定返回的元素数量。
如果 weight
输入为负或者 NaN
,此函数会返回错误。
支持的参数类型
expression
可以是 GROUP BY
子句支持的任意数据类型。
weight
必须是以下项之一:
- INT64
- NUMERIC
- FLOAT64
number
必须是 INT64。
返回的数据类型
返回 ARRAY 类型的结果,其元素为 STRUCT 类型。该 STRUCT 包含两个字段:value
和 sum
。value
字段包含输入表达式的值。sum
字段与 weight
类型相同,是与 value
字段关联的输入权重的近似总和。
如果不存在任何输入行,则返回 NULL
。
示例
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([
STRUCT("apple" AS x, 3 AS weight),
("pear", 2),
("apple", 0),
("banana", 5),
("pear", 4)
]);
+--------------------------+
| approx_top_sum |
+--------------------------+
| [{pear, 6}, {banana, 5}] |
+--------------------------+
NULL 处理
APPROX_TOP_SUM 不会忽略 expression
和 weight
参数的 NULL 值。
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, NULL AS weight), ("pear", 0), ("pear", NULL)]);
+----------------------------+
| approx_top_sum |
+----------------------------+
| [{pear, 0}, {apple, NULL}] |
+----------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, 2)]);
+-------------------------+
| approx_top_sum |
+-------------------------+
| [{NULL, 2}, {apple, 0}] |
+-------------------------+
SELECT APPROX_TOP_SUM(x, weight, 2) AS approx_top_sum FROM
UNNEST([STRUCT("apple" AS x, 0 AS weight), (NULL, NULL)]);
+----------------------------+
| approx_top_sum |
+----------------------------+
| [{apple, 0}, {NULL, NULL}] |
+----------------------------+