言葉のさんぽ道

~気ままに写真とゲームの話題が更新されるはず~

Oracle 一覧表示のテクニック

久々にお仕事で知って嬉しくなった技術を紹介。

ページング方法

膨大な量のデータを表示する時、1画面に全部は表示しきれないから分散して表示する事になる。それは一般にページングと呼ばれるけど、これを技術的に実現しようとすると案外難しい。何も考えないと全件検索してから対象の情報だけ抜き取る、とかする。でも検索量が多ければ多いほど、検索条件が複雑になればなるほど処理が重くなる。その回避方法を以下に示す。おなじみのROW_NUMBER()が使えるのよね。


(テーブル例)

  • 社員テーブル
    • 社員番号
    • 社員名
    • 役職名

単純にやろうとすると

select
 社員番号,
 社員名,
 役職名
from
 社員テーブル
order by
 社員番号

ってなると思うけど、そこに一手間。

select *
from (
 select
  社員番号,
  社員名,
  役職名,
  ROW_NUMBER() OVER (ORDER BY 社員番号) RNUM
 from
  社員テーブル
 order by
  社員番号
 )
where RNUM > 100 and RNUM < 200

とか。これで100件目から200件目まで拾えます。
SQLも軽くて万々歳!やっほ〜ぅ。

最新データを抽出する方法

たまーに厄介なデータ構造のテーブルがありまして。削除フラグがなく更新日付だけでデータ管理しているようなテーブル。いわゆる履歴管理系テーブルですな。これから主キー(のようなもの)は一意で最新の情報を取ってこいや。というようなムチャな要望がありました。
それで調べていたらありましたよ。これもROW_NUMBER関数関連で解決できるらしい。すごいなROW_NUMBER!正確にはPARTITION BYを使うんだけども。
ってことで以下例。


(テーブル例)

  • 社員テーブル
    • 社員番号
    • 社員名
    • 役職名
    • 更新日

(データ例)

0424, Logos, 一般, 2004/04/01
0424, Logos, リーダ, 2005/04/01
0424, Logos, マネージャ, 2006/04/01
0425, Namahage, 一般, 2004/04/01
0425, Namahage, 社長, 2005/04/01

select *
from (
 select
  社員番号,
  社員名,
  役職名,
  ROW_NUMBER() OVER (PARTITION BY 社員番号 ORDER BY 更新日 DESC) RNUM
 from
  社員テーブル
 order by
  社員番号
 )
where RNUM = 1

これは内部Select文の結果はこんな事になっている
(実行結果)

0424, Logosuke, 一般, 2004/04/01, 3
0424, Logos, 一般, 2005/04/01, 2
0424, Logos, リーダ, 2006/04/01, 1
0425, Namahage, 一般, 2004/04/01, 2
0425, Namahage, 社長, 2005/04/01, 1

つまりPARTITION BY句で指定した社員番号毎にROW_NUMBERが振られ、その順番はORDER BY句で指定した更新日の降順毎、というわけ。
なのでRNUMの1が必ず社員番号毎の最新の情報になり、検索結果も素早く出る、というわけさ。


ここを見てくれている人の一体どれくらいが参考になったかは分からないけど、お役に立てば幸いさ!それではSeeYouNext!