カテゴリ：

クエリ構文

GROUP BY GROUPING SETS

GROUPING SETSは、 GROUP BY 句の強力な拡張機能であり、1つのステートメントで複数のGROUP BY句を計算します。グループセット は、一連のディメンション列です。

GROUPING SETS式は他のGROUP BY式と組み合わせることができ、この構造体は個別ではなく、GROUP BY句の統合された一部になっています。たとえば、 GROUP BY x, GROUPING SETS(y, z) と記述し、列 x でグループ化すると共に y および z に対する個別グループ化を組み合わせることができます。

GROUPING SETSは、同じ結果セット内の2つ以上の GROUP BY 操作の結合と同等です。例：

  • GROUP BY GROUPING SETS((a)) は、単一のグループ化集合操作 GROUP BY a と同等です。

  • GROUP BY GROUPING SETS((a), (b)) は、 GROUP BY a UNION ALL GROUP BY b と同等です。

追加の括弧がない GROUPING SETS(a, b) は、論理的には GROUPING SETS((a), (b)) と同等です。これは、両方とも列 a 用に1つ、 b 用に1つの2つの個別のグループ化セットを作成するためです。この式は、両方の列でグループ化する単一のグループ化セットが作成される GROUPING SETS((a, b)) とは大きく異なります。

構文

SELECT ...
FROM ...
[ ... ]
GROUP BY [ groupItem [ , groupItem [ , ... ] ] , ] GROUPING SETS ( groupSet [ , groupSet [ , ... ] ] )
[ ... ]
Copy

条件:

groupItem ::= { <column_alias> | <position> | <expr> }

groupSet ::= groupItem | ( groupItem [ , groupItem [ , ... ] ] )
Copy

パラメーター

column_alias

クエリブロックの SELECT リストに表示される列エイリアス。

position

SELECT リスト内の式の位置。

expr

現在のスコープ内のテーブルの式。

使用上の注意

  • Snowflakeでは、同じクエリブロックで最大128個のグループ化セットを使用できます。

  • 括弧付きの構文バリエーション：

    • GROUPING SETS(a, b) は、 GROUPING SETS((a), (b)) の簡略形です。両方とも、2つの個別のグループ化セットを作成します。列 a でグループ化するものと、列 b でグループ化するものです。

    • GROUPING SETS((a, b)) は、列 a と b の両方でグループ化する単一のグループ化セットを作成します（ GROUP BY a, b に類似）。

  • 通常のGROUP BY列をGROUPING SETSと組み合わせることができます。 GROUP BY x, GROUPING SETS(y, z) は、列 x でグループ化すると共に、 y および z に対する個別のグループ化と組み合わせます。

  • 出力には通常、いくつかの NULL 値が含まれます。GROUP BY GROUPING SETSは2つ以上の結果セットの結果をマージするためそれぞれが異なる基準でグループ化され、1つの結果セットの値が単一である列には、他の結果セットに対応する値が多数ある場合があります。たとえば、部門ごとにグループ化された従業員のセットを年功序列でグループ化して結合を実行すると、最高年功序列のセットのメンバーは必ずしも全員が同じ部門に所属するとは限らないため、 department_name の値がNULLに設定されます。このため、以下の例にはNULL値が含まれています。

こちらもご覧ください

例

これらの例では、災害を支援する訓練を受けた看護師に関する情報の表を使用しています。これらの看護師はすべて、看護師としての免許を持っています（たとえば、RNは「登録看護師」としての免許を持っています）。また、捜索救助、無線通信などの災害関連の専門分野の追加免許を持っています。この例では、免許の2つのカテゴリのみを単純化して使用しています。

  • 看護: RN （登録看護師）および LVN （認可職業看護師）。

  • アマチュア（「ハム」）無線: ハム無線の免許には、「Technician」、「General」、および「Amateur Extra」が含まれます。

次のコマンドは、テーブルを作成してロードします。

CREATE or replace TABLE nurses (
  ID INTEGER,
  full_name VARCHAR,
  medical_license VARCHAR,   -- LVN, RN, etc.
  radio_license VARCHAR      -- Technician, General, Amateur Extra
  )
  ;

INSERT INTO nurses
    (ID, full_name, medical_license, radio_license)
  VALUES
    (201, 'Thomas Leonard Vicente', 'LVN', 'Technician'),
    (202, 'Tamara Lolita VanZant', 'LVN', 'Technician'),
    (341, 'Georgeann Linda Vente', 'LVN', 'General'),
    (471, 'Andrea Renee Nouveau', 'RN', 'Amateur Extra')
    ;
Copy

このクエリはGROUP BY GROUPING SETSを使用します。

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license)
  ORDER BY 3 DESC NULLS FIRST;
Copy

最初の2行は、 RNs と LVNs （2種類の看護師免許）の数を示しています。これら2行の NULL 列の radio_license 値は意図的です。クエリでは、無線免許に関係なく、すべての LVNs （およびすべての RNs ）がグループ化されているため、その行にグループ化されたすべての radio_license または LVNs が必ず適用される各行の RNs 列に1つの値を表示できません。

次の3行は、各種のアマチュア無線免許（「Technician」、「General」、および「Amateur Extra」）を持つ看護師の数を示しています。これら3行それぞれの NULL の medical_license 値は意図的なものです。それは、これら各行のメンバーすべてに単一の医療免許が適用されるとは限らないためです。

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        3 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
+----------+-----------------+---------------+

次の例は、列で個別にグループ化する場合と、列で同時にグループ化する場合の違いを示しています。クエリは、 medical_license および radio_license の両方の組み合わせでグループ化します。

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS ((medical_license, radio_license))
  ORDER BY 3 DESC NULLS FIRST;
Copy

このクエリは、 medical_license および radio_license の各組み合わせがそのカウントとともに表示される行を生成します。前の例とは異なり、出力にNULL値はありません。これは、クエリが各列の個別のグループ化を作成するのではなく、両方の列を共にグループ化するためです。

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

次の例は、一部の列に NULL 値が含まれる場合を示しています。まだアマチュア無線の免許を持っていない3人の新しい看護師を追加することから始めます。

INSERT INTO nurses
    (ID, full_name, medical_license, radio_license)
  VALUES
    (101, 'Lily Vine', 'LVN', NULL),
    (102, 'Larry Vancouver', 'LVN', NULL),
    (172, 'Rhonda Nova', 'RN', NULL)
    ;
Copy

次に、前と同じクエリを実行します。

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license)
  ORDER BY 3 DESC NULLS FIRST;
Copy

なぜ両方の列にNULLがある行ができたのでしょうか。そして、すべての値がNULLである場合、COUNT(*) が3になるのはなぜですか。

答えは、その行の radio_license 列のNULLは、3人の看護師が無線免許を持っていないためです。（クエリ SELECT DISTINCT radio_license FROM nurses は4つの異なる値を返すようになりました。「Technician」、「General」、「Amateur Extra」、「NULL」です。

medical_licenses 列のNULL値は、以前のクエリ結果でNULL値が発生するのと同じ理由で発生します。この行でカウントされる看護師が保持する医療免許は異なるため、1つの値（ RN または LVN ）は、この行でカウントされるすべての看護師に絶対に適用されます。

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | RN              | NULL          |
|        5 | LVN             | NULL          |
|        3 | NULL            | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
+----------+-----------------+---------------+

次の例は、GROUPING SETSを持つ通常のGROUP BY列の組み合わせを示しています。このクエリは medical_license でグループ化し、各医療免許グループ内で、各 radio_license 値およびすべての無線免許の組み合わせに対して個別の集計を作成します。

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, GROUPING SETS (radio_license, ())
  ORDER BY 3 DESC NULLS FIRST;
Copy

各医療免許（LVNおよびRN）で、出力は次を示します。

  • 各特定 radio_license 値（技術者、一般、アマチュアエクストラ、または無線免許を持たない場合はNULL）でグループ化された行

  • 無線免許に関係なく、その医療免許を持つすべての看護師を表す、 radio_license 列にNULLがあるサマリー行

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | RN              | NULL          |
|        5 | LVN             | NULL          |
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

GROUPING SETS句なしでこの出力をGROUP BYの出力と比較できます。

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, radio_license
  ORDER BY 3 DESC NULLS FIRST;
Copy
+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        2 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | LVN             | Technician    |
|        1 | LVN             | General       |
|        1 | RN              | Amateur Extra |
+----------+-----------------+---------------+

GROUPING 関数の使用

GROUPING ユーティリティ関数は、各行を生成した集約レベルを識別するのに役立ちます。これは、グループ化操作の結果として得られるNULL値と実際のデータのNULL値を区別するのに特に役立ちます。

GROUPING関数は次を返します。

  • 指定した列でグループ化された行の場合、 0

  • 指定された列でグループ化されていない行の場合（集計によりNULLが表示される）、 1

この例では、、出力を明確にするためにクエリにGROUPING関数を追加しています。

SELECT
    COUNT(*),
    medical_license,
    radio_license,
    GROUPING(medical_license) AS grp_medical,
    GROUPING(radio_license) AS grp_radio
  FROM nurses
  GROUP BY GROUPING SETS (medical_license, radio_license);
Copy

grp_medical および grp_radio 列は、グループ化に使用された列を示します。

  • 行1～2：medical_license （ grp_medical=0 ）によってグループ化、 radio_license （ grp_radio=1 ）ではない

  • 行3～6：radio_license （ grp_radio=0 ）によってグループ化、 medical_license （ grp_medical=1 ）ではない

  • 行6：radio_license 内のNULL値は実際のデータ（ grp_radio=0 ）で、一方 medical_license のNULLは集計からのものです（ grp_medical=1 ）

+----------+-----------------+---------------+-------------+-----------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE | GRP_MEDICAL | GRP_RADIO |
|----------+-----------------+---------------+-------------+-----------|
|        2 | RN              | NULL          |           0 |         1 |
|        5 | LVN             | NULL          |           0 |         1 |
|        2 | NULL            | Technician    |           1 |         0 |
|        1 | NULL            | General       |           1 |         0 |
|        3 | NULL            | NULL          |           1 |         0 |
|        1 | NULL            | Amateur Extra |           1 |         0 |
+----------+-----------------+---------------+-------------+-----------+