本日も前回に引き続き集合としてのSQLに入っていきます。
Contents
完全一致のテーブル
下記のようなテーブル(supparts)があり、持っている部品が完全に一致する業者を求めます。
sup | part
———————————-+————————————–
A | ボルト
A | ナット
A | パイプ
B | ボルト
B | パイプ
C | ボルト
C | ナット
C | パイプ
D | ボルト
D | パイプ
E | ヒューズ
E | ナット
E | パイプ
F | ヒューズ
この場合だとAとC、BとDになります。
手順としてはまず自己結合にて組み合わせを作ります。
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT s1.sup AS s1_sup , s1.part AS s1_part , s2.sup AS s2_sup, s2.part AS s2_part FROM supparts s1 , supparts s2 WHERE s1.part = s2.part and s1.sup <> s2.sup ORDER BY s1_sup ASC , s2_sup ASC |
単なる結合条件としては、下記２点になります。
- 自分は当然除く
- パーツが同じもの
s1_sup | s1_part | s2_sup | s2_part
———————————-+————————————–+———————————-+————————————-
A | ボルト | B | ボルト
・・・・・・・・・・・・・・(略)
E | ヒューズ | F | ヒューズ
F | ヒューズ | E | ヒューズ
———————————-+————————————–+———————————-+————————————–
これで組み合わせが同一の組み合わせが判明しました。ここからGROUP BYして、数量を求めてみましょう。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT s1.sup AS s1_sup , COUNT( s1.part) AS s1_count , s2.sup AS s2_sup , COUNT( s2.part) AS s2_count FROM supparts s1 , supparts s2 WHERE s1.part = s2.part and s1.sup <> s2.sup GROUP BY s1.sup , s2.sup ORDER BY s1_sup ASC , s2_sup ASC |
s1_sup | s1_count | s2_sup | s2_count
———————————-+———-+———————————-+———-
A | 2 | B | 2
・・・・・・・・・・・・・・(略)
E | 1 | F | 1
F | 1 | E | 1
これでs1,s2ごとの数がでました。
あとはこの１つ１つの組み合わせの中からすべてを満たしているものを選びます。
例えばs1がAであればAの列数は３になりますので、s1_countが3のものがあればすべてを満たしていることになります。
これですと片側だけになってしまうのでs2側も求めます。
(例えば B | 2 | A| 2 はBからみるとみたしていますが、Aからは満たされていません。)
すでにGROUP BYしているのでHAVINGで抽出します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT s1.sup AS s1_sup , COUNT( s1.part) AS s1_count , s2.sup AS s2_sup , COUNT( s2.part) AS s2_count FROM supparts s1 , supparts s2 WHERE s1.part = s2.part and s1.sup <> s2.sup GROUP BY s1.sup , s2.sup HAVING COUNT( s1.part) = ( SELECT COUNT(s3.*) FROM supparts s3 WHERE s3.sup = s1.sup ) AND COUNT( s2.part) = ( SELECT COUNT(s3.*) FROM supparts s3 WHERE s3.sup = s2.sup ) |
s1_sup | s1_count | s2_sup | s2_count
———————————-+———-+———————————-+———-
A | 3 | C | 3
C | 3 | A | 3
D | 2 | B | 2
B | 2 | D | 2
ちなみにA→CとC→Aが両方出ますが、片方だけでいい場合はWHEREにs1.sup > s2.supをいれれば重複はしません。
重複行の削除
下記のテーブルで重複行を削除するSQLを書いてみましょう。
rowid | name | price
———————+——-
1 | りんご | 50
2 | みかん | 100
3 | みかん | 100
4 | みかん | 100
5 | バナナ | 80
こちらはサブクエリを使って重複行を抽出する方法があります。
|
1 2 3 4 5 |
DELETE FROM products p1 WHERE p1.rowid < ( SELECT MAX(p2.rowid) FROM products p2 WHERE p2.name = p1.name ) |
ただサブクエリは遅いので下記のような方法もあります。
|
1 2 3 4 5 6 |
DELETE FROM products WHERE rowid IN ( ( SELECT rowid FROM products ) EXCEPT ( SELECT MAX(rowid) FROM products GROUP BY name,price ) ) |
EXCEPT で全体のidの集合から後半のグルーピングされたidを取り除くと、重複のものだけがでてきます。
