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のレプリケーションについて

MySQLのレプリケーションについて調べたことなどを。 Contents1 レプリケーションの基礎知識1.1 定義1.2 構成1.3 メリット1.3.1 参照性能の向上1.3.2 可用性の向上1.4 …

no image

浮動小数点に関して

金額計算なんかでfloatを使うと誤差が出るっていうのは基礎的な話ではありますが、背景知識を含めて理解しておこうと思ったのでメモります。 Contents1 float,doubleでの誤差2 金額の …

no image

日付がらみの処理に関して(MySQL&Java)

MySQL触りだして3年ぐらいたつんですがいまだに整理できないことが多いです。(特に日付がらみ) ちょっとJavaのネタと合わせて整理しておこうかなーと思います。 Contents1 MySQLの日付 …

no image

MySQL safe mode

MySQLに関してしっかりパスワードをチェックしていれば問題ありませんが、中にはrootパスワードをわすれた!なんてこともあるでしょう。 そんなときはsafe modeで実行することでrootのパスワ …

no image

論理設計のグレーノウハウ サロゲートキー

前回まではアンチパターンやバッドノウハウについて学習してきましたが、今回はグレーノウハウについて特集します。 グレーノウハウとは読んで字のごとくホワイトともブラックとも言えないという手法ですね。 つま …

アーカイブ