SQLで重複行を日付の古いレコードを削除したい

Question

以下のような商品ごとにタイプ違いデータがあります。

カテゴリーをグループ化して更新日付（update）が違う場合、
古い更新日付のレコードを削除するSQLがわかりません。

重複行を抽出するSQLはできたいのですが、、、ご教示お願いいたします。

SELECT
  * 
FROM
  table a
WHERE
  a.category in (
    SELECT
      b.category
    FROM
      table b
    GROUP BY
      b.category
    HAVING
      COUNT(*) > 1
  )

id	category	type	update	item
1001	100	1	2021-01-01	商品11
1002	100	2	2021-02-01	商品12
2001	200	1	2021-02-01	商品21
2002	200	2	2021-02-01	商品22
3001	300	1	2021-03-10	商品31
3002	300	2	2021-03-01	商品32
4001	400	1	商品41
4002	400	2	商品42
5001	500	1	2021-01-15	商品51
5002	500	2	2021-01-10	商品52

削除したいレコードは

id	category	type	update	item
1001	100	1	2021-01-01	商品11
3002	300	2	2021-03-01	商品32
5002	500	2	2021-01-10	商品52

になります。

Comments

[Orlofsky]

質問は修正できます。

> 重複行を抽出するSQLはできたいのですが、
質問に「重複行を抽出するSQL」も追記しては？

>ご教授お願いいたします。
教授 → 教示

[takumil]

どうぞよろしくお願いいたします。

[takanaweb5]

MySQLのバージョンはいくつでしょうか？

[takumil]

MySQL5.7になります。

[takanaweb5]

MySQL 8.0以上であれば分析関数を使用してEXISTS を使用しない方法を思いついたのですが、MySQL5.7では使用できませんね。

Answer 1

• idにより行が一意に特定できる (idがprimary key)
• 同じcategoryのレコードは必ず2つ
• 同じcategoryの2つのレコードのupdateは両方NULLか両方値が入力されているかのどちらか
• updateが両方NULLのcategoryは削除対象ではない
• updateに値が入力されているcategoryについては、よりupdateがより古い行を削除したい

いろいろ明示されていない条件がありそうなので、整理してみましたが、これであっていますか?

であれば、以下のようなレコードで実現できるかと思います (MySQLの制約の関係上、やや複雑なクエリになっています)

DELETE 
FROM `table`
WHERE id IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table` A
    WHERE EXISTS (
      SELECT *
      FROM `table` B
      WHERE A.category = B.category
      AND A.update < B.update
    ) 
  ) C
);

Comments

[takumil]

回答いただきありがとうございます。
要件も書き出していただきありがとうございます。
すべて合っています。

まずは、selectのみ試してみましたが、
数万行のレコードで完了することができませんでした。

[neko_the_shadow]

対象のテーブルのDDLとSELECTのEXPLAINの結果を提示することはできますか?

[takumil]

ありがとうございます。

SELECT
*
FROM table
WHERE
id IN (
SELECT id
FROM (
SELECT id
FROM table A
WHERE EXISTS (
SELECT *
FROM table B
WHERE
A.category = B.category
AND A.public_date < B.public_date
)
) C
)

EXPLANは
id,select_type,table,partitions,type,possible_keys,key_len,ref,rows,filtered,Extra
1,PRIMARY,dat_chintai_room_dup,,ALL,PRIMARY,,,,10760,100.00,
1,PRIMARY,A,,eq_ref,PRIMARY,PRIMARY,8,table.id,1,100.00,Using where
4,DEPENDENT SUBQUERY,B,,ALL,,,,,10760,33.33,Using where

[neko_the_shadow]

categoryにインデックスを設定すると改善しないでしょうか?

CREATE INDEX idx ON `table` (category);

[takumil]

無事抽出することができ削除対象レコードを絞り込むことができました。

ありがとうございます。