Jak pozostawić 200 rekordów każdej serii w tabeli, w której mam rekordy z wielu serii?

Witam serdecznie.

Mam tabelę REKORDY, w której znajdują się zbiorczo rekordy z wielu serii.

CREATE TABLE IF NOT EXISTS `rekordy` (
  `rekord_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `rekord_seria` int(10) unsigned DEFAULT NULL,
  `rekord_wartosc` tinyint(3) unsigned DEFAULT NULL,
  `rekord_data` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`rekord_id`),
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1195 ;

Każdy rekord ma unikalne id: rekord_id
Każda seria rekordów ma swój unikalny nr: rekord_seria
Każdy rekord ma jakąś daną w polu: rekord_wartosc
Każdy rekord ma timestamp dodania do bazy: rekord_data

Rekordy z poszczególnych serii dodawane są do bazy bardzo różnych odstępach czasu i w różnych ilościach.

Chcę za pomocą krona czyścić tę tabelę na dwa sposoby jednocześnie:

1. kasować starsze rekordy niż doba niezależnie, do której serii należą (to już zaimplementowałem),
2. pozostawiać w tej tabeli dla każdej serii nie więcej niż 200 najnowszych rekordów w danej serii. To kasowanie kron będzie wykonywał co 5 minut.

Pytanie więc dotyczy punktu 2. Chcę to wykonać tylko i wyłącznie za pomocą MySQL, bez używania do tego odczytywania rekordów do tablicy PHP i później w pętli usuwania ich rekord po rekordzie.

Kombinuję z wykorzystaniem NOT IN, czyli coś w stylu:

DELETE FROM 
	rekordy
WHERE
	rekord_id NOT IN 
				(
					SELECT 
						rekord_id,
					FROM 
						rekordy
					ORDER BY 
						`rekord_data` DESC 
					LIMIT 200
				)

ale do tego trzeba jeszcze to grupować po numerze serii: rekord_seria

Jak to dobrze napisać, by osiągnąć cel?

Mam nadzieję, że wystarczająco dokładnie i zrozumiale opisałem mój problem.

Możesz zadeklarować kursor przechowujący numery serii, a następnie już łatwo usunąć dla każdej serii rekordy pomijając 200.

DELETE FROM rekordy WHERE rekord_seria=@cursor_seria ORDER BY rekord_id DESC LIMIT 200,18446744073709551615;

Coś takiego także powinno zadziałać. Co prawda trochę nieeleganckie ale zawsze pojedyncze zapytanko.

DELETE FROM rekordy
WHERE rekord_id IN
  (SELECT rekord_id FROM -- podwójne zagnieżdżenie bo mysql czepia się selecta z tabeli, z której usuwamy
    (SELECT rekord_id FROM rekordy r WHERE rekord_data < 
      (SELECT rekord_data FROM rekordy WHERE rekord_seria = r.rekord_seria ORDER BY rekord_data DESC LIMIT 1 OFFSET 200)) as rtmp)

Trzeba by jednak sprawdzić czy się nie wysypie gdy mamy mniej niż 200 rekordów w jakiejś serii (nic nie trzeba usuwać).

Musisz stworzyć tabelę tymczasową, ponieważ MySQL z tylko sobie znanych przyczyn nie pozwala w DELETE umieścić we WHERE tej samej tabeli.

@sihox
Ale w ten sposób usuniesz ostatnie 200 rekordów, a one mają zostać. Jeśli chcemy iść tym sposobem to nierówność w drugą stronę i offset 199. Przy zapytaniach skorelowanych trzeba też uważać, zauważmy że wykonamy order by na tej samej tabeli tyle razy ile mamy rekordów. Nie jest to kolumna indeksowana więc w przypadku dużej ilości rekordów pojawi się problem z wydajnością. Ale to można prosto rozwiązać, wystarczy operować na rekord_id. Chodzi nam przecież tylko o kolejność dodań tych rekordów w obrębie jednej serii, a rekord_id pozwala to ustalić w takim samym stopniu jak rekord_data.

We WHERE nie może być, ale w JOIN już tak... Ot, taka ciekawostka (jakich wiele w MySQL-u).
Można zrobić bez tabel tymczasowych i bez kursorów. Uzywając zmiennych. Np tak:

/*
create table _mm_test1
(id int not null auto_increment primary key,
typ varchar(1) not null,
x float not null)
;

insert into _mm_test1(typ, x)
select * from (select 'x' typ union select 'a' union select 'b' union select 'm') typy, (select 1 d union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10) l;

insert into _mm_test1(typ, x)
select t.typ, t.x+10*d from _mm_test1 t, (select 1 d union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9 union select 10) l;
*/

select * from _mm_test1 t order by t.typ, t.id;

delete t.* from _mm_test1 t join (select @row:= case when @typ=t.typ then @row+1 else 1 end lp_by_typ, @typ:= t.typ typ, t.id from (select @typ:='', @row:=0) x, _mm_test1 t order by t.typ, t.id) x on x.id=t.id
where lp_by_typ>20;

select * from _mm_test1 t order by t.typ, t.id;

sihox napisał(a):

Coś takiego także powinno zadziałać. Co prawda trochę nieeleganckie ale zawsze pojedyncze zapytanko.

DELETE FROM rekordy
WHERE rekord_id IN
  (SELECT rekord_id FROM -- podwójne zagnieżdżenie bo mysql czepia się selecta z tabeli, z której usuwamy
    (SELECT rekord_id FROM rekordy r WHERE rekord_data < 
      (SELECT rekord_data FROM rekordy WHERE rekord_seria = r.rekord_seria ORDER BY rekord_data DESC LIMIT 1 OFFSET 200)) as rtmp)

Trzeba by jednak sprawdzić czy się nie wysypie gdy mamy mniej niż 200 rekordów w jakiejś serii (nic nie trzeba usuwać).

Działa bardzo dobrze z wyjątkiem tego, że zostawia max 201 najmłodszych rekordów w każdej serii. Zamiast liczby 200 powinno być więc 199 i wtedy działa tak jak chciałem niezależnie od tego, czy seria ma więcej niż 200 rekordów, czy mniej.

Bardzo dziękuję - znowu czegoś się nauczyłem :)

Pozostałym kolegom także dziękuję, za poświęcony czas.