SQLの自己結合を用いて数学の順列や組み合わせを表現する

6655 ワード
SQL 自己結合 組み合わせ 順列 SQL テキストリンク

はじめに

SQLにおいて、自分はあまり聞き慣れなかった自己結合の今後役立つかもしれないテクニックを紹介していきます。

実行結果はこちらで確認できます。

基本となるデータ

何かのメンバー一覧だと思ってください。

CREATE TABLE members (
  id INT NOT NULL PRIMARY KEY auto_increment,
  name TEXT
);

INSERT INTO members VALUES
    (1, 'tanaka'),
    (2, 'okada'),
    (3, 'hiranuma');

自己結合を用いて重複順列を作る

重複順列とは...異なるn個のものから、重複を許し、r個とる順列

n^r

Query

    SELECT
      M1.name,
      M2.name
    FROM members M1 CROSS JOIN members M2;

Results

name1 name2
tanaka tanaka
okada tanaka
hiranuma tanaka
tanaka okada
okada okada
hiranuma okada
tanaka hiranuma
okada hiranuma
hiranuma hiranuma

これはいわゆる総当たり戦で全ての組み合わせを列挙してるだけです。

自己結合を用いて順列を作る（重複を許さない）

では、テーブル members を使って、SQLで順列を表現します。

順列は、並び順を区別し、当然ながら重複ありません。

今回であれば、3つのメンバーの2人で並べる方法なので、式にすると。

_{3}P_{2}

3×2=6パターンの並べ方があります。

これをSQLの自己結合を用いて表現すると以下のようになります。

Query

    SELECT
      M1.name AS name1,
      M2.name AS name2
    FROM members M1
    INNER JOIN members M2 ON M1.name <> M2.name;

Results

name1 name2
okada tanaka
hiranuma tanaka
tanaka okada
hiranuma okada
tanaka hiranuma
okada hiranuma

自己結合を用いて組み合わせを作る

今回のケースを計算すると

_{3}C_{2} \\
= \frac{3\times2}{2} \\
= 3 \\

パターンの並べ方があります。

これをSQLの自己結合を用いて表現すると以下のようになります。後でもいい。

Query

    SELECT
      M1.name AS name1,
      M2.name AS name2
    FROM members M1
    INNER JOIN members M2 ON M1.name > M2.name;

文字コードの順にソートして、自分よりも前に来るようにしています。

Results

name1 name2
tanaka okada
tanaka hiranuma
okada hiranuma

参照

44 - 48p

アウトプット100本ノック実施中