各カテゴリ最新3件を除いて削除するSQL

Question

id,categoryの複合主キー

id | category | date
001 | A | 1967-12-23 22:09:00
001 | B | 2009-04-28 18:13:02
002 | B | 2073-01-15 14:52:04
002 | A | 1924-03-16 14:32:52
005 | A | 2084-10-19 00:50:50
001 | C | 2029-04-14 17:53:06
002 | C | 2090-04-04 11:15:13
008 | C | 2062-09-22 06:09:02
009 | B | 2023-01-28 09:18:09
010 | B | 1953-09-01 03:06:55
011 | A | 1969-11-09 08:36:23
012 | A | 2020-08-13 15:14:35
013 | C | 1957-08-26 23:50:01
014 | C | 2037-12-01 08:19:41

上記のようなテーブルがあり、各カテゴリ最新3項目のみ残して、
他は削除というSQL(MySQL)でシンプルな方法はありますでしょうか？
※今後カテゴリはAがなくなったり、Dが追加されたりする
※同日データがある場合、3項目以上残っても良い

下記のようなサブクエリを使用した方法を各カテゴリの数だけ繰り返すしか無いでしょうか？
MySQL 最新5件を除いて削除｜teratail
https://teratail.com/questions/30350

よろしくお願いいたします。

Comments

[yambejp]

プライマリキーの設定がないと削除時に使うキーにすべての項目を指定しないといけませんが
指定はできませんか？

[webgoto]

ありがとうございます。
本当はID項目もありましたのでテーブルの内容を修正致しました。

Answer 1

• 元データ

create table tbl(id int , category varchar(10),d datetime,primary key(id,category));
insert into tbl values
(1,'A','1967-12-23 22:09:00'),
(1,'B','2009-04-28 18:13:02'),
(2,'B','2073-01-15 14:52:04'),
(2,'A','1924-03-16 14:32:52'),
(5,'A','2084-10-19 00:50:50'),
(1,'C','2029-04-14 17:53:06'),
(2,'C','2090-04-04 11:15:13'),
(8,'C','2062-09-22 06:09:02'),
(9,'B','2023-01-28 09:18:09'),
(10,'B','1953-09-01 03:06:55'),
(11,'A','1969-11-09 08:36:23'),
(12,'A','2020-08-13 15:14:35'),
(13,'C','1957-08-26 23:50:01'),
(14,'C','2037-12-01 08:19:41');

• 参考：カテゴリごとの最新3件

select * from tbl as t1
where not exists(
select 1 from tbl 
where category=t1.category and d>t1.d  having count(*)>=3)
order by category asc,d desc;

これ以外のデータを削除するので、サブクエリにいれてnotで引っ掛ける

delete from tbl
where not (id,category) in (select * from(
select id,category from tbl as t1
where not exists(
select 1 from tbl 
where category=t1.category and d>t1.d  having count(*)>=3)
) as sub)

• 確認

select * from tbl order by category asc,d desc;

Comments

[webgoto]

素晴らしいご回答ありがとうございます！
希望どうりの結果を取得できました。
検証の方法も記載いただき、ありがとうございます。

まだなぜ取得できるのか理解できていないので
2,3番目のSQLを読み解いていこうと思います。
他のSQLを考えるときにも大変参考になりそうです。