Dotaz: Smazat radky starsi nez... ale ponechat alespon 3 posledni

[tom]

Zdravim,

mam zhruba nasledujici MySQL tabulku MSG_LOG:

VARCHAR username
TEXT    msg
DATE    created_at

A potreboval bych odmazat zpravy starsi nez 1 mesic, ale ponechat alespon 3 posledni pro kazdy username (i kdyz budou treba starsi nez 1 mesic)

Verim, ze uz to nekdo musel resit, ale asi se googlu spatne ptam :(

Dekuji

Odpovědi

[X]

Pokud se jedna o jednorazovou akci, tak si nejprve pro kazdeho uzivatele dohledej ID prvnich tri zaznamu starsixh nez jeden mesic a nasledne smaz vsechny zaznamy starsi jeden mesic s vyjimkou ID zaznamu ktere jsi si dohledal predtim..

[X]

Neco ve smyslu:

DELETE starsi nez mesic EXCEPT (SELECT prvni tri starsi nez mesic pro kazdeho usera);

[okbobcz]

Tohle je klasická úloha nalezení top N pro každou skupinu. Korelovaným poddotazem se dá udělat top 1 pro každou skupinu. Ale top n je dost komplikované bez moderního SQL, které je v MySQL až od 8čky. Dá se to udělat jednoduše LATERAL joinem, ještě jednodušeji window funkcemi, a poměrně komplikovaně rekurzivním dotazem. Nejsem si jistý jestli některá z těchto možností byla ve starších verzích MySQL než je nejnovější řada. Ve starších řadách je to možné vyřešit aplikačně nebo uloženou procedurou - skrz iteraci přes kurzor nad uživateli.

[EtDirloth]

Trochu nestastne definovane zadanie vzhladom na dve veci:

• kedze je pouzity typ DATE a predpokladam, ze v jednom dni moze byt viac zaznamov, tak sa tazko urci, ktore 3 z viacerych s rovnakym datumom su tie posledne
• "ponechat aspon 3" je dost vagne - ale zhodou okolnosti to zjednodusi implementaciu tej query - len jej vysledok nebude deterministicky

ako uz Pavel nadomnou hovoril, v starom mysql chyba podpora korelovanych poddotazov v DELETE - pre mysql 5.6, ktore som skusal na sqlfiddle som prisiel iba na toto:

CREATE TABLE msg_log
(
   username   VARCHAR(100)
 , msg        TEXT(100)
 , created_at DATE
);
CREATE INDEX idx_msg_log_created_at ON msg_log (username, created_at);
INSERT INTO msg_log VALUES ('y', 'A', CURRENT_DATE - interval 6 month);
INSERT INTO msg_log VALUES ('x', 'B', CURRENT_DATE - interval 5 month);
INSERT INTO msg_log VALUES ('x', 'C', CURRENT_DATE - interval 4 month);
INSERT INTO msg_log VALUES ('x', 'D', CURRENT_DATE - interval 4 month);
INSERT INTO msg_log VALUES ('x', 'E', CURRENT_DATE - interval 4 month);
INSERT INTO msg_log VALUES ('x', 'F', CURRENT_DATE - interval 3 month);
INSERT INTO msg_log VALUES ('x', 'G', CURRENT_DATE - interval 2 month);
INSERT INTO msg_log VALUES ('x', 'H', CURRENT_DATE - interval 1 month);
INSERT INTO msg_log VALUES ('x', 'I', CURRENT_DATE - interval 1 day);
INSERT INTO msg_log VALUES ('y', 'J', CURRENT_DATE);
INSERT INTO msg_log VALUES ('z', 'K', CURRENT_DATE - interval 10 month);
INSERT INTO msg_log VALUES ('z', 'L', CURRENT_DATE - interval 9 month);
INSERT INTO msg_log VALUES ('z', 'M', CURRENT_DATE - interval 8 month);
INSERT INTO msg_log VALUES ('z', 'N', CURRENT_DATE - interval 7 month);
INSERT INTO msg_log VALUES ('z', 'O', CURRENT_DATE - interval 6 month);
INSERT INTO msg_log VALUES ('z', 'P', CURRENT_DATE - interval 3 month);

DELETE d
  FROM msg_log AS d
  WHERE d.username = 'z'
    AND d.created_at < (
   SELECT min(x.created_at) min_ts
     FROM (
      SELECT *
         FROM msg_log l
         WHERE l.created_at < CURRENT_DATE - interval 1 month
           AND l.username = 'z'
         ORDER BY l.created_at DESC
         LIMIT 3
   ) x)
;

...a volat to pre kazdeho usera zvlast (v priklade 'z').

tu je kompletny priklad pre PostgreSQL (testovane na 12.6):

CREATE TABLE msg_log
(
   username   VARCHAR
 , msg        TEXT
 , created_at DATE
;
CREATE INDEX idx_msg_log_created_at ON msg_log (username, created_at);

INSERT INTO msg_log VALUES ('y', 'A', current_date - interval '6 month');
INSERT INTO msg_log VALUES ('x', 'B', current_date - interval '5 month');
INSERT INTO msg_log VALUES ('x', 'C', current_date - interval '4 month');
INSERT INTO msg_log VALUES ('x', 'D', current_date - interval '4 month');
INSERT INTO msg_log VALUES ('x', 'E', current_date - interval '4 month');
INSERT INTO msg_log VALUES ('x', 'F', current_date - interval '3 month');
INSERT INTO msg_log VALUES ('x', 'G', current_date - interval '2 month');
INSERT INTO msg_log VALUES ('x', 'H', current_date - interval '1 month');
INSERT INTO msg_log VALUES ('x', 'I', current_date - interval '1 day');
INSERT INTO msg_log VALUES ('y', 'J', current_date);
INSERT INTO msg_log VALUES ('z', 'K', current_date - interval '10 month');
INSERT INTO msg_log VALUES ('z', 'L', current_date - interval '9 month');
INSERT INTO msg_log VALUES ('z', 'M', current_date - interval '8 month');
INSERT INTO msg_log VALUES ('z', 'N', current_date - interval '7 month');
INSERT INTO msg_log VALUES ('z', 'O', current_date - interval '6 month');
INSERT INTO msg_log VALUES ('z', 'P', current_date - interval '3 month');

WITH users AS (
-- idealne mat ulozene v tabulke, na ktoru odkazuje cudzi kluc na msg_log(username)
      SELECT DISTINCT username FROM msg_log
   ), maxDateKeptPerUser AS (
      SELECT u.username, (
         SELECT min(x.created_at)
             FROM (SELECT *
                     FROM msg_log l
                     WHERE l.created_at < CURRENT_DATE - interval '1 month'
                       AND l.username = u.username
                     ORDER BY l.created_at DESC
                     LIMIT 3
                  ) x
         ) AS created_at
         FROM users AS u
   )
DELETE FROM msg_log AS d
   USING maxDateKeptPerUser AS m
   WHERE d.username   = m.username
     AND d.created_at < m.created_at
;

[ajtacka]

To je otazka jazyka nad tym, napriklad RoR(RUR), z ktoreho ale bola odstranene pokrocilejsie veci v zaujme pokroku, lenze je to ako skrabanie sa lavou nohou za pravym uchom.

Zda sa to neustaly proces, lenze clovek nevie, co si ma o tom mysliet a ako to vyriesit. Ponukaju sa moznosti co s tym, ale ani jedna sa mi nepaci.

Nieco je chybne.