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

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

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

no image

データベースアンチパターン・グレーパターンまとめ

本で勉強したものと自分で個人的に経験したことのまとめ Contents1 値渡しと参照渡しの混同2 型の制約が弱い3 変更していいデータ、していけないデータ4 参照系と履歴系のデータを区別する5 リレ …

no image

JPQLでの算術関数

複雑なJPQLを書いていると、通常のレコードの取り出しだけではなく、合計(SUM)や算出(COUNT)などのいわゆる算術関数を使うことが一般的です。 JPQLでもこれらを通常通り扱うことができます。 …

no image

GlassFishでDB接続

一般的にWEB系の言語で、DBを使う場合、WEBサーバーとDBサーバーは単独に動くことが一般的です。 JavaEEではアプリケーションサーバーとしてGlassFishを使いますが、先日、GlassFi …

no image

persistence.xmlのプロパティについて

JavaEEではデータベースとの設定情報はpersistence.xmlに記述します。 (ユーザー名、パスワード、ポート、driver名、データベース名などの情報はglassfish-resource …