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

（ 目次 ）

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関数」をご覧ください。

▲ ページTOPに戻る

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