SQL の相関サブクエリ (3) - 量化された比較述語 ALL, ANY

「SQL の相関サブクエリ (2) – EXISTS 述語」 のつづき

量化

SQL をはじめて触ったとき、exists 述語と並んで直感的に理解しずらかったのが ALL や ANY を伴った比較。

WHERE 句において単体の値を比較するのは問題ないけれど、 「すべての XXXX と比べてどうか？」 とか、「少なくとも 1 つの XXXX と比べてどうなのか？」 となるとイメージしずらくなる。

量化 – Wikipedia によると、

全ての人間の言語は、完全な数体系がない場合でも、量化を利用している（Wiese 2004）。例えば、日本語での例は次の通りである。

• 「全ての方針に目を通す必要がある」
• 「川を渡っている人のうち何人かが白い腕章をしている」
• 「私が話した人々のほとんどが、誰に投票するか決めていなかった」
• 「待合室の誰もが小沢氏に対する少なくとも1つの不満を持っていた」
• 「クラスの誰かが、私の出した全ての問題に答えられるはずだ」
• 「多くの人々は賢明である」

これらを量化を使わずに、複数の文の論理和や論理積で表す単純な方法は存在しない。例えば、「Aの方針に目を通す必要がある」かつ「Bの方針に目を通す必要がある」……などと続くことになる。

日常会話の中でこれらの言葉を使うのに抵抗はない。しかし、SQL で書こうとすると難しく感じることがしばしば。特に相関サブクエリを利用する場合に。

 

ALL, ANY の仕様

ところで、SQL-92 における 「述語」 は "BNF Grammar for ISO/IEC 9075:1992 - Database Language SQL (SQL-92)" によると以下の 9 つが列挙されている。

<predicate>    ::=
<comparison predicate>
     | <between predicate>
     | <in predicate>
     | <like predicate>
     | <null predicate>
     | <quantified comparison predicate>
     | <exists predicate>
     | <match predicate>
     | <overlaps predicate>

上記の 「量化された比較述語」、

<quantified comparison predicate>    ::=   <row value constuctor> <comp op> <quantifier> <table subquery>

の一部として定義されているのが 「量化子」  ALL, SOME または ANY のキーワード。

<quantifier>    ::=   <all> | <some>

<all>    ::=   ALL

<some>    ::=   SOME | ANY

値を比較するための演算子 <comp op> とサブクエリ <table subquery> と共に用いられるのがわかる。

 

サブクエリの結果が固定されている場合

前回と同じデータベースを対象にして ALL, ANY の使用例を考える。

 

ALL 述語

例えば、

すべての男性よりも年齢の高い女性

を抽出したいとする。

すべての男性の年齢を取得するには、

select age
from persons
where gender = 1

上記で抽出された 「すべての年齢」 と比較するために使う述語が ALL 。 各々の 「人」 の年齢と 「すべての男性の年齢」 を比較する。

select *
from persons as p
where p.gender = 2 and
      p.age > all (select age
                   from persons
                   where gender = 1)

このサブクエリは、外側のクエリの値を参照していないので相関サブクエリではない。

上記の SQL を実行すると結果は、

+----+--------+--------+-----+
| id | name   | gender | age |
+----+--------+--------+-----+
|  6 | Sadayo |      2 |  70 |
+----+--------+--------+-----+
1 row in set (0.03 sec)

ただし、この場合 ALL 述語を使うよりも、素直に最大値と比較した方が効率は良いと思うけれど。

 

ANY 述語

次に、ALL と対照的な ANY 述語。

例として、

いずれかの男性よりも年齢の高い女性

を求める。

select *
from persons as p
where p.gender = 2 and
      p.age > any (select age
                   from persons
                   where gender = 1)

結果は、

+----+--------+--------+-----+
| id | name   | gender | age |
+----+--------+--------+-----+
|  2 | Hanako |      2 |  20 |
|  6 | Sadayo |      2 |  70 |
|  7 | Hiroko |      2 |  15 |
+----+--------+--------+-----+
3 rows in set (0.00 sec)

ALL, ANY いずれもサブクエリの値が固定されているので、割とイメージしやすい。

 

Haskell で all, any 述語を利用して書く

上記の SQL に類似したコードを Haskell で書くなら、

[p | p <- persons
   , gender p == 2
   , all (\p' -> age p > age p') 
         [p | p <- persons
            , gender p == 1]]

( cf. gist: 645292 - GitHub )

[p | p <- persons
   , gender p == 2
   , any (\p' -> age p > age p') 
         [p | p <- persons
            , gender p == 1]]

( cf. gist: 645292 – GitHub )

 

相関サブクエリにより要素ごとに結果が異なる場合

次は、要素によってサブクエリの結果が異なる場合を考える。

例えば、

同性の中で最高齢の 「人」

を求めたいとする。

もし、ALL 述語を使うなら、

1. 抽出したいのは、とある 「人」なんだけれど、
2. その 「人」 はすべての同性の年齢以上である

というように意識して書けばよい。

select *
from persons as p1
where p1.age >= all (select age
                     from persons as p2
                     where p2.gender = p1.gender)

ここで注目するのは、外側のクエリにおける persons テーブルを指し示す名前 p1 と、サブクエリで persons テーブルを指し示す名前 p2 。 同じテーブルに対して別名を付与している。

この意味は下図のように、p1 で指し示す 「人」 の要素に対して、p2 で指し示す同じく 「人」 の要素を組み合わせて評価するということ。

今回の場合は 「同性」 が条件なので、組み合わせの抽出条件として gender を用いている。

結果は、

+----+---------+--------+-----+
| id | name    | gender | age |
+----+---------+--------+-----+
|  4 | Saburou |      1 |  40 |
|  6 | Sadayo  |      2 |  70 |
+----+---------+--------+-----+
2 rows in set (0.09 sec)

 

Haskell で書くと…

毎度、Haskell で類似したコードを書くなら、

[p1 | p1 <- persons
    , all (\p -> age p1 >= age p)
          [p2 | p2 <- persons
              , gender p2 == gender p1]]

( cf. gist: 645292 – GitHub )

 

集約関数 max を使う場合

素直に最高齢を求めてから考えるなら、

select gender, max(age)
from persons as p
group by gender

により、性別ごとの最高年齢が得られるのを確認し、

+--------+----------+
| gender | max(age) |
+--------+----------+
|      1 |       40 |
|      2 |       70 |
+--------+----------+
2 rows in set (0.09 sec)

この結果と各々の 「人」 を比較して、gender と max(age) が一致する人を抽出すればいいので、

select *
from persons as p1
where exists (select *               
              from (select gender, max(age) as mage
                    from persons as p
                    group by gender) as m
              where m.gender = p1.gender and
                    m.mage   = p1.age)

しかし、全体としてはわかりずらいし、相関が上手く使えておらず冗長な感じ。 (+_+)

そこで、先ほど ALL で求めたときの真似をして次のように変更。

select *
from persons as p1
where age = (select max(p2.age)
             from persons as p2
             where p2.gender = p1.gender)

これでシンプルになった。

各々の要素に述語を適用するとき、比較対象をその要素の属性を用いて限定するということを意識すればよい。

 

Haskell で…

先ほどとほとんど変わらないけれど、Haskell で類似したコードを書くなら、

[p1 | p1 <- persons
    , age p1 == maximum [age p2 | p2 <- persons
                                , gender p2 == gender p1]]

( cf. gist: 645292 – GitHub )

「SQL の相関サブクエリ (4) - SELECT 句で使う」 につづく

参考サイト

• SQL – Wikipedia
• SQL-92 - Wikipedia
• SQLアタマアカデミー：第9回 SQLでループ！ 相関サブクエリの使い方