skillup

技術ブログ

Database

集合としてのSQL その2

投稿日:

本日も前回に引き続き集合としてのSQLに入っていきます。

完全一致のテーブル

下記のようなテーブル(supparts)があり、持っている部品が完全に一致する業者を求めます。

sup | part

———————————-+————————————–
A | ボルト
A | ナット
A | パイプ
B | ボルト
B | パイプ
C | ボルト
C | ナット
C | パイプ
D | ボルト
D | パイプ
E | ヒューズ
E | ナット
E | パイプ
F | ヒューズ

この場合だとAとC、BとDになります。

手順としてはまず自己結合にて組み合わせを作ります。

単なる結合条件としては、下記2点になります。

  • 自分は当然除く
  • パーツが同じもの

s1_sup | s1_part | s2_sup | s2_part
———————————-+————————————–+———————————-+————————————-
A | ボルト | B | ボルト
・・・・・・・・・・・・・・(略)
E | ヒューズ | F | ヒューズ
F | ヒューズ | E | ヒューズ
———————————-+————————————–+———————————-+————————————–

これで組み合わせが同一の組み合わせが判明しました。ここからGROUP BYして、数量を求めてみましょう。

s1_sup | s1_count | s2_sup | s2_count
———————————-+———-+———————————-+———-
A | 2 | B | 2
・・・・・・・・・・・・・・(略)
E | 1 | F | 1
F | 1 | E | 1

これでs1,s2ごとの数がでました。

あとはこの1つ1つの組み合わせの中からすべてを満たしているものを選びます。

例えばs1がAであればAの列数は3になりますので、s1_countが3のものがあればすべてを満たしていることになります。

これですと片側だけになってしまうのでs2側も求めます。

(例えば B | 2 | A| 2 はBからみるとみたしていますが、Aからは満たされていません。)

すでにGROUP BYしているのでHAVINGで抽出します。

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
こちらはサブクエリを使って重複行を抽出する方法があります。

ただサブクエリは遅いので下記のような方法もあります。

EXCEPT で全体のidの集合から後半のグルーピングされたidを取り除くと、重複のものだけがでてきます。

-Database
-

執筆者:


comment

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

関連記事

no image

HAVING句の活用 発展編

今回からはまたまたHAVING句です。 下記のようなテーブル(teams)があり、全員が待機状態のチーム(全員がそろっている)を求めます。 member | team_id | status &#82 …

no image

SQLの高速化について&explain

本日はSQLの高速化について。 高速化といってもさまざまなテクがあると思うのですが、代表的な考え方に関して。 Contents1 高速化に関して1.1 index1.2 ディスクアクセスを減らす1.3 …

no image

日付がらみの処理に関して(MySQL&Java)

MySQL触りだして3年ぐらいたつんですがいまだに整理できないことが多いです。(特に日付がらみ) ちょっとJavaのネタと合わせて整理しておこうかなーと思います。 Contents1 MySQLの日付 …

no image

自己結合に関して

以前もこのエントリーで学習しましたが、SQLの結合では自己結合という考え方があります。 下記のようなテーブルProductsがあるとします。 name | price ——&# …

no image

JavaでのSQLの書き方

ちょっと小ネタです。 通常SQLを記述する場合、縦に分けて書くのが見た目にも見やすく、保守性も高いです。 例として

と書くよりは、 [ …