「達人に学ぶSQL徹底指南書」（ミック著）を読んで（眺めて）「自己結合は使いこなせば非常に便利な技術です」とあった。そしてミックさんは本の中でも多用している。
自己結合ってなんか頭の中でこんがらがって難しい。
でも理解したら一段高いところに行けそう。だから理解してみようと努める。
なお、小学生レベルの脳みその持ち主の私が納得できるように分解したものを記載しています。実際の動きや原理とは異なる場合があると思いますのでご承知おきください。
今回は自己結合を利用して何ができるか。
の続編
③ひとつ前の値と比べる
④ランキング
③同じテーブルのひとつ前の値と比べる
このテーブルを使う。とある文房具屋さんのお客さんの入店記録。
ID , 入店時刻
1 , 9:00
2 , 9:10
3 , 9:35
4 , 9:35
5 , 10:30
IDは入店した人順に連続した数値が割り当てられ、入店時刻が記録される。
前の人との入店時間の間隔はどんなもんかなあ、と知りたい。
SQL文①
SELECT T1.ID, datediff(“n”,T1.入店,T2.入店) AS 間隔
FROM 入店記録 AS T1 INNER JOIN 入店記録 AS T2
ON T1.ID = T2.ID – 1;
SQL文②（この書き方でもいけました）
SELECT T1.ID, datediff(“n”,T1.入店,T2.入店) AS 間隔
FROM 入店記録 AS T1, 入店記録 AS T2
WHERE T1.ID = T2.ID – 1;
結果
ID , 間隔
1 , 10
2 , 25
3 , 0
4 , 55
しくみがいまいちわかったような、わからないような。
なので、ためしに、T1テーブルとT2テーブルを並べてみる。
SELECT T1.ID, T1.入店,T2.ID,T2.入店,
datediff(“n”, T1.入店, T2.入店) AS 間隔
FROM 入店記録 AS T1, 入店記録 AS T2
WHERE T1.ID = T2.ID – 1;
結果
T1.ID , T1.入店, T2.ID , T2.入店, 間隔
1 , 9:00 , 2 , 9:10 , 10
2 , 9:10 , 2 , 9:35 , 35
3 , 9:35 , 2 , 9:35 , 0
4 , 9:35 , 2 , 10:30 , 55
うん、まあ、わかったような。へーこんなことができるんだ、という感想。
ちなみにID 5 は次の入店がないので 結果は1~4までになります。
ちなみにこれは書籍を参考としたわけではなく、自分でやってみたかったことなので、一番良い書き方なのかはわかりません。
内部結合を使った方がよいのかWHEREでつなげた方がよいのか、どちらが負荷的に軽いのかなど、わかりません。。。
④自己結合を利用したランキング
とある文房具屋の商品の売り上げ順に順位を表示し、順位順に出力したい。
このテーブルを使う。テーブル名「T売り上げ」
商品名 , 売上
鉛筆 , 1500
定規 , 2000
ボールペン , 1300
シャープペン , 3500
テープ , 2500
のり , 2300
書籍に乗っているやり方を参考にしたものがこちら
SELECT T1.商品名, T1.売上,
(SELECT COUNT(T2.売上) FROM T売り上げ AS T2
WHERE T2.売上 > T1.売上)+1 AS 順位
FROM T売り上げ AS T1
ORDER BY T1.売上 DESC;
結果
商品名 , 売上 , 順位
シャープペン , 3500 , 1
テープ , 2500 , 2
のり , 2300 , 3
定規 , 2000 , 4
鉛筆 , 1500 , 5
ボールペン , 1300 , 6
順位が出た。しかりサブクエリを使った自己非等値結合（※）らしいけど、理解ができない。
ほかの方法でやってみる。
方法１
SELECT T1.商品名, T1.売上, COUNT(T2.商品名) AS 順位
FROM 売り上げ AS T1, 売り上げ AS T2
WHERE T1.売上 <= T2.売上
GROUP BY T1.商品名, T1.売上
ORDER BY T1.売上 DESC;
これを順序だてて理解していく。
１）直積をつくり、WHERE T1.売上 <= T2.売上 Falseの行（✕）を落としていく
商品名1 , 売上1 , 商品名2 , 売上2
鉛筆 , 1500 , 鉛筆 , 1500 売上1<=売上2 〇 同じ金額
鉛筆 , 1500 , 定規 , 2000 売上1<=売上2 〇
鉛筆 , 1500 , ボールペン , 1300 売上1<=売上2 ✕ 売上1の方が大
鉛筆 , 1500 , シャープペン , 3500 売上1<=売上2 〇
鉛筆 , 1500 , テープ , 2500 売上1<=売上2 〇
鉛筆 , 1500 , のり , 2300 売上1<=売上2 〇
定規 , 2000 , 鉛筆 , 1500 売上1<=売上2 ✕ 売上1の方が大
定規 , 2000 , 定規 , 2000 売上1<=売上2 〇 同じ金額
定規 , 2000 , ボールペン , 1300 売上1<=売上2 ✕ 売上1の方が大
定規 , 2000 , シャープペン , 3500 売上1<=売上2 〇
定規 , 2000 , テープ , 2500 売上1<=売上2 〇
定規 , 2000 , のり , 2300 売上1<=売上2 〇
ボールペン, 1300 , 鉛筆 , 1500 売上1<=売上2 〇
ボールペン, 1300 , 定規 , 2000 売上1<=売上2 〇
ボールペン, 1300 , ボールペン , 1300 売上1<=売上2 〇 同じ金額
ボールペン, 1300 , シャープペン , 3500 売上1<=売上2 〇
ボールペン, 1300 , テープ , 2500 売上1<=売上2 〇
ボールペン, 1300 , のり , 2300 売上1<=売上2 〇
シャープペン, 3500 , 鉛筆 , 1500 売上1<=売上2 ✕ 売上1の方が大
シャープペン, 3500 , 定規 , 2000 売上1<=売上2 ✕ 売上1の方が大
シャープペン, 3500 , ボールペン , 1300 売上1<=売上2 ✕ 売上1の方が大
シャープペン, 3500 , シャープペン , 3500 売上1<=売上2 〇 同じ金額
シャープペン, 3500 , テープ , 2500 売上1<=売上2 ✕ 売上1の方が大
シャープペン, 3500 , のり , 2300 売上1<=売上2 ✕ 売上1の方が大
テープ、のりも同様に（ここでは省略）。
２）〇の行を抽出
商品名1 , 売上1 , 商品名2 , 売上2
鉛筆 , 1500 , 鉛筆 , 1500 売上1<=売上2 〇 同じ金額
鉛筆 , 1500 , 定規 , 2000 売上1<=売上2 〇
鉛筆 , 1500 , シャープペン , 3500 売上1<=売上2 〇
鉛筆 , 1500 , テープ , 2500 売上1<=売上2 〇
鉛筆 , 1500 , のり , 2300 売上1<=売上2 〇
定規 , 2000 , 定規 , 2000 売上1<=売上2 〇 同じ金額
定規 , 2000 , シャープペン , 3500 売上1<=売上2 〇
定規 , 2000 , テープ , 2500 売上1<=売上2 〇
定規 , 2000 , のり , 2300 売上1<=売上2 〇
ボールペン, 1300 , 鉛筆 , 1500 売上1<=売上2 〇
ボールペン, 1300 , 定規 , 2000 売上1<=売上2 〇
ボールペン, 1300 , ボールペン , 1300 売上1<=売上2 〇 同じ金額
ボールペン, 1300 , シャープペン , 3500 売上1<=売上2 〇
ボールペン, 1300 , テープ , 2500 売上1<=売上2 〇
ボールペン, 1300 , のり , 2300 売上1<=売上2 〇
シャープペン, 3500 , シャープペン , 3500 売上1<=売上2 〇 同じ金額
３）商品名1でグループ化、商品名2の行数をカウント
鉛筆は ５
定規は ４
ボールペンは ６
シャープペンは １
商品名２の行数カウントが順位になっている。
方法２
SELECT T1.商品名, MAX(T1.売上) AS 売上,
COUNT(T2.商品名) + 1 AS 順位
FROM 売り上げ AS T1 LEFT OUTER JOIN 売り上げ AS T2
ON T1.売上 < T2.売上
GROUP BY T1.商品名
ORDER BY COUNT(T2.商品名);
LEFT JOIN による自己非等値結合（※）、というのでしょうか。
自分自身より売上金額が大きいものだけをつなげる
順を追って。
上記①直積を参照。
そこから、売上2の方が小さいまたは同じ金額の場合は条件を満たさないのでつながらない。表の右側（商品名2,売上2）には商品名1より売上金額が大きいものだけつながる。
シャープペンは一番売上金額が大きいので、右側はNULLとなる。インナージョインだとシャープペンはつながるものが無くて消えるが、レフトジョインなのでNULLでも残る。
商品名1 , 売上1 , 商品名2 , 売上2
鉛筆 , 1500 , 定規 , 2000
鉛筆 , 1500 , シャープペン , 3500
鉛筆 , 1500 , テープ , 2500
鉛筆 , 1500 , のり , 2300
定規 , 2000 , シャープペン , 3500
定規 , 2000 , テープ , 2500
定規 , 2000 , のり , 2300
ボールペン, 1300 , 鉛筆 , 1500
ボールペン, 1300 , 定規 , 2000
ボールペン, 1300 , シャープペン , 3500
ボールペン, 1300 , テープ , 2500
ボールペン, 1300 , のり , 2300
シャープペン, 3500 , NULL
テープ、のりも同様に（ここでは省略）。
②商品名1でグループ化し、商品名2の個数をカウント 。NULLはカウントに入らない
鉛筆 , 4
定規 , 3
ボールペン , 5
シャープペン , 0
③1位のシャープペンが0なので、1をプラスすると順位になる
左外部結合にしたところがポイントで、インナージョインにすると1位のシャープペンがなくなってしまう。右側が空集合（書籍では「空集合」という言い方をしているがよくわからないのでここではNULLとした）でも外部結合だと残るみたい。
方法3
いよいよ
SELECT T1.商品名, T1.売上,
(SELECT COUNT(T2.売上) FROM T売り上げ AS T2
WHERE T2.売上 > T1.売上)+1 AS 順位
FROM T売り上げ AS T1
ORDER BY T1.売上 DESC;
１）まずは ( ) 内のサブクエリから見る。
SELECT COUNT(T2.売上) FROM T売り上げ AS T2
WHERE T2.売上 > T1.売上
なんでFROM T売り上げ AS T2 しか書いてないのでいきなりT1.売上がでてくるんだと。どうも、関数の引数みたいな感じで親クエリから渡されているらしい。
鉛筆からスキャン
引数として鉛筆の売上 1500 が渡された
SELECT COUNT(T2.売上) FROM T売り上げ AS T2
WHERE T2.売上 > 1500
1500円より大きい売り上げは、定規2000、シャープペン3500、テープ2500、のり 2300 の4つ、なのでカウントは４
プラス１があるので
鉛筆 , 5 が返る
次、定規
引数として鉛筆の売上 2000 が渡された
SELECT COUNT(T2.売上) FROM T売り上げ AS T2
WHERE T2.売上 > 2000
2000円より大きい売り上げは、シャープペン3500、テープ2500、のり2300 の3つ、なのでカウントは3
プラス１があるので
定規 , 4 が返る
ボールペン以降も同様に。
わかったような気がする。
サブクエリって難しい。
前にやったエクセル関数のSUMPRODUCT関数になにか似たようなものを感じる。
【Excel/関数】SUMPRODUCT関数をやっと理解できた（かもしれない）
とりあえず、シリーズ自己結合はこれでおしまいです。
お読みいただいた方、ありがとうございました。
※非等値結合：＝ではなく、＞や＜、＜＞、＞＝等による結合（たぶん）
コメント