階層型集計の異なる値の数の計算

異なる値を持つ行数を計算するには、 SQL COUNT 関数を呼び出して DISTINCT キーワードを使用できます。異なる値のおおよその数が必要な場合は、 HyperLogLog 関数(例: APPROX_COUNT_DISTINCT)を使用できます。詳細については、 異なる値の数の推定 をご参照ください。

階層的な集計(例: 複数のグループ化セット、ロールアップ、キューブ)の異なる値をカウントする場合は、 COUNT(DISTINCT <式>) を使用するのではなく、ビットマップを作成して使用するとパフォーマンスを向上させられます。

このトピックでは、ビットマップを作成および使用して、異なる値の数を計算する方法について説明します。

このトピックの内容:

紹介

階層的な集計(例: 複数のグループ化セット、ロールアップ、キューブ)の異なる値の数を計算する場合は、考えられるすべての異なる値のセットを表すビットマップを生成してクエリを実行すると、計算を高速化できます。

  • このビットマップでは、データに存在する異なる値に対応するビットを設定します。

  • 異なる値の数を計算するときは、ビットマップ関数を使用して(COUNT(DISTINCT <式>) でテーブルをクエリするのではなく)、ビットマップに設定されているビットをカウントします。

ビットマップ関数は、次の条件下で COUNT(DISTINCT <式>) よりも優れたパフォーマンスを発揮します。

  • クエリは、異なる値をカウントする階層的な集計(例: 複数のグループ化セット、ロールアップ、またはキューブ)を実行します。

    COUNT(DISTINCT <式>) (各グループに対して実行が必要)とは異なり、ビットマップ関数を呼び出すことでビットマップを作成して再利用できます。これにより、クエリプランのコストを削減できます。

  • 値の範囲は密です(例: 値はシーケンスによって生成)

    値の範囲がスパースの場合は、 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の範囲にある異なる値のビットは、2つの別々のバケットにバケット化されます。一方のバケットのビットマップは値1~32,768を表し、もう一方のバケットのビットマップは値32,769~65,536を表します。各バケットのビットマップには、異なる値を表すビットのサブセットが含まれています。

次の図は、ビットマップの論理表現を示しています。(前述のように、 BINARY 値のビットマップの物理的表現は異なる場合があります。)

Logical representation of a bitmap

異なる値は、ビットマップを含むバケットとそのビットマップに設定されているビットの組み合わせで表されます。特定の値を表すバケットとビットを識別するには、次の関数を使用します。

  • 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 |
+-------------------------+------------------------+

数値32,768は、ビットマップ1の位置32,767のビットで表されます。

select bitmap_bucket_number(32768), bitmap_bit_position(32768);

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

別の例として、数値32,769は、ビットマップ2の位置0のビットで表されます。

select bitmap_bucket_number(32769), bitmap_bit_position(32769);

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

ビットマップの作成

考えられるすべての異なる値を表すビットマップを作成するには、 SELECT ステートメントで BITMAP_CONSTRUCT_AGG 関数を呼び出します。

  1. BITMAP_BIT_POSITION によって返された列の値を BITMAP_CONSTRUCT_AGG 関数に渡します。

  2. SELECT ステートメントで BITMAP_BUCKET_NUMBER を選択し、 GROUP BY を使用して特定のビットマップ(「バケット番号」で識別)の結果を集計します。

BITMAP_CONSTRUCT_AGG は、集計関数です。このコンテキストでの集計とは、いずれかの行に異なる値がある場合に、異なる値のビットを設定することを意味します。複数の行に値3が含まれている場合、 BITMAP_CONSTRUCT_AGG は3に対するビットを1回設定するだけで、3を含む追加の行のビットに対する値は変更しません。

たとえば、数値の列を含む次のテーブルを作成します。2つの異なる値を挿入します。そのうちの1つは32768より大きい値です。

select bitmap_bucket_number(32769), bitmap_bit_position(32769);
create or replace table bitmap_test_values (val int);
insert into bitmap_test_values values (1), (32769);

次のコマンドを実行して、異なる値を表すビットを含むビットマップを生成します。

-- 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 |
+-----------+----------------------+

上記の BITMAP 列に示されているバイナリ値は、実際のビットマップではないことに注意してください。ビットマップの物理的表現はビットマップ関数によって管理され、表現される値の数に応じて変更される場合があります。ビットマップのバイナリ値が特定の形式になることは期待できません。

同じ値で追加の行を挿入しても、結果のビットマップは変更されません。 BITMAP_CONSTRUCT_AGG 関数は、異なる値のビットを1回だけ設定します。

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 |
+-----------+----------------------+

他の異なる値を挿入すると、それらの値に対応するビットが設定された別のビットマップが生成されます。

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 |
+-----------+----------------------+

ビットマップの集計

同じバケット(BITMAP_BUCKET_NUMBER によって返されるバケット番号で識別)に異なるビットマップを集計する必要がある場合は、 BITMAP_OR_AGG を呼び出します。

ビットマップからの異なる値の数を計算

ビットマップから異なる値の総数を取得するには、 BITMAP_BIT_COUNT を呼び出し、 BITMAP_CONSTRUCT_AGG または BITMAP_OR_AGG によって作成されたビットマップを渡します。

例:

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 |
+-----------+-----------------+

ビットマップの使用による、クエリのパフォーマンスの向上

次の例は、 COUNT(DISTINCT <式>) の代わりにビットマップ関数を使用する方法を示しています。

例1:単一のテーブル内の異なる値をカウントする。

次のコマンドを実行して、 内の異なる値の数をカウントするとします。

-- Original query using COUNT(DISTINCT <expression>)
SELECT COUNT(DISTINCT <col>) FROM <table>;

代わりにビットマップ関数を使用するようにこのクエリを変更できます。

-- The same query using bitmap functions
SELECT SUM(cnt) FROM
(
SELECT
  BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(<col>))) cnt
FROM <table>
GROUP BY BITMAP_BUCKET_NUMBER(<col>)
);

の値の範囲が0から32,768の場合は、代わりに次の簡単なステートメントを使用できます。

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

例2: GROUP BY を使用してグループごとのカウントを計算する。

次のコマンドを実行して、 の異なる値の数を キー でカウントするとします。

-- Original query using COUNT(DISTINCT <expression>)
SELECT COUNT(DISTINCT <col>) FROM <table> GROUP BY <keys>;

代わりにビットマップ関数を使用するようにこのクエリを変更できます。

-- The same query using bitmap functions
SELECT <keys>, SUM(cnt) FROM
(
SELECT
  <keys>,
  BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(<col>))) cnt
FROM <table>
GROUP BY <keys>, BITMAP_BUCKET_NUMBER(<col>)
)
GROUP BY <keys>;

例3: GROUP BY ROLLUP を使用してグループごとにカウントをロールアップする。

ビットマップ関数は、 GROUP BY ROLLUP 集計クエリに対してさらに効率的に機能します。ビットマップは(COUNT(DISTINCT <式>) とは対照的に)コンポーザブルであるため、計算作業が少なくなり、実行時間が短くなります。

次のコマンドを実行して、 の異なる値の数を キー でロールアップするとします。

-- Original query using COUNT(DISTINCT <expression>)
SELECT COUNT(DISTINCT <col>) FROM <table> GROUP BY ROLLUP(<keys>);

代わりにビットマップ関数を使用するようにこのクエリを変更できます。

-- The same query using bitmap functions
SELECT <keys>, SUM(cnt) FROM
(
SELECT
  <keys>,
  BITMAP_COUNT(BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(<col>))) cnt
  FROM <table>
  GROUP BY ROLLUP(<keys>), BITMAP_BUCKET_NUMBER(<col>)
)
GROUP BY <keys>;

ビットマップの事前計算

パフォーマンスを向上させるために、テーブルまたはマテリアライズドビューの異なる値のカウントを事前計算できます。

たとえば、データウェアハウスに複数のディメンションを持つファクトテーブルが含まれているとします。 COUNT(DISTINCT <式>) を必要とする最終的な集計またはキューブを計算する前に、ビットマップを構築して粗粒の荒い事前計算または事前集計を実行するマテリアライズドビューを定義できます。

次の例では、ビットマップを含むテーブルを作成し、このテーブルを使用して、さまざまなディメンションで集計された異なる値の数を計算します。

-- precomputation table
CREATE TABLE precompute AS
SELECT
  <dim1>,
  <dim2>,
  BITMAP_BUCKET_NUMBER(<col>) bucket,
  BITMAP_CONSTRUCT_AGG(BITMAP_BIT_POSITION(<col>)) bmp
FROM <table>
GROUP BY 1, 2, 3;

-- compute final aggregate for <dim1> and <dim2>
SELECT
  <dim1>,
  <dim2>,
  SUM(BITMAP_COUNT(bmp))
FROM precompute
GROUP BY 1, 2;

-- compute final aggregate for <dim1>-only
SELECT <dim1>, SUM(cnt) FROM
(
SELECT
  <dim1>,
  BITMAP_COUNT(BITMAP_OR_AGG(bmp)) cnt
FROM precompute
GROUP BY 1, bucket
)
GROUP BY 1;

-- compute final aggregate for <dim2>-only
SELECT <dim2>, SUM(cnt) FROM
(
SELECT
  <dim2>,
  BITMAP_COUNT(BITMAP_OR_AGG(bmp)) cnt
FROM precompute
GROUP BY 1, bucket
)
GROUP BY 1;