使用数组计算分层聚合的非重复值¶

简介¶

计算分层聚合（例如多个分组集、汇总或多维数据集）的非重复值的数量时，可以通过调用生成包含非重复值的数组的函数来加快计算速度。然后，您可以调用 ARRAY_SIZE 计算这些非重复值的数量。

这些生成非重复值 ARRAYs 的聚合函数在以下形式的查询中可以比 COUNT(DISTINCT <expression>) 表现得更好：

• GROUP BY ROLLUP 汇总查询

• 包含多个分组集的查询。

与 COUNT(DISTINCT <expression>) （需要对每个组执行）不同，您可以编写和重用包含非重复值的 ARRAYs。对于分层聚合，通过一次性生成这些 ARRAYs 并在更高的聚合级别中重用它们，可以避免重复计算不同的计数。

此外，为了进一步提高性能，可以提前（例如，在物化视图中）生成这些 ARRAYs，而不是在查询期间生成，并且可以在查询中使用这些预先计算的 ARRAYs。

创建包含非重复值的 ARRAY¶

要创建包含列中非重复值的 ARRAY，请在 SELECT 语句中调用 ARRAY_UNIQUE_AGG 函数。

ARRAY_UNIQUE_AGG 是一个聚合函数。在这种情况下，聚合意味着仅返回出现在多行中的值的一个实例。如果多行包含值 3，则 ARRAY_UNIQUE_AGG 在返回的 ARRAY 中只包含一次 3。

例如，创建包含一列数值的下表，然后在该列中插入一些值。

CREATE OR REPLACE TABLE array_unique_agg_test (a INTEGER);
INSERT INTO array_unique_agg_test VALUES (5), (2), (1), (2), (1);

运行以下命令以生成包含列中非重复值的 ARRAY：

SELECT ARRAY_UNIQUE_AGG(a) AS distinct_values FROM array_unique_agg_test;

+-----------------+
| DISTINCT_VALUES |
|-----------------|
| [               |
|   5,            |
|   2,            |
|   1             |
| ]               |
+-----------------+

计算 ARRAYs 中非重复值的数量¶

要从 ARRAY 中获取非重复值的总数，请调用 ARRAY_SIZE，传入 ARRAY_UNIQUE_AGG 创建的 ARRAY。

例如：

SELECT ARRAY_SIZE(ARRAY_UNIQUE_AGG(a)) AS number_of_distinct_values FROM array_unique_agg_test;

+---------------------------+
| NUMBER_OF_DISTINCT_VALUES |
|---------------------------|
|                         3 |
+---------------------------+

使用数组提高查询性能¶

以下示例演示如何使用生成非重复值 ARRAYs 的聚合函数作为 COUNT(DISTINCT <expression>) 的替代方法。

• 示例 1：计算单个表中的非重复值数量

• 示例 2：使用 GROUP BY 按组计算数量

• 示例 3：使用 GROUP BY ROLLUP 按组汇总数量

SELECT
  COUNT(DISTINCT my_column_1),
  COUNT(DISTINCT my_column_2)
FROM my_table;

SELECT
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_1)),
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_2))
FROM my_table;

SELECT
  COUNT(DISTINCT my_column_1),
  COUNT(DISTINCT my_column_2)
FROM my_table
GROUP BY my_key_1, my_key_2;

SELECT
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_1)),
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column_2))
FROM my_table
GROUP BY my_key_1, my_key_2;

SELECT
  COUNT(DISTINCT my_column)
FROM my_table
GROUP BY ROLLUP(my_key_1, my_key_2);

SELECT
  ARRAY_SIZE(ARRAY_UNIQUE_AGG(my_column))
FROM my_table
GROUP BY ROLLUP(my_key_1, my_key_2);

预先计算 ARRAYs¶

为了提高性能，可以预先计算表或物化视图中非重复值的 ARRAYs。

例如，假设数据仓库包含一个具有多个维度的事实表。您可以定义物化视图来构造 ARRAYs，以便在计算需要 COUNT(DISTINCT <expression>) 的最终汇总或多维数据集之前，执行粗粒度的预计算或预汇总。

要在每行中收集 ARRAYs 中的非重复值，请调用 ARRAY_UNION_AGG 函数。

以下示例创建了一个包含 ARRAYs 的表，并使用此表来计算按不同维度汇总的非重复值的数量。

以下语句创建了一个名为 precompute 的表，其中包含 ARRAYs：

CREATE TABLE precompute AS
SELECT
  my_dimension_1,
  my_dimension_2,
  ARRAY_UNIQUE_AGG(my_column) arr
FROM my_table
GROUP BY 1, 2;

以下语句计算 my_dimension_1 和 my_dimension_2 的汇总：

SELECT
  my_dimension_1,
  my_dimension_2,
  ARRAY_SIZE(arr)
FROM precompute
GROUP BY 1, 2;

以下语句仅计算 my_dimension_1 的汇总：

SELECT
  my_dimension_1,
  ARRAY_SIZE(ARRAY_UNION_AGG(arr))
FROM precompute
GROUP BY 1;

以下语句仅计算 my_dimension_2 的汇总：

SELECT
  my_dimension_2,
  ARRAY_SIZE(ARRAY_UNION_AGG(arr))
FROM precompute
GROUP BY 1;

限制¶

在 Snowflake 中，ARRAY 数据类型大小限制为 16 MiB，这意味着如果输出 ARRAY 的物理大小超过此限制，ARRAY_UNIQUE_AGG 或 ARRAY_UNION_AGG 将生成错误。

在这些情况下，可以考虑改用 位图聚合。作为替代方案，还可以应用类似于位图聚合的分区化技术，但使用与 BITMAP_BUCKET_NUMBER 不同的分区化函数。