SQL ROLLUP

概要：このチュートリアルでは、SQL ROLLUP を使用して複数のグルーピングセットを生成する方法を学習します。

SQL ROLLUP入門

ROLLUP は GROUP BY 句の拡張機能です。 ROLLUP オプションを使用すると、小計を表す追加の行（一般的にスーパー集計行と呼ばれます）と、総計行を含めることができます。 ROLLUP オプションを使用することで、単一のクエリを使用して複数のグルーピングセットを生成できます。

グルーピングセットとは、グループ化の基準となる列のセットです。たとえば、倉庫別に在庫を返すクエリでは、グルーピングセットは（倉庫）です。

SELECT
    warehouse, 
    SUM (quantity) qty
FROM
    inventory
GROUP BY
    warehouse;Code language: SQL (Structured Query Language) (sql)

GROUPING SETSの詳細については、グルーピングセットのチュートリアルをご覧ください。

以下は、SQL ROLLUP の基本構文を示しています。

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table
GROUP BY ROLLUP (c1, c2);
Code language: SQL (Structured Query Language) (sql)

ROLLUP は、入力列間に階層があると想定します。たとえば、入力列が（c1、c2）の場合、階層は c1 > c2 です。 ROLLUP は、この階層を考慮して意味のあるすべてのグルーピングセットを生成します。そのため、レポート作成の目的で小計と総計を生成するために ROLLUP をよく使用します。

上記の構文では、ROLLUP(c1,c2) は次の3つのグルーピングセットを生成します。

(c1,c2)
(c1)
()Code language: SQL (Structured Query Language) (sql)

この構文は、Oracle、Microsoft SQL Server、およびPostgreSQLでサポートされています。ただし、MySQLでは以下に示すように構文が少し異なります。

SELECT 
    c1, c2, aggregate_function(c3)
FROM
    table_name
GROUP BY c1, c2 WITH ROLLUP;
Code language: SQL (Structured Query Language) (sql)

SQL ROLLUPの例

デモンストレーションには、GROUPING SETS チュートリアルで設定した inventory テーブルを使用します。

1つの列を使用したSQL ROLLUPの例

次のステートメントでは、GROUP BY 句と SUM() 関数を使用して、倉庫ごとの総在庫を求めています。

SELECT 
    warehouse, SUM(quantity)
FROM
    inventory
GROUP BY warehouse;
Code language: SQL (Structured Query Language) (sql)

すべての倉庫の製品の合計を取得するには、次のように GROUP BY 句に ROLLUP を追加します。

SELECT 
    warehouse, SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse);
Code language: SQL (Structured Query Language) (sql)

結果でわかるように、warehouse 列のNULL値は、総計のスーパー集計行を指定します。この例では、ROLLUP オプションにより、クエリはすべての倉庫の製品の合計を示す別の行を生成します。

出力をより読みやすくするために、COALESCE() 関数を使用して、NULL値を すべての倉庫 に置き換えることができます。

SELECT 
    COALESCE(warehouse, 'All warehouses') AS warehouse,
    SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse);
Code language: SQL (Structured Query Language) (sql)

複数の列を使用したSQL ROLLUPの例

次のステートメントは、倉庫と製品別に在庫を計算します。

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, product;
Code language: SQL (Structured Query Language) (sql)

GROUP BY 句に ROLLUP を追加してみましょう。

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY ROLLUP (warehouse , product);
Code language: SQL (Structured Query Language) (sql)

出力は、1つではなく2つの分析レベルでの要約情報で構成されていることに注意してください。

部分的なロールアップを使用したSQL ROLLUPの例

次の例に示すように、ROLLUP を使用して、計算される小計の数を減らす部分的なロールアップを実行できます。

SELECT 
    warehouse, product, SUM(quantity)
FROM
    inventory
GROUP BY warehouse, ROLLUP (product);
Code language: SQL (Structured Query Language) (sql)

この例では、ROLLUP は warehouse 列ではなく、product 列のスーパー集計のみを作成します。

このチュートリアルでは、SQL ROLLUP を使用して、単一のクエリで複数レベルの分析を実行する方法を学習しました。