前回に引き続き結合について考えます。
例えば下記のようなテーブルがあり、重複行を削除するとします。
1 | りんご | 50 |
2 | みかん | 100 |
3 | みかん | 100 |
4 | みかん | 100 |
5 | バナナ | 80 |
この場合、
下記のようなSQLを書けば削除できます。テーブルに関しては前エントリーを参照。
1 2 3 4 5 6 |
DELETE FROM Products p1 WHERE id < ( SELECT MAX(p2.id) FROM Products p2 WHERE p1.name = p2.name AND p1.price = p2.price ); |
これはP1とP2を集合とみるとnameとpriceが同一なレコードがある場合、りんごとばななではともにSELECT句の中のidが1になり、条件が不等号でイコールを含まないので何も起こりません。
逆にみかんに関しては34が条件に合致するため削除されます。2つの図を紙で書いて組み合わせを考えるとわかりやすいかも・・
部分的に不一致なキーの検索
http://mickindex.sakura.ne.jp/database/db_support_sinan.html
この例にあるような部分的に不一致なキー検索ですが、family_idが一緒でなおかつ住所が違うものですので、下記のように書けます。
1 2 3 4 5 6 7 8 |
SELECT a1.* FROM addresses a1, addresses a2 WHERE a1.family_id = a2.family_id AND a1.address <> a2.address |
ランキング
またさきほどのリンクページにあるような検索についても解説します。
Postgresなら下記のような分析計の関数(RANKなど)が使えるので非常に楽です。
1 2 3 4 5 6 7 |
SELECT name, price, RANK() OVER ( ORDER BY price DESC ) AS rank1, DENSE_RANK() OVER ( ORDER BY price DESC ) AS rank2 FROM Products; |
ただしMySQLでは使えませんので、これをサブクエリを使って書くことになります。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
SELECT p1.name, p1.price, --p1は自分 --p2が自己結合されるテーブルと考える ( SELECT --トップは0なので+1をする COUNT(p2.price) + 1 FROM Products p2 WHERE --自分以外でなおかつ、自分より安いもの --わからなければ具体値で p1.name <> p2.name --自分はp1相手はp2なので -- p2 > p1になる(求めたいのは自分より大きい相手なので) AND p2.price > p1.price ) AS rank_1 FROM Products p1 ORDER BY rank_1 asc ※なお、COUNT(DISTINCT(p2.price))と書けば順位が飛び石にならない |
自己結合のイメージ
「達人に学ぶSQL徹底指南書」には上記の結合を集合の考えを使って非常にわかりやすく書いてあります。
集合 | 自分(p1)の金額 | 自分より高い値段(p2) | 自分より高い値段の個数 |
s0 | 100 | 0 | 0 |
s1 | 80 | 100 | 1 |
s2 | 50 | 100,80 | 2 |
s3 | 30 | 100,80,50 | 3 |
なお集合の考え方でいくとs3が最大でs0が最小となり同心円上の形になっているのがわかるかと思います。
結合でかくとさらにこれは下記のように書けます。
なお、わかりやすくするため金額の重複をはずし、果物の種類を減らします。具体的にはhttp://mickindex.sakura.ne.jp/database/db_support_sinan.htmlにすべてのっています。
1 2 3 4 5 6 7 8 9 |
SELECT p1.name, p2.name FROM Products p1 LEFT JOIN Products p2 ON p1.price < p2.price |
結果が下記のようになります。
name | name
——–+——–
みかん |
ぶどう | みかん
ぶどう | スイカ
スイカ | みかん
レモン | みかん
レモン | ぶどう
レモン | スイカ
自己結合の注意点
自己結合に関しては下記のような注意点が必要です。
- 非等値結合(<や>などの=以外の記号)と組み合わせる
- GROUP BYと組み合わせると再帰的集合を作ることができる
- 本当のテーブルが2つあると考えるとわかりやすい。
- 集合で考える(図、表を使う)
- 処理コストが高いので結合キーにはインデックスを貼る