🔢
MySQLでwindow関数を使わずに重複したレコードごとに連番を生成する
やりたいこと
id | group_id | name |
---|---|---|
1 | 1 | hoge |
2 | 1 | hoge |
3 | 2 | fuga |
4 | 2 | fuga |
5 | 2 | fuga |
6 | 2 | piyo |
7 | 3 | foo |
8 | 4 | bar |
9 | 4 | bar |
このようなレコードを持つテーブルに対して、以下のように group_id ごとに重複する name に対して suffix を付与したい
id | group_id | name |
---|---|---|
1 | 1 | hoge |
2 | 1 | hoge_2 |
3 | 2 | fuga |
4 | 2 | fuga_2 |
5 | 2 | fuga_3 |
6 | 2 | piyo |
7 | 3 | foo |
8 | 4 | bar |
9 | 4 | bar_2 |
やり方
MySQL8 以上であれば window 関数が使えるかと思いますが、今回は MySQL5.7 の環境で使えるクエリを解説します
クエリ全体
対象のテーブルは test_group テーブルと仮定します
-- 重複したレコードごとにシーケンスを振る
SET @group_id = NULL, @name = NULL, @seq = 0;
CREATE TEMPORARY TABLE tmp_test_group AS
SELECT
id,
group_id,
name,
@seq := IF(@group_id = group_id AND @name = name, @seq + 1, 1) AS seq,
@group_id := group_id AS hoge,
@name := name AS hoge2
FROM
test_group
WHERE
(group_id, name) IN (
SELECT
group_id, name
FROM
test_group
GROUP BY
group_id, name
HAVING
count(group_id) > 1 AND count(name) > 1
)
ORDER BY
group_id, name;
-- name に suffix を付与
UPDATE
tmp_test_group
SET
name = CONCAT(name, '_', seq)
WHERE
seq <> 1;
-- 元テーブルの name を更新
UPDATE
group AS org
INNER JOIN
tmp_test_group AS tmp ON org.id = tmp.id
SET
org.name = tmp.name
解説
重複するレコードの取得
SELECT
group_id, name
FROM
test_group
GROUP BY
group_id, name
HAVING
count(group_id) > 1 AND count(name) > 1
GROUP BY
と HAVING
を使用して、group_id, name
が重複するレコードを取得
重複したレコードにシーケンスを振る
SET @group_id = NULL, @name = NULL, @seq = 0;
CREATE TEMPORARY TABLE tmp_test_group AS
SELECT
id,
group_id,
name,
@seq := IF(@group_id = group_id AND @name = name, @seq + 1, 1) AS seq,
@group_id := group_id AS hoge,
@name := name AS hoge2
SELECT と変数を組み合わせて、前回取得したレコードと group_id, name
が一致しているか?を毎回判定しています
以下動作イメージです
- 最初のレコードが SELECT される
id | group_id | name |
---|---|---|
1 | 1 | hoge |
-
@seq = 1
- この時点の変数は
@group_id, @name
ともにNULL
のため IF はfalse
になり、1
が代入される
- この時点の変数は
@group_id = 1
@name = 'hoge'
- tmp_test_group に INSERT される
id | group_id | name | seq |
---|---|---|---|
1 | 1 | hoge | 1 |
- 次のレコードが select される
id | group_id | name |
---|---|---|
2 | 1 | hoge |
-
@seq = 2
- この時点の変数は
@group_id = 1
,@name = 'hoge'
- 今 SELECT している
group_id, name
と一致するため IF はtrue
になり、@seq + 1
が代入される
- この時点の変数は
@group_id = 1
@name = 'hoge'
- tmp_test_group に INSERT される
id | group_id | name | seq |
---|---|---|---|
1 | 1 | hoge | 1 |
2 | 1 | hoge | 2 |
- 次のレコードが SELECT される
id | group_id | name |
---|---|---|
3 | 2 | fuga |
-
@seq = 1
- この時点の変数は
@group_id = 1 @name = 'hoge'
- 今 SELECT している
group_id, name
と一致しないため IF はfalse
になり、1
が代入される
- この時点の変数は
@group_id = 2
@name = 'fuga'
- tmp_test_group に INSERT される
id | group_id | name | seq |
---|---|---|---|
1 | 1 | hoge | 1 |
2 | 1 | hoge | 2 |
3 | 2 | fuga | 1 |
上記処理が対象のレコードがなくなるまで繰り返されるイメージです
シーケンスを元に suffix を付与
UPDATE tmp_test_group
SET name = CONCAT(name, '_', seq)
WHERE seq <> 1;
hoge, hoge_2, hoge_3... というレコードにしたいので、seq が 1 以上のレコードに対して更新しています
元テーブルに対して更新を行なう
UPDATE
group AS org
INNER JOIN
tmp_test_group AS tmp ON org.id = tmp.id
SET
org.name = tmp.name
テンポラリテーブルに更新後の name が保存されているので、id で join して name を更新します
ちなみに
MySQL8 以降の場合は window 関数が使えるので、もっと楽に書けると思います
WITH tmp AS (
SELECT
id,
group_id,
name,
ROW_NUMBER() OVER (PARTITION BY group_id, name ORDER BY id) AS seq
FROM
test_group
WHERE (group_id, name) IN (
SELECT
group_id, name
FROM
test_group
GROUP BY
group_id, name
HAVING
count(group_id) > 1 AND count(name) > 1
)
)
UPDATE
test_group
JOIN
tmp ON test_group.id = tmp.id
SET
test_group.name = CONCAT(test_group.name, '_', tmp.seq)
WHERE
tmp.seq > 1;
Discussion