SQL 嫌い
SQL は昔から嫌い。そのため、C.J.Date の 「データベース実践講義」 でバッサリと斬り捨てている様はあまりにも心地良く、SQL 批判を全部箇条書きに抜き出そうと考えたけれど、あまりの量に断念した。
複雑で長い SQL は読みにくいし、保守しずらい。パフォーマンスに問題がない限り SQL はできるだけシンプルに保ち、やっかいなことは直感的な理解が可能なアプリケーション層で対応したい。
しかし、自分が苦手だったのは、集合的で宣言的なものの考え方であり、C.J.Date が SQL に対して批判しているタプルの重複や NULL の問題とは無関係だったことに気がついたのは後のこと。
相関サブクエリとは
特に理解しずらかったのが 「相関サブクエリ」 。「相関」と言えば 「相関係数」 をすぐに連想してしまうので余計に「何のこっちゃ？」と感じた。
相関サブクエリとは、Correlated subquery – Wikipedia によると、
It is a sub-query (a query nested inside another query) that uses values from the outer query in its WHERE clause. The sub-query is evaluated once for each row processed by the outer query.
つまり、どんなクエリかと言えば、
- WHERE 句の中で、外側のクエリの値を利用するサブクエリ。
- 外側のクエリの各行ごとにサブクエリが評価される。
この動作を、クエリの対象として表をイメージし、各々の行ごとに順にサブクエリが実行される様をループとして頭の中に描くと、それは手続き的な見方であり、宣言的な理解からかけ離れてしまう。
以前これを誤解し、「何だかんだ言っても、実行の順序を考えなくてはいけないのか…」 と思っていた。しかし、概念的に言えば、各行のサブクエリが実行される順序に意味はなく、集合の要素として同時に計算が行われる。いや、そもそも同時であるかないか考えること自体お角違い。実際の具体的な計算の過程は実装が考えることであって、リレーショナル代数を利用する側が関心を持つことではない。
データベースのサンプル
例えば、「人」 を 「グループ」 に 「割当て」 るモデルを仮定する。
![CropperCapture[3]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_vBxqVMyPraUL9V_KB18iaYSFbIlzf9w8cm4-Q3pHO0wnglEw6MKfQsh0Duivao3fuM8LB74xJ9otZKxr9MZ_jJwBUEoJ5nQ-CUohjzCqkTkwjVZkwYMjNm7WzPXTci5g=s0-d)
ERD では下図の通り。 ( MySQL Workbench を利用。 )
![CropperCapture[7]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_tKYqbhXXyRS1-frvYJfwbcay_f_stU0j0s1JckwB-QOeEfIyFtCEULnRiSOU4fQfOdyImhJdqbS-tCriaf-congNwC08ETIAfDTWrfSuUHXrHwewmCR-cateYSjljze9g=s0-d)
各々のテーブルの値を以下の表に示す。
( cf. ODBC 経由でデータベースにデータを挿入 )
表だと見にくいのでオブジェクト図を示しておく。
![CropperCapture[2]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_uguysz49IEJut2p3Bphe_DtaIz5MPwSWGA7p6sPFZbQKPGBxLIdd9Th41yoC4C2Lezqc_JluJGbomg0iP3zuAjz-JkCosbbvvwdxQULyVlLooV-_u7JeMJpwL7FqPEmA4=s0-d)
相関サブクエリの例
上記のデータベースに対して、以下の問合せをしたい。
2010 年に 「グループ」 に 「割当て」 られた 「人」 は？
相関サブクエリを使わない場合
ここで最初に頭の中に思い浮かんだのは、
- 「割当て」 の日付を見て、2010 年のタプルを抽出
- 上記 1 のタプルに 「人」 を結合。
- ただし、同じ 「人」 が重複しないように気をつける。
という方法。
具体的なクエリは、
select distinct p.*
from (select *
from assignments
where year(date) = 2010) as a
inner join persons as p on p.id = a.p_id
実行すると結果は、
+----+-------+--------+-----+
| id | name | gender | age |
+----+-------+--------+-----+
| 1 | Tarou | 1 | 10 |
| 3 | Jirou | 1 | 30 |
+----+-------+--------+-----+
2 rows in set (0.04 sec)
このクエリは、 where 句の中で外側のクエリの値を利用していないので、相関サブクエリではない。
相関サブクエリを使う場合
相関サブクエリを利用してクエリを書くなら、
select *
from persons as p
where 2010 in (select year(a.date)
from assignments as a
where p.id = a.p_id)
この書き方は、
- 抽出したいのは、とある 「人」 なんだけれど、
- その人は、 2010 年に 「グループ」 に 「割当て」 られている。
ということを意識している。
先に 「割当て」 に目を付けるのではなく、 各々の「人」 を中心に考えているところが先ほどと違う点。しかし、当初この書き方がしっくり来なかった。というか where 句の部分をどのようにイメージすればいいのかわからなかった。
WHERE 句は集合の要素に対して条件を指定している
ところで、
性別が男性である 「人」
を抽出する SQL はシンプルでわかりやすい。
select *
from persons
where gender = 1
where 句は 「男性である」 という条件を素直に表現していると感じる。
where gender = 1
これに対し、先ほどの where 句はシンプルとは言い難い。
where 2010 in (select year(a.date)
from assignments as a
where p.id = a.p_id)
特に相関サブクエリにおける where 句の p, a をどのように考えればいいのだろう？
Haskell で類似したコードを書くと …
まずは、
性別が男性である 「人」
を抽出する SQL と類似したコードを Haskell で考えてみる。
data Person = P { p_id :: Int
, name :: String
, gender :: Int
, age :: Int
} deriving Show
persons = [ P 1 "Tarou" 1 10
, P 2 "Hanako" 2 20
, P 3 "Jirou" 1 30
, P 4 "Saburou" 1 40]
main = print $ filter (\p -> gender p == 1) persons
特定の 「人」 を抽出するための filter 関数の第1引数の述語が、 SQL の where 句の 「条件」 に相当する。言うまでもなく、この述語の引数 p は各々の 「人」 を表わし、それぞれの人ごとに検査が行われる。
比較すると、SQL ではこの p に相当するものが関数の引数の如く明示されていない。
where gender = 1
これにより、where 句が要素を抽出するための述語であることをつい忘れてしまう。
相関サブクエリに類似したものを Haskell で …
次に、
2010年に 「グループ」 に 「割当て」 られた 「人」 は？
の問い合わせを実装することを考える。
全体の形としては、 persons の中から条件に合った人を抽出すればいいので、
filter (\p -> ... ) persons
各々の 「人」 については、SQL の相関サブクエリで書いたコードよろしく、 値 2010 がある集合の要素に含まれるかどうかを調べれば良さげなので、
filter (\p -> 2010 `elem` ... ) persons
ここで上記 ... の中に注目。 各々の 「人」 に対して 「グループ」 への 「割当て」 を調べたいので、その中身は以下のようになるはず。
filter (\a -> ... ) assignments
「割当て」 の抽出条件は、外側の filter で指定されている 「人」 になるので、外側の変数 p を参照する。
filter (\a -> p_id p == a_p_id a) assignments
後は値 2010 と比較できるように要素を変換。
全体を示すと以下の通り 。
filter (\p -> 2010 `elem`
map (\a -> case toGregorian (date a) of
(y,_,_) -> y)
(filter (\a -> p_id p == a_p_id a)
assignments))
persons
( cf. gist: 637741 - GitHub )
上記をいきなり見たらわかりやすいとは言い難いけれど、少なくとも変数 p と a が指し示しているものが何であるのかはっきりとする。SQL の where 句は集合の要素に対する述語。要素を参照する変数が明示的に述べられていないが、上記のコードと比較すると相関サブクエリでなぜあのように書くのかイメージしやすくなった。
リスト内包表記
ついでなので、上記をリスト内包表記で書くなら、
main = print [p | p <- persons
, 2010 `elem` [case toGregorian (date a) of
(y,_,_) -> y | a <- assignments
, p_id p == a_p_id a]]
( cf. gist: 637741 - GitHub )
モナド
余談ながら、モナドの bind で書くにはどうすればいいのかな？
直積を作って、後で重複を取り除くという方法で考えるなら、
main = print $ nub $
persons >>= \p ->
assignments >>= \a ->
guard (p_id p == a_p_id a &&
case toGregorian (date a) of
(y,_,_) -> y == 2010) >>
return p
( cf. gist: 637741 – GitHub )
上記を do 式で書き直すと、
main = print $ nub $ do
p <- persons
a <- assignments
guard (p_id p == a_p_id a &&
case toGregorian (date a) of
(y,_,_) -> y == 2010)
return p
( cf. gist: 637741 – GitHub )
「SQL の相関サブクエリ (2) – EXISTS 述語」 につづく。
![CropperCapture[1]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_tzBwruOvpgsCkiRWnEhP_LFHTp6jJjMUsgpwXWyMNIBbPSEWBLyg07R8eGmAHHyG6jSbaN6uFkwejgdEWV0qBIu-D0nh30SCL1vAf774gypXHUQCeF0VU5NI0YbSVYqQ=s0-d)
![210-31-2010CropperCapture[2]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_uGAyeIrQCxUKlBk5t3s2chnd_Z9oxpTpn0OjWuIWQb45L7Ka5vdS7Dc81Mmkvvze4RXOyNV1tYE1PWdexWKObedj-_a242uXyBzec_o_Dh8Qrl3z3q2KuZiy198MwVTJM=s0-d)
![CropperCapture[5] - 2](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_uyTz8VQ0HYYALVVQU8vFsgwes0DYeqTtdh7A0GCcqK8WmXz_28unHRzbK0w6WLpFwsHKtBnY49YTipaKN-003ylmeQOk3ERAXr6bb9MBzKJhh8G9AT8YvFYCnrw_kLQII=s0-d)
![CropperCapture[3]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_sZR39zpr7UU6y90JMX4y0uJfGNTeoCHjKllNDNKR153RF4DbzWarb-vpTY0SmitbgUNN5EBQ7mPo1cawl8l_Sud7hLpNUpuoiOVWnX7N6ItPVFj-urnoaY32wyd0FyL4c=s0-d)
![CropperCapture[4]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_s6MZdIVuwDloocHyueocP7qNOQbsdlCgUweFWISHCFDiQs0R4Ro9njqhi3UpOupsc8aeMv2D65pG7uR4uzyGP39ImOHqovS31aS3IRRQD__Rx_6Xg4cFjbaoju3vgWyw=s0-d)
![CropperCapture[5]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_upZks9YYNWMkbjzUQrd1bOEsWfyAKUd5_8xAivuzN7Dmu37MkzUKNEGZnUhIInuW3Yh-PoRMK7tgI7bKtUhz9dtfzY8is6TZ7dI6e7VJdglkmUMRYTD94Bq4dbadUGCq0=s0-d)
![CropperCapture[6]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_vGNj2_Au653A7LRHW7wq6Zk2p-abma3bu0kM5p4o7HHiTrx23mufFCSnEKm3C5LPQ0uc5RbiAzcbJfNkxSGEay-Uh2GesIdrFvG6_TWRZKDPlC3taAhFPduWda3CI0vio=s0-d)
![CropperCapture[2]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_svGdA0JzzT2PsQh4kX5xvX6HwNyA2Iq8m0JV5KWZPA7CG9SwSjr4VvnkiXIUNljdwbi2_jR_ihzBIJ6JcNq3JlZgE46hBP9-NoTAouJaFcTVN6XYVDvBhLgsAUD2Q7E9M=s0-d)
![CropperCapture[2]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_vAcUUyZ_BFgeoj0T5FukFHMdrOShTf2Xvp5Jj4CUHL_1XROs2iNs0_TEn9Ie7t6x9O4w-36WkQq6_6UlMTFjFxEHKEvGJ5AgI7byCmiqsrRLL7KsHXq6rpKZc17LtEJzY=s0-d)
![img [2]](https://lh3.googleusercontent.com/blogger_img_proxy/AEn0k_u8uB2c4dZNtKe0UtUJ4JHkv366XhMh3BqWL3aZSBrWfynPC7X5H_k8lojQbXTQ73uC4rWp4h0kxH-laVI6w_K8ESoVvWoOlYhSznPM_wEBdylAriiK6mgv8s4BDA2dbAU=s0-d)
