SQL修行vol.4

『達人に学ぶSQL徹底指南書』ー3.自己結合の使い方

順列・組合せ・集合、、、数学で出てきたワードに再会。SQLの結合演算には、内部結合、外部結合、クロス結合など色々あるが、今回は自己結合の回。うまく使えるようになれば便利そうというイメージはできるもののいまいち、じゃあどこで一体使うんだよ、って感じなところからスタート。（本を見ながらnoteを書いているのだが、うかんむりクリップの優秀さに脱帽）

自己結合とは何か

同一のテーブルを対象に結合を行うこと。
物理的には１つのテーブルなんだが、論理的に２つのテーブルとみなして、結合して色々できるよって話。

活用ケース１：重複順列・順列・組み合わせ

順序体：並び順を意識する。<1,2>のように表記する。つまり、順列。
非順序体：並び順を意識しない。{1,2}のように表記する。つまり、組み合せ。

🐣クロス結合
重複順列を作る。
例：Products.name=[a,b,c]だった場合、3の2乗=9行のレコードが取得できる。

SELECT
   P1.name AS name_1,
   P2.name AS name_2
FROM
   Products P1
CROSS JOIN
   Products P2;

🐣内部結合
順列を作る。
例：Products.name=[a,b,c]だった場合、3P2=6行のレコードが取得できる。

SELECT
   P1.name AS name_1,
   P2.name AS name_2
FROM
   Products P1
      INNER JOIN
         Products P2
         ON P1.name <> P2.name

組み合わせを作る。
例：Products.name=[a,b,c]だった場合、3C2=3行のレコードが取得できる。

SELECT
   P1.name AS name_1,
   P2.name AS name_2,
   P3.name AS name_3,
FROM
   Products P1
      INNER JOIN
         Products P2
            ON P1.name > P2.name
            INNER JOIN
               Products P3
                  ON P2.name > P3.name;

等号「=」以外の比較演算子である<や>、<>を使って行う結合を非等値結合と呼ぶ。今回は、自己結合と組み合わせているので自己非等値結合と呼ぶよ。>、<などの比較演算は文字列でも（一般的には）辞書順比較として機能する（これ便利だよな）。

活用ケース２：重複行を削除する

自己相関サブクエリを使って重複を削除するので、結合と演算としては異なるが考え方は似ていて、SQLを同値変換できる場合も多いらしい。いや、そもそも自己相関サブクエリってなんやねん。

🐣自己相関サブクエリ
サブクエリ、相関サブクエリ、自己相関サブクエリの順に見てみる。

サブクエリ（つまり非相関サブクエリ）：SELECT文を入れ子にして記述する構文で、抽出した結果をさらに何かしたい時に使う。まずサブクエリが実行され、その結果をメインクエリに渡す。FROM句、WHERE句、SELECT文の指定列の中で使う。

--FROM句
SELECT
   fieldName1,
   fieldName2
FROM
   (SELECT
       fieldName1, COUNT(fieldName1) as fieldName2
    FROM students
    GROUP BY fieldName1
   );

--WHERE句
SELECT DISTINCT
   fieldName1
FROM
   tableName1
WHERE fieldName2 IN 
   (SELECT fieldName3
    FROM tableName2
    WHERE fieldName4 = 'X');

--SELECT文
SELECT fieldName1,
       fieldName2,
       (SELECT AVG(fieldName2)
        FROM tableName1)
FROM  tableName1;

相関サブクエリ：メインクエリの各行に対してサブクエリが実行される。

SELECT
   *
FROM
   tableName1
WHERE
   fieldName1 = (SELECT
                   MAX(fieldName2)
                FROM tableName2
                WHERE tableName1.fiedlName1 = tableName2.fiedlName1
                );

自己相関サブクエリ：自己結合＋相関サブクエリ。

🐣非等値結合を利用した場合

DELETE FROM
   Products P1
WHERE EXISTS (SELECT
                 *
              FROM
                 Products P2
              WHERE
                 P1.name = P2.name
                 AND
                 P1.price = P2.price
                 AND
                 P1.rowid < P2.rowid

🐣極値関数を利用した場合
極値関数
SQLのMAX関数やMIN関数など、データベース上にあるテーブルの中から、指定したグループの値極値（ex: 最大値 or 最小）が含まれるレコードを取得する関数（極値は数Ⅲで習った極大値・極小値とかのこと）

DELETE FROM
   Products P1
WHERE rowid < (SELECT
                  MAX(P2.rowid)
               WHERE
                 P1.name = P2.name
                 AND
                 P1.price =P2.price
               );

活用ケース３：部分的に不一致なキーの検索
例１：家族IDは同じだが、住所が違うレコードを検索する

SELECT DISTINCT
   A1.name,
   A1.adress
FROM
   Addresses A1
   INNER JOIN
      Addresses A2
         ON A1.familyId = A2.familyId
            AND
            A1.address <> A2.address;

例２：値段が同じ商品の組み合わせを取得する

SELECT DISTINCT
   P1.name,
   P1.price
FROM
   Products P1
      INNER JOIN
         Products P2
         ON P1.name <> P2.name
            AND
            P1.price = P2.price;

活用ケース３：ランキング算出

例：価格のランキングを算出する

SELECT
   P1.name,
   MAX(P1.price) AS price,
   COUNT(P2.name) +1 AS rank
FROM
   Products P1
      LEFT JOIN
         Products P2
            ON P1.price < P2.price
GROUP BY
   P1.name;

--ウィンドウ関数を使う場合
SELECT
   name,
   price,
   RANK() OVER(ORDER BY price DESC) AS rank_1
   DENSE_RANK() OVER(ORDER BY price DESC) AS rank_2
FROM
   Products;