カテゴリ:

クエリ構文

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 [ , ... ] ] )
[ ... ]

条件:

groupSet ::= { <column_alias> | <position> | <expr> }
列エイリアス

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

ポジション

SELECT リスト内の式の位置。

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

使用上の注意

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

  • The output typically contains some NULL values. Because GROUP BY ROLLUP merges the results of two or more result sets, each of which was grouped by different criteria, some columns that have a single value in one result set might have many corresponding values in the other result set. For example, if you do a UNION of a set of employees grouped by department with a set grouped by seniority, the members of the set with the greatest seniority are not necessarily all in the same department, so the value of department_name is set to NULL. The examples below include examples that contain NULLs for this reason.

These examples use a table of information about nurses who are trained to assist in disasters. All of these nurses have a license as nurses (e.g. an RN has a license as a "Registered Nurse"), and an additional license in a disaster-related specialty, such as search and rescue, radio communications, etc. This example simplifies and uses just two categories of licenses:

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

  • アマチュア(「ハム」)無線:ハム無線の免許には、「技術者」、「一般」、および「アマチュアその他」が含まれます。

テーブルを作成してロードするコマンドは次のとおりです。

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')
    ;

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

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

出力:

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

The next three rows show the number of nurses with each type of ham radio license ("Technician", "General", and "Amateur Extra"). The NULL value for MEDICAL_LICENSE in each of those three rows is deliberate because no single medical license necessarily applies to all members of each of those rows.

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

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

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

出力:

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

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

答えは、その行の RADIO_LICENSE 列の NULL は、3人の看護師が無線免許を持っていないために発生するからです。(「SELECT DISTINCT RADIO_LICENSE FROM nurses」は次の4つの異なる値を返すようになりました:「技術者」、「一般」、「アマチュアその他」、および「NULL」。

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 の出力と比較できます。

... GROUP BY medical_license, radio_license;

出力:

+----------+-----------------+---------------+
| COUNT(*) | MEDICAL_LICENSE | RADIO_LICENSE |
+==========+=================+===============+
|        2 |            LVN  | NULL          |
|        2 |            LVN  | TECHNICIAN    |
|        1 |            LVN  | GENERAL       |
|        1 |            RN   | AMATEUR EXTRA |
|        1 |            RN   | NULL          |
+----------+-----------------+---------------+