GoldenLine
Zaloguj się
Adam W.

Adam W. senior php
developer, Symfony

Temat: pytanie o zapytanie - X ostatnich wpisów dla każdego

witam,

tabela msg:
id, iduser, created_at, message

sytuacja teraz jest taka, że userzy dodają dane do tej tabeli. chciałbym teraz wyczyścić trochę tą tabelę i usunąć stare wpisy. za stare uznaję wpisy, starsze niż 10 ostatnio dodanych wpisów przez każdego użytkownika.

czyli po wykonaniu zapytania mają zostać najnowsze wpisy (10 ostatnio dodanych przez użytkownika).

jak to ugryźć? tabela tymczasowa z id?
Link do wypowiedziLink

konto usunięte

Temat: pytanie o zapytanie - X ostatnich wpisów dla każdego

Nie piszesz w jakim systemie pracujesz, podaję jedno z wielu możliwych rozwiązań dla Oracle.

Kod tworzący tabelę (zakładam, że id to klucz główny):

CREATE TABLE msg (
id NUMBER PRIMARY KEY,
iduser NUMBER,
created_at DATE,
message VARCHAR2(100)
);

Dodaję pseudolosowe rekordy do tabeli (iduser to liczby: 1,2,3,4,5):

BEGIN
FOR i IN 1..1000 LOOP
INSERT INTO msg(id, iduser, created_at, message) VALUES(i, TRUNC(DBMS_RANDOM.VALUE(1,5)), SYSDATE, DBMS_RANDOM.STRING('u',50));
END LOOP;
COMMIT;
END;
/

Robię porządki tak jak prosisz:

BEGIN
FOR i IN 1..5 LOOP
DELETE FROM msg WHERE id IN (SELECT id FROM msg WHERE iduser = i
MINUS
SELECT id FROM (SELECT id FROM msg WHERE iduser = i ORDER BY 1 DESC) WHERE rownum <= 10);
END LOOP;
COMMIT;
END;
/

Poprawność można sprawdzić dwiema kwerendami:

SELECT iduser, COUNT(*) FROM msg GROUP BY iduser;
SELECT * FROM msg;

Profilaktycznie sprawdź na kopii tabeli produkcyjnej, czy nie ma jakiegoś błędu :) Powyższe rozwiązanie nie jest optymalne kosztowo (w niedzielę optymalizacja mi nie idzie).Ireneusz Pastusiak edytował(a) ten post dnia 14.06.09 o godzinie 09:29
Link do wypowiedziLink
Adam W.

Adam W. senior php
developer, Symfony

Temat: pytanie o zapytanie - X ostatnich wpisów dla każdego

Ireneusz Pastusiak:
Nie piszesz w jakim systemie pracujesz, podaję jedno z wielu możliwych rozwiązań dla Oracle.

Profilaktycznie sprawdź na kopii tabeli produkcyjnej, czy nie ma jakiegoś błędu :) Powyższe rozwiązanie nie jest optymalne kosztowo (w niedzielę optymalizacja mi nie idzie).Ireneusz Pastusiak edytował(a) ten post dnia 14.06.09 o godzinie 09:29

dzięki bardzo za profesjonalne podejście:)
fakt - zapomniałem o tym żeby podac, że to ma być na mysql.
ale tak właśnie myślałem, że będzie trzeba do tego 3 selecty zrobić jak nie więcej;) dla mysql pewnie jakąś tabelę pomocniczą/tymczasową trzeba będzie utworzyć, żeby można było zrobić takie bajery jak w Oracle są w standardzie.
Link do wypowiedziLink

konto usunięte

Temat: pytanie o zapytanie - X ostatnich wpisów dla każdego

zastosuj konstrukcję analogiczną do RANK
tu masz przykład: http://vbamania.blogspot.com/2009/03/dzis-po-chwili-sz...

możesz wziąć id elementów o rank mniejszym niż 10 i je skasowaćPrzemysław R. edytował(a) ten post dnia 14.06.09 o godzinie 14:53
Link do wypowiedziLink
Sebastian Kolski

Sebastian Kolski programista/DBA

Temat: pytanie o zapytanie - X ostatnich wpisów dla każdego

Ireneusz Pastusiak:
Robię porządki tak jak prosisz:

BEGIN
FOR i IN 1..5 LOOP
DELETE FROM msg WHERE id IN (SELECT id FROM msg WHERE iduser = i
MINUS
SELECT id FROM (SELECT id FROM msg WHERE iduser = i ORDER BY 1 DESC) WHERE rownum <= 10);
> END LOOP;
COMMIT;
END;
/

W Oracle, można użyć 



select id, iduser, created_at, row_number() over (partition by iduser order by created_at desc) rn from msg;


co dodaje kolumnę rn z numerem wiadomości numerowanych po kolei od ostatniej dla każdego iduser osobno.

Czyli kasowanie to:



delete from msg m where exists (

select id from (select id, row_number() over (partition by iduser order by created_at desc) rn from msg) subquery

where rn > 10 and subquery.id = m.id);


Koszt 2 pełne odczyty tabeli aby obsłużyć wszystkich iduser.

Ponieważ ma to być mysql to ja bym to zrobił programem, który by zrobił "select id, iduser from msg order by created_at desc", następnie w miarę wczytywania danych zliczał ilość wiadomości dla każdego user'a i jeśli przekroczyła by 10 przesyłał bym id do osobnego procesu który by grupował te id w paczki i kasował paczkami.

Przy dużych ilościach danych (np kilkadziesiąt milionów wpisów) takie podejście pozwoliło mi przy podobnym problemie skrócić czas przetwarzania o 4 rzędy wielkości.

Jedyny problem to, że o ile mi wiadomo mysql nie ma pseudo kolumny rowid, którą w oracle można użyć do identyfikacji wiersza do usunięcia (select rowid, iduser ... i przekazywanie rowid). Co sprawia, że oprócz pełnego odczytu tablicy w celu wyszukania rekordów do usunięcia, samo usunięcie wiąże się z dostępem do tabeli przez indeks na id.Sebastian Kolski edytował(a) ten post dnia 14.06.09 o godzinie 14:30
Link do wypowiedziLink
Janusz Skudrzyk

Janusz Skudrzyk Członek zarządu,
weblabs.pl

Temat: pytanie o zapytanie - X ostatnich wpisów dla każdego

Jeżeli tabela jest bardzo duża, a możesz pozwolić sobie na parę minut przerwy, to: zrób nowa tabelę, przerzuć do niej odpowiednie wiadomości, ubij starą tabelę, zmień nazwę nowej tabeli. Przy okazji będziesz mógł podrasować indeksy, o ile wymagają uwagi.
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

[Oracle] Zapytanie z przyro...




Wyślij zaproszenie do
Anuluj