SQL CUBE

概要: このチュートリアルでは、クエリ出力のサブトータルを生成するために SQL のCUBE を使用する方法を学びます。

SQL CUBE の概要

ROLLUP と同様、CUBE は GROUP BY 句の拡張です。CUBE を使用すると、ROLLUP 拡張のようにサブトータルを生成できます。さらに、CUBE 拡張は GROUP BY 句で指定されたすべてのグループ化列の組み合わせのサブトータルを生成します。

CUBE 拡張の構文を以下に示します

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

この構文では、CUBE に 2 つの列を指定します。このステートメントは 2 つのサブトータル組み合わせを作成します。一般的に、CUBE に n 個の列がリストされている場合、ステートメントは 2ⁿ 個のサブトータル組み合わせを作成します。

SQL CUBE の例

ROLLUP チュートリアルで作成された inventory テーブルを再利用します。

1 列を使用する SQL CUBE の例

次のステートメントは SUM() 関数と GROUP BY 句を使用して、すべての倉庫の合計在庫を検索します。

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

すべての倉庫の合計在庫を知る必要がある場合は、次のように GROUP BY 句に CUBE 拡張を使用します。

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

この例では、CUBE 拡張は倉庫列に null 値を持つ合計在庫行を追加します。この効果は、ROLLUP 関数と同じです。出力をより読みやすくするために、次に示すように COALESCE() 関数を使用するとよいでしょう。

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

複数列を使用する SQL CUBE の例

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

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

CUBE 関数を使用すると、クエリは 4 つのサブトータルを作成します。

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

出力にわかるように、4 つのサブトータル行があります。

• 3 行目と 6 行目は、サンフランシスコとサンノゼ倉庫内のすべての製品の合計在庫を示しています。product 列の値は null です。
• 7 行目と 8 行目は、すべての倉庫内の製品 Samsung と iPhone 別の合計在庫を表示しています。そのため、倉庫 列の値は null です。

最後の列は総計であり、すべての倉庫の合計在庫を示しています。

次のステートメントは COALESCE() 関数を使用して null 値をより有意義なデータに置き換えます。

SELECT
   COALESCE(warehouse, '...All Warehouses') warehouse,
  COALESCE(product, '...All Products') product,
   SUM(quantity) 
FROM
   inventory
GROUP BY
   CUBE(warehouse,product)
ORDER BY
   warehouse,
   product; 
Code language: SQL (Structured Query Language) (sql)

クロス集計レポートの作成

次のクエリは、CUBE 拡張を使用して、サンプルデータベース内の employees テーブルからデータを取得してクロス集計レポートを作成します。

SELECT
   COALESCE(department_name, '-') department,
   COALESCE(job_title,'-') job,
   COUNT(*) ,
   SUM(salary) salary
FROM
   employees
INNER JOIN departments USING (department_id)
INNER JOIN jobs USING (job_id)
GROUP BY
   CUBE(department_name,job_title)
ORDER BY
   department_name ASC NULLS LAST;Code language: SQL (Structured Query Language) (sql)

結果は以下のとおりです。

このチュートリアルは、SQL CUBE の拡張機能を使用して、クエリ結果の中の小計を生成する方法を学習します。