Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

分析関数の衝撃(前編)

CodeZineに掲載されたSQLを分析関数で記述する

  • LINEで送る
  • このエントリーをはてなブックマークに追加
2007/06/12 14:00

ダウンロード ソースコード (795.0 B)

SQLを使う業務では、分析関数を使いこなすと、生産性と、SQLの可読性とパフォーマンスを、大きく向上させることができます。分析関数を使う際の、考え方と、処理のイメージを解説します。

目次

はじめに

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

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

対象読者

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

分析関数について

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

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

 また、この記事では「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でも似たような考え方を使います。

分析関数で書き換えた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)まで、seqRankが等しいことが分かります。
  2. case4に注目すると、全レコードのseqRankが等しいことが分かります。
  3. case1とcase2とcase4では、seqRankが等しいなかで最大のseqに1足した値が、歯抜けの最小値になっていることが分かります。
  4. case3とcase5では、seqRankが等しいなかで最大のseqが、nullとなるので、nvl関数で対応してます。

  • LINEで送る
  • このエントリーをはてなブックマークに追加

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

All contents copyright © 2005-2018 Shoeisha Co., Ltd. All rights reserved. ver.1.5