« HTML + CSS で数式組版 (その2) | トップページ | HTML + CSS で数式組版 (その 3 ) »

2009年3月17日 (火)

イヤンの SQL

例えば、こういう "イベント型" のテーブルがあったとき :

select
  ev_id,
  ev_date,
  ev_str
from t_event
order by ev_date;

 ev_id |  ev_date   |  ev_str
-------+------------+----------
    23 | 2003-01-01 | event1
    24 | 2003-01-21 | event2
    25 | 2003-02-20 | event3
    26 | 2003-03-19 | event4
    27 | 2003-04-05 | event5
    28 | 2003-04-22 | event6
    29 | 2003-05-12 | event7
    30 | 2003-06-04 | event8
-- More  --

"前の日付" とか、 "前の前の日付" とかを一緒にとりたい場合があります。

すなわち :

  ev_date   |  ev_str  | p_ev_date  | pp_ev_date
------------+----------+------------+------------
 2003-01-01 | event1   |            |
 2003-01-21 | event2   | 2003-01-01 |
 2003-02-20 | event3   | 2003-01-21 | 2003-01-01
 2003-03-19 | event4   | 2003-02-20 | 2003-01-21
 2003-04-05 | event5   | 2003-03-19 | 2003-02-20
 2003-04-22 | event6   | 2003-04-05 | 2003-03-19
 2003-05-12 | event7   | 2003-04-22 | 2003-04-05
 2003-06-04 | event8   | 2003-05-12 | 2003-04-22
-- More  --

こんな SQL :

select
  e1.ev_date,
  e1.ev_str,
  e2.p_ev_date,
  max(e3.ev_date) as pp_ev_date
from t_event e1
inner join (
  select
    e1.ev_id,
    e1.ev_date,
    max(e2.ev_date) as p_ev_date
  from t_event e1
  left join (
    select
      ev_date
    from t_event) e2
    on e2.ev_date < e1.ev_date
  group by
    e1.ev_id,
    e1.ev_date) e2
  on e2.ev_id = e1.ev_id
left join (
  select
    ev_date
  from t_event) e3
  on e3.ev_date < e2.p_ev_date
group by
  e1.ev_date,
  e1.ev_str,
  e2.p_ev_date
order by
  e1.ev_date;

往々にして、この種のテーブルは、レコードが 1億件 とかあるんですよね。そのようなテーブルで、上のような SQL を実行しますと、テンポラリ領域がオーバー・フローします。

これこそ、非正規化して、 "前の日付" 、 "前の前の日付" をカラムに持たせるべきじゃないでしょうか?

と、ここで私の中のゴースト X が語りかけてきます。

「おいおい、データベースの中に linked list を作ろう ってのかい? INSERT 、 DELETE、 UPDATE でどうなるか考えてみなよ。」

こうなりますね :

INSERT 時
INSERT するレコードに、 その "前の日付" 、 "前の前の日付" を 代入。 INSERT するレコードの "次の" レコードの "前の日付"、 "前の前の日付" を 更新。 さらにその "次の" レコードの "前の前の日付" を更新。
DELETE 時
DELETE するレコードの "次の日付" のレコードを SELECT して、その "前の日付" 、 "前の前の日付" を、それぞれ、"前の前の日付" 、 "前の x 3 日付" にする。 さらにその "次の" レコードの (以下略)。
UPDATE 時
DELETE + INSERT 。

まさに linked list 。

"みんなの" データベース でそんなことしたら、どうなる? われらがレコードをまさにデータベースに突っ込まんとす、ってときに、他の奴が "前の" レコードを消しちまったら? RDBMS が java.util.ConcurrentModificationException を throw してくださるってのか?」

ですわな。

とすると、やっぱり、 テーブルはこのまま正規形にしておいて、読み取り側のアプリケーションに泣いてもらうのがベターですかね。 SQL 式でとろうとせずに、ホスト言語側でループするなり、 PL/SQL − PL/pgSQL か − を使うなりすれば、何とかなるでしょう。

|

« HTML + CSS で数式組版 (その2) | トップページ | HTML + CSS で数式組版 (その 3 ) »

コメント

コメントを書く



(ウェブ上には掲載しません)


コメントは記事投稿者が公開するまで表示されません。



トラックバック

この記事のトラックバックURL:
http://app.f.cocolog-nifty.com/t/trackback/80472/28666325

この記事へのトラックバック一覧です: イヤンの SQL:

« HTML + CSS で数式組版 (その2) | トップページ | HTML + CSS で数式組版 (その 3 ) »