分析関数の衝撃（前編）

はじめに

　Oracle9iから（厳密には、8.1.6 Enterprise Edition）から使用可能な分析関数について解説します。分析関数はSQL ServerやDB2でも使用できますし、標準SQLなので、いずれは他のDBでも使えるようになるはずです。

　本稿では分析関数の便利さを実感しやすくするために、CodeZineのミック氏の記事で記載されたSQLを、分析関数を使って記述していきます。

対象読者

• SQLで分析関数を使ったことがない方
• SQLの可読性を向上させたい方
• SQLのパフォーマンスを向上させたい方

分析関数について

　SQLで分析関数を使ったことがない方は、

• ＠IT 『SQLクリニック（8）　極めよう！ 分析関数によるSQL高速化計画』
• ＠IT 『SQLクリニック（9）　SQL分析関数をさらに深く追求してみよう』
• ＠IT 『SQLクリニック（10）　まだまだあるぞ！ 分析関数の究極テクニック』

　を先に読んでおくと理解しやすいと思います。

　また、この記事では「HAVING句の力」に記載されているSQLを分析関数を使って記述していきますので、こちらについても先に読まれた方が理解がしやすいと思います。

必要な環境

　Oracle 10.2.0.1.0で動作確認しました。その他、

• Oracle9i以降
• DB2
• SQL Server 2005

　でも応用できます。

1. 歯抜けを探す

　まずは歯抜けを探すSQLについてです。「HAVING句の力」では、歯抜けの最小値を探す2つのSQLが提示されています。

-- 結果が返れば歯抜けあり
SELECT '歯抜けあり' AS gap
  FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);

-- 歯抜けの最小値を探す
SELECT MIN(seq + 1) AS gap
  FROM SeqTbl
 WHERE (seq+ 1) NOT IN ( SELECT seq FROM SeqTbl);

　これらを分析関数で書き換えてみます。

　最初のSQLで歯抜けの有無を調べ、次のSQLで歯抜けの最小値を探してますね。これら2つをまとめて、以下の仕様を満たすSQLを作ります。

case1   case2   case3   case4   case5
  seq     seq     seq     seq     seq
-----   -----   -----   -----   -----
    1       1       2       1   データなし
    2       2       3       2
    3       3       4       3
    5       4               4
    6       6               5

1. case1では、歯抜けの最小値として4を返す。
2. case2では、歯抜けの最小値として5を返す。
3. case3では、歯抜けの最小値として1を返す。
4. case4では、歯抜けの最小値として6を返す。
5. case5では、歯抜けの最小値として1を返す。

　手続き型の言語であれば、

1. 処理1　1から始まるループ
2. 処理2　1があるか？
3. 処理3　2があるか？
4. 処理4　3があるか？

　といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。

select nvl(max(seq), 0) + 1 as gap
  from (select seq, Row_Number() over(order by seq) as Rank
          from SeqTbl)
 where seq = Rank;

　SQLの処理イメージと考え方は以下の通りです。なお、seqの順位を持つRank列も付与して考えます。

  case1       case2       case3
seq  Rank   seq  Rank   seq  Rank
---  ----   ---  ----   ---  ----
  1     1     1     1     2     1
  2     2     2     2     3     2
  3     3     3     3     4     3
  5     4     4     4
  6     5     6     5

  case4       case5
seq  Rank   seq  Rank
---  ----   ---  ----
  1     1   データなし
  2     2
  3     3
  4     4
  5     5

1. case1とcase2に注目すると、1から（歯抜けの最小値 - 1）まで、seqとRankが等しいことが分かります。
2. case4に注目すると、全レコードのseqとRankが等しいことが分かります。
3. case1とcase2とcase4では、seqとRankが等しいなかで最大のseqに1足した値が、歯抜けの最小値になっていることが分かります。
4. case3とcase5では、seqとRankが等しいなかで最大のseqが、nullとなるので、nvl関数で対応してます。