SQL Academy · レッスン

すべての組み合わせのためのCUBE

Q: 「すべての組み合わせのためのCUBE」で何を学びますか？

すべてのグループ化の組み合わせを一度に取得します。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

すべてのグループ化の組み合わせを一度に取得します。

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

CUBEとは

SQLのCUBE拡張機能は、列のリストから考えられるすべてのグループ化の組み合わせを生成します。ROLLUPが階層を作成するのに対し、CUBEは総計を含む、すべての小計の組み合わせを生成します。

これは、「これらの列から計算できる小計をすべて表示してください」と指示するようなものです。

テーブルの準備

年、地域、商品カテゴリごとの売上を記録するsalesテーブルを使用します。このような多次元データでこそ、CUBEの力が発揮されます。

CREATE TABLE sales (
  year     INT,
  region   VARCHAR(20),
  category VARCHAR(20),
  revenue  NUMERIC(10,2)
);

INSERT INTO sales VALUES
  (2023, 'North', 'Electronics', 12000),
  (2023, 'North', 'Clothing',     8000),
  (2023, 'South', 'Electronics',  9500),
  (2023, 'South', 'Clothing',     6000),
  (2024, 'North', 'Electronics', 14000),
  (2024, 'North', 'Clothing',     9500),
  (2024, 'South', 'Electronics', 11000),
  (2024, 'South', 'Clothing',     7200);

初めてのCUBEクエリ

構文は単純です。GROUP BYをGROUP BY CUBE(...)に置き換え、列をその中に列挙します。SQLが、それらの列の組み合わせをすべてグループ化セットとして生成します。

SELECT
  year,
  region,
  category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY year, region, category;

CUBEはいくつのグループ化を生成するか

n個の列に対して、CUBEは列リストの部分集合ごとに1つ、つまり2ⁿ個のグループ化セットを生成します。空のセット（総計）も含まれます。

3列（year、region、category）の場合は、2³ = 8個のグループ化セットです。各列単独、2列の各組み合わせ、3列すべての組み合わせ、そして列を指定しない組み合わせが含まれます。

-- 3 columns => 8 grouping sets:
-- (year, region, category)
-- (year, region)
-- (year, category)
-- (region, category)
-- (year)
-- (region)
-- (category)
-- () <- grand total
SELECT COUNT(*) AS row_count
FROM (
  SELECT year, region, category, SUM(revenue)
  FROM sales
  GROUP BY CUBE(year, region, category)
) sub;

NULLでROLLUP対象のディメンションを示す

ROLLUPと同様に、CUBEは列が集計対象になったことをNULLで示します。結果の行でregionがNULLの場合、その行は残りの列の組み合わせにおけるすべての地域を対象とした集計です。

GROUPING(col)を使うと、実際のNULL値と集計を示すマーカーを区別できます。

SELECT
  GROUPING(year)     AS g_year,
  GROUPING(region)   AS g_region,
  GROUPING(category) AS g_category,
  year,
  region,
  category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY g_year, g_region, g_category;

COALESCEでNULLを読みやすくする

レポートの出力を読みやすくするには、各グループ化列をCOALESCEで囲み、集計によるNULLを'ALL'のような説明的なラベルに置き換えます。

SELECT
  COALESCE(CAST(year AS VARCHAR), 'ALL YEARS')     AS year,
  COALESCE(region,   'ALL REGIONS')                AS region,
  COALESCE(category, 'ALL CATEGORIES')             AS category,
  SUM(revenue)                                     AS total_revenue
FROM sales
GROUP BY CUBE(year, region, category)
ORDER BY year, region, category;

CUBEとROLLUP — 主な違い

ROLLUP(a, b, c)は、1つの階層に沿った小計だけを作成します。(a,b,c)、(a,b)、(a)、()の順です。列の左から右への順序が反映されます。

CUBE(a, b, c)は、(a,c)や(b)単独など、ROLLUPでは完全に省略される組み合わせも含め、すべての部分集合を作成します。あらかじめ決められた階層を使わず、完全な多次元分析が必要な場合はCUBEを使用します。

-- ROLLUP: 4 grouping sets
SELECT year, region, SUM(revenue)
FROM sales
GROUP BY ROLLUP(year, region);

-- CUBE: 4 grouping sets for 2 columns (same count here)
-- but adds the (region) subtotal that ROLLUP omits
SELECT year, region, SUM(revenue)
FROM sales
GROUP BY CUBE(year, region);

部分的なCUBE

通常のGROUP BY列と、CUBEのサブリストを組み合わせることができます。CUBE(...)の外側に記述した列はすべてのグループ化セットに常に含まれ、CUBEの完全な組み合わせ処理が適用されるのは内側の列だけです。

-- year is fixed; CUBE only over region and category
SELECT
  year,
  COALESCE(region,   'ALL REGIONS')    AS region,
  COALESCE(category, 'ALL CATEGORIES') AS category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY year, CUBE(region, category)
ORDER BY year, region, category;

HAVING を使用した CUBE 結果の絞り込み

HAVING は、通常の GROUP BY とまったく同じように CUBE の出力に対して機能します。集計値がしきい値を満たさないグループ化行を除外できます。たとえば、総売上が最低額を超える行だけを残せます。

SELECT
  COALESCE(region,   'ALL REGIONS')    AS region,
  COALESCE(category, 'ALL CATEGORIES') AS category,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY CUBE(region, category)
HAVING SUM(revenue) > 15000
ORDER BY total_revenue DESC;

CUBE とウィンドウ関数の組み合わせ

CUBE クエリを CTE でラップし、その結果にウィンドウ関数を適用して、結果内の行を順位付けしたり比較したりできます。これは、経営層向けダッシュボードを構築するための強力なパターンです。

WITH cube_result AS (
  SELECT
    COALESCE(region,   'ALL') AS region,
    COALESCE(category, 'ALL') AS category,
    SUM(revenue) AS total_revenue
  FROM sales
  GROUP BY CUBE(region, category)
)
SELECT
  region,
  category,
  total_revenue,
  RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
FROM cube_result
ORDER BY revenue_rank;

CUBE を選ぶタイミング

CUBE は、次のような場合に適しています。

アドホックレポートや OLAP 形式のレポートで、すべての小計の組み合わせが必要な場合。
グループ化する列の間に自然な階層がない場合。
アナリストが自由にデータを切り分けられるようにしたい場合。

列数が多い場合は CUBE を避けてください。4 列ではすでに 16 個、5 列では 32 個のグルーピングセットが生成されます。結果セットを管理しやすくするには、ROLLUP または明示的な GROUPING SETS を使用してください。

理解度チェック

GROUP BY CUBE(a, b, c, d) は、異なるグルーピングセットをいくつ生成しますか。

レッスンのまとめ

このレッスンでは、CUBE が列リストから考えられるすべてのグルーピングセットの組み合わせを生成する仕組みを学びました。これにより、CUBE は多次元レポートに適しています。

主なポイント：

GROUP BY CUBE(a, b, c) は 2ⁿ 個のグルーピングセットを生成します。
結果列の NULL は、そのディメンションが集計対象になったことを示します。GROUPING() を使用して検出できます。
COALESCE を使用すると、集計による NULL を意味のあるラベルに変換できます。
部分的な CUBE（例：GROUP BY year, CUBE(region, category)）では、一部の列を固定し、残りの列にだけ CUBE を適用します。
完全なアドホック分析には CUBE を使用し、階層が決まっている場合や列数が多い場合は ROLLUP または明示的な GROUPING SETS を使用してください。

よくある質問

「すべての組み合わせのためのCUBE」レッスンは無料ですか？

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

「すべての組み合わせのためのCUBE」で何を学びますか？

すべてのグループ化の組み合わせを一度に取得します。ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

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

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

「すべての組み合わせのためのCUBE」レッスンにはどのくらい時間がかかりますか？

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

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

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

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

← SQL Academyに戻る