ここはとある街の総合病院。
ここには通常の診療科のほかに、
何軒もの病院をたらいまわしにされた、
それが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人がロバートの解をあれこれ論評していると、
あら、
今日は世間じゃサブクエリ記念日なのか? ワイリー、
は、
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;
※ テーブルのレイアウトとデータは図1と同じとする
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式には惚れ惚れするな。
あ、
なにい。お前ほんとに大学行ってるのか。今日は居残りだ。このクエリの意味を完全に理解するまで帰ることは許さん。
そ、
ウィンドウ関数の昇順ソートと降順ソートをうまく使おう
読者の中にも、
あとは、

残る問題は、
稿末の参考資料をヒントに考えてください。なお、
おわりに~困難は分割するな~
(AM7:00 夜は完全に明けた。休憩室でワイリーが何やら机に向かっている。課題に苦しんでいるようだ。そこへ、
はあはあ…。ようやく課題が終わった。難しかった。でもCASE式って便利だなあ。今度は僕も使ってみよう。それにしても、
別にサブクエリは絶対悪ではないわ。特に、
そう、
そうね。ただ非手続き型であるSQLの本質として、
うーん、
(ロバートが休憩室のドアを乱暴に開けて入ってくる)
おい、
一難去ってまた一難ね。行くわよ、
そ、
泣きごと言うな。永遠に寝られる薬打ってやろうか。行くぞ!
えーん。
(次号に続く)
【参考資料】
※すべて拙著
- 1.WEB+DB PRESS Vol.
60連載 「DBアタマアカデミー」 第4回 “クエリ評価エンジンと実行計画” 実行計画の読み方、
DBMS内部でのSQLの実行のされ方、 結合時のアルゴリズムなどについて知りたい方はこちらを読むと基礎的なことがわかります。 - 2.
「CASE式のススメ (前編) 」 患者2号に対するヘレンの解で、
CASE式の使い方が理解できなかった人はこちらをどうぞ。このCASE式を集約関数の中で使う技術は、 SQLをマスターするうえでは必須です。 - 3.
「自己結合の使い方」 自己結合のイメージが湧かない人向け。自己結合は、
使いこなせば便利な道具です。しかし、 パフォーマンス上の問題点を抱えているため、 乱用は危険。 - 4.本WEB+DB PRESS Vol.
55 連載 「SQL アタマアカデミー」 最終回 “OLAP関数で強力な統計処理を実現!” ウィンドウ関数の使い方を勉強したい方はこちらをどうぞ。なお
「OLAP関数」 とは、 ウィンドウ関数の (やや古い) 別名です。 - 5.WEB+DB PRESS Vol.
45連載 「SQLアタマアカデミー」 第1回 “連番の特性を利用してデータ操作をもっと自由に” ウィンドウ関数の中でも特にROW_
NUMBER関数でレコードに連番を振る技術の応用方法を解説しています。