ランキングを取得するSQL
ミックさんの本を最近ちょっとずつ読み進めています。
達人に学ぶ SQL徹底指南書 (CodeZine BOOKS)
- 作者: ミック
- 出版社/メーカー: 翔泳社
- 発売日: 2008/02/07
- メディア: 単行本（ソフトカバー）
- 購入: 54人 クリック: 1,004回
- この商品を含むブログ (78件) を見る
例えば以下の表データがあった場合、
+-----------+-------+ | name | price | +-----------+-------+ | りんご | 50 | | みかん | 100 | | ぶどう | 50 | | スイカ | 80 | | レモン | 30 | | バナナ | 50 | +-----------+-------+
価格の高い順にランキングを生成したければ相関サブクエリを使って以下のようにできます。
select P1.name, P1.price,
(select count(P2.price)
from products P2
where P2.price > P1.price) + 1 as rank_1
from
products P1
order by rank_1
;
/*
+-----------+-------+--------+
| name | price | rank_1 |
+-----------+-------+--------+
| みかん | 100 | 1 |
| スイカ | 80 | 2 |
| りんご | 50 | 3 |
| ぶどう | 50 | 3 |
| バナナ | 50 | 3 |
| レモン | 30 | 6 |
+-----------+-------+--------+
6 rows in set (0.00 sec)
*/
相関サブクエリは他の言語の「ループ」に似ている
上記SELECT文ではベースとなるproductsテーブルの各行に対して、COUNTを別々に取って結果を返しています。
python風に書くとこんな感じでしょうか。
for p in products:
rank = count(p.name, products)
print(p.name, p.rank)相関サブクエリの「相関」の意味をネットの辞書で引いてみたところ、
相関とは
http://www.weblio.jp/content/%E7%9B%B8%E9%96%A2
二つのものの間に関連があること。互いに影響し合うこと。 「―する二つの現象」
つまり、上のSQLだとメインクエリとサブクエリが互いに関係し合っているので、「相関」というらしいです。
僕が書いた擬似pythonでも、ループを回しているproductsというシーケンスを、ループの中でcount関数の引数に渡していますよね。
汎用言語をやっていてSQLを触るとループみたいなことをしたいときにどうすればいいか分からなくなってしまうけど、こうすればいいんですね！
ちなみに、ミックさんはこんなふうに説明しています。
SQLアタマアカデミー -- 第9回 SQLでループ！ 相関サブクエリの使い方〜切れ過ぎるナイフにご用心〜 （1）サブクエリ
http://gihyo.jp/dev/serial/01/sql_academy2/000901
条件分岐のためのCASE式に対して， ループのための道具はそれほど十分に解説してこなかったかもしれません。 そこで今回は，SQLでループを記述する方法を中心に解説したいと思います。 そのための中心的な道具がサブクエリ， 特に2つのテーブルを関係づけて使う相関サブクエリ（co-related subquery）です。
なるほど、if文はcase式で、相関サブクエリはfor文なんですね！
同じことを自己結合でも実現できる
LEFT OUTER JOINを使っても同じことを実現できます。
SQLはこうなります。
select
P1.name, max(P1.price), count(P2.name) + 1 as rank_1
from
products P1 LEFT OUTER JOIN products P2
on P1.price < P2.price
group by
P1.name
order by
rank_1
;
/*
+-----------+---------------+--------+
| name | max(P1.price) | rank_1 |
+-----------+---------------+--------+
| みかん | 100 | 1 |
| スイカ | 80 | 2 |
| ぶどう | 50 | 3 |
| りんご | 50 | 3 |
| バナナ | 50 | 3 |
| レモン | 30 | 6 |
+-----------+---------------+--------+
6 rows in set (0.00 sec)
*/これは、それぞれのレコードは集約関数(max, count)によって集約された結果です。
だから、元々はばらばらの集合です。
集約しないで結果を出してみるとわかります。
select
P1.name, P1.price, P2.name, P2.price
from
products P1
left outer join products P2
on P1.price < P2.price
order by
P1.name
;
/*
+-----------+-------+-----------+-------+
| name | price | name | price |
+-----------+-------+-----------+-------+
| ぶどう | 50 | スイカ | 80 |
| ぶどう | 50 | みかん | 100 |
| みかん | 100 | NULL | NULL |
| りんご | 50 | スイカ | 80 |
| りんご | 50 | みかん | 100 |
| スイカ | 80 | みかん | 100 |
| バナナ | 50 | みかん | 100 |
| バナナ | 50 | スイカ | 80 |
| レモン | 30 | りんご | 50 |
| レモン | 30 | バナナ | 50 |
| レモン | 30 | スイカ | 80 |
| レモン | 30 | ぶどう | 50 |
| レモン | 30 | みかん | 100 |
+-----------+-------+-----------+-------+
13 rows in set (0.00 sec)
*/ランキングの順位が低いデータの集合は、高い順位の集合を含んでいます。
最下位の「レモン」は「バナナ」や「りんご」の集合を含み、それらはより上位の集合を含んでいます。
つまり、トップの「みかん」の集合が真ん中にあって、ランクの低い集合がその周りを囲むように広がっているってイメージです。
ちょっと難しい話かもしれないけど、最初に掲載しておいた本を読むと図と式を使ってとても分かりやすく書かれていますよ☆
