使用位图计算分层聚合的非重复值¶

如果要对分层聚合（例如多个分组集、汇总或多维数据集）的非重复值进行计数，则可以生成表示非重复值的位图，然后从这些位图中计算非重复值的数量，以便提高性能。使用这种方法可能比使用 COUNT(DISTINCT <expr>) 更快。

本主题说明如何使用位图对非重复值进行计数。

有关对非重复值进行计数的其他方法，请参阅计算非重复值的数量。

本主题内容：

简介¶

计算分层聚合（例如多个分组集、汇总或多维数据集）的非重复值数量时，可以生成位图来表示所有可能非重复值的集合，然后查询该位图，以便加快计算速度。

在此位图中，设置与数据中存在的非重复值相对应的位。
计算非重复值的数量时，使用位图函数对位图中设置的位进行计数，而不是使用 COUNT(DISTINCT <expression>) 来查询表。

在以下情况下，位图函数的表现可能优于 COUNT(DISTINCT <expression>)：

查询执行对非重复值进行计数的分层聚合（例如，对于多个分组集、汇总或多维数据集）。

与需要为每个组执行的 COUNT(DISTINCT <expression>) 不同，您可以通过调用位图函数来组合及重用位图。这可以降低查询计划的成本。
值范围较密集（例如，值是由序列生成的）。

请注意，如果值范围较稀疏，则可以使用 DENSE_RANK 窗口函数将稀疏的值范围转换为密集的值范围。
值范围较小。较大的值范围可能需要多个位图，这些位图不能装入主内存，必须保存到磁盘。

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

了解位图如何标识非重复值¶

位图是存储为 BINARY 数据类型的连续内存片段。位图实际上是可以单独设置的位的数组。例如，一个 4 字节的位图由 32 位（4 字节 * 每字节 8 位）组成。

对于每个可能的非重复值，可以使用位图中的位来表示数据中是否存在非重复值。例如，如果数据中存在值 3 和 5，则可以在位图中将第 3 位和第 5 位设置为 1。（如果非重复值不是数值，则必须将这些值映射到数值。）

对于 Snowflake 中的位图函数，位图的默认大小为 32,768 位 (4 KiB)。请注意，此大小与 BINARY 值的物理大小并不对应。在内部，位图函数管理位图的物理表示形式，该表示形式可能不是实际的位图。（例如，函数可能使用索引向量。）位图的物理大小可以从 10 字节到 4108 字节不等。

如果非重复值的数量大于 32,768 位，则需要多个位图来表示所有值。将非重复值的位划分为不同位图的过程称为桶化。例如，介于 1–65,536 之间的非重复值的位将桶化为两个单独的桶。一个桶中的位图表示值 1–32,768，另一个桶中的位图表示值 32,769–65,536。每个桶中的位图包含表示非重复值的位的子集。

下图显示了位图的逻辑表示形式。（如前所述，BINARY 值中位图的物理表示形式可能不同。）

非重复值由包含位图的桶和在该位图中设置的位组合表示。要标识表示特定值的桶和位，请使用以下函数：

调用 BITMAP_BUCKET_NUMBER 来标识桶，其包含的位图含有值对应的位。
调用 BITMAP_BIT_POSITION 来标识值对应的位在位图中的位置（从零开始）。

例如，数值 1 由位图 1 中位置 0 处的位表示：

select bitmap_bucket_number(1), bitmap_bit_position(1);

+-------------------------+------------------------+
| BITMAP_BUCKET_NUMBER(1) | BITMAP_BIT_POSITION(1) |
|-------------------------+------------------------|
|                       1 |                      0 |
+-------------------------+------------------------+

Copy

数值 32,768 由位图 1 中位置 32,767 处的位表示：

select bitmap_bucket_number(32768), bitmap_bit_position(32768);

+-----------------------------+----------------------------+
| BITMAP_BUCKET_NUMBER(32768) | BITMAP_BIT_POSITION(32768) |
|-----------------------------+----------------------------|
|                           1 |                      32767 |
+-----------------------------+----------------------------+

Copy

作为另一个示例，数值 32,769 由位图 2 中位置 0 处的位表示：

select bitmap_bucket_number(32769), bitmap_bit_position(32769);

+-----------------------------+----------------------------+
| BITMAP_BUCKET_NUMBER(32769) | BITMAP_BIT_POSITION(32769) |
|-----------------------------+----------------------------|
|                           2 |                          0 |
+-----------------------------+----------------------------+

Copy

创建位图¶

若要创建位图来表示所有可能的非重复值，请在 SELECT 语句中调用 BITMAP_CONSTRUCT_AGG 函数：

