SQL の相関サブクエリ (4) - SELECT 句で使う

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

これまでは WHERE 句において相関サブクエリを利用する例を見た。今回は SELECT 句で使う。

前回と同じデータベースを対象にして

例えば、

性別ごとに年齢の順位

を求めたい。

 

SELECT 句で相関サブクエリ

… とは言ったものの、いきなり書こうと思っても頭が混乱するのでゆっくりと。

まずは、「人」の属性に 「全体の人数」 を加えた結果を取得する。

select *, 
       (select count(*) 
        from persons) as num
from persons as p1

結果は、

+----+---------+--------+-----+------+
| id | name    | gender | age | num  |
+----+---------+--------+-----+------+
|  1 | Tarou   |      1 |  10 |    7 |
|  2 | Hanako  |      2 |  20 |    7 |
|  3 | Jirou   |      1 |  30 |    7 |
|  4 | Saburou |      1 |  40 |    7 |
|  5 | Akemi   |      2 |   8 |    7 |
|  6 | Sadayo  |      2 |  70 |    7 |
|  7 | Hiroko  |      2 |  15 |    7 |
+----+---------+--------+-----+------+
7 rows in set (0.00 sec)

次に、性別ごとに人数のトータルを求めるため、相関サブクエリを使う。

方法は、

1. 各々の 「人」 ごとにその性別を同じくする集合を求め
2. その要素数を得る。

頭に思い浮かべたのは下図。

select *, 
       (select count(*) 
        from persons as p2
        where p2.gender = p1.gender) as num
from persons as p1

結果は、

+----+---------+--------+-----+------+
| id | name    | gender | age | num  |
+----+---------+--------+-----+------+
|  1 | Tarou   |      1 |  10 |    3 |
|  2 | Hanako  |      2 |  20 |    4 |
|  3 | Jirou   |      1 |  30 |    3 |
|  4 | Saburou |      1 |  40 |    3 |
|  5 | Akemi   |      2 |   8 |    4 |
|  6 | Sadayo  |      2 |  70 |    4 |
|  7 | Hiroko  |      2 |  15 |    4 |
+----+---------+--------+-----+------+
7 rows in set (0.00 sec)

最後に、順位を求めるために各々の 「人」 要素において、

• 自分の年齢よりも小さい人の人数をカウントして 1 を足す

ついでなので性別ごとに昇順に表示。

select *, 
       (select count(*) + 1
        from persons as p2
        where p2.gender = p1.gender and
              p2.age    < p1.age) as rank
from persons as p1
order by gender, age

結果は、

+----+---------+--------+-----+------+
| id | name    | gender | age | rank |
+----+---------+--------+-----+------+
|  1 | Tarou   |      1 |  10 |    1 |
|  3 | Jirou   |      1 |  30 |    2 |
|  4 | Saburou |      1 |  40 |    3 |
|  5 | Akemi   |      2 |   8 |    1 |
|  7 | Hiroko  |      2 |  15 |    2 |
|  2 | Hanako  |      2 |  20 |    3 |
|  6 | Sadayo  |      2 |  70 |    4 |
+----+---------+--------+-----+------+
7 rows in set (0.00 sec)

 

Haskell で書く

Haskell で類似したコード書いてみる。

順に考えるなら、最初は全体の人数を結果に追加。

[(p1, length [p2 | p2 <- persons]) | p1 <- persons]

次に、性別ごとに人数のトータルを追加。

[(p1, length [p2 | p2 <- persons
                 , gender p2 == gender p1]) | p1 <- persons]

最後に順位を求める。

[(p1, 1 + length [p2 | p2 <- persons      
                     , gender p2 == gender p1
                     , age p2 < age p1]) | p1 <- persons]

( cf. gist: 645292 – GitHub )

「SQL の相関サブクエリ (5) – forall (∀) の exists (∃) への読み替え」へつづく