ここはとある街の総合病院。
ここには通常の診療科のほかに、
何軒もの病院をたらいまわしにされた、
それがSQL緊急救命室、
そう、
ロバート
救命室部長。腕の立つエンジニアだが、
（AM3:00：仮眠室。ソファーでロバートが熟睡しているところへワイリーがやってくる）
……先生、
グーむにゃむにゃ。おうっ、
……先生！
グー。うひゃひゃもっと下。
先生ってば！
うるさい。耳元で怒鳴るな。
先生のほうがずっとうるさいし不気味ですよ
今何時だと思ってる。ほっとけ、
風邪じゃないんだから、
まったく、
（救命室。雑然とした器具が散乱しているなか、
遅いわよ。連絡を受けたら3分以内に来るのがルールでしょ。
3分で死ぬわけじゃあるまい。それで、
典型的なサブクエリ・
どれ…なるほど、
はい、
cust_id | seq | price --------+-----+------- A | 1 | 500 B | 5 | 100 C | 10 | 600 D | 3 | 2000
SELECT R1.cust_id, R1.seq, R1.price
FROMReceiptsR1 （※）
INNER JOIN
(SELECT cust_id, MIN(seq) AS min_seq
FROMReceipts ※
GROUP BY cust_id) R2
ON R1.cust_id = R2.cust_id
AND R1.seq = R2.min_seq;※ 同一テーブルを結合する自己結合
さてワイリー、
ええっ、
そうね。この問題で難しいのは、
ヘレンの言うとおり、
相関サブクエリは解にならない
なるほどわかりました。よーし、
SELECT cust_id, seq, price
FROM Receipts R1
WHERE seq = (SELECT MIN(seq)
FROM Receipts R2
WHERE R1.cust_id = R2.cust_id);馬鹿者。これじゃ解決になっとらんぞ。
え？ 間違ってました？
結果は元のクエリと同値よ。その意味で間違いではないわ。でも別の観点から不適切なの。
いいか、
ええ、
古い教科書だな。そんなもの読むと頭が悪くなるから捨てろ。お前の治療ではもう一つの問題に対処できていない。それはパフォーマンスだ。
なぜ自己結合はダメなのか～ディスクに触る者は不幸になる～
患者の実行計画
ロバートの言うことを確認するために、
------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 4 | 96 | 8 (25)| 00:00:01 | |* 1 | HASH JOIN | | 4 | 96 | 8 (25)| 00:00:01 | | 2 | VIEW | | 4 | 64 | 4 (25)| 00:00:01 | | 3 | HASH GROUP BY | | 4 | 20 | 4 (25)| 00:00:01 | | 4 | TABLE ACCESS FULL| RECEIPTS | 13 | 65 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | RECEIPTS | 13 | 104 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------
Hash Join (cost=1.35..2.58 rows=1 width=10)
Hash Cond: ((r1.cust_id = receipts.cust_id) AND (r1.seq = (min(receipts.seq))))
-> Seq Scan on receipts r1 (cost=0.00..1.13 rows=13 width=10)
-> Hash (cost=1.28..1.28 rows=4 width=12)
-> HashAggregate (cost=1.19..1.24 rows=4 width=6)
-> Seq Scan on receipts (cost=0.00..1.13 rows=13 width=6)
問題は、
テーブルの規模が小さいならこれでもかまいません。数百行程度のテーブルへのアクセスコストはとても小さく、
ワイリーの実行計画
次にワイリーの治療プランにおける実行計画を、
------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time | ------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 4 | 96 | 8 (25)| 00:00:01 | |* 1 | HASH JOIN | | 4 | 96 | 8 (25)| 00:00:01 | | 2 | VIEW | VW_SQ_1 | 4 | 64 | 4 (25)| 00:00:01 | | 3 | HASH GROUP BY | | 4 | 20 | 4 (25)| 00:00:01 | | 4 | TABLE ACCESS FULL| RECEIPTS | 13 | 65 | 3 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL | RECEIPTS | 13 | 104 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------
Seq Scan on receipts r1 (cost=0.00..16.54 rows=1 width=10)
Filter: (seq = (SubPlan 1))
SubPlan 1
-> Aggregate (cost=1.17..1.18 rows=1 width=4)
-> Seq Scan on receipts r2 (cost=0.00..1.16 rows=3 width=4)
Filter: ($0 = cust_id)
Oracle、
結合をなくせ！
ポイントは、
そうだ。よく見とけ
SELECT cust_id, seq, price
FROM (SELECT cust_id, seq, price,
ROW_NUMBER() OVER (PARTITION BY cust_id
ORDER BY seq) AS row_seq
FROM Receipts ) WORK
WHERE WORK.row_seq = 1;うわあ、
ROW_
ROW_
--------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost(%CPU)|Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 13 | 546 | 4 (25)| 00:00:01 | |* 1 | VIEW | | 13 | 546 | 4 (25)| 00:00:01 | |* 2 | WINDOW SORT PUSHED RANK | | 13 | 104 | 4 (25)| 00:00:01 | | 3 | TABLE ACCESS FULL | RECEIPTS | 13 | 104 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------
Subquery Scan work (cost=1.37..1.79 rows=1 width=16)
Filter: (work.row_seq = 1)
-> WindowAgg (cost=1.37..1.63 rows=13 width=10)
-> Sort (cost=1.37..1.40 rows=13 width=10)
Sort Key: receipts.cust_id, receipts.seq
-> Seq Scan on receipts (cost=0.00..1.13 rows=13 width=10)
長期的な視野を持て
患者やワイリーのクエリに比べて、
また、
結合を使うクエリには次の2つの不安定要因があります。
- アルゴリズムの変動リスク
- 環境起因の遅延リスク
（インデックス、 メモリ、 パラメータなど）
ワイリーの解で使われている相関サブクエリも、
1．アルゴリズムの変動リスク
結合のアルゴリズムは、
すると、
また、
2．環境起因の遅延リスク
こちらはもう少し簡単な話です。よく知られているように、
結合を利用するときは注意
結合を使うということは、 私たちは、 このことを、 （救命室で3人がロバートの解をあれこれ論評していると、 ※ テーブルのレイアウトとデータは図1と同じとする 読者の中にも、 あとは、 残る問題は、 稿末の参考資料をヒントに考えてください。なお、 （AM7:00 夜は完全に明けた。休憩室でワイリーが何やら机に向かっている。課題に苦しんでいるようだ。そこへ、 （ロバートが休憩室のドアを乱暴に開けて入ってくる）
（次号に続く） ※すべて拙著 実行計画の読み方、 患者2号に対するヘレンの解で、 自己結合のイメージが湧かない人向け。自己結合は、 ウィンドウ関数の使い方を勉強したい方はこちらをどうぞ。なお ウィンドウ関数の中でも特にROW_
長期国債はお得？
なるほどー。先生の解はいろんなリスクに備えた、その喩えはあってるようなあってないような…
短期の金融商品にはリスクもあるが、うーん、
いずれにせよ、
あら、今日は世間じゃサブクエリ記念日なのか？ ワイリー、は、
SELECT TMP_MIN.cust_id, TMP_MIN.price - TMP_MAX.price
FROM (SELECT R1.cust_id, R1.seq, R1.price
FROM Receipts R1
INNER JOIN
(SELECT cust_id, MIN(seq) AS min_seq
FROM Receipts
GROUP BY cust_id) R2
ON R1.cust_id = R2.cust_id
AND R1.seq = R2.min_seq) TMP_MIN
INNER JOIN
(SELECT R3.cust_id, R3.seq, R3.price
FROM Receipts R3
INNER JOIN
(SELECT cust_id, MAX(seq) AS min_seq
FROM Receipts
GROUP BY cust_id) R4
ON R3.cust_id = R4.cust_id
AND R3.seq = R4.min_seq) TMP_MAX
ON TMP_MIN.cust_id = TMP_MAX.cust_id;cust_id | diff
---------+-------
A -200
B -900
C 550
D 0
行間比較でも結合は必要ない
またずいぶんな大作ね。どれだけサブクエリを使えば気が済むのかしら。ワイリー、えっと、上出来だ。一種の行間比較をやりたいってわけだ。よくある話さ。では、可読性はさっきの患者にも増して悪いですね。サブクエリの階層が深くて、よくできました。それじゃ治療に入るわよ。いい、SELECT cust_id,
SUM(CASE WHEN min_seq = 1 THEN price ELSE 0 END)
- SUM(CASE WHEN max_seq = 1 THEN price ELSE 0 END) AS diff
FROM (SELECT cust_id, price,
ROW_NUMBER() OVER (PARTITION BY cust_id
ORDER BY seq) AS min_seq,
ROW_NUMBER() OVER (PARTITION BY cust_id
ORDER BY seq DESC) AS max_seq
FROM Receipts ) WORK
WHERE WORK.min_seq = 1
OR WORK.max_seq = 1
GROUP BY cust_id;いやあこれは見事だ。いつ見てもヘレンのCASE式には惚れ惚れするな。あ、なにい。お前ほんとに大学行ってるのか。今日は居残りだ。このクエリの意味を完全に理解するまで帰ることは許さん。そ、
ウィンドウ関数の昇順ソートと降順ソートをうまく使おう
おわりに～困難は分割するな～
はあはあ…。ようやく課題が終わった。難しかった。でもCASE式って便利だなあ。今度は僕も使ってみよう。それにしても、別にサブクエリは絶対悪ではないわ。特に、そう、そうね。ただ非手続き型であるSQLの本質として、うーん、
おい、一難去ってまた一難ね。行くわよ、
そ、
泣きごと言うな。永遠に寝られる薬打ってやろうか。行くぞ！
えーん。
【参考資料】