将 BITMAP_BIT_POSITION 为列返回的值传递给 BITMAP_CONSTRUCT_AGG 函数。
在 SELECT 语句中，选择 BITMAP_BUCKET_NUMBER，并使用 GROUP BY 聚合给定位图（由“桶编号”标识）的结果。

BITMAP_CONSTRUCT_AGG 是一个聚合函数。在此上下文中，聚合意味着如果任何行具有非重复值，则设置该非重复值对应的位。如果有多行包含值 3， BITMAP_CONSTRUCT_AGG 只会设置一次 3 对应的位，并且不会为包含 3 的其他行更改位的值。

例如，创建包含一列数值的下表。插入两个非重复值，其中一个大于 32,768。

CREATE OR REPLACE TABLE bitmap_test_values (val INT);
insert into bitmap_test_values values (1), (32769);

Copy

运行以下命令以生成位图，它们包含表示非重复值的位：

-- Display the bitmap in hexadecimal
alter session set binary_output_format='hex';

select bitmap_bucket_number(val) as bitmap_id,
    bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
    from bitmap_test_values
    group by bitmap_id;

+-----------+----------------------+
| BITMAP_ID | BITMAP               |
|-----------+----------------------|
|         1 | 00010000000000000000 |
|         2 | 00010000000000000000 |
+-----------+----------------------+

Copy

备注

BITMAP 列包含位图的物理表示形式，不一定是实际位图。在此示例中，该列包含表示位图的索引向量。

索引向量是位图函数存储位图物理表示形式的一种方式。根据位图表示的值数量，位图函数可以对位图使用不同的物理表示形式。

位图的二进制值不一定以特定格式存储。若要确定设置了哪些位，请使用位图函数，而不是自己检查二进制值。

插入具有相同值的其他行不会更改生成的位图。BITMAP_CONSTRUCT_AGG 函数仅设置一次非重复值对应的位。

insert into bitmap_test_values values (32769), (32769), (1);

select bitmap_bucket_number(val) as bitmap_id,
    bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
    from bitmap_test_values
    group by bitmap_id;

+-----------+----------------------+
| BITMAP_ID | BITMAP               |
|-----------+----------------------|
|         1 | 00010000000000000000 |
|         2 | 00010000000000000000 |
+-----------+----------------------+

Copy

插入其他非重复值将生成另一个位图，其中设置了这些值的对应位。

insert into bitmap_test_values values (2), (3), (4);

select bitmap_bucket_number(val) as bitmap_id,
    bitmap_construct_agg(bitmap_bit_position(val)) as bitmap
    from bitmap_test_values
    group by bitmap_id;

+-----------+----------------------+
| BITMAP_ID | BITMAP               |
|-----------+----------------------|
|         1 | 00040000010002000300 |
|         2 | 00010000000000000000 |
+-----------+----------------------+

Copy

聚合位图¶

如果需要聚合同一个桶（由 BITMAP_BUCKET_NUMBER 返回的桶编号标识）中的不同位图，请调用 BITMAP_OR_AGG。

计算位图中非重复值的数量¶

若要从位图中获取非重复值的总数，请调用在 BITMAP_CONSTRUCT_AGG 或 BITMAP_OR_AGG 创建的位图中传入的 BITMAP_COUNT。

例如：

select bitmap_bucket_number(val) as bitmap_id,
    bitmap_count(bitmap_construct_agg(bitmap_bit_position(val))) as distinct_values
    from bitmap_test_values
    group by bitmap_id;

+-----------+-----------------+
| BITMAP_ID | DISTINCT_VALUES |
|-----------+-----------------|
|         1 |               4 |
|         2 |               1 |
+-----------+-----------------+

Copy

使用位图提高查询性能¶

下面的示例演示如何使用位图函数来替代 COUNT(DISTINCT <expression>)。

示例 1：计算单个表中的非重复值数量
示例 2：使用 GROUP BY 按组计算数量
示例 3：使用 GROUP BY ROLLUP 按组汇总数量

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

假设您想计算 my_column 中非重复值的数量。下表将使用 COUNT(DISTINCT expression) 来执行此任务的 SQL 语句与位图函数进行了比较。

以 COUNT(DISTINCT <expression>) 为例	使用位图函数的示例
SELECT COUNT(DISTINCT my_column) FROM my_table; Copy	SELECT SUM(cnt) FROM ( SELECT BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(my_column))) cnt FROM my_table GROUP BY BITMAP_BUCKET_NUMBER(my_table) ); Copy 请注意，如果 `my_column` 中的值范围为 0 到 32,768，则可以改用以下更简单的语句： -- If the full value range of my_column fits into the bitmap: -- MIN(my_column) >= 0 AND MAX(my_column) < 32,768 SELECT BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(my_column)) FROM my_table; Copy

以 COUNT(DISTINCT <expression>) 为例

使用位图函数的示例

SELECT
  COUNT(DISTINCT my_column)
FROM my_table;

Copy

SELECT SUM(cnt) FROM (
  SELECT
    BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(my_column))) cnt
  FROM my_table
  GROUP BY BITMAP_BUCKET_NUMBER(my_table)
);

Copy

请注意，如果 my_column 中的值范围为 0 到 32,768，则可以改用以下更简单的语句：

-- If the full value range of my_column fits into the bitmap:
--   MIN(my_column) >= 0 AND MAX(my_column) < 32,768
SELECT
  BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(my_column))
FROM my_table;

Copy

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

假设您想通过 my_key_1 和 my_key_2 计算 my_column 中非重复值的数量。下表将使用 SQL 来执行此任务的 COUNT(DISTINCT expression) 语句与位图函数进行了比较。

以 COUNT(DISTINCT <expression>) 为例	使用位图函数的示例
SELECT my_key_1, my_key_2, COUNT(DISTINCT my_column) FROM my_table GROUP BY my_key_1, my_key_2; Copy	SELECT my_key_1, my_key_2, SUM(cnt) FROM ( SELECT my_key_1, my_key_2, BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(my_column))) cnt FROM my_table GROUP BY my_key_1, my_key_2, BITMAP_BUCKET_NUMBER(my_column) ) GROUP BY my_key_1, my_key_2; Copy

以 COUNT(DISTINCT <expression>) 为例

使用位图函数的示例

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

Copy

SELECT my_key_1, my_key_2, SUM(cnt) FROM (
  SELECT
    my_key_1,
    my_key_2,
    BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(my_column))) cnt
  FROM my_table
  GROUP BY my_key_1, my_key_2, BITMAP_BUCKET_NUMBER(my_column)
)
GROUP BY my_key_1, my_key_2;

Copy

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

对于 GROUP BY ROLLUP 汇总查询，位图函数的工作效率更高。位图是可组合的（与 COUNT(DISTINCT <expression>) 相反），这样可以减少计算工作量和缩短执行时间。

假设您想通过 my_key_1 和 my_key_2 汇总 my_column 中非重复值的数量。下表将使用 SQL 来执行此任务的 COUNT(DISTINCT expression) 语句与位图函数进行了比较。

以 COUNT(DISTINCT <expression>) 为例	使用位图函数的示例
SELECT my_key_1, my_key_2, COUNT(DISTINCT my_column) FROM my_table GROUP BY ROLLUP(my_key_1, my_key_2); Copy	SELECT my_key_1, my_key_2, SUM(cnt) FROM ( SELECT my_key_1, my_key_2, BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(my_column))) cnt FROM my_table GROUP BY ROLLUP(my_key_1, my_key_2), BITMAP_BUCKET_NUMBER(my_column) ) GROUP BY my_key_1, my_key_2; Copy

以 COUNT(DISTINCT <expression>) 为例

使用位图函数的示例

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

Copy

SELECT my_key_1, my_key_2, SUM(cnt) FROM (
  SELECT
    my_key_1,
    my_key_2,
    BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(my_column))) cnt
  FROM my_table
  GROUP BY ROLLUP(my_key_1, my_key_2), BITMAP_BUCKET_NUMBER(my_column)
)
GROUP BY my_key_1, my_key_2;

Copy

预先计算位图¶

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

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

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

以下语句创建了一个名为 precompute 的表，其中包含位图和桶信息：

CREATE TABLE precompute AS
SELECT
  my_dimension_1,
  my_dimension_2,
  BITMAP_BUCKET_NUMBER(my_column) bucket,
  BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(my_column)) bmp
FROM my_table
GROUP BY 1, 2, 3;

Copy

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

SELECT
  my_dimension_1,
  my_dimension_2,
  SUM(BITMAP_COUNT(bmp))
FROM precompute
GROUP BY 1, 2;

Copy

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

SELECT my_dimension_1, SUM(cnt) FROM (
  SELECT
    my_dimension_1,
    BITMAP_COUNT(BITMAP_OR_AGG(bmp)) cnt
  FROM precompute
  GROUP BY 1, bucket
)
GROUP BY 1;

Copy

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

SELECT my_dimension_2, SUM(cnt) FROM (
  SELECT
    my_dimension_2,
    BITMAP_COUNT(BITMAP_OR_AGG(bmp)) cnt
  FROM precompute
  GROUP BY 1, bucket
)
GROUP BY 1;

Copy