データをグループ化して表示してみよう（2）：Webブラウザで気軽に学ぶ実践SQL講座（10）（3/3 ページ）

GROUP BYの拡張機能 GROUPING SETS

　CUBEを使うと、指定したすべての列の組み合わせに対してクロス集計値を出すことができます。しかし、集計する要素が多い場合は、組み合わせの数も非常に大きくなります。

　例えば、集計要素として5列を指定すると、CUBEによる組み合わせの数は全部で 32通り（25）となります。ここまで増えてしまうと、すべての組み合わせを表示するのではなく、必要な集計結果だけに制限したい場合には、使いにくいかもしれません。

　このような場合には、GROUPING SETS句を利用します。GROUPING SETSを使用すると、作成するグループの集合を指定して絞り込めます。

SELECT   グループ化する列名, 関数 (列名), …
FROM     表名
GROUP BY GROUPING SETS 
((グループ化したい列のセット1), (グループ化したい列のセット2),…)
・・・

　例えば、集計を 「入社年と部門でグループした結果」と、「入社年と職種でグループ化した結果」で行いたい場合は、「GROUPING SETS((入社年,部門),(入社年,職種))」のように指定します。グループ化したい列の組み合わせを指定すればよいので、シンプルに記述することができますね。

　それでは、試してみましょう。入社年は、連載第6回で紹介したEXTRACT関数を使って、入社日（hiredate）からyear要素を取り出せば調べることができます。

SELECT EXTRACT(year from hiredate) AS "入社年", deptno, job, 
       COUNT(*) AS 社員数,TRUNC(AVG(sal)) AS "平均給与"
FROM   emp
GROUP BY GROUPING SETS((deptno,EXTRACT(year from hiredate)),
                       (job,   EXTRACT(year from hiredate))
                      );

図4

　このように、「入社年と部門でグループした結果（1）」と、「入社年と職種でグループ化した結果（2）」だけが表示されました。

コラム

　今回の連載では、部門内の職種ごと、部門ごと、さらに社員全体の人数と平均給与を調べるSQLとして、UNIONを使った方法（図1）とROLLUPを使った方法（図2）をご紹介しました。どちらも同じ実行結果になりますが、見た目のシンプルさ、実行の効率性から、ROLLUPを使った方法をお勧めします。

　それでは、それぞれの内部的な実行方法を見てみましょう。連載第4回でも紹介した通り、結果の右にある「実行計画」のタブから、SQL文の実行計画（Oracle Databaseの内部的な実行手順）を確認できます。

図5　UNIONを使ったSQL文の実行計画

図6　ROLLUPを使ったSQL文の実行計画

　（図5）の実行計画を見ると、Oracle Databaseの内部的にも、3つのSQL文が別々に実行されていることが分かります。それに対して（図6）の実行計画では、1回の実行で結果が得られることが分かります。

　それぞれの「コスト」を比較してみても、UNIONを使ったSQL文の方が約3倍のコストが掛かっていますね。このように、実行効率から考えても、UNIONを使った方法より、ROLLUPやCUBEを使った方が良いと言えます。

須々木尚子（すすき なおこ）

日本オラクル オラクルダイレクト所属。オンラインセミナーの講師や、お客様への提案、案件の支援などを担当。著書に「Oracle SQLクイズ」（翔泳社）があります。