ここはとある街の総合病院。
ここには通常の診療科のほかに、
何軒もの病院をたらいまわしにされた、
それが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式が導入されるまで、
そうか。条件によって比較の列を切り替えればよかったのか。このクエリだと、
うむ。だが今回のように中で集約を行っていたり、
年輩のDBエンジニアには、
今から新しく覚える僕は勝ち組ってことですか。
ふ、
ぐぬぬ。
言語の進化とエンジニアの進化
さてここで少し、
年 | 名称 | 主な機能追加 | Oracle 11gR2 | SQLServer 2008 R2 | DB2 V9. | PostgreSQL 9. | MySQL 5. |
---|---|---|---|---|---|---|---|
1999 | SQL:1999 | 再帰クエリ | ○ | ○ | ○ | ○ | ○ |
トリガー | ○ | ○ | ○ | ○ | ○ | ||
正規表現 | ○ | ○ | △ | ○ | ○ | ||
非スカラ型 | △ | ○ | △ | ○ | △ | ||
OLAP機能 | ○ | ○ | ○ | × | △ | ||
共通表式 | ○ | ○ | ○ | ○ | × | ||
2003 | SQL:2003 | XML機能 | ○ | ○ | ○ | ○ | × |
ウィンドウ関数 | ○ | ○ | ○ | ○ | × | ||
シーケンスオブジェクト | ○ | × | ○ | ○ | × | ||
MERGE文 | ○ | ○ | ○ | × | △ | ||
オートナンバー型 | × | ○ | ○ | ○ | ○ | ||
2008 | SQL:2008 | TRUNCATE文のサポート | ○ | ○ | ○ | ○ | ○ |
INSTEAD OF トリガ | ○ | ○ | ○ | ○ | × |
※1)
※2)
※3)
※4)
※5)
※6)
※7)
※8)
改訂の内容は、
新規追加された機能についても、
表1を見ていただいてもわかるように、
SQLは寿命の長い言語か?
40年間主流であり続けた
SQLは寿命の長い言語だとよく言われます。たしかに、
自らを大きく変化させてきたSQL
実はSQLの場合、
その大きな理由は、
そのため、
これは、
こうしたことから、
見た目上の
次の患者も、
カルテ2:図4のような製造業者のテーブルManufacturersを追加する。部品の要求をかけている製造業者と同じ地域

冗長さはコードをわかりにくくする
部品を要求している製造業者は、
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の標準化の中で最も画期的だったのは、
また、
ほかにも覚えておいたほうがいい新機能ってあります?
そうね、
あとは新機能というわけではないけど、
逆に、
えっ、
たとえばSQL:1999で導入された非スカラ型
標準化といえど完璧ではないんですね。
そうだな。SQLは言語の理論的基礎がかっちりしているから、
あ、
お、
普段は威張ってるくせに、
【参考資料】
- 1.ミック WEB+DB PRESS Vol.
63 連載 「SQL緊急救命室」 第2回 「冗長性症候群」 - 冗長なSQLコーディングの代表格が、
条件分岐をUNION (ALL) で表現してしまうことでした。これをCASE式で簡潔に記述する方法を紹介しています。gihyo. jp でも公開しております。 - 2.ミック
『SQL ゼロからはじめるデータベース操作』 翔泳社、 2010年 - 外部結合の標準構文、
CUBE/ ROLLUP、 ウィンドウ関数、 CASE式といったSQLの重要な機能の使い方の基礎を解説した書籍です。SQLを基礎から正しく学びたい方にお勧めです。 - 3.Wikipedia - SQL - Standardization
- SQLの標準化は数年単位で行われています。各改訂の内容は、
上記Wikipediaのサイトからたどるのが調べやすいでしょう。