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

DBUtils活用

普段はJavaEEで開発を行っているのでデータベースへのアクセスはJPAを活用しています。 ただ、JavaEE環境だとglassfish経由でデータベースを動かしているので、glassfishを動かさ …

no image

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

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

no image

JPAでのリレーションに関して

JPAではテーブルをクラスで定義します。もちろん例外とかはいろいろあるのですが、1テーブル1クラスというつくりで、これをエンティティと呼びます。 もともとクラスを作ってからDBを作成したり、JTAの規 …

no image

第二、第三の正規化&ER図&Check制約

前回第一正規化を話したので、第二、第三に進んでいきます。 Contents1 第二正規化とは?2 第三正規化とは?3 正規化のメリット4 ER図5 本日のSQLネタ Check制約 第二正規化とは? …

no image

SQL基礎 case式について

case式に関して。 集約系の関数では複雑な処理を一気に行うことができる。 case式は1列のみ有効。複数の列に対して行うことはできない。 case ~ when ・・・thenではwhenが評価され …

アーカイブ