カテゴリ：

クエリ構文

# 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.
)
;

INSERT INTO nurses
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


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.

+----------+-----------------+---------------+
+==========+=================+===============+
|        3 |            LVN  | NULL          |
|        1 |            RN   | NULL          |
|        2 |            NULL | TECHNICIAN    |
|        1 |            NULL | GENERAL       |
|        1 |            NULL | AMATEUR EXTRA |
+----------+-----------------+---------------+


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


SELECT COUNT(*), medical_license, radio_license
FROM nurses


+----------+-----------------+---------------+
+==========+=================+================+
|        5 |            LVN  | NULL          |
|        2 |            RN   | NULL          |
|        2 |            NULL | TECHNICIAN    |
|        1 |            NULL | GENERAL       |
|        1 |            NULL | AMATEUR EXTRA |
|        3 |            NULL | NULL          |
+----------+-----------------+---------------+


... GROUP BY medical_license, radio_license;


+----------+-----------------+---------------+