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

[thcom]

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

[Reklama]

[Ivan Nový]

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]

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ý]

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ý]

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.

[Reklama]

[pb.]

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 :-)

[Miroslav Šilhavý]

Ano, je to pěkně hloupá myšlenka ukazující na špatný návrh.

[Lemming]

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]

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]

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ý]

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]

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]

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.

[Filip Jirsák]

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
  )


[Filip Jirsák]

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ů.