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

Firebaseについて

前回Lambdaに少し触れましたが、2019年6月現在、サーバーレスなアプリというものが活況(?)のようです。 大規模なアプリというと Webサーバー+RDB+サーバーサイドプログラミング言語 が必須 …

no image

CASE式のすすめ その3

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

no image

アンチパターン バインド変数の未使用+直積組み合わせ+データ量爆発+インデックス関連

本日はSQLコーディングに関して。 ここら辺は実際にプログラムを書く際に重要になってくるネタ。 Contents1 バインド変数1.1 デメリット1.2 対策2 直積により組み合わせが爆発する2.1 …

no image

ajaxのasync:falseと複数DBのjoinに関して

本日も小ネタ集です。 Contents1 ajax:false2 複数DBでのJOIN ajax:false ajaxは非同期処理が基本ですが、async:falseとすると同期処理になります。 ただ …

no image

NULLについて

「達人に学ぶSQL徹底指南書」を読み進めていきましたがNULLについていろいろ書いてあるのでメモリます。 Contents1 NULLには未知と適用不能の2パターンがある2 = NULL が使えず I …