SQL Academy · レッスン

GROUPING SETSを理解する

Q: 「GROUPING SETSを理解する」で何を学びますか？

必要なグループ化だけを正確に選びます。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

必要なグループ化だけを正確に選びます。

「GROUPING SETSを理解する」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン4/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

GROUPING SETS とは

GROUP BY 句を記述すると、グループ化する列の組み合わせを 1 つ定義します。しかし、クエリを何度も実行して UNION ALL で結合することなく、1 つのクエリで複数の異なるグループ化が必要になる場合があります。

GROUPING SETS を使用すると、必要なグループ化を正確に指定し、データを 1 回走査するだけで処理できます。リスト内の各セットが、結果に独自の集計行を生成します。

サンプルテーブル: sales

このレッスンでは、sales テーブルを一貫して使用します。このテーブルには、地域、商品カテゴリ、金額ごとの取引が記録されています。

コードを実行してテーブルを作成・入力し、すべての例を実際に確認できるようにしてください。

CREATE TABLE sales (
  region   TEXT,
  category TEXT,
  amount   NUMERIC
);

INSERT INTO sales VALUES
  ('East',  'Electronics', 500),
  ('East',  'Clothing',    200),
  ('West',  'Electronics', 300),
  ('West',  'Clothing',    400),
  ('North', 'Electronics', 150),
  ('North', 'Clothing',    250);

従来の方法: UNION ALL

GROUPING SETS が存在しなかった頃は、複数のレベルの合計を取得するために、個別のクエリを記述して UNION ALL で連結する必要がありました。この方法は繰り返しが多く、読みにくいうえ、テーブルを複数回走査します。

次の例では、3 つの個別の SELECT 文を使用して、地域ごとの合計とカテゴリごとの合計を返します。

SELECT region, NULL AS category, SUM(amount) AS total
FROM sales
GROUP BY region

UNION ALL

SELECT NULL, category, SUM(amount)
FROM sales
GROUP BY category

UNION ALL

SELECT NULL, NULL, SUM(amount)
FROM sales;

GROUPING SETS の構文

GROUPING SETS 句は、通常の GROUP BY の内部に記述するか、通常の GROUP BY の代わりに使用します。必要な各グループ化を、括弧で囲んだ列リストとして指定します。空のセット () は、グループ化をまったく行わない総合計を意味します。

この 1 つのクエリは、先ほどの 3 つの部分からなる UNION ALL と同じ処理を、より簡潔に、しかもテーブルを 1 回走査するだけで実行します。

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region),
  (category),
  ()
);

結果の読み方

出力の各行は、必ず 1 つのグルーピングセットに属します。列が現在のグルーピングセットに含まれていない場合、その値は NULL になります。

region = 'East' かつ category = NULL の行は、(region) セットに属します。
region = NULL かつ category = 'Electronics' の行は、(category) セットに属します。
両方が NULL の行は、() セットによる総合計です。

これらの NULL は構造を表す値です。そのディメンションの「すべての値」を意味しており、データの欠損を表しているわけではありません。

複数列のグルーピングセット

各グルーピングセットには、複数の列を含めることができます。(region, category) セットは、通常の GROUP BY region, category と同じように、両方の列を組み合わせてグループ化します。

これを個別のセットや総合計と組み合わせることで、1 つのクエリから 4 つのレベルの集計結果を取得できます。

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  (category),
  ()
);

GROUPING() 関数

NULL は「この列はグループ化されていない」ことと、実際の欠損データのどちらも意味する可能性があります。そのため SQL には、両者を区別するための GROUPING() 関数が用意されています。

GROUPING(col) は、その列が現在のグルーピングセットから省略されている場合（つまり NULL が構造を表す場合）に 1 を返し、その列がグループ化に含まれている場合（または実際の NULL 値である場合）に 0 を返します。

SELECT
  region,
  category,
  SUM(amount)      AS total,
  GROUPING(region) AS is_region_total,
  GROUPING(category) AS is_category_total
FROM sales
GROUP BY GROUPING SETS (
  (region),
  (category),
  ()
);

NULL ラベルの置き換え

よく使われる方法として、構造を表す NULL を説明的なラベルに置き換えると、レポートが読みやすくなります。CASE WHEN GROUPING(...) = 1 THEN 'All ...' ELSE col END を使用すると、集計行に対してのみラベルを置き換えられます。

SELECT
  CASE WHEN GROUPING(region)   = 1 THEN 'All Regions'    ELSE region   END AS region,
  CASE WHEN GROUPING(category) = 1 THEN 'All Categories' ELSE category END AS category,
  SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region),
  (category),
  ()
);

GROUPING SETS と ROLLUP の比較

ROLLUP(a, b) は、(a, b), (a), () というグルーピングセットに展開される短縮記法です。常に階層に沿った小計と総合計を追加します。

GROUPING SETS では、表示する組み合わせを正確に選択できるため、完全な制御が可能です。階層全体が必要なければ、一部のレベルを省略できます。次の 2 つのクエリは同じ結果を生成します。

-- Using ROLLUP
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(region, category);

-- Equivalent explicit GROUPING SETS
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  ()
);

GROUPING SETS と CUBE の比較

CUBE(a, b) は、指定した列について考えられるすべての組み合わせ、つまり (a, b), (a), (b), () を生成します。ディメンションをまたいだ分析に便利ですが、多くの行を生成します。

GROUPING SETS を使用すると、必要な組み合わせだけを選択できるため、結果を絞り込み、クエリを高速に保てます。

-- CUBE produces 4 grouping sets for 2 columns
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY CUBE(region, category);

-- GROUPING SETS: omit the (category)-only set if not needed
SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  ()
);

実践的なユースケース: 売上ダッシュボード

実際のダッシュボードでは、行レベルの詳細、部門ごとの小計、総合計をすべて 1 つの結果セットに含めることがよくあります。GROUPING SETS を使用すれば、アプリケーション側で集計したり、複数回のラウンドトリップを行ったりすることなく、これを簡単に実現できます。

このクエリは、地域とカテゴリごとの詳細行、地域だけの小計、1 行の総合計を返します。総合計が最後に表示されるよう、すべての行が並べ替えられています。

SELECT
  COALESCE(region,   'TOTAL')      AS region,
  COALESCE(category, 'ALL')        AS category,
  SUM(amount)                      AS total
FROM sales
GROUP BY GROUPING SETS (
  (region, category),
  (region),
  ()
)
ORDER BY
  GROUPING(region),
  region NULLS LAST,
  GROUPING(category),
  category NULLS LAST;

理解度チェック

GROUPING SETS の理解度を確認しましょう。

レッスンのまとめ

GROUPING SETS について、次のことを学びました。

GROUPING SETS を使用すると、1 つのクエリで複数のグループ化の組み合わせを定義でき、繰り返しの多い UNION ALL パターンを置き換えられます。
各セットは、GROUP BY GROUPING SETS (...) 内で括弧に囲んだ列リストとして指定します。空のセット () は総合計を生成します。
現在のセットに含まれない列は、その行では NULL になります。GROUPING(col) を使用して、これらの構造を表す NULL を検出できます。
ROLLUP と CUBE は一般的なパターンに使える便利な短縮記法ですが、GROUPING SETS を使用すると、含めるグループ化を完全かつ正確に制御できます。
COALESCE または CASE WHEN GROUPING(...) と組み合わせると、レポート内の NULL を読みやすいラベルに置き換えられます。

よくある質問

「GROUPING SETSを理解する」レッスンは無料ですか？

はい。「GROUPING SETSを理解する」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「GROUPING SETSを理解する」で何を学びますか？

必要なグループ化だけを正確に選びます。ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン4/4です。

「GROUPING SETSを理解する」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Academyに戻る