「システム基盤構築のプロフェッショナル」レック・テクノロジー・コンサルティング

技術ブログ

~技術ブログ ~

集計に便利なROLLUP関数

はじめまして。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には隠れた機能がいっぱいあります(むろん知っている人は知っていますが)。
そんなちょっとした機能、小技のノウハウを少しでも紹介していき、活用頂ければ幸いです。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

資料請求・お問い合わせはこちら

▲ ページトップに戻る

技術ブログ

2019年5月
« 4月    
 12345
6789101112
13141516171819
20212223242526
2728293031  
採用に関するお問い合わせはこちら