ここはとある街の総合病院。
ここには通常の診療科のほかに、
何軒もの病院をたらいまわしにされた、
それがSQL緊急救命室、
そう、
ロバート
救命室部長。腕の立つエンジニアだが、
（PM3:00 休憩室。ロバート、
そろそろ専門の希望を出す時期ね。もうどこにするか考えた？
はい。救命室に決めました。やりがいありますし。
?!
そ、
はい。体力には自信があります。救命室は最もタフな専門ですから、
はあ……でももう志望書提出しちゃいましたし……。
( ﾟдﾟ)
あ、
お、
そうね……。
いやー楽しみだなあ。来年も頑張るぞー。
ふう……。
繰り返されるサブクエリ
これがカルテです。
カルテ1：ある部品の供給業者の一覧を管理するSuppliersテーブル
- ※）
今回のコードはPostgreSQLで動作確認をしています。Oracle、 SQL Server、 DB2ともに動作します。MySQLには未実装の機能が含まれています。
sup | city | ship_flg | item_cnt -----+------+----------+---------- B | 東京 | 不可 | 30 C | 東京 | 可 | 40 E | 大阪 | 不可 | 40 I | 大阪 | 可 | 30
ちょっと複雑な業務仕様ですね。
在庫調整をする際に、
都市別の出荷可能品数と不可能品数は、
……
SELECT SP.sup, -- 出荷可能業者のパート
SP.city,
SP.ship_flg,
SP.item_cnt
FROM Suppliers SP
INNER JOIN
(SELECT city, ┐
SUM(item_cnt) AS able_cnt │
FROM Suppliers │
WHERE ship_flg = '可' │─地域別の出荷可能品数
GROUP BY city) SUM_ITEM ┘
ON SP.city = SUM_ITEM.city
AND SP.item_cnt >= (SUM_ITEM.able_cnt * 0.5)
WHERE SP.ship_flg = '可'
UNION ALL
SELECT SP.sup, -- 出荷不可能業者のパート
SP.city,
SP.ship_flg,
SP.item_cnt
FROM Suppliers SP
INNER JOIN
(SELECT city, ┐
SUM(item_cnt) AS disable_cnt │
FROM Suppliers │
WHERE ship_flg = '不可' │―地域別の出荷不可能品数
GROUP BY city) SUM_ITEM ┘
ON SP.city = SUM_ITEM.city
AND SP.item_cnt >= (SUM_ITEM.disable_cnt * 0.5)
WHERE SP.ship_flg = '不可';え、
お前の解はいつも期待を裏切らないな。
先生にそう言っていただけると。はい。
誉めとらんわ！ だいたいこの程度のことをするのにこのコードは長過ぎるぞ。美的センスのカケラもない。
先生にセンスのことでとやかく言われたくないですね。見た目が不恰好でも良いコードだってあるでしょう。
ない。SQLにおいて見た目は大事だ。シンプルさは正義であり力だ。
おおっ、
ワイリーの解は、
共通表式
1つのSQLの中で同じサブクエリを何度も参照するようなケースでは、
WITH SUM_ITEM AS ( ┐
SELECT city, │
SUM(CASE WHEN ship_flg = '可' │
THEN item_cnt │
ELSE NULL END) AS able_cnt, │
SUM(CASE WHEN ship_flg = '不可' │
THEN item_cnt │
ELSE NULL END) AS disable_cnt │
FROM Suppliers │―共通表式
GROUP BY city) ┘
SELECT SP.sup,
SP.city,
SP.ship_flg,
SP.item_cnt
FROM Suppliers SP
INNER JOIN SUM_ITEM
ON SP.city = SUM_ITEM.city
AND SP.item_cnt >= (SUM_ITEM.able_cnt * 0.5)
WHERE SP.ship_flg = '可'
UNION ALL
SELECT SP.sup,
SP.city,
SP.ship_flg,
SP.item_cnt
FROM Suppliers SP
INNER JOIN SUM_ITEM
ON SP.city = SUM_ITEM.city
AND SP.item_cnt >= (SUM_ITEM.disable_cnt * 0.5)
WHERE SP.ship_flg = '不可';うむ。SQLには元来、
これはすっきりしますね。1つ質問なんですが、
それは実装依存ね。優秀なオプティマイザなら、
ロバートも言うとおり、
CASE式
ところでワイリー、
UNION ALL……ですかね。
そうだ。お前のクエリは、
WITH SUM_ITEM AS (
SELECT city,
SUM(CASE WHEN ship_flg = '可'
THEN item_cnt
ELSE NULL END) AS able_cnt,
SUM(CASE WHEN ship_flg = '不可'
THEN item_cnt
ELSE NULL END) AS disable_cnt
FROM Suppliers
GROUP BY city)
SELECT SP.sup,
SP.city,
SP.ship_flg,
SP.item_cnt
FROM Suppliers SP
INNER JOIN SUM_ITEM
ON SP.city = SUM_ITEM.city
AND SP.item_cnt >= CASE WHEN SP.ship_flg = '可' THEN SUM_ITEM.able_cnt ┐
WHEN SP.ship_flg = '不可' THEN SUM_ITEM.disable_cnt │
ELSE NULL END * 0.5; ┘第2回で取り上げた冗長性症候群を覚えているでしょうか。SQL:1992でCASE式が導入されるまで、 さてここで少し、 ※1） 改訂の内容は、 新規追加された機能についても、 表1を見ていただいてもわかるように、 SQLは寿命の長い言語だとよく言われます。たしかに、 実はSQLの場合、 その大きな理由は、 そのため、 これは、 こうしたことから、 見た目上の カルテ2：図4のような製造業者のテーブルManufacturersを追加する。部品の要求をかけている製造業者と同じ地域 東京 - 北区 東京 - 荒川区 東京 - 江戸川区 東京 - 福島区 ←ありえない組み合わせ 大阪 - 北区 ← 要求フラグが不要になっているため、 大阪 - 荒川区 ←ありえない組み合わせ 大阪 - 江戸川区 ←ありえない組み合わせ 大阪 - 福島区 SQLの標準化の中で最も画期的だったのは、 また、 あとは新機能というわけではないけど、そうか。条件によって比較の列を切り替えればよかったのか。このクエリだと、うむ。だが今回のように中で集約を行っていたり、年輩のDBエンジニアには、今から新しく覚える僕は勝ち組ってことですか。
ふ、ぐぬぬ。
言語の進化とエンジニアの進化
年 名称 主な機能追加 Oracle
11gR2SQLServer
2008 R2DB2
V9.PostgreSQL
9.MySQL
5.1999 SQL:1999 再帰クエリ ○ ○ ○ ○ ○ トリガー ○ ○ ○ ○ ○ 正規表現 ○ ○ △ ○ ○ 非スカラ型 △ ○ △ ○ △ OLAP機能 ○ ○ ○ × △ 共通表式 ○ ○ ○ ○ × 2003 SQL:2003 XML機能 ○ ○ ○ ○ × ウィンドウ関数 ○ ○ ○ ○ × シーケンスオブジェクト ○ × ○ ○ × MERGE文 ○ ○ ○ × △ オートナンバー型 × ○ ○ ○ ○ 2008 SQL:2008 TRUNCATE文のサポート ○ ○ ○ ○ ○ INSTEAD OF トリガ ○ ○ ○ ○ ×
※2）
※3）
※4）
※5）
※6）
※7）
※8）
SQLは寿命の長い言語か？
40年間主流であり続けた
自らを大きく変化させてきたSQL
次の患者も、
冗長さはコードをわかりにくくする
部品を要求している製造業者は、sup | city | area
----+------+--------
A | 東京 | 北区
C | 東京 | 荒川区
SELECT sup,
city,
area
FROM Suppliers
WHERE ship_flg = '可'
AND city IN (SELECT city
FROM Manufacturers
WHERE req_flg = '要')
AND area IN (SELECT area
FROM Manufacturers
WHERE req_flg = '要');sup | city | area
----+------+--------
A | 東京 | 北区
C | 東京 | 荒川区
G | 大阪 | 北区
お前の解は、
あれ……本当だ。余計なあなたの解は、
ああっ、比較できるのは列だけではない
そういうこと。いい？ この問題では、SELECT sup,
city,
area
FROM Suppliers
WHERE ship_flg = '可'
AND city || area IN (SELECT city || area
FROM Manufacturers
WHERE req_flg = '要');うむ。そしてここからがようやく本題だ。リスト5はさらにリスト6のように書き換えるのが正しい。これはSQL-92で標準化された行式SELECT sup,
city,
area
FROM Suppliers
WHERE ship_flg = '可'
AND (city, area) IN (SELECT city, area
FROM Manufacturers
WHERE req_flg = '要');なるほど。しかしこれってヘレンさんの解メリットは大きいぞ。まず左辺の列に連結演算子を使わなくてよいため、ああ、
ほかにも覚えておいたほうがいい新機能ってあります？
そうね、逆に、
えっ、
たとえばSQL:1999で導入された非スカラ型標準化といえど完璧ではないんですね。
そうだな。SQLは言語の理論的基礎がかっちりしているから、あ、お、
普段は威張ってるくせに、【参考資料】