配列の使用による、階層的な集計の異なる値の計算¶

紹介¶

階層的な集計（例: 複数のグループ化セット、ロールアップ、キューブ）の異なる値の数を計算する場合は、異なる値を含む配列を生成する関数を呼び出すと、計算を高速化できます。次に、 ARRAY_SIZE を呼び出して、これらの異なる値の数を計算できます。

異なる値の ARRAYs を生成するこれらの集計関数は、次の形式のクエリで COUNT(DISTINCT <式>) よりも優れたパフォーマンスを発揮します。

• GROUP BY ROLLUP 集計のクエリ

• 複数のグループ化セットを含むクエリ。

COUNT(DISTINCT <式>) （各グループに対して実行が必要）とは異なり、異なる値を含む ARRAYs は、作成して再利用できます。階層的な集計の場合、これらの ARRAYs を一度生成し、それより上位の集計レベルで再利用することにより、異なるカウントを繰り返し計算することを回避します。

加えて、パフォーマンスをさらに向上させるために、クエリ中ではなく、事前に（例: マテリアライズドビュー内で）これらの ARRAYs を作成できます。また、これらの事前計算された ARRAYs をクエリで使用できます。

異なる値を含む ARRAY の作成¶

列に異なる値を含む ARRAY を作成するには、 SELECT ステートメントで ARRAY_UNIQUE_AGG 関数を呼び出します。

ARRAY_UNIQUE_AGG は、集計関数です。このコンテキストでの集計とは、複数の行に表示される値のインスタンスを1つだけ返すことを意味します。複数の行に値3が含まれている場合、 ARRAY_UNIQUE_AGG には返された ARRAY に3が1回だけ含まれます。

たとえば、数値の列を含む次のテーブルを作成し、その列にいくつかの値を挿入します。

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

配列を使用した、クエリパフォーマンスの改善¶

次の例は、 COUNT(DISTINCT <式>) の代わりに、異なる値の ARRAYs を生成する集計関数を使用する方法を示しています。

• 例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 を事前計算できます。

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

各行の ARRAYs から異なる値を収集するには、 ARRAY_UNION_AGG 関数を呼び出します。

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

次のステートメントは、 ARRAYs を含む precompute という名前のテーブルを作成します。

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 とは異なるバケット化関数を使用します。