SQL Academy · レッスン

単一のGROUP BYを超えて

Q: 「単一のGROUP BYを超えて」で何を学びますか？

複数のレベルで集約します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

複数のレベルで集約します。

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

単一のGROUP BYの限界

標準的なGROUP BY句では、特定の1つの階層で行を集計できます。たとえば、地域ごとの売上合計です。しかし、同じクエリで商品カテゴリごとの合計と総計も取得したい場合はどうでしょうか。

クエリを3回繰り返してUNION ALLを使う方法でも実現できますが、冗長で処理も遅くなります。SQLには、この問題を1回の処理でエレガントに解決するGROUPING SETS、ROLLUP、CUBEという3つの強力な拡張機能があります。

SELECT region, SUM(amount) AS total_sales
FROM sales
GROUP BY region;

サンプルテーブルの準備

このレッスンでは、各売上を地域、カテゴリ、金額とともに記録するシンプルなsalesテーブルを使用します。これから実行するすべてのクエリを文脈に沿って理解できるように、テーブルを作成してデータを入力しましょう。

CREATE TABLE sales (
  id       SERIAL PRIMARY KEY,
  region   TEXT,
  category TEXT,
  amount   NUMERIC
);

INSERT INTO sales (region, category, amount) VALUES
  ('North', 'Electronics', 1200),
  ('North', 'Clothing',     800),
  ('South', 'Electronics',  950),
  ('South', 'Clothing',     600),
  ('East',  'Electronics', 1100),
  ('East',  'Clothing',     750);

GROUPING SETSとは

GROUPING SETSを使うと、1つのGROUP BY句の中で、複数の独立したグループ化レベルを定義できます。リスト内の各セットは、個別のクエリを記述してUNION ALLで結合した場合と同じように、それぞれ独自の行グループを生成します。

構文は次のとおりです。GROUP BY GROUPING SETS ( (col1, col2), (col1), () )。空のセット()は、すべての行を対象とした総計を表します。

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

GROUPING SETSの出力を読み取る

GROUPING SETSクエリを実行すると、異なるグループ化レベルの行がまとめて並べられます。特定のセットに含まれていない列は、その行ではNULLとして表示されます。

たとえば、(region)セットに属する行では、category列がNULLになります。これは、その地域のすべてのカテゴリを対象とした合計であることを示します。総計の行（空のセット）では、regionとcategoryの両方がNULLになります。

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

ROLLUPの概要

ROLLUPは、よく使われるグループ化セットの階層を簡潔に記述するための機能です。列(A, B)に対してROLLUP(A, B)を指定すると、(A, B)、(A)、()のセットが自動的に生成されます。

これは、年から月、日へ、または地域からカテゴリへといった階層構造を持つデータに最適です。生成されるセットの数は、列の数をnとすると常にn + 1です。

-- ROLLUP(region, category) is equivalent to:
-- GROUPING SETS ( (region, category), (region), () )

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

3つのレベルでROLLUPを使う

ROLLUPに3つ目の列を追加すると、階層がさらに1つ深くなります。ROLLUP(A, B, C)は、(A, B, C)、(A, B)、(A)、()の4つのセットを生成します。

以下の例では、yearが最上位のレベルで、categoryが最も詳細なレベルです。クエリは、階層の各段階で小計を生成し、さらに総計の行を1行生成します。

SELECT
  year,
  region,
  category,
  SUM(amount) AS total
FROM (
  VALUES
    (2024, 'North', 'Electronics', 1200),
    (2024, 'North', 'Clothing',     800),
    (2024, 'South', 'Electronics',  950),
    (2025, 'North', 'Electronics', 1400),
    (2025, 'South', 'Clothing',     700)
) AS t(year, region, category, amount)
GROUP BY ROLLUP(year, region, category)
ORDER BY year NULLS LAST, region NULLS LAST, category NULLS LAST;

CUBEの概要

CUBEは、ROLLUPよりもさらに広範囲の集計を行います。n個の列を指定すると、総計を含む、グループ化セットの2^n通りの組み合わせすべてを生成します。

CUBE(region, category)では、(region, category)、(region)、(category)、()の4つのセットが生成されます。単一の階層だけでなく、あらゆる方向から見た切り口ごとの合計が必要な場合に便利です。

-- CUBE(region, category) produces:
-- GROUPING SETS ( (region,category), (region), (category), () )

SELECT region, category, SUM(amount) AS total
FROM sales
GROUP BY CUBE(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

CUBEとROLLUP — 使い分け

ディメンションに自然な階層があるかどうかに応じて選択します。

列が階層構造を持つ場合（国から都市、店舗へなど）はROLLUPを使用します。小計は1つの経路に沿ってのみ集計されます。
列が独立したディメンション（地域とカテゴリなど）で、考えられるすべての切り口が必要な場合はCUBEを使用します。
細かく指定する必要があり、ROLLUPやCUBEでは要件に合わない場合はGROUPING SETSを使用します。

GROUPING()関数

NULLは、実際の欠損値とグループ化のプレースホルダーのどちらも意味する可能性があります。そのためSQLにはGROUPING()関数が用意されています。この関数は、列が上位集計（プレースホルダー）行の一部である場合は1を、実際にその列でグループ化されている場合は0を返します。

これにより、実際のNULLの地域と、すべての地域を対象とした小計行を区別できます。

SELECT
  region,
  category,
  SUM(amount)        AS total,
  GROUPING(region)   AS is_region_subtotal,
  GROUPING(category) AS is_category_subtotal
FROM sales
GROUP BY CUBE(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

GROUPING()で行にラベルを付ける

GROUPING()とCASE式を組み合わせて、値がそのままのNULLではなく、人間が読みやすいラベルを生成するのはよくあるパターンです。これにより、ROLLUPやCUBEクエリの出力をレポートで読みやすくできます。

SELECT
  CASE GROUPING(region)
    WHEN 1 THEN 'Grand Total'
    ELSE region
  END AS region_label,
  CASE GROUPING(category)
    WHEN 1 THEN 'All Categories'
    ELSE category
  END AS category_label,
  SUM(amount) AS total
FROM sales
GROUP BY ROLLUP(region, category)
ORDER BY GROUPING(region), region, GROUPING(category), category;

固定列とROLLUPを組み合わせる

同じ句の中で、通常のGROUP BY列とROLLUPまたはCUBEを組み合わせることができます。ROLLUP(...)の外側に記述した列は、すべてのグループ化セットに常に含まれ、ROLLUPの対象にはなりません。

以下のクエリでは、yearが固定されたグループ化列で、regionとcategoryがROLLUPに含まれます。そのため、すべての年をまたいだ小計ではなく、年ごとの小計が得られます。

SELECT
  2024           AS year,
  region,
  category,
  SUM(amount)    AS total
FROM sales
GROUP BY 2024, ROLLUP(region, category)
ORDER BY region NULLS LAST, category NULLS LAST;

理解度チェック

GROUPING SETS、ROLLUP、CUBEについての理解度を確認しましょう。

振り返り — 1つのGROUP BYを超えて

このレッスンでは、1つのクエリで複数の集計レベルを生成できる、強力なSQL拡張機能を3つ学びました。

GROUPING SETS — 完全に手動で制御できます。必要な組み合わせを正確に列挙します。
ROLLUP — 階層構造に最適です。最も詳細なレベルから総計まで順に集計します。
CUBE — 指定したディメンションで考えられるすべての切り口を生成します。

また、GROUPING()によって実際のNULL値と、上位集計のプレースホルダー行を区別できることや、CASE GROUPING(...)パターンによって、すっきりと読みやすいレポート出力を作成できることも学びました。

多次元の集計が必要な場合、これらのツールは欠かせません。ピボット形式のレポート、ダッシュボード、データウェアハウスのクエリでは、いずれもこれらが頻繁に利用されます。

よくある質問

「単一のGROUP BYを超えて」レッスンは無料ですか？

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

「単一のGROUP BYを超えて」で何を学びますか？

複数のレベルで集約します。ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「単一のGROUP BYを超えて」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← SQL Academyに戻る