おかしなデータがDBに入っている場合のSQLでの確認

Question

おかしなデータがDBに入っている場合の確認方法についての質問です。

商品規格テーブル(products_class)に
product_class_id(auto_increment)
product_code
category1
category2
create_date
shop_id
カラムがある。

本来同じproduct_code, category1, category2の組み合わせは2つ以上あってはいけない、しかし

mysql>select product_class_id,category1,category2 from products_class where product_code=100 and shop_id=24;
+------------------+---------------------+---------------------+
| product_class_id | category1 | category2 |
+------------------+---------------------+---------------------+
| 1　　| 0 　　| B |
| 2　　| 0 　　| B |
| 3　　| 0 　　| B |
+------------------+---------------------+---------------------+

と存在している。ここで
①shop_id=24に全てのproduct_codeに上記のように３つ重複しているデータが存在しているかの確認
②もしそうであったら古い2つをdelete
②shop_id=24以外にも同じ現象が起きてないかを確認

このSQL文を皆さまならどうするか投稿していただけたらありがたいです、手段が１つではなさそうなので。
また不明部分がございましたら、ご指摘お願いいたします。

Comments

[退会済みユーザー]

DB のことを質問しているのですからそれが何かぐらいは書きましょうよ。Oracle? MySQL? SQL Server?

[kazoogon]

MySQLです。タグに入れるのを忘れておりました、申し訳ございません。

[退会済みユーザー]

今からでも遅くないので修正してください。質問の一番上の行に追記してもいいです。バージョンも書いてください。

[退会済みユーザー]

最初から「おかしなデータ」は入れられないように、連結主キーにするというのはダメなのですか？

[kazoogon]

解決法でなくデータが存在しているかの有無の確認、削除が今の課題ですね。　その後対策という流れです

Answer 1

「○○ごとのレコード数・最大値・最小値・平均値」といった集計には GROUP BY 構文をつかいます。

product_code, category1, category2, shop_id の組み合わせの数を出すには

SELECT product_code, category1, category2, shop_id, COUNT(*) AS dup 
FROM products_class GROUP BY product_code, category1, category2, shop_id;

とします。さらに重複、つまり集計した組み合わせの数が2つ以上あるものをフィルタするには HAVING 構文を使います。

SELECT product_code, category1, category2, shop_id, COUNT(*) AS dup 
FROM products_class GROUP BY product_code, category1, category2, shop_id
HAVING dup >= 2;

次に削除ですが、下記サイトが役立ちそうです。

[SQL]重複データを１件だけ残して削除する方法 - Qiita

---

最後に一般論ですが、そもそもこのように重複したデータが登録できるようなデータベース構造が間違っています。

最低限の対策として、重複を許さないのであればUNIQUE制約を付与しておくべきです。

またカラム名から察するに本来であればショップ情報を入れておくテーブルとプロダクト情報を入れておくテーブルは正規化して分けるべきです。そうしないと今後もデータベース不整合が発生して大きなトラブルに発展しかねません。

Answer 2

重複のある現状のデータを正規化するためのメンテナンス中で、そのための手法を聞いているのか、
正規化されていない外部データを定期的にとりこむ時の実装法についての質問なのか、
どちらでしょう。

前者なら、
product_code, category1, category2 を複合主キーに（あるいは、UNIQUE制約を付与）した空のテーブルを作成しておいて、そこに、現状のテーブルをproduct_class_id(auto_increment)で降順に並び替えたものを INSERT ... SELECT文で追加すれば、重複データははじかれて追加されます。
これが一番簡単ではないでしょうか。
できたテーブルは、正しくUNIQUE制約がついたものになります。

INSERT INTO tbl_temp2 (product_code, category1, category2, create_date, shop_id)
  SELECT product_code, category1, category2, create_date, shop_id
  FROM products_class ORDER BY product_class_id DESC;

後者でも、同様に、UNIQUE制約のついたテーブルに追加していくのがいいでしょう