skillup

技術ブログ

Database

自己結合のイメージ

投稿日:2016年11月5日 更新日:

前回に引き続き結合について考えます。

例えば下記のようなテーブルがあり、重複行を削除するとします。

1 りんご 50
2 みかん 100
3 みかん 100
4 みかん 100
5 バナナ 80

この場合、

下記のようなSQLを書けば削除できます。テーブルに関しては前エントリーを参照。

これはP1とP2を集合とみるとnameとpriceが同一なレコードがある場合、りんごとばななではともにSELECT句の中のidが1になり、条件が不等号でイコールを含まないので何も起こりません。

逆にみかんに関しては34が条件に合致するため削除されます。2つの図を紙で書いて組み合わせを考えるとわかりやすいかも・・

部分的に不一致なキーの検索

http://mickindex.sakura.ne.jp/database/db_support_sinan.html

この例にあるような部分的に不一致なキー検索ですが、family_idが一緒でなおかつ住所が違うものですので、下記のように書けます。

 ランキング

またさきほどのリンクページにあるような検索についても解説します。

Postgresなら下記のような分析計の関数(RANKなど)が使えるので非常に楽です。

ただしMySQLでは使えませんので、これをサブクエリを使って書くことになります。

自己結合のイメージ

「達人に学ぶSQL徹底指南書」には上記の結合を集合の考えを使って非常にわかりやすく書いてあります。

集合 自分(p1)の金額 自分より高い値段(p2) 自分より高い値段の個数
s0 100 0 0
s1 80 100 1
s2 50 100,80 2
s3 30 100,80,50 3

なお集合の考え方でいくとs3が最大でs0が最小となり同心円上の形になっているのがわかるかと思います。

結合でかくとさらにこれは下記のように書けます。

なお、わかりやすくするため金額の重複をはずし、果物の種類を減らします。具体的にはhttp://mickindex.sakura.ne.jp/database/db_support_sinan.htmlにすべてのっています。

結果が下記のようになります。

name | name
——–+——–
みかん |
ぶどう | みかん
ぶどう | スイカ
スイカ | みかん
レモン | みかん
レモン | ぶどう
レモン | スイカ

自己結合の注意点

自己結合に関しては下記のような注意点が必要です。

  • 非等値結合(<や>などの=以外の記号)と組み合わせる
  • GROUP BYと組み合わせると再帰的集合を作ることができる
  • 本当のテーブルが2つあると考えるとわかりやすい。
  • 集合で考える(図、表を使う)
  • 処理コストが高いので結合キーにはインデックスを貼る

-Database
-

執筆者:


comment

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

関連記事

no image

Postgresメモ(データベース、スキーマ、他pgadminなど)

postgresをちょこちょこ触っており、超基礎的な事に関してMySQLでは触れられていないネタなどについて。 以前のリンク Postgresの基礎(主にMySQLとの違いなど) Contents1 …

no image

データ構造の基礎知識 後編 木構造

データベースの学習をしていたときの復習です。 データ構造の基礎知識 前編 メモリとポインタ、配列と連結リスト データ構造の基礎知識 中編 ハッシュ 今回はもう少し複雑な「木構造」について考えてみます。 …

no image

NOT EXISTSの利用

引き続きNOT EXISTSの利用です。 思った以上に使えますね・・・がムズイ。 今回は下記のようなテーブル(seats)があるとします。 ケースとしては新幹線の座席番号で空は席が空いている状態、占は …

no image

正規化のデメリット

Contents1 正規化のデメリット2 本日のSQL 正規化のデメリット 正規化についていろいろ書いてきましたが、メリットもあればデメリットもあります。 メリットとしては データの不整合が起きにくい …

no image

外部結合について 行列変換

今回から外部結合について学習します。 これはCASE式でもやった集合の考え方が大事になってきます。 例えば下記のようなテーブルCoursesとがあるとします。 name | course &#8212 …