自己結合の使い方

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

　次のような住所録テーブルを考えます。主キーは個人名で、同じ家族の人間は家族IDも一致します。年賀状用などでこういう住所録を作っている人も、結構いると思います。

　基本的に、同じ家族であれば同じ住所に住んでいますが（例：加藤家）、ホームズとワトソンのように、家族ではないけど同居しているカップルもいます。さて、前田夫妻に注目です。別に二人は別居中なのではなく、単に夫人の住所が間違っているだけです。本当は、家族IDが同じなら住所も同じでなくてはなりません。これは修正が必要です。では、前田夫妻のような「同じ家族だけど住所が不一致なレコード」を検出するにはどうすればよいでしょう？

　いくつかの方法が考えられますが、ここでも自己非等値結合を使うと簡潔に書けます。

--同じ家族だけど、住所が違うレコードを検索するSQL
SELECT DISTINCT A1.name, A1.address
  FROM Addresses A1,
       Addresses A2
 WHERE A1.family_id = A2.family_id
   AND A1.address <> A2.address ;

　「同じ家族で、かつ、住所が違う」をSQLに逐語訳しただけなので、意味的に悩む箇所はないと思います。このように、自己結合と非等値結合の組み合わせは、実に強力です。またこのSQLは、こういうデータ不整合を発見する場合以外にも、次のような商品分析のケースにも応用がききます。

問い：下の商品テーブルから、値段が同じ商品の組み合わせを取得せよ。

答え：さっきの住所録の例題と構造的にまったく同じです。

家族ID → 値段
住所 → 商品名

　に置き換えてください。すると、次のようになります。

--同じ値段だけど、商品名が違うレコードを検索するSQL
SELECT DISTINCT P1.name, P1.price
  FROM Products P1,
       Products P2
 WHERE P1.price = P2.price
   AND P1.name <> P2.name;

name        price
------      ------
りんご         50
ぶどう         50
いちご        100
みかん        100
バナナ        100

　この場合は、住所録の例題と違ってDISTINCTをつけないと結果に冗長な行が現れるので注意してください。ポイントは、同一のキーを持つレコードの数です。住所録の場合も、もし前田家に子供がいれば、やはりDISTINCTがないと冗長な行が現れます。なお、結合の代わりに相関サブクエリを使って書けば、DISTINCTは不要になります。練習問題として、各自、書き換えてみてください。

ランキング

　ときどき、点数や人数、売上といった数値に基づく順位表を作るという案件に遭遇します。DBMSによっては、こういう場合に対応した独自拡張の機能を持っていることもあります（Oracle、DB2のRANK関数など）。しかし、実装依存の機能に頼るのは最後の選択肢です。まずは標準SQLの範囲内で実現することを考えましょう。

　上のような商品テーブルから、値段の高い順に順位をつけます。ただし、同じ値段の商品は同じ順位になるようにして、次の順位は飛び石になるようにします。これは、次のように自己非等値結合（本当によく使う）を使って書きます。

--ランキング  1位から始まる。同順位が続いた後は不連続
SELECT P1.name,
       P1.price,
       (SELECT COUNT(P2.price)
          FROM Products P2
         WHERE P2.price > P1.price) + 1 AS rank
  FROM Products P1
 ORDER BY rank;

name        price        rank
------      ------      ------
みかん        100           1
スイカ         80           2
りんご         50           3
ぶどう         50           3
バナナ         50           3
レモン         30           6

　おそらく、これが一般的な順位づけの方式だと思いますが、ここからカスタマイズもできます。スカラ・サブクエリの後ろの +1 を除外すれば、トップが0位から始まりますし、サブクエリ内の結合条件に等号を付けて「P2.price >= P1.price」とすることで、同じ値段の商品の順位を低い方に揃えることができます。また、「COUNT(DISTINCT P2.price)」とすることで、同じ順位のレコードが存在する場合でも、順位が飛び石にならず、連続的に出力されます（DENSE_RANK関数に相当）。このように、要件に応じてさまざまな順位づけ方式にカスタマイズすることが可能な、柔軟なSQLです。

　さて、それではこのSQLの動作について解説しますが、実はこれは、集合指向的な発想の格好の例題なのです。このサブクエリ内でやっていることは、自分よりも高い値段のレコード数を数えて、それを順位に使う、というものです。話を簡単にするために、値段から重複を除外して、

{ 100, 80, 50, 30 }

　という4つの値段で、トップを0位から始める場合について考えましょう。まず、一番高い100についてみると、これより高い値段は存在しませんから、COUNT関数は0を返します。次に、二番目に高い80の場合、自分より高い値段は、100の一つなので、COUNT関数は1を返します。以下同様に、50の場合は2を、30の場合は3を返します。すると、結果として、各値段について次のような集合を作っていることになります。

　つまりこのSQLは、

S0 = φ
S1 = {100}
S2 = {100, 80}
S3 = {100, 80, 50}

　という「同心円的な」（セルコ）再帰的集合を作り、それらの要素数を数えているのです。「同心円的」という語が示すように、この4つの集合には

S3 ⊃ S2 ⊃ S1 ⊃ S0

　という包含関係が見て取れます。

集合の中に集合の中に集合の中に...

　実は、「再帰的集合を用いた数の割り当て」という、このアイデア自体は目新しいものではありません。興味深いことに、集合論では100年以上前から使われている、自然数（0も含む）の再帰的定義（recursive definition）と同じものです。その方法も研究者によっていくつか流儀がありますが、今回の例題と同型なのは、コンピュータの父の一人である数学者フォン・ノイマンの考えた方法です。ノイマンは、0を空集合で定義することから始めて、順次、次のようなルールで自然数全体を定めました。

0 = φ
1 = {0}
2 = {0, 1}
3 = {0, 1, 2}
     ・
     ・
     ・

　0を定義したら、それを使って1を定義する。次に、0と1を使って2を定義する、次に、0と1と2を使って3を定義する、……以下同様。このやり方は、上のS0〜S3集合の作り方と構造的に同じものです（この比較をしたいがために、順位を0から始めるケースを例に使ったのでした）。SQLと集合論が直接的に結びついていることを示す好例と言えるでしょう。そして、両者をつなぐ道が、自己結合というわけです。

終わりに

　以上、4つの応用例を通して、自己結合について解説してきました。重要なポイントをまとめると、以下の4点になります。

1. 非等値結合と組み合わせて使うのが基本
2. GROUP BYと組み合わせると、再帰的集合を作ることができる
3. 本当に異なるテーブルを結合していると考えると理解しやすい
4. テーブルを行の集合に見立てて、集合指向的な発想で考えよう

　自己結合は、CASE式に劣らず強力な道具なので、ぜひ使いこなしてください。また非常に応用範囲の広い技術のため、本稿で紹介した使い方はほんの一端に過ぎません。まだまだ興味深い応用が多くあります。本稿で入門を終えたら、さらに先へと進んでください。

参考資料

• 『プログラマのためのSQL　第2版』 J.セルコ 著、秋田昌幸 訳、ピアソン・エデュケーション、2001年4月
• 『Practical Issues in Database Management: A Refernce for the Thinking Practitioner』 Fabian Pascal 著、Addison-Wesley Pub、2000年5月