Pomoc - Szukaj - Użytkownicy - Kalendarz
Pełna wersja: Kasowanie rekordów do maksymalnej ilości X w każdej grupie
Forum PHP.pl > Forum > Bazy danych
L_Devil
Witam, mam pewien dość irytujący problem:

Mam w MySQL 5 tabelę:

Kod
+--+-----+----------------+
|id|owner|    blablabla   |
+--+-----+----------------+
|1 | 1   |     kobakovga  |
|2 | 1   |    dwawdadw    |
|3 | 1   |    d21r1rtt2   |
|4 | 2   |    koawdawdka  |
|5 | 2   |   dawawdaddwa  |
+--+-----+----------------+
Pole blablabla nie jest dla nas istotne. Jak widać rekordy mają id własne i id grupy (pole owner). Teraz chciałbym jednym zapytaniem pozbyć się starszych rekordów z danej grupy. Zależy mi, żeby w każdej grupie pozostały np. maks 2 rekordy, przy czym w pierwszej kolejności należy kasować te z niższymi numerami id. Nie ma reguły że te same grupy owner będą następowały bezpośrednio po sobie (np. owner =1 mogą mieć rekordy o id 2, 5, 15). Po wykonaniu takiego zapytania powyższa tabelka powinna dojść do takiego rezultatu:
Kod
+--+-----+----------------+
|id|owner|    blablabla   |
+--+-----+----------------+
|2 | 1   |    dwawdadw    |
|3 | 1   |    d21r1rtt2   |
|4 | 2   |    koawdawdka  |
|5 | 2   |   dawawdaddwa  |
+--+-----+----------------+

Zacząłem kombinować coś takiego:
  1. DELETE FROM tabelka GROUP BY owner
  2. ORDER BY id ASC LIMIT
  3. (SELECT 2-count(id) FROM tabelka GROUP BY owner)

Czy to jest poprawne rozumowanie?
teutates
Jak piszesz masz MySQL 5 wiec prosta procedurka lub trigger z kursorkiem i po sprawie:)
osiris
moze cos takiego (mozliwe bledy - nie sprawdzalem):
  1. DELETE FROM tabelka WHERE id IN (
  2. SELECT t1.id
  3. FROM tabela AS t1
  4. INNER JOIN ( SELECT owner, COUNT(*)
  5. FROM tabelka GROUP BY owner
  6. HAVING COUNT(*) > 2
  7. ) AS t3 ON t1.owner = t3.owner
  8. WHERE (SELECT COUNT(*) FROM tabela AS t2 WHERE t2.owner = t1.owner AND t2.id >= t1.id) > 2
  9. )

jak to dziala:
- podzapytanie w zlaczeniu sluzy do wyciagniecia tylko tych grup ktore maja nadmiarowe rekordy (w tym przykladzie > 2) - dla polepszenia efektywnosci
- podzapytanie w klauzuli WHERE oblicza odwrotny ranking dla danej pozycji w grupie
- wybieramy tylko te rekordy z dla ktorych ranking > 2
- usuwamy pasujace rekordy

dla rozjasnienia podaje jak bedzie wygladala tabela z obliczonym odwrotnym rankingiem:
  1. <?php
  2. id | owner | odwrotny ranking
  3.  1 1 3
  4.  2 1 2
  5.  3 1 1
  6.  4 2 4
  7.  5 2 3
  8.  6 2 2
  9.  7 2 1 
  10.  8 3 2
  11.  9 3 1
  12. ?>


Jak widac przy obliczonym odwrotnym rankingu wybranie rekordow do usuniecia jest juz banalne.

W innych bazach danych mozna to latwo uproscic jesli istnieje tam funkcja RANK.
Nie wiem jak wydajne to bedzie przy duzej liczbie rekordow na grupe, albo przy duzej liczbie grup. Sporym ograniczeniem efektywnosci jest tu na pewno operator IN, moze daloby sie to jakos zamienic na EXISTS.

Oczywiscie to zapytanie bedzie usuwac najstarsze rekordy, tylko jesli wartosci pola id beda odzwierciedlaly kolejnosc wstawiania rekordow (tak jak ma to miejsce dla kolumn z atrybutem AUTO_INCREMENT).

Pozdrawiam
To jest wersja lo-fi głównej zawartości. Aby zobaczyć pełną wersję z większą zawartością, obrazkami i formatowaniem proszę kliknij tutaj.
Invision Power Board © 2001-2025 Invision Power Services, Inc.