集計要件の多階層化と従来の課題

データ分析の現場では、単一の粒度だけでなく、複数の階層レベルで集計を行うニーズが頻繁に発生します。例えば、小売業のトランザクションデータ（トランザクションテーブル）を対象に、「全店」「地域（大区）」「都道府県（省）」「市区町村（市）」「個別店舗」というそれぞれのレベルで、特定月における売上件数を算出する場合を想定します。

古典的なアプローチでは、各階層ごとに個別のSQLクエリを作成し、その結果をExcel等で結合していました。あるいは、SQL内で`UNION ALL`を使用して縦方向に結果を結合する方法も一般的です。以下に、`UNION ALL`を用いた場合の実装例を示します。ここではテーブル名をsales_log、列名をregion（地域）、prefecture（都道府県）、city（市区町村）、store_id（店舗ID）とし、対象期間を2023年1月とします。

SELECT NULL, NULL, NULL, NULL, COUNT(transaction_id) AS total_sales
FROM sales_log
WHERE tx_date BETWEEN '2023-01-01' AND '2023-01-31'

UNION ALL

SELECT region, NULL, NULL, NULL, COUNT(transaction_id) AS total_sales
FROM sales_log
WHERE tx_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY region

UNION ALL

SELECT region, prefecture, NULL, NULL, COUNT(transaction_id) AS total_sales
FROM sales_log
WHERE tx_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY region, prefecture

UNION ALL

SELECT region, prefecture, city, NULL, COUNT(transaction_id) AS total_sales
FROM sales_log
WHERE tx_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY region, prefecture, city

UNION ALL

SELECT region, prefecture, city, store_id, COUNT(transaction_id) AS total_sales
FROM sales_log
WHERE tx_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY region, prefecture, city, store_id;

この方法で目的は達成できますが、クエリが冗長になり、可読性や保守性が低下します。また、`UNION ALL`を使用するため、列数を合わせるために`NULL`を明示的に指定する必要があります。これを解決するために、SQLの標準機能や主要DBMSで実装されている「拡張GROUP BY」機能を活用します。

GROUPING SETSによる柔軟な集計定義

GROUPING SETSを使用すると、1つのクエリ内で任意のグルーピング組み合わせ（集計単位）を定義できます。先ほどのUNION ALLによるクエリは、以下のように大幅に簡潔に記述できます。

SELECT
    region,
    prefecture,
    city,
    store_id,
    COUNT(transaction_id) AS total_sales,
    GROUPING_ID(region, prefecture, city, store_id) AS grp_id
FROM
    sales_log
WHERE
    tx_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY
    region,
    prefecture,
    city,
    store_id
GROUPING SETS (
    (),                                    -- Grand Total (全体)
    (region),                              -- 地域別
    (region, prefecture),                  -- 都道府県別
    (region, prefecture, city),            -- 市区町村別
    (region, prefecture, city, store_id)   -- 店舗別
)
ORDER BY
    grp_id;

このクエリでは、GROUP BY句の後に集計対象の全列を列挙し、GROUPING SETS内で実際にグルーピングしたい組み合わせを括弧で定義します。()は集計キーを持たない全体の合計（Grand Total）を表します。GROUPING_ID関数は、どのレベルの集計行であるかを示すビットマスク値を返し、結果のフィルタリングや識別に役立ちます。

CUBEによる全組み合わせ集計

CUBEは、指定された列の「全ての可能な組み合わせ」で集計を行う機能です。例えば、regionとprefectureの2列に対してCUBEを適用すると、以下の4つのパターンで集計が生成されます。

1. region, prefecture（地域×都道府県）
2. region（地域のみ）
3. prefecture（都道府県のみ）
4. ()（全体）

具体的なクエリは以下の通りです。

SELECT
    region,
    prefecture,
    COUNT(transaction_id) AS total_sales,
    GROUPING_ID(region, prefecture) AS grp_id
FROM
    sales_log
WHERE
    tx_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY
    region,
    prefecture
WITH CUBE
ORDER BY
    grp_id;

WITH CUBEを使用することで、多次元的なクロス集計表を作成する際に非常に有用です。結果として得られる行数は、列数$n$に対して$2^n$個の組み合わせ（および基底データ）になります。

ROLLUPによる階層的集計

ROLLUPは、指定された列を階層的に扱い、「小計」および「合計」を生成する機能です。CUBEが全ての組み合わせを作るのに対し、ROLLUPは左側の列から順に階層を掘り下げていくような集計を行います。

regionとprefectureに対してROLLUPを使用した場合、生成される集計レベルは以下の3つです。

1. region, prefecture（各地域内の都道府県ごとの集計）
2. region（地域ごとの小計）
3. ()（全体の総計）

具体的なクエリは以下のようになります。

SELECT
    region,
    prefecture,
    COUNT(transaction_id) AS total_sales,
    GROUPING_ID(region, prefecture) AS grp_id
FROM
    sales_log
WHERE
    tx_date BETWEEN '2023-01-01' AND '2023-01-31'
GROUP BY
    region,
    prefecture
WITH ROLLUP
ORDER BY
    grp_id;

CUBEとの違いは、(prefecture)のみの集計（都道府県全体の集計）が含まれない点です。ROLLUPは、最も左側の列を親階層とみなした階層構造のデータに対して、上位レベルの集計値を効率的に算出したい場合に適しています。