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

CASE式のすすめ

SQL実践入門を7割がたぐらい終えたところで同著者の方の「達人に学ぶSQL徹底指南書」を学習しようと思っています。 この方の著書は気づいたら結構読んでましたね。 Contents1 CASE式での注意 …

no image

CASE式のすすめ その2

本日も「達人に学ぶSQL徹底指南書」を地道に進めていきます。 Contents1 CASE式の利用2 UPDATE文のCASE3 テーブル同士のマッチング CASE式の利用 私自身はCHECK制約を使 …

no image

CASE式のすすめ その3

本日もCASE式です。 下記のようなテーブル(studentclub)があるとします。 std_id | club_id | club_name | main_club_flg —&#82 …

no image

sql_modeに関して

開発では動いていたのに本番ではint型のカラムで空白が入らない!みたいなエラーが出て、データベースにデータが入らないことが発覚し、調査をすることに・・・ sql_modeが原因でした・・・ sql_m …

no image

論理設計のグレーノウハウ サロゲートキー

前回まではアンチパターンやバッドノウハウについて学習してきましたが、今回はグレーノウハウについて特集します。 グレーノウハウとは読んで字のごとくホワイトともブラックとも言えないという手法ですね。 つま …