SQL の相関サブクエリ – filter 関数から考える

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 句の中で、外側のクエリの値を利用するサブクエリ。
• 外側のクエリの各行ごとにサブクエリが評価される。

この動作を、クエリの対象として表をイメージし、各々の行ごとに順にサブクエリが実行される様をループとして頭の中に描くと、それは手続き的な見方であり、宣言的な理解からかけ離れてしまう。

以前これを誤解し、「何だかんだ言っても、実行の順序を考えなくてはいけないのか…」 と思っていた。しかし、概念的に言えば、各行のサブクエリが実行される順序に意味はなく、集合の要素として同時に計算が行われる。いや、そもそも同時であるかないか考えること自体お角違い。実際の具体的な計算の過程は実装が考えることであって、リレーショナル代数を利用する側が関心を持つことではない。

 

データベースのサンプル

例えば、「人」 を 「グループ」 に 「割当て」 るモデルを仮定する。

ERD では下図の通り。 ( MySQL Workbench を利用。 )

各々のテーブルの値を以下の表に示す。

( cf. ODBC 経由でデータベースにデータを挿入 )

表だと見にくいのでオブジェクト図を示しておく。

 

 

相関サブクエリの例

上記のデータベースに対して、以下の問合せをしたい。

2010 年に 「グループ」 に 「割当て」 られた 「人」 は？

 

相関サブクエリを使わない場合

ここで最初に頭の中に思い浮かんだのは、

1. 「割当て」 の日付を見て、2010 年のタプルを抽出
2. 上記 1 のタプルに 「人」 を結合。
3. ただし、同じ 「人」 が重複しないように気をつける。

という方法。

具体的なクエリは、

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)

この書き方は、

1. 抽出したいのは、とある 「人」 なんだけれど、
2. その人は、 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 述語」 につづく。