Shoeisha Technology Media

CodeZine(コードジン)

特集ページ一覧

分析関数の衝撃5(総集編)

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

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

ダウンロード ソースコード (3.0 KB)

SQLを使う業務では分析関数を使いこなすと、生産性とSQLの可読性とパフォーマンスを、大きく向上させることができます。分析関数を使う際の考え方と、処理のイメージを解説します。今回は総集編として、前編、中編、後編、完結編に関連した内容を扱います。

目次

はじめに

 今回の総集編では、前編中編後編完結編に関連した内容を扱います。

対象読者

  • SQLの可読性を向上させたい方
  • SQLのパフォーマンスを向上させたい方

必要な環境

 本稿で扱うSQLは、Oracle 10.2.0.1.0で動作確認しました。ソースコードはDB2 V9.5でも動作確認しました。その他、次の環境でも応用が可能です。

  • Oracle9i以降
  • DB2
  • SQL Server 2005以降

1. 旅人算の感覚を応用する(2人旅人算)

 旅人算は、有名な算数の問題です。旅人算の問題を解くには、複数の旅人を脳内でイメージする感覚が有効ですが、SQLで旅人算の感覚を応用できるのです。

 本稿では、旅人算の感覚の使用例を2つ扱います。最初は、2人旅人算の感覚を応用したSQLです。

 前編の「1. 歯抜けの最小値を探す」では、下記のselect文を場合分けを行って検証しました。

歯抜けの最小値を探す
select nvl(max(seq), 0) + 1 as gap
  from (select seq, Row_Number() over(order by seq) as Rank
          from SeqTbl)
 where seq = Rank;

 上記のselect文を、旅人算の感覚(2人旅人算)を使って検証してみましょう。速さが異なる2人の旅人(旅人R、旅人S)が数直線の原点からプラス方向に同時にスタートしたとして、

速さが1の旅人Rの位置
Row_Number() over(order by seq)
速さが1以上の整数の旅人Sの位置
seq

 と考えると、2人の旅人の位置の差は、広義の単調増加(大きくなるかそのまま)であると分かります。

 そして、歯抜けが発生するなら、その位置は(2人の旅人が最後に同じだった位置)+1だと分かります。歯抜けが発生しなくても、同様に(2人の旅人が最後に同じだった位置)+1が求めるべき値となります。

 SQLのイメージは下記となります。

case1 歯抜けの最小値は4
seq  Rank  2人の旅人の位置
---  ----  - 1- 2- 3- 4- 5- 6-
  1     1  |SR|  |  |  |  |  |
  2     2  |  |SR|  |  |  |  |
  3     3  |  |  |SR|  |  |  |
  5     4  |  |  |  | R|S |  |
  6     5  |  |  |  |  | R|S |
case2 歯抜けの最小値は5
seq  Rank  2人の旅人の位置
---  ----  - 1- 2- 3- 4- 5- 6-
  1     1  |SR|  |  |  |  |  |
  2     2  |  |SR|  |  |  |  |
  3     3  |  |  |SR|  |  |  |
  4     4  |  |  |  |SR|  |  |
  6     5  |  |  |  |  | R|S |
case3 歯抜けの最小値は1
seq  Rank  2人の旅人の位置
---  ----  - 1- 2- 3- 4-
  2     1  | R|S |  |  |
  3     2  |  | R|S |  |
  4     3  |  |  | R|S |
case4 歯抜けの最小値は6
seq  Rank  2人の旅人の位置
---  ----  - 1- 2- 3- 4- 5-
  1     1  |SR|  |  |  |  |
  2     2  |  |SR|  |  |  |
  3     3  |  |  |SR|  |  |
  4     4  |  |  |  |SR|  |
  5     5  |  |  |  |  |SR|
case5 歯抜けの最小値は1
seq  Rank   2人の旅人の位置
---  ----   - 1-
データなし  |  |

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

著者プロフィール

バックナンバー

連載:分析関数の衝撃

もっと読む

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