Fórum Root.cz
Hlavní témata => Server => Téma založeno: thcom 17. 01. 2018, 17:20:28
-
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
-
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;
-
Ještě jsem Zapoměl napsat, že ty průběžné sumy bych potřeboval uložit do té původní tabulky :-P
-
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ě 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.
-
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, je to pěkně hloupá myšlenka ukazující na špatný návrh.
-
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á.
-
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
-
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
-
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)
-
na jeden pruchod tabulkou
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;
-
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.
-
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:
UPDATE tabulka t1
SET vklad_celkem = (
SELECT SUM(vklad)
FROM tabulka t2
WHERE t1.klient_id = t2.klient_id
AND t2.datum <= t1.datum
)
-
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ů.
-
update na jeden průchod
update t as t1
join
(select id,
@s := IF(@prev = klient_id, @s + vklad, vklad) as vklad_celkem,
@prev := klient_id
from t
order by klient_id,
datum) as t2 on t1.id=t2.id
set t1.vklad_celkem=t2.vklad_celkem;
-
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:
UPDATE tabulka t1
SET vklad_celkem = (
SELECT SUM(vklad)
FROM tabulka t2
WHERE t1.klient_id = t2.klient_id
AND t2.datum <= t1.datum
)
Ano, to je lepší.
-
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
A ona ta editace někdy nastane? Nevím, o co přesně jde a jaký je zdroj těch záznamů, ale tipuji že to bude poměrně výjimečná situace. A v té se dá projet ten dotaz znovu s omezením na daného klienta.
-
A ona ta editace někdy nastane? Nevím, o co přesně jde a jaký je zdroj těch záznamů, ale tipuji že to bude poměrně výjimečná situace. A v té se dá projet ten dotaz znovu s omezením na daného klienta.
Pokud nenastane, mělo by na té tabulce být omezení, které editaci neumožní. Pokud nastat může, měl by na tabulce být trigger, který přepočítání zajistí automaticky. Spoléhat na to, že si někdo vzpomene, že by se měla přepočítat data, je nejlepší způsob, jak získat nekonzistentní databázi.
-
A ona ta editace někdy nastane? Nevím, o co přesně jde a jaký je zdroj těch záznamů, ale tipuji že to bude poměrně výjimečná situace. A v té se dá projet ten dotaz znovu s omezením na daného klienta.
Cokoliv, co z podstaty věci nemůže být vyloučeno, že nenastane, musíte počítat, že nastane. Je už pak jedno, jestli to bude nastávat pravidelně, nebo jen zřídka.
Pokud jde o aktuální (poslední) zůstatek, asi bych to řešil opravdu v jiné tabulce, ale i to bych se snažil velmi opatrně, aby byla data konzistentní. V Postgresu bych na to zvažoval materializovaný pohled - podle toho, o kolik dat se jedná, jestli by bylo výkonnostně únosné pouštět materializaci v zápětí po zápisu do hlavní tabule.
-
Ano, je to pěkně hloupá myšlenka ukazující na špatný návrh.
univerzální odpověď zakrývající neschopnost odpovědět na původní dotaz.
-
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ů.
+1
-
Kolik tam bude zaznamu?
Ja bych se klonil ve vlozene procedure, ktera to spocita. Nez delat nejakou dalsi tabulku, kde budes uchovavat aktualni stav