EGSTOCK,Inc.Publicationへの投稿
🔢

MySQLでwindow関数を使わずに重複したレコードごとに連番を生成する

プレーンわに
に公開
MySQL
SQL
MySQL 5.7
window関数
tech

やりたいこと

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 が一致しているか？を毎回判定しています
以下動作イメージです

  1. 最初のレコードが SELECT される
id group_id name
1 1 hoge
  • @seq = 1
    • この時点の変数は @group_id, @name ともに NULL のため IF は false になり、1が代入される
  • @group_id = 1
  • @name = 'hoge'
  1. tmp_test_group に INSERT される
id group_id name seq
1 1 hoge 1
  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'
  1. tmp_test_group に INSERT される
id group_id name seq
1 1 hoge 1
2 1 hoge 2
  1. 次のレコードが 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'
  1. 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;
EGSTOCK,Inc.
EGSTOCK,Inc.Publication

EGSTOCK, Inc. 札幌の開発会社 FE/BEはじめインフラも対応してます [言語]PHP/Golang/Vue.js/React.js [クラウド]AWS, GCP [その他]Docker/OpenApi/Notion

Discussion