Oracle Database 11g Release 2に関する10の重要なこと – askTom Live -
Point2: Analytics are the coolest thing to happen to SQL, since the keyword SELECT 【分析関数】

オラクル・コーポレーション
サーバー・テクノロジー部門 シニア・テクニカル・アーキテクト兼エバンジェリスト
Thomas(Tom) Kyte (トム・カイト)

2.Analytics are the coolest thing to happen to SQL, since the keyword SELECT 【分析関数】

私はよく、「分析関数は、SQLにSELECTというキーワードが加わって以来のすばらしい出来事だ」と言っています。
分析関数は、Oracle8i Database R1(8.1.5)で追加された機能です。それから現在に至るまでに7つのバージョンがリリースされ、当初は27だった機能が60以上に増えています。
分析関数はSQLでは、かつては実現できなかったことを可能にしてくれます。分析関数によって、SQLはプロシージャ言語のようなものに変わり、前の行や次の行へのアクセスが可能となるのです。これは、もともとのSQLにはなかった発想です。

Oracle Database 11g R2では、具体的にどのような分析関数が追加されたのでしょうか。
まず私の目を惹いたのは、LISTAGG(LIST AGGREGATE)です。簡単にいうと、グループ化された行に存在する値を集約し、1つのリストを生成します。
従業員のリストを例にとってみましょう。LISTAGGはCOUNTの機能のように、この表を集約し、すべての文字列を1つの大きな文字列に連結してくれます。

Oracle9i Database R2以降でも、同様の処理をおこなう方法がいろいろありました。Oracle9i Database R2では、PL/SQLでユーザーが関数を作って実現できました。私が作成したのは、STRAGG関数です。グループ内の一定のリストを生成する関数でしたが、100マイルに及ぼうかというほど長いコードで、オブジェクトタイプなども必要でした。(参照: STRAGG関数(英語)

Oracle Database 10g R1では、SYS_CONNECT_BY_PATHという関数がありました。データを部門番号で分割し、従業員の名前でソートして、行に番号を付け、階層ごとに文字列を処理するものです。非常に複雑ですが、当時はこれがもっとも簡単な方法でした。

Oracle Database 11g R2では、LISTAGGを使うことによって、同じ処理がいたって簡単におこなえるようになりました。
ようやく、それまで長い間求められていた機能が組み込まれたのです。
具体的に、どのくらい簡単になったかを見てみましょう。

SQL> select deptno,
  2         listagg( ename, '; ' )
  3         within group
  4         (order by ename) enames
  5     from emp
  6    group by deptno
  7    order by deptno
  8   /

  DEPTNO   ENAMES
---------  --------------------
  10       CLARK; KING; MILLER
  20       ADAMS; FORD; JONES;
           SCOTT; SMITH
  30       ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD

この方法を、以下のSYS_CONNECT_BY_PATHを使う方法と比較してみてください。LISTAGGが、複雑な工程をどれほど簡略化しているかがわかるはずです。

SQL> select deptno,
  2         substr(
  3         max(sys_connect_by_path(ename, '; ')),
  4         3) enames
  5      from (
  6    select deptno,
  7        ename,
  8        row_number()
  9        over
 10        (partition by deptno
 11         order by ename) rn
 12     from emp
 13         )
 14    start with rn = 1
 15   connect by prior deptno = deptno
 16      and prior rn+1 = rn
 17    group by deptno
 18    order by deptno
 19   /

  DEPTNO   ENAMES
---------  --------------------
  10       CLARK; KING; MILLER
  20       ADAMS; FORD; JONES;
           SCOTT; SMITH

  30       ALLEN; BLAKE;
           JAMES; MARTIN;
           TURNER; WARD

Oracle Database 11g R2の分析関数の、注目すべきもう1つの新機能がNTH_VALUEです。これは名前(Nth)からわかるとおり、N番目のエントリをウィンドウから選び出す機能です。

これまでも、FIRST_VALUEなどの関数はありました。同じ例でいうと、データを部門ごとに分けてenameでソートした場合の、最初の従業員名がわかります。また、LAST_VALUEという関数は、部門別リストの最後の従業員名を表示します。
しかし、最初や最後ではなく、2番目や3番目の名前が知りたいこともあります。
NTH_VALUEはこれを可能にします。
「最初のレコード」「最後のレコード」「n番目のレコード」という概念は、SQLにはなかったものです。分析関数が、このような手法を可能にしたのです。
もし、まだ使ったことがないのであれば、ぜひ活用することをお勧めします。

SQL> break on deptno skip 1
SQL> select deptno,
  2         ename,
  3         row_number()
  4         over (partition by deptno
  5               order by ename) rn,
  6         first_value(ename)
  7         over (partition by deptno
  8               order by ename) "1st ename”,
  9         nth_value(ename,3)
 10         over (partition by deptno
 11               order by ename) "3rd ename”,
 12         last_value(ename)
 13         over (partition by deptno
 14               order by ename
 15               rows between current row
 16               and unbounded following) "last ename”
 17    from emp
 18   order by deptno, ename
 19  / 

 DEPTNO  ENAME     RN    1st ename   3rd ename    last ename
-------  ------    ---   ---------   ---------    ---------- 
 10      CLARK     1     CLARK                    MILLER
         KING      2     CLARK                    MILLER
         MILLER    3     CLARK       MILLER       MILLER

 20      ADAMS     1     ADAMS                    SMITH
         FORD      2     ADAMS                    SMITH
         JONES     3     ADAMS       JONES        SMITH
         SCOTT     4     ADAMS       JONES        SMITH
         SMITH     5     ADAMS       JONES        SMITH

 30      ALLEN     1     ALLEN                    WARD
         BLAKE     2     ALLEN                    WARD
         JAMES     3     ALLEN       JAMES        WARD
         MARTIN    4     ALLEN       JAMES        WARD
         TURNER    5     ALLEN       JAMES        WARD
         WARD      6     ALLEN       JAMES        WARD

14 rows selected.

これらは、Oracle Database 11g R2の新しい分析関数のほんの一部です。ほかにも新しい機能が追加されていますし、既存の機能もIGNORE NULLSのサポートで強化されています。詳しくは 「Oracle Database データウェアハウス・ガイド 11gリリース2」の21章「分析計算およびレポート用SQL関数」をご覧ください。

Copyright © 2010, Oracle Corporation Japan. All rights reserved.
無断転載を禁ず

この文書はあくまでも参考資料であり、掲載されている情報は予告なしに変更されることがあります。日本オラクル社は本書の内容に関していかなる保証もいたしません。また、本書の内容に関連したいかなる損害についても責任を負いかねます。

Oracleは米国Oracle Corporationの登録商標です。文中に参照されている各製品名及びサービス名は米国Oracle Corporationの商標または登録商標です。その他の製品名及びサービス名はそれぞれの所有者の商標または登録商標の可能性があります。

Thomas(Tom) Kyte (トム・カイト) Thomas(Tom) Kyte (トム・カイト)
2000年にAsk Tomブログ( http://asktom.oracle.com ) を開設して以来10年にわたり、全世界のオラクル技術者のありとあらゆる質問に答え、データベース技術の活用を世に広めてきた世界的に有名なエバンジェリスト。