【MySQL5.7】相関サブクエリーで、グループ単位で所属レコードに採番する方法
2020/03/29
masyus
仕事で相関サブクエリーに接する機会がありましたので、備忘録を兼ねて記事にします。検証したMySQLバージョンは5.7.26です。
グループ単位で所属レコードに採番したい
とあるブログメディアがあったとします。DBテーブルスキーマは下記です。
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;
各テーブルの中身は下記です。
INSERT INTO authors (name, password_hash, delete_flg) VALUES
('authorA', 'XXXXXXXX', 0)
,('authorB', 'YYYYYYYY', 0)
,('authorC', 'ZZZZZZZZ', 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);
この2テーブルを組み合わせて、クエリーで著者毎の記事に連番を振った結果を得たいとします。具体的には下記です。
+-------------+---------+------------+
| 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が使えそうではありますが、グルーピングだけではうまくいかなさそうです。次に考えることは「自己結合を使ったら上手くいくだろうか？」となりますが、このような時は相関サブクエリーが効果を発揮します。
サブクエリーとは
簡潔に言いますと、「クエリーの中に入れ子でクエリーを記述すること」を指します。副問合せとも呼びます。MySQLリファレンスマニュアルに記載されている例は下記になります。
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
相関サブクエリーとは
簡潔に言いますと、「サブクエリーの外側のクエリーをサブクエリー内で記述すること」を指します。MySQLリファレンスマニュアルに記載されている例は下記になります。
SELECT * FROM t1
WHERE column1 = ANY (SELECT column1 FROM t2
WHERE t2.column2 = t1.column2);
「= ANY」は「IN」と同義です。サブクエリーの中にサブクエリーの外側のクエリが登場するため解読しづらい印象はありますが、
- サブクエリー: SQLをネストの一番深いところから解読する
- 相関サブクエリー: SQLを一番外側から解読する
のようにすると多少クエリーを解読しやすいかと思います。とはいえ、WebアプリケーションフレームワークのORMで相関サブクエリーを使い始めると解読が辛くなります。
相関サブクエリーによるアプローチ
相関サブクエリーを使い、冒頭の「著者毎の記事に連番を振った結果を得る」ためのアプローチをしていきます。
+-------------+---------+------------+
| author_name | row_num | article_id |
+-------------+---------+------------+
| authorA | 1 | 1 |
| authorA | 2 | 2 |
| authorA | 3 | 3 |
| authorB | 1 | 4 |
| authorB | 2 | 5 |
| authorC | 1 | 6 |
+-------------+---------+------------+
この結果内容を見て、まず私が思いついた相関サブクエリーは下記です。
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 |
+-------------+---------+------------+
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 > 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 > a1.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 |
+-------------+---------+------------+
求めている結果にかなり近づいてきました。あとはar1_numに+1をすれば採番できそうです。
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 |
+-------------+---------+------------+
このクエリーが完成系となります。
ただ、クエリーの内容を見ても想像がつくかと思いますが相関サブクエリは結構重めのSQLになりがちです。採用する際はEXPLAINしたり実際のデータに対してクエリーを投げてみてどうかを確かめる等、入念に検討したほうが良いかと思われます。もしくば相関サブクエリーを使わずとも済むよう、データベーススキーマもしくは要件レベルから見直すか等できると良さそうです。
MySQL8系からはROW_NUMBER()関数で代用できる
MySQL8なら同様のことが相関サブクエリーを使わず、ROW_NUMBER()を使えば済むようです。今度試してみます。