MySQL: jak projít všechny záznamy tabulky a udělat pro každý UPDATE?

thcom

  • ***
  • 147
    • ICQ komunikátor - 968601
    • Zobrazit profil
    • E-mail
Ahoj, mam tabulku, se sloupecky klient_id, datum, vklad, vklad_celkem
potreboval bych tabulku projit serazenou podle pole datum, postupne nascitat polozku vklad pro kazde klient_id a aktualnu prubeznou sumu ulozit do pole vklad_celkem

popisuju to asi dost dementne, proste potrebuji aby v poli vklad_celkem byl vzdy aktualni kumulovany vklad pro kazdeho klienta

vim, ze to jde udelat kodem a postune projit celou tabulku, ale zajima me, zda toto lze vyresit i SQL dotazem


dekuji
« Poslední změna: 17. 01. 2018, 22:13:43 od Petr Krčmář »


Ivan Nový

Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #1 kdy: 17. 01. 2018, 18:45:22 »
Ahoj, mam tabulku, se sloupecky klient_id, datum, vklad, vklad_celkem
potreboval bych tabulku projit serazenou podle pole datum, postupne nascitat polozku vklad pro kazde klient_id a aktualnu prubeznou sumu ulozit do pole vklad_celkem

popisuju to asi dost dementne, proste potrebuji aby v poli vklad_celkem byl vzdy aktualni kumulovany vklad pro kazdeho klienta

vim, ze to jde udelat kodem a postune projit celou tabulku, ale zajima me, zda toto lze vyresit i SQL dotazem


dekuji

Jde to :-)

SELECT  klient, MAX(datum), MIN(datum), SUM(vklad) FROM tbl GROUP BY klient;

Pokud by to mělo být průběžné použijete něco jako:

SELECT *, (SELECT SUM(vklad) FROM tbl t2 WHERE t1.date >= t2.date AND t1.klient = t2.klient) FROM tbl t1;


thcom

  • ***
  • 147
    • ICQ komunikátor - 968601
    • Zobrazit profil
    • E-mail
Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #2 kdy: 17. 01. 2018, 18:55:57 »
Ještě jsem Zapoměl napsat, že ty průběžné sumy bych potřeboval uložit do té původní tabulky :-P

Ivan Nový

Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #3 kdy: 17. 01. 2018, 19:02:11 »
Ještě jsem Zapoměl napsat, že ty průběžné sumy bych potřeboval uložit do té původní tabulky :-P

UPDATE tbl u1, (SELECT (SELECT SUM(vklad) FROM tbl t2 WHERE t1.date >= t2.date AND t1.klient = t2.klient) as vklad_celkem FROM tbl t1) u2 SET u1.vklad_celkem = u2.vklad_celkem;

ale bude fungovat jen pokud bude jeden vklad za den, pokud by jich bylo více, musíte tabulky synchronizovat třeba podle nějakého čísla transakce, nebo data i s přesným časem.


Ivan Nový

Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #4 kdy: 17. 01. 2018, 19:10:06 »
Ještě jsem Zapoměl napsat, že ty průběžné sumy bych potřeboval uložit do té původní tabulky :-P

UPDATE tbl u1, (SELECT (SELECT SUM(vklad) FROM tbl t2 WHERE t1.date >= t2.date AND t1.klient = t2.klient) as vklad_celkem FROM tbl t1) u2 SET u1.vklad_celkem = u2.vklad_celkem;

ale bude fungovat jen pokud bude jeden vklad za den, pokud by jich bylo více, musíte tabulky synchronizovat třeba podle nějakého čísla transakce, nebo data i s přesným časem.

ještě malá změna

UPDATE tbl u1, (SELECT (SELECT SUM(vklad) FROM tbl t2 WHERE t1.datum >= t2.datum AND t1.klient = t2.klient ORDER BY datum) AS vklad_celkem FROM tbl t1) u2 SET u1.vklad_celkem = u2.vklad_celkem ORDER BY datum WHERE u1.datum = u2.datum AND u1.klient = u2.klient;

Ale neladil jsem to, nezkoušel.


pb.

Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #5 kdy: 17. 01. 2018, 19:36:17 »
UPDATE tbl u1, (SELECT (SELECT SUM(vklad) FROM tbl t2 WHERE t1.datum >= t2.datum AND t1.klient = t2.klient ORDER BY datum) AS vklad_celkem FROM tbl t1) u2 SET u1.vklad_celkem = u2.vklad_celkem ORDER BY datum WHERE u1.datum = u2.datum AND u1.klient = u2.klient;

Jde to. Ale s přibývajícími záznamy vám začne být jasné, že tohle nechcete :-)

Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #6 kdy: 17. 01. 2018, 20:17:22 »
Ano, je to pěkně hloupá myšlenka ukazující na špatný návrh.

Lemming

Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #7 kdy: 17. 01. 2018, 20:36:50 »
Dělat to opakovaně je samozřejmě nesmysl, ale naplnit na začátku existující tabulku nově rozšířenou o ten sloupec a pak ho plnit automaticky při vytváření každého záznamu, to smysl dává.

thcom

  • ***
  • 147
    • ICQ komunikátor - 968601
    • Zobrazit profil
    • E-mail
Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #8 kdy: 17. 01. 2018, 20:48:07 »
Ano, je to pěkně hloupá myšlenka ukazující na špatný návrh.

me nic jineho nenapadlo, potrebuji pri zobrazovani pohybu a stavu portfolia spocitat aktualni podil na celkove investici
takze je lepsi si ten soucet predpocitat, nez poustet celkem zbytecne slozity SUM pri kazdem zobrazeni

ale rad si necham poradit neco lepsiho

thcom

  • ***
  • 147
    • ICQ komunikátor - 968601
    • Zobrazit profil
    • E-mail
Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #9 kdy: 17. 01. 2018, 20:48:36 »
Dělat to opakovaně je samozřejmě nesmysl, ale naplnit na začátku existující tabulku nově rozšířenou o ten sloupec a pak ho plnit automaticky při vytváření každého záznamu, to smysl dává.

to ma smysl dokud nenastane editace ve starsich datech

Ivan Nový

Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #10 kdy: 17. 01. 2018, 21:12:01 »
UPDATE tbl u1, (SELECT (SELECT SUM(vklad) FROM tbl t2 WHERE t1.datum >= t2.datum AND t1.klient = t2.klient ORDER BY datum) AS vklad_celkem FROM tbl t1) u2 SET u1.vklad_celkem = u2.vklad_celkem ORDER BY datum WHERE u1.datum = u2.datum AND u1.klient = u2.klient;

Jde to. Ale s přibývajícími záznamy vám začne být jasné, že tohle nechcete :-)

Ano přesně.

Explain plan pro tento příkaz bude: select -> nested loops -> full scan(ALL) -> Subquery -> Full Scan (ALL) -> Subquery -> Unique index scan.

A příkaz SELECT klient, SUM(vklad) FROM vklad GROUP BY klient;

a Explain plan, při indexu na klienta je: Full index scan(index)


mmm

Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #11 kdy: 17. 01. 2018, 21:40:28 »
na jeden pruchod tabulkou
Kód: [Vybrat]
select @s := IF(@prev = client_id, @s + vklad, vklad) as vklad_celkem, @prev := client_id as client_id from t order by client_id, date;

Kit

Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #12 kdy: 17. 01. 2018, 21:43:09 »
S podobným úkolem jsem si kdysi také lámal hlavu. Nakonec jsem to vyřešil vloženou procedurou - milióny záznamů pak nepředstavovaly žádný problém.

Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #13 kdy: 17. 01. 2018, 22:32:33 »
UPDATE tbl u1, (SELECT (SELECT SUM(vklad) FROM tbl t2 WHERE t1.date >= t2.date AND t1.klient = t2.klient) as vklad_celkem FROM tbl t1) u2 SET u1.vklad_celkem = u2.vklad_celkem;
ale bude fungovat jen pokud bude jeden vklad za den, pokud by jich bylo více, musíte tabulky synchronizovat třeba podle nějakého čísla transakce, nebo data i s přesným časem.
Tohle řešení je správně (teda pokud je možné v MySQL udělat takovýhle JOIN v UPDATE), ty přidané ORDER BY jsou tam k ničemu, respektive rozumná databáze vám to s nimi ani nedovolí spustit. Vzhledem k tomu, že je řeč o MySQL a je takováhle struktura databáze, o miliony záznamů asi nepůjde.

Já bych použil korelovaný subdotaz, připadá mi to čitelnější a nevyžaduje to ten divný UPDATE JOIN, který je myslím specifický pro MySQL:
Kód: [Vybrat]
UPDATE tabulka t1
  SET vklad_celkem = (
    SELECT SUM(vklad)
      FROM tabulka t2
      WHERE t1.klient_id = t2.klient_id
        AND t2.datum <= t1.datum
  )

Re:MySQL, jak projit vsechny zaznamy tabulky a udelat pro kazdy UPDATE ?
« Odpověď #14 kdy: 17. 01. 2018, 22:47:55 »
Ano, je to pěkně hloupá myšlenka ukazující na špatný návrh.

me nic jineho nenapadlo, potrebuji pri zobrazovani pohybu a stavu portfolia spocitat aktualni podil na celkove investici
takze je lepsi si ten soucet predpocitat, nez poustet celkem zbytecne slozity SUM pri kazdem zobrazeni

ale rad si necham poradit neco lepsiho
Takže nepotřebujete znát historický součet po každém vkladu, ale stačí vám znát aktuální součet (po posledním vkladu). Takže si vytvořte druhou tabulku, kde bude klient_id a vklad_celkem, a tuto tabulku aktualizujte triggerem při změně v tabulce vkladů.