はじめに
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
- case1では、歯抜けの最小値として4を返す。
- case2では、歯抜けの最小値として5を返す。
- case3では、歯抜けの最小値として1を返す。
- case4では、歯抜けの最小値として6を返す。
- case5では、歯抜けの最小値として1を返す。
手続き型の言語であれば、
- 処理1 1から始まるループ
- 処理2 1があるか？
- 処理3 2があるか？
- 処理4 3があるか？
といった処理を行うと思いますが、分析関数を使ったSQLでも似たような考え方を使います。
分析関数で書き換えた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
- case1とcase2に注目すると、1から（歯抜けの最小値 - 1）まで、
seqとRankが等しいことが分かります。 - case4に注目すると、全レコードの
seqとRankが等しいことが分かります。 - case1とcase2とcase4では、
seqとRankが等しいなかで最大のseqに1足した値が、歯抜けの最小値になっていることが分かります。 - case3とcase5では、
seqとRankが等しいなかで最大のseqが、nullとなるので、nvl関数で対応してます。
