例えば、こういう "イベント型" のテーブルがあったとき :
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 か − を使うなりすれば、何とかなるでしょう。
最近のコメント