【MySQL5.7】相関サブクエリで、グループ単位で所属レコードに採番する方法

仕事で久しぶりに相関サブクエリを見たので、備忘録の意味合いも込めて記事に残しておく。検証したMySQLバージョンは5.7.26。

相関サブクエリとは

サブクエリが何かを分かっている前提で解説する。簡単に言うと、サブクエリの外側のクエリをサブクエリ内で使うことができるというもの。例としては下記のような書き方ができる：

SELECT
    au.name author_name
    ,(
        SELECT
            COUNT(ar1.id) + 1
        FROM
            articles ar1
        WHERE
            ar1.author_id = ar2.author_id
            AND ar1.id < ar2.id
    ) row_num
    ,ar2.id article_id
FROM
    articles ar2
INNER JOIN authors au
    ON au.id = ar2.author_id
;

SELECT

au.name author_name

SELECT

COUNT(ar1.id) + 1

FROM

articles ar1

WHERE

ar1.author_id = ar2.author_id

AND ar1.id < ar2.id

) row_num

,ar2.id article_id

FROM

articles ar2

INNER JOIN authors au

ON au.id = ar2.author_id

;

パッと見サブクエリの中で外側のクエリを使うことができるのはちょっと気持ち悪い感じもあるが、

非相関サブクエリ（＝通常のサブクエリ）の時はSQLをネストの一番深いところから解読する
相関サブクエリの時は、SQLの流れを一番外側から解読する

という感じで読み分ければ解読しやすい（ただ、フレームワークのORMで相関サブクエリを使い始めると解読辛め）。

相関サブクエリなら、グループ単位で所属レコードに採番できる

例えば著者毎に記事それぞれに採番をしたいとしよう。登場するテーブルはauthors, articlesの2つ。

CREATE TABLE `authors` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `password_hash` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `delete_flg` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `authors` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) COLLATE utf8mb4_bin NOT NULL,

`password_hash` varchar(255) COLLATE utf8mb4_bin NOT NULL,

`delete_flg` tinyint(1) NOT NULL DEFAULT 0,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `articles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `author_id` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8mb4_bin NOT NULL,
  `content` text COLLATE utf8mb4_bin NOT NULL,
  `delete_flg` tinyint(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

CREATE TABLE `articles` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`author_id` int(11) NOT NULL,

`title` varchar(255) COLLATE utf8mb4_bin NOT NULL,

`content` text COLLATE utf8mb4_bin NOT NULL,

`delete_flg` tinyint(1) NOT NULL DEFAULT 0,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

著者は全部で3人いるとする：

INSERT INTO authors (name, password_hash, delete_flg) VALUES
('authorA', 'XXXXXXXX', 0)
,('authorB', 'YYYYYYYY', 0)
,('authorC', 'ZZZZZZZZ', 0);

INSERT INTO authors (name, password_hash, delete_flg) VALUES

('authorA', 'XXXXXXXX', 0)

,('authorB', 'YYYYYYYY', 0)

,('authorC', 'ZZZZZZZZ', 0);

記事は全部で6記事。それぞれの著者が執筆しているとする：

INSERT INTO articles (author_id, title, content, delete_flg) VALUES
(1, 'titleA1', 'AAA', 0)
,(1, 'titleA2', 'BBB', 0)
,(1, 'titleA3', 'CCC', 0)
,(2, 'titleB1', 'DDD', 0)
,(2, 'titleB2', 'EEE', 0)
,(3, 'titleC1', 'FFF', 0);

INSERT INTO articles (author_id, title, content, delete_flg) VALUES

(1, 'titleA1', 'AAA', 0)

,(1, 'titleA2', 'BBB', 0)

,(1, 'titleA3', 'CCC', 0)

,(2, 'titleB1', 'DDD', 0)

,(2, 'titleB2', 'EEE', 0)

,(3, 'titleC1', 'FFF', 0);

さて、著者毎に記事それぞれに採番をしたいというニーズはすなわち、

+-------------+---------+------------+
| author_name | row_num | article_id |
+-------------+---------+------------+
| authorA     |       1 |          1 |
| authorA     |       2 |          2 |
| authorA     |       3 |          3 |
| authorB     |       1 |          4 |
| authorB     |       2 |          5 |
| authorC     |       1 |          6 |
+-------------+---------+------------+

+-------------+---------+------------+

| author_name | row_num | article_id |

+-------------+---------+------------+

| authorA | 1 | 1 |

| authorA | 2 | 2 |

| authorA | 3 | 3 |

| authorB | 1 | 4 |

| authorB | 2 | 5 |

| authorC | 1 | 6 |

+-------------+---------+------------+

こんな結果が帰ってきてほしいというわけである。

この場合、まずはどの著者にどの記事が結びついているかが分かりたいのでgroup byの使用を思い描く思うが、group byは集計用関数なのですぐに違う方法を検討しなければならないと気付くのではないか。

となると次は自己結合だと思うわけだが、どうも単に自己結合しただけでは上手くいかなそうな気配がある。articles1レコードずつに対して採番したいのだからサブクエリ、それも相関サブクエリを使ったほうが良さそうだと連想する必要がある。

試しに下記SQLを実行してみる：

SELECT
    au.name author_name
    ,(
        SELECT
            COUNT(ar1.id)
        FROM
            articles ar1
        WHERE
            ar1.author_id = ar2.author_id
    ) ar1_num
    ,ar2.id article_id
FROM
    articles ar2
INNER JOIN authors au
    ON au.id = ar2.author_id
;

SELECT

au.name author_name

SELECT

COUNT(ar1.id)

FROM

articles ar1

WHERE

ar1.author_id = ar2.author_id

) ar1_num

,ar2.id article_id

FROM

articles ar2

INNER JOIN authors au

ON au.id = ar2.author_id

;

+-------------+---------+------------+
| author_name | ar1_num | article_id |
+-------------+---------+------------+
| authorA     |       3 |          1 |
| authorA     |       3 |          2 |
| authorA     |       3 |          3 |
| authorB     |       2 |          4 |
| authorB     |       2 |          5 |
| authorC     |       1 |          6 |
+-------------+---------+------------+

+-------------+---------+------------+

| author_name | ar1_num | article_id |

+-------------+---------+------------+

| authorA | 3 | 1 |

| authorA | 3 | 2 |

| authorA | 3 | 3 |

| authorB | 2 | 4 |

| authorB | 2 | 5 |

| authorC | 1 | 6 |

+-------------+---------+------------+

この場合、ar1とar2をauthor_id単位で直交させた結果のカウントがar1_numとして反映されている。ar1_numをあともう1段階加工すれば良さげな感じだ。

次は、ar2.idとar1.idに何かしらの関係性を持たせるのはどうかと考える。直交結果をCOUNT()しないで考えた場合、

ar2.id | ar1.id
---------------
1 | 1
1 | 2
1 | 3
2 | 1
2 | 2
2 | 3
3 | 1
3 | 2
3 | 3
4 | 4
4 | 5
5 | 4
5 | 5
6 | 6

ar2.id | ar1.id

---------------

1 | 1

1 | 2

1 | 3

2 | 1

2 | 2

2 | 3

3 | 1

3 | 2

3 | 3

4 | 4

4 | 5

5 | 4

5 | 5

6 | 6

上記のような間柄になっている。

例えばar2.id > a1.idという条件を付与したとしたらどうだろうか？

ar2.id | ar1.id | ar2.id > ar1.id
----------------------------------------
1 | 1 | FALSE
1 | 2 | FALSE
1 | 3 | FALSE
2 | 1 | TRUE
2 | 2 | FALSE
2 | 3 | FALSE
3 | 1 | TRUE
3 | 2 | TRUE
3 | 3 | FALSE
4 | 4 | FALSE
4 | 5 | FALSE
5 | 4 | TRUE
5 | 5 | FALSE
6 | 6 | FALSE

ar2.id | ar1.id | ar2.id > ar1.id

----------------------------------------

1 | 1 | FALSE

1 | 2 | FALSE

1 | 3 | FALSE

2 | 1 | TRUE

2 | 2 | FALSE

2 | 3 | FALSE

3 | 1 | TRUE

3 | 2 | TRUE

3 | 3 | FALSE

4 | 4 | FALSE

4 | 5 | FALSE

5 | 4 | TRUE

5 | 5 | FALSE

6 | 6 | FALSE

これは何か使えそうな気配。

この条件を付与した状態で、ar1_numのCOUNT()を取ってみる。

SELECT
    au.name author_name
    ,(
        SELECT
            COUNT(ar1.id)
        FROM
            articles ar1
        WHERE
            ar1.author_id = ar2.author_id
            AND ar1.id < ar2.id
    ) ar1_num
    ,ar2.id article_id
FROM
    articles ar2
INNER JOIN authors au
    ON au.id = ar2.author_id
;

SELECT

au.name author_name

SELECT

COUNT(ar1.id)

FROM

articles ar1

WHERE

ar1.author_id = ar2.author_id

AND ar1.id < ar2.id

) ar1_num

,ar2.id article_id

FROM

articles ar2

INNER JOIN authors au

ON au.id = ar2.author_id

;

+-------------+---------+------------+
| author_name | ar1_num | article_id |
+-------------+---------+------------+
| authorA     |       0 |          1 |
| authorA     |       1 |          2 |
| authorA     |       2 |          3 |
| authorB     |       0 |          4 |
| authorB     |       1 |          5 |
| authorC     |       0 |          6 |
+-------------+---------+------------+

+-------------+---------+------------+

| author_name | ar1_num | article_id |

+-------------+---------+------------+

| authorA | 0 | 1 |

| authorA | 1 | 2 |

| authorA | 2 | 3 |

| authorB | 0 | 4 |

| authorB | 1 | 5 |

| authorC | 0 | 6 |

+-------------+---------+------------+

お、ということはar1_numに+1すれば採番になるんじゃない？

ってことで、ar1_num + 1の名前をrow_numとして

SELECT
    au.name author_name
    ,(
        SELECT
            COUNT(ar1.id) + 1
        FROM
            articles ar1
        WHERE
            ar1.author_id = ar2.author_id
            AND ar1.id < ar2.id
    ) row_num
    ,ar2.id article_id
FROM
    articles ar2
INNER JOIN authors au
    ON au.id = ar2.author_id
;

SELECT

au.name author_name

SELECT

COUNT(ar1.id) + 1

FROM

articles ar1

WHERE

ar1.author_id = ar2.author_id

AND ar1.id < ar2.id

) row_num

,ar2.id article_id

FROM

articles ar2

INNER JOIN authors au

ON au.id = ar2.author_id

;

+-------------+---------+------------+
| author_name | row_num | article_id |
+-------------+---------+------------+
| authorA     |       1 |          1 |
| authorA     |       2 |          2 |
| authorA     |       3 |          3 |
| authorB     |       1 |          4 |
| authorB     |       2 |          5 |
| authorC     |       1 |          6 |
+-------------+---------+------------+

+-------------+---------+------------+

| author_name | row_num | article_id |

+-------------+---------+------------+

| authorA | 1 | 1 |

| authorA | 2 | 2 |

| authorA | 3 | 3 |

| authorB | 1 | 4 |

| authorB | 2 | 5 |

| authorC | 1 | 6 |

+-------------+---------+------------+

ようやく欲しい結果にたどり着いた。

ただ、相関サブクエリは結構重めのSQLになりがちなので、採用する際はexplainしたり実際のデータに対してSQL投げてみてどうかを確かめる等、入念に検討したほうが良い。

MySQL8系からはROW_NUMBER()関数で代用できる

なんと、MySQL8なら同じことが相関サブクエリを使わずROW_NUMBER()を使えば済むとのこと。むう、技術の進歩には日々ついてかないと大変だ。