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

MySQLのマイグレーション(workbench使用)

以前cakePHPにてマイグレーションの手法を紹介したのですが、当然PHP以外をつかっていたり、PHPでもcakeを使っていなければこの方法は通用しません。 何か、汎用的にデータベースの構造の差分がチ …

no image

HAVING句について NULL時の動き

HAVING句のNULLがあった時の挙動にたいしてメモリます。 COUNTの処理 対象上の列数を数えるのにCOUNTを使うと思いますが、COUNT(*)とCOUNT(列名)では動きが若干違っており、前 …

no image

Postgresの基礎(主にMySQLとの違いなど)

えー来月(2020年3月)より、postgresを使うかもしれないので、ちょっと復習を。 Contents1 以前のリンク2 基本コマンド比較2.1 超頻出系3 テーブル比較4 SELECT文5 do …

no image

Firebaseでのミニアプリデプロイ

KVS的なDBを使うかもしれず、firebaseの復習をしております。以前、firebaseのサンプルなどちょこちょこ動かしましたが、firebaseに登録をしただけでデプロイまではしておりませんでし …

no image

MySQLのメモリ設定

実務で結構サーバーをヘビーに使われるお客さんがいて、SQLが遅くなったりとしています。 なんとか対策しないといけないので、いろいろと調査をいます。 それで今回はMySQLのメモリ問題など。 MySQL …