「達人に学ぶSQL徹底指南書」（ミック著）を読んで（眺めて）「自己結合は使いこなせば非常に便利な技術です」とあった。そしてミックさんは本の中でも多用している。
自己結合ってなんか頭の中でこんがらがって難しい。
でも理解したら一段高いところに行けそう。だから理解してみようと努める。
自己結合では、同じテーブル同士を直積でくっつけることが多いみたい。なので、まずは直積とはなんぞや、から始めたのが前回の記事。
前回【access/SQL】自己結合を理解するように努める～①その前に、直積 とは
今回は自己結合で直積等をやってみる。
なお、小学生レベルの頭の私が理解するため、わかりやすいように勝手に解釈をつけている場合があります（とにかく小学生脳がなるほど～と納得できるレベルに持ってきた）。ちゃんと本当の説明ができていない部分があると思いますのでその点はご承知おきください。
①自己結合の基本
②順列（サークルの代表、副代表を選ぶ）
③組み合わせ（サークルでボール係２人を選ぶ）
①自己結合
同じテーブル同士を対象に行う結合。自己結合 self join
自己結合を理解するポイントは
・同じテーブルだけど、2つのテーブルがあると思うこと
・2次元の表でなく「集合」であるという考え方をすること（2つの集合をぶつけている、と考える）
このあたりらしい。わかったようなわからないような。
こんどはこんなテーブルで考えてみる。
先日の婚活パーティーで意気投合した4人がテニスサークルを作った。
その名簿
テーブル：名簿
氏名, 性別
高島, 男
加藤, 男
山田, 女
田中, 女
その名簿を直積自己結合する。
SELECT T1.氏名 AS 氏名1, T2.氏名 AS 氏名2
FROM 名簿 AS T1, 名簿 AS T2;
以下のように出る
氏名1, 氏名2
高島, 高島
高島, 加藤
高島, 山田
高島, 田中
加藤, 高島
加藤, 加藤
加藤, 山田
加藤, 田中
山田, 高島
山田, 加藤
山田, 山田
山田, 田中
田中, 高島
田中, 加藤
田中, 山田
田中, 田中
これを重複順列というらしい。４の二乗で16通り。
はたして何に使えるか。。。使い道が分からない表、という感想。
自己結合は等値結合（＝）、非等値結合（＞とか＜とか＜＞とか）（＝）とか使って、初めて利用価値がでてくるらしい。
②順列の自己結合
順列とは、例えばテニスサークルの高島、加藤、山田、田中の4人の中から代表と副代表を選ぶこと。重複順列の「代表山田、副代表山田」一人二役はないので直積で重複を排除する方法を考える。
SELECT T1.氏名 AS 部長, T2.氏名 AS 副部長
FROM 名簿 AS T1, 名簿 AS T2
WHERE T1.氏名 <> T2.氏名;
以下のように出る
部長, 副部長
高島, 加藤
高島, 山田
高島, 田中
加藤, 高島
加藤, 山田
加藤, 田中
山田, 高島
山田, 加藤
山田, 田中
田中, 高島
田中, 加藤
田中, 山田
計算式は分からないが12通り。これが順列。
このいずれかがサークル内での多数決？話し合い？の結果、代表・副代表になるのでしょう。
③組み合わせの自己結合
順列は 1,2<>2,1 だけど、1,2 も 2,1 も一緒だぜい、という場合は「組み合わせ」という。
組み合わせは、例えばテニスサークルの高島、加藤、山田、田中の4人の中からボール係2名を選ぶこと。
SELECT T1.氏名 AS 氏名1, T2.氏名 AS 氏名2
FROM 名簿 AS T1, 名簿 AS T2
WHERE T1.氏名 < T2.氏名
以下のように出る
氏名1, 氏名2
高島, 加藤
高島, 山田
高島, 田中
加藤, 山田
加藤, 田中
山田, 田中
6通りの組み合わせになった。
氏名の文字コード順で大小を比べているんだろうけど、意味が分からなくなってきたので、名簿にIDをつけて整理。
ID, 氏名, 性別
1 , 高島, 男
2 , 加藤, 男
3 , 山田, 女
4 , 田中, 女
まずは直積でくっつけて、WHERE T1.ID < T2.ID の判定を行い。Falseの行は落としていく。
SELECT T1.ID AS ID1, T1.氏名 AS 氏名1, T1.ID AS ID2, T2.氏名 AS 氏名2
FROM 名簿 AS T1, 名簿 AS T2
WHERE T1.ID < T2.ID
ID1, 氏名1, ID2, 氏名2
1 , 高島, 1 , 高島 ← ID1 > ID2 ではない ✕
1 , 高島, 2 , 加藤 ← ID1 > ID2 である 〇
1 , 高島, 3 , 山田 ← ID1 > ID2 である 〇
1 , 高島, 4 , 田中 ← ID1 > ID2 である 〇
2 , 加藤, 1 , 高島 ← ID1 > ID2 ではない ✕
2 , 加藤, 2 , 加藤 ← ID1 > ID2 ではない ✕
2 , 加藤, 3 , 山田 ← ID1 > ID2 である 〇
2 , 加藤, 4 , 田中 ← ID1 > ID2 である 〇
3 , 山田, 1 , 高島 ← ID1 > ID2 ではない ✕
3 , 山田, 2 , 加藤 ← ID1 > ID2 ではない ✕
3 , 山田, 3 , 山田 ← ID1 > ID2 ではない ✕
3 , 山田, 4 , 田中 ← ID1 > ID2 である 〇
4 , 田中, 1 , 高島 ← ID1 > ID2 ではない ✕
4 , 田中, 2 , 加藤 ← ID1 > ID2 ではない ✕
4 , 田中, 3 , 山田 ← ID1 > ID2 ではない ✕
4 , 田中, 4 , 田中 ← ID1 > ID2 ではない ✕
結果 ✕の行は消えて丸の行だけ残る
ID1, 氏名1, ID2, 氏名2
1 , 高島, 2 , 加藤 ← ID1 > ID2 である 〇
1 , 高島, 3 , 山田 ← ID1 > ID2 である 〇
1 , 高島, 4 , 田中 ← ID1 > ID2 である 〇
2 , 加藤, 3 , 山田 ← ID1 > ID2 である 〇
2 , 加藤, 4 , 田中 ← ID1 > ID2 である 〇
3 , 山田, 4 , 田中 ← ID1 > ID2 である 〇
この組み合わせのいずれかが、ボール係を任命されるのでしょう。
う～ん。わかったようなわからないような。
シリーズ自己結合 続編はこちら
コメント