テーブル table_a
id | name | typeID |
---|---|---|
1 | 星宮いちご | 1 |
2 | 霧矢あおい | 2 |
3 | 紫吹蘭 | 3 |
4 | 神崎美月 | 3 |
5 | 星宮いちご | 1 |
6 | 大空あかり | 1 |
7 | 藤堂ユリカ | 2 |
8 | 有栖川おとめ | 4 |
9 | 星宮いちご | 1 |
10 | 霧矢あおい | 2 |
このテーブルからname
とtypeID
が重複されているデータを最初の1件だけ残して削除したいと思います。
重複しているデータ
重複しているデータは下記のクエリで調べることが出来ました
SELECT * FROM table_a WHERE (typeID, name) IN ( SELECT typeID, name FROM table_a GROUP BY typeID, name HAVING COUNT(typeID) >= 2 AND COUNT(name) >= 2 ); +----+-----------------+--------+ | id | name | typeID | +----+-----------------+--------+ | 1 | 星宮いちご | 1 | | 2 | 霧矢あおい | 2 | | 5 | 星宮いちご | 1 | | 9 | 星宮いちご | 1 | | 10 | 霧矢あおい | 2 | +----+-----------------+--------+
このデータをそのまま削除するようにすると、重複しているデータ全てが削除されてしまいます。
グループ化したデータの最初の1件を除くか、最初の1件だけを取得できれば良いのですが…
例えばmin(id)
で取得すると
SELECT min(id) FROM table_a WHERE (typeID, name) IN ( SELECT typeID, name FROM table_a GROUP BY typeID, name HAVING COUNT(typeID) >= 2 AND COUNT(name) >= 2 ); +---------+ | min(id) | +---------+ | 1 | +---------+
それぞれのグループからではなく、重複している全てのデータから最も小さいid
のものと取得するので重複しているデータが複数ある場合は不適切です。
と、良い方法が思いつかなかったので方法を変えます。
重複のないユニークなデータだけのテーブルを作成し、それ以外を削除する
同じテーブルを比較して、name
とtypeID
が元のテーブルと同じで、id
が元のテーブル以上のものが1件ならユニークなデータになりそうです。
SELECT * FROM table_a AS t1 WHERE 1 = ( SELECT COUNT(*) FROM table_a AS t2 WHERE t1.name = t2.name AND t1.typeID = t2.typeID AND t1.id >= t2.id ); +----+-----------------------+--------+ | id | name | typeID | +----+-----------------------+--------+ | 1 | 星宮いちご | 1 | | 2 | 霧矢あおい | 2 | | 3 | 紫吹蘭 | 3 | | 4 | 神崎美月 | 3 | | 6 | 大空あかり | 1 | | 7 | 藤堂ユリカ | 2 | | 8 | 有栖川おとめ | 4 | +----+-----------------------+--------+
ユニークなデータが取得できました。
このユニークなリスト以外を削除するか、カウントが1件より大きいものが重複しているデータなのでそれを削除するかすれば良さそうです。
ユニークなリスト以外の方が見通しが良さそうなのでそちらで
DELETE FROM table_a WHERE id NOT IN ( SELECT id FROM( SELECT * FROM table_a AS t1 WHERE 1 = ( SELECT COUNT(*) FROM table_a AS t2 WHERE t1.name = t2.name AND t1.typeID = t2.typeID AND t1.id >= t2.id ) ) AS tmp ); > Query OK, 3 rows affected (0.10 sec) SELECT * FROM table_a; +----+-----------------------+--------+ | id | name | typeID | +----+-----------------------+--------+ | 1 | 星宮いちご | 1 | | 2 | 霧矢あおい | 2 | | 3 | 紫吹蘭 | 3 | | 4 | 神崎美月 | 3 | | 6 | 大空あかり | 1 | | 7 | 藤堂ユリカ | 2 | | 8 | 有栖川おとめ | 4 | +----+-----------------------+--------+
DELETE, UPDATE時にサブクエリに同じ名前のテーブルがあるとエラーになるので、ユニークなリストを取得するサブクエリをAS tmp
で別名扱いにしています。
これで無事重複しているデータはidが最小のもの1件を残して削除することができました!
MySQL 8.0 以上 WITH を使う
MySQL 8.0から使えるようになった WITH Syntax (Common Table Expressions) を使えば、テンポラリーなテーブルを作ることが出来るようなので本来は同じサブクエリーを複数使う時に威力を発揮するのだと思いますが、サブクエリーが入れ子になっているクエリの見通しを良くするのにも使えそうです。(メモリ消費はどうなんだ?って部分はあるかもですが…)
先の例だとユニークなリストを作成する部分をWITHにしてしまえば、見通しが良くなりそうです。
削除対象の重複しているデータを表示
WITH unique_list AS ( SELECT * FROM table_a AS t1 WHERE 1 = ( SELECT COUNT(*) FROM table_a AS t2 WHERE t1.name = t2.name AND t1.typeID = t2.typeID AND t1.id >= t2.id ) ) SELECT * FROM table_a WHERE id NOT IN (SELECT id FROM unique_list); +----+-----------------+--------+ | id | name | typeID | +----+-----------------+--------+ | 5 | 星宮いちご | 1 | | 9 | 星宮いちご | 1 | | 10 | 霧矢あおい | 2 | +----+-----------------+--------+
重複を削除
WITH unique_list AS ( SELECT * FROM table_a AS t1 WHERE 1 = ( SELECT COUNT(*) FROM table_a AS t2 WHERE t1.name = t2.name AND t1.typeID = t2.typeID AND t1.id >= t2.id ) ) DELETE FROM table_a WHERE id NOT IN (SELECT id FROM unique_list); > Query OK, 3 rows affected (0.09 sec) SELECT * FROM table_a; +----+-----------------------+--------+ | id | name | typeID | +----+-----------------------+--------+ | 1 | 星宮いちご | 1 | | 2 | 霧矢あおい | 2 | | 3 | 紫吹蘭 | 3 | | 4 | 神崎美月 | 3 | | 6 | 大空あかり | 1 | | 7 | 藤堂ユリカ | 2 | | 8 | 有栖川おとめ | 4 | +----+-----------------------+--------+
WITHを使えば既に別名になっているので、DELETE, UPDATE時にサブクエリに同じ名前のテーブルがあるとエラーになる問題の回避も楽になります。それでもNOT IN unique_list
とするとエラーになるので(SELECT 〜)
しなければならないのですが...
重複しているデータの最後(idが最も大きい)ものを残して重複を削除したい
上記のidが最も小さいものを残すパターンの場合id
を比較することで、ユニークなリストを作成できましたが、重複するデータでid
が最も大きいものを残そうとした場合は別のアプローチが必要になりそうです。
重複しているデータでid
が最大のもの以外
id
が最大のものはname
とtypeID
が同じで、元テーブルのid
以上のid
の時は1度なので、id
が元テーブルのid
以上の回数が1回より多い場合がid
最大値以外の重複しているデータになりそうです。
SELECT * FROM table_a AS t1 WHERE 1 < ( SELECT COUNT(*) FROM table_a AS t2 WHERE t1.name = t2.name AND t1.typeID = t2.typeID AND t1.id <= t2.id ); +----+-----------------+--------+ | id | name | typeID | +----+-----------------+--------+ | 1 | 星宮いちご | 1 | | 2 | 霧矢あおい | 2 | | 5 | 星宮いちご | 1 | +----+-----------------+--------+
重複しているデータの削除
WITH dup_list AS ( SELECT * FROM table_a AS t1 WHERE 1 < ( SELECT COUNT(*) FROM table_a AS t2 WHERE t1.name = t2.name AND t1.typeID = t2.typeID AND t1.id <= t2.id ) ) DELETE FROM table_a WHERE id IN (SELECT id FROM dup_list); > Query OK, 3 rows affected (0.02 sec) SELECT * FROM table_a; +----+--------------------+--------+ | id | name | typeID | +----+--------------------+--------+ | 3 | 紫吹蘭 | 3 | | 4 | 神崎美月 | 3 | | 6 | 大空あかり | 1 | | 7 | 藤堂ユリカ | 2 | | 8 | 有栖川おとめ | 4 | | 9 | 星宮いちご | 1 | | 10 | 霧矢あおい | 2 | +----+--------------------+--------+
これで、重複しているものは最後のものだけ残して削除することが出来ました!
感想
最初は、同じテーブル同士で比較するアイディアが思いつかず、UNION ALL
とか使ってユニークなデータを作成しようと頑張ってました。
それにしてもWITH便利ですね!MySQL では8.0からしでしか使えないみたいですが、WITHに慣れると使えないとつらみになりそうです。
SQL勉強しなければと思うと同時にMySQLとPostgreSQLとか種類によって微妙に使える式が違うのが厄介です… (同じようなSQL文で検索結果に出てくるので、調べて出てきたのがPostgreSQLでMySQLで試してみるとシンタックスエラーになったりとかと…つらみ
[参考]
- グループ単位で上位n件を取得するSQL
- MySQL :: MySQL 8.0 Reference Manual :: 13.2.13 WITH Syntax (Common Table Expressions)
- MySQL8.0 新機能 WITH句、共通テーブル式(CTE)のSQLの書き方教えます! - Database JUNKY
- 作者: Bill Karwin,和田卓人,和田省二,児島修
- 出版社/メーカー: オライリージャパン
- 発売日: 2013/01/26
- メディア: 大型本
- 購入: 9人 クリック: 698回
- この商品を含むブログ (46件) を見る