はじめまして。Re:Qの入江です。
数ヶ月前にOracleの構築案件に携わったのですが、その使用目的がデータウェアハウスのデータマイニング用でした。データマイニングとは？という状態だったこともあり、好奇心からデータウェアハウスおよびデータマイニングについて色々調べてみました。
今日はそのとき見つけた、ちょっとした小技？小ネタ？を紹介したいと思います。
調べていくと、Oracleのマニュアル「Databaseデータ・ウェアハウス・ガイド 11gリリース2(11.2) B56309-03」のROLLUP（GROUP BYの拡張）という関数に目がとまりました。
ROLLUP関数...皆さん、ご存知ですか？
比較的よく使用していたGROUP BYの拡張という言葉にひきつけられ調べてみた結果、ROLLUP関数って便利だな～と感じましたので、DBAの観点からその良さをお伝えします。
ROLLUP関数
---------------------------------------------------------------------------
使い方：
SELECT文のGROUP BY句に追加
用途：
①総計（合計）を取得
②小計を複数のレベルで取得
---------------------------------------------------------------------------
以下、例を使って説明します。
用途①：総計（合計）を取得
例1）表領域毎のデータファイルサイズ＋全てのデータファイルサイズの合計を調べたい
（※TEMP表領域を除く）
・ROLLUP関数を使わない場合だと・・・
SQL> SELECT tablespace_name,SUM(bytes)/1024/1024 mb FROM dba_data_files
2 GROUP BY tablespace_name;
TABLESPACE MB
---------- ----------
SYSAUX 600
UNDOTBS1 505
USERS 5
SYSTEM 710
と
SQL> SELECT SUM(bytes)/1024/1024 mb FROM dba_data_files;
MB
--------
1820
の2つのSQLになります。
(結合して1つのSQLにも可能ですが、文が長くなります。)
そこで、ROLLUP関数を使用すると・・・
SQL> SELECT tablespace_name,SUM(bytes)/1024/1024 mb FROM dba_data_files
2 GROUP BY ROLLUP(tablespace_name);
TABLESPACE MB
---------- ----------
SYSAUX 600
SYSTEM 710
UNDOTBS1 505
USERS 5
1820 ★合計値
上記★にて、tablespace_name列がNullのレコードに合計値が出ていることに気付いたでしょうか。
ROLLUPを使用することにより、プラスアルファで同時に総計（合計）を取得できるのです。
用途②：小計を複数のレベルで取得
例2）スキーマ毎、オブジェクトタイプ毎の数を確認し集計したい
（※SYSTEM、SYSMANスキーマに限定）
SQL> SELECT owner,object_type,COUNT(*) FROM dba_objects
2 WHERE owner in ('SYSTEM','SYSMAN')
3 GROUP BY ROLLUP(owner,object_type);
OWNER OBJECT_TYPE COUNT(*)
-------------- ---------------------------------------- ----------
SYSMAN LOB 75
SYSMAN TYPE 672
SYSMAN VIEW 471
SYSMAN INDEX 1028
SYSMAN QUEUE 12
SYSMAN TABLE 729
SYSMAN PACKAGE 193
SYSMAN TRIGGER 97
SYSMAN FUNCTION 12
SYSMAN RULE SET 4
SYSMAN SEQUENCE 13
SYSMAN PROCEDURE 3
SYSMAN TYPE BODY 49
SYSMAN PACKAGE BODY 193
SYSMAN MATERIALIZED VIEW 1
SYSMAN EVALUATION CONTEXT 2
SYSMAN 3554 ★1
SYSTEM LOB 25
SYSTEM TYPE 1
SYSTEM VIEW 14
SYSTEM INDEX 241
SYSTEM QUEUE 4
SYSTEM TABLE 161
SYSTEM PACKAGE 1
SYSTEM SYNONYM 8
SYSTEM TRIGGER 2
SYSTEM SEQUENCE 20
SYSTEM PROCEDURE 1
SYSTEM PACKAGE BODY 1
SYSTEM INDEX PARTITION 52
SYSTEM TABLE PARTITION 39
SYSTEM 570 ★2
4124 ★3
★1：SYSMANのオブジェクト小計
★2：SYSTEMのオブジェクト小計
★3：SYSTEM、SYSMANスキーマのオブジェクト総計
と小計と総計が取得できたことをお分かりいただけたでしょうか。
例1と2のように、サクッとSQLを実行して、小計や総計も取得して調べたい時、ROLLUP関数を知っておくと便利です。DBAの方も集計を必要な時、ROLLUP関数の使用を検討してみてはいかがでしょうか。
Oracleには隠れた機能がいっぱいあります（むろん知っている人は知っていますが）。
そんなちょっとした機能、小技のノウハウを少しでも紹介していき、活用頂ければ幸いです。
集計に便利なROLLUP関数
