カテゴリ：: クエリ構文

GROUP BY GROUPING SETS¶

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

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

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

構文¶

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

Copy

条件:

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

Copy

column_alias: クエリブロックの SELECT リストに表示される列エイリアス。
position: SELECT リスト内の式の位置。
expr: 現在のスコープ内のテーブルの式。

使用上の注意¶

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

例¶

これらの例では、災害を支援する訓練を受けた看護師に関する情報の表を使用しています。これらの看護師はすべて、看護師としての免許を持っています（例えば、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);
Copy
出力：

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

次の3行は、各種のアマチュア無線免許（「Technician」、「General」、および「Amateur Extra」）を持つ看護師の数を示しています。これら3行それぞれの MEDICAL_LICENSE の NULL 値は意図的なものです。それは、これら各行のメンバーすべてに単一の医療免許が適用されるとは限らないためです。
+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        3 | LVN             | NULL          |
|        1 | RN              | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | 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);
Copy
出力：

最初の5行は、前のクエリと同じです。

最後の行については最初混乱するかもしれません。両方の列に NULL がある行が存在するのはなぜですか？そして、すべての値が NULL である場合、 COUNT（*）が3になるのはなぜですか？

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

MEDICAL_LICENSES 列の NULL は、以前のクエリ結果で NULL 値が発生するのと同じ理由で発生します。この行でカウントされる看護師の MEDICAL_LICENSES は異なるため、1つの値（「RN」または「LVN」）は、この行でカウントされるすべての看護師に絶対に適用されます。
+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
|----------+-----------------+---------------|
|        5 | LVN             | NULL          |
|        2 | RN              | NULL          |
|        2 | NULL            | Technician    |
|        1 | NULL            | General       |
|        1 | NULL            | Amateur Extra |
|        3 | NULL            | NULL          |
+----------+-----------------+---------------+

必要に応じて、 GROUPING SETS 句なしでこの出力を GROUP BY の出力と比較できます。

SELECT COUNT(*), medical_license, radio_license
  FROM nurses
  GROUP BY medical_license, radio_license;

Copy

出力：

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