データをグループ化して表示してみよう(2):Webブラウザで気軽に学ぶ実践SQL講座(10)(2/3 ページ)
前回はグループ関数を使った集計結果を説明しました。今回も、前回に引き続きグループ関数を使ってデータをさまざまな切り口で分析・集計する方法を紹介します(編集部)
GROUP BYの拡張機能 ROLLUP
今回のように小計、総計を求める処理をしたい場合、上記のように、3つのSQL文を別々に書き、UNION演算子で結果をひとつにして見せることもできます。しかし、このような書き方では構文が長くなってしまいますよね。
また、詳しくはコラム欄で紹介しますが、内部的にもSQL文が3回実行されるため、パフォーマンス面でもよくありません。実は、SQLにはもっと簡単に小計や総計を出すためのキーワード「ROLLUP」が用意されています。
GROUP BYの拡張であるROLLUPを使用すると、図1と同じ処理を、もっと簡単に書くことができます。一般的に「ROLLUP」とは、データを最も細かいグループから総計まで、レベルを上げながら集計する手法です。ROLLUPを使用すると、このような処理をSQL文で実現することができます。
SELECT グループ化する列名, 関数 (列名), … FROM 表名 GROUP BY ROLLUP (グループ化列1, グループ化列2…) ・・・
ROLLUPは、指定した列を右から左に評価し、最も細かいレベルのグループ化から、徐々にレベルを上げて、最後に総計を出す処理を行います。少し分かりにくいので、具体的な例で考えてみましょう。
例えば、「ROLLUP (A列, B列, C列) 」と指定した場合、以下のような4つのパターンの集計が行われます。
- A、B、Cすべての列でグループ化した結果
- A、B列でグループ化した結果
- A列のみでグループ化した結果
- グループ化しない結果、つまり総計
ROLLUPは、このような集計処理を実現するために用意された、GROUP BY句の拡張キーワードなのです。
ROLLUPを使って集計結果を表示してみよう
それでは、今回の人事部の例で考えてみましょう。
「ROLLUP ( deptno, job) 」と指定すると、(1)から(3)の結果を一度に求めることができます。
- 全社員を部門別および職種別でグループ化した結果(GROUP BY deptno, job)
- 全社員を部門別でグループ化した結果(GROUP BY deptno)
- 全社員の総計結果(グループ化なし)
SELECT deptno, job, COUNT(*) AS "社員数",TRUNC(AVG(sal)) AS "平均給与" FROM emp GROUP BY ROLLUP(deptno, job);
結果は、図1(3つのSQLを別々に実行してUNIONでつなぎ合わせた結果)と同じですが、よりシンプルな構文で、結果を得ることができましたね。
GROUP BYの拡張機能 CUBE
ROLLUPと同じような使い方ができるGROUP BY句の拡張キーワードに、CUBEがあります。
CUBEは、指定された列のすべての組み合わせに対してグループ化を行い、小計を作成します。例えば、「CUBE (A列, B列) 」のように2列を指定した場合、以下のような4つ(22)のパターンの集計が行われます。
- A、B両列でグループ化した結果
- A列のみでグループ化した結果
- B列のみでグループ化した結果
- グループ化しない結果、つまり総計
同様にCUBEに3列を指定すると8つ(23)のグループ化が、4列を指定すると16個(24)のグループ化が行われます。
指定したすべての列を組み合わせたクロス集計レポートを作成する場合には、CUBEを利用すると便利です。
それでは、図2の実行例のROLLUPをCUBEに変えて実行してみましょう。以下のようなグループ化が行われ、結果が表示されます。(図3の実行例では、結果が見やすいようにデータをソートして表示しています。)
- 全社員を部門別および職種別でグループ化した結果(GROUP BY deptno, job)
- 全社員を部門別でグループ化した結果(GROUP BY deptno)
- 全社員を職種別でグループ化した結果(GROUP BY job)
- 全社員の総計結果(グループ化なし)
SELECT deptno, job, COUNT(*) AS "社員数",TRUNC(AVG(sal)) AS "平均給与" FROM emp GROUP BY CUBE(deptno, job) ORDER BY deptno, job;
Copyright © ITmedia, Inc. All Rights Reserved.