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

DBの基礎 テーブルとは

CSSを学習したあとはデータベースの学習などを。 参考図書:「達人に学ぶDB設計指南書」基本的にはこれを読んでいきます。が、現状よくわからないところは飛ばしていきます・・・ Contents1 テーブ …

no image

netbeansのJPQL補助機能

JPAでは基本的に生のSQLではなく、JPQLを使って書きます。 SQLでもタイプミスにイライラさせられることは多いのですが、JPQLはもっとですね・・・爆 そこで使えるのがNetbeansの入力補助 …

no image

CASE式のすすめ

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

no image

JPAでのリレーション(アノテーション使用)

JPAでリレーションを管理するとき、最初はアノテーションでやろうとしたんですが、結局やり方がわからずコンストラクタ式をかいて対処してました。 JPAでのリレーションに関して 外部キー制約があるやり方は …

no image

SQLの高速化について&explain

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