MySQL (30) - průběžné součty
Jak vznést dotaz třeba na zůstatky na běžném účtu? A lze použít vícenásobných spojení téže tabulky i k aktualizaci dat?
22.7.2005 06:00 |
Petr Zajíc
| Články autora
| přečteno 27646×
Poslední ze "záhad" jazyka SQL, kterou bych se v tomto seriálu rád
zabýval, je vracení tzv. průběžných součtů (running totals). Ty si
můžete představit jako například zůstatky peněz na běžném účtu nebo
jako zůstatky zboží při pohybu ve skladu. Jak uvidíme, opět se něco
takového dá provést pomocí spojení tabulky se sebou samotnou.
Dotaz na průběžné součty
Vyjděme z příkladu se zůstatky na běžném účtu. Mějme například
následující tabulku s pohyby na účtu a tato data:
create table ucet (datum
date, castka decimal (10,2));
insert into ucet (datum, castka) values ('20050101', 5000);
insert into ucet (datum, castka) values ('20050110', -2700);
insert into ucet (datum, castka) values ('20050111', -1500);
insert into ucet (datum, castka) values ('20050120', 2200);
Sestavme nyní něco jako "výpis z účtu", tedy dotaz, který vrátí
všechny pohyby i s jejich aktuálními zůstatky! Pokud zavzpomínáte na
díl seriálu o číslování
záznamů, bude i teď postup velmi podobný. Stačí šikovně spojit
tabulku samu se sebou pomocí poddotazu:
select ucet.datum,
ucet.castka,
(select sum(vypocet.castka) from ucet as vypocet where vypocet.datum
<= ucet.datum)
from ucet order by datum;
Všimněte si, že tohle je jeden z mála příkladů, kdy poddotaz logicky
smí obsahovat klauzuli "sum" bez odpovídající kaluzule "group by". To
proto, že součet bez definování skupiny sečte všechna data v daném
sloupci dotazu, což je přesně to, co potřebujeme. Jak jsme již v tomto
seriálu uvedli, drtivá většina poddotazů jde přepsat na spojení, tohle
nevyjímaje. Takže, pokud jste spíše milovníky spojení, nebo pokud se ve
vašem případě spojení prokáže být rychlejší, můžete to napsat takto:
select ucet.datum,
ucet.castka, sum(vypocet.castka)
from ucet join ucet as vypocet on ucet.datum>=vypocet.datum
group by ucet.datum, ucet.castka order by ucet.datum;
V tomto spojení se nevyskytují legrácky typu "klauzule SELECT v
klauzuli JOIN" a podobné. Je to vlastně velmi čtivý dotaz, když se na
něj podíváte. Jsou však oba uvedené dotazy v pořádku? Na první pohled
jistě ano (i když se v reálné aplikaci jistě budou lišit rychlostí
provádění). Co však, když v jeden den proběhne více operací najednou?
Pojďme přidat následující řádek do tabulky!
insert into ucet (datum,
castka) values ('20050110', -500);
Pokud si dotazy vyzkoušíte teď, zjistíte, že pro oba (teoreticky pro
všechny) záznamy ze dne 10.1.2005 vracejí stejný mezisoučet. To je z
hlediska, z něhož jsme dotazy psali naprosto logické, protože pro každý
den zjišťují stav teprve po sečtení všech záznamů. Abychom to obešli,
musíme jednotlivé záznamy od sebe nějak rozlišit a sčítat tyto
rozlišené záznamy. V praxi to
nejspíš nebude žádný velký problém,
protože tabulka, jako je tato, bude mít téměř jistě nějaký klíč, třeba
automaticky číslované řádky. Takže sem s ním!
alter table ucet add id
int not null auto_increment primary key;
A přepišme naše dotazy tak, aby vzaly číslování řádků v úvahu. Ten
první
by mohl vypadat asi následovně:
select ucet.datum,
ucet.castka,
(select sum(vypocet.castka) from ucet as vypocet where vypocet.datum
<= ucet.datum
and vypocet.id <= ucet.id)
from ucet order by datum;
a ten druhý takhle nějak:
select ucet.datum,
ucet.castka, sum(vypocet.castka)
from ucet join ucet as vypocet on ucet.datum>=vypocet.datum and ucet.id >= vypocet.id
group by ucet.datum, ucet.castka order by ucet.datum;
Tím, že se údaje rozliší, již nehrozí, že by se provedl mezisoučet
pro více než jeden řádek.
Pozn.:Člověk někdy podlehne dojmu, že by
stačilo odlišit od sebe
jednotlivé záznamy například podle znaménka nebo podle částky, a že by
se k tomu nemuselo volat na pomoc automatické číslování. Jenomže není
to tak. Kdybychom odlišovali částky podle znaménka, seskupily by se nám
součty pro všechny příjmy a výdaje za den, ale opět by nebyly rozlišeny
jednotlivé pohyby. Kdybychom seskupovali podle částky, mohl by
vzniknout stejný problém v případě, když by v jeden den přišly nebo
odešly dvě platby ve stejné výši.
A co akční dotazy?
Mohla by vzniknout otázka, zda se dají triky nastíněné v několika
předchozích dílech seriálu použít rovněž k úpravě dat, nejen k jejich
vybírání. To je dobrá otázka. Například, daly by se využít zkušenosti z
tohoto dílu k zapsání zůstatků na účtu do samostatného sloupce? Mějme
tedy sloupec zachycující zůstatek:
alter table ucet add
column zustatek decimal (10,2);
Do něj se dají celkem
elegantně přepsat zůstatky pouhou úpravou
výběrového dotazu na akční:
update ucet join
(select ucet.id, sum(vypocet.castka) as zustatek
from ucet join ucet as vypocet on ucet.datum>=vypocet.datum and
ucet.id >= vypocet.id
group by ucet.id) vypocet on ucet.id = vypocet.id
set ucet.zustatek = vypocet.zustatek;
Přiznám se ale, že se tato technika moc často nevyužívá a že ji
příliš v lásce nemám. Důvod je velmi jednoduchý - v praxi to nemá moc
velký smysl. Kdybyste totiž chtěli na sloupec zůstatků spoléhat, musíte
jej přepočítat po každé změně tabulky - a to při větším množství
záznamů může docela trvat.
Na druhou stranu se tato technika uchovávání výsledků výpočtů
používá celkem často u "statických" snímků dat, která pak slouží pro
nějaké analýzy či rozhodování. Tam to potom funguje zhruba tak, že:
- Z "ostrých" dat se pořídí kopie
- Na ní se provedou výpočty a uloží se pomocné hodnoty na
odpovídající místa (například, jak jsme viděli, hodnoty zůstatků do
vlastního sloupce)
- Výsledek se předloží člověku (nebo programu), který s tím pak
pracuje jako s daty "jen pro čtení"
- Po ukončení analýz se data zničí (nejsou již nejspíš aktuální)
- V "ostré" databázi se mezitím vesele zapisuje dál.
To proto, že někdy je třeba vyhodnotit miliony záznamů - ostrý
systém by to mohlo zatěžovat, ale malá nepřesnost způsobéná tím, že
máme kopii "pouze včerejších" dat nemusí vadit.
Verze pro tisk
|
Nejsou žádné diskuzní příspěvky u dané položky.
Příspívat do diskuze mohou pouze registrovaní uživatelé.
|
|

Vyhledávání software

Vyhledávání článků
28.11.2018 23:56 /František Kučera Prosincový sraz spolku OpenAlt se koná ve středu 5.12.2018 od 16:00 na adrese Zikova 1903/4, Praha 6. Tentokrát navštívíme organizaci CESNET. Na programu jsou dvě přednášky: Distribuované úložiště Ceph (Michal Strnad) a Plně šifrovaný disk na moderním systému (Ondřej Caletka). Následně se přesuneme do některé z nedalekých restaurací, kde budeme pokračovat v diskusi.
Komentářů: 1
12.11.2018 21:28 /Redakce Linuxsoft.cz 22. listopadu 2018 se koná v Praze na Karlově náměstí již pátý ročník konference s tématem Datová centra pro business, která nabídne odpovědi na aktuální a často řešené otázky: Jaké jsou aktuální trendy v oblasti datových center a jak je optimálně využít pro vlastní prospěch? Jak si zajistit odpovídající služby datových center? Podle jakých kritérií vybírat dodavatele služeb? Jak volit vhodné součásti infrastruktury při budování či rozšiřování vlastního datového centra? Jak efektivně datové centrum spravovat? Jak co nejlépe eliminovat možná rizika? apod. Příznivci LinuxSoftu mohou při registraci uplatnit kód LIN350, který jim přinese zvýhodněné vstupné s 50% slevou.
Přidat komentář
6.11.2018 2:04 /František Kučera Říjnový pražský sraz spolku OpenAlt se koná v listopadu – již tento čtvrtek – 8. 11. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma umění a technologie, IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář
4.10.2018 21:30 /Ondřej Čečák LinuxDays 2018 již tento víkend, registrace je otevřená.
Přidat komentář
18.9.2018 23:30 /František Kučera Zářijový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 20. 9. 2018 od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Tentokrát bez oficiální přednášky, ale zato s dobrým jídlem a pivem – volná diskuse na téma IoT, CNC, svobodný software, hardware a další hračky.
Přidat komentář
9.9.2018 14:15 /Redakce Linuxsoft.cz 20.9.2018 proběhne v pražském Kongresovém centru Vavruška konference Mobilní řešení pro business.
Návštěvníci si vyslechnou mimo jiné přednášky na témata: Nejdůležitější aktuální trendy v oblasti mobilních technologií, správa a zabezpečení mobilních zařízení ve firmách, jak mobilně přistupovat k informačnímu systému firmy, kdy se vyplatí používat odolná mobilní zařízení nebo jak zabezpečit mobilní komunikaci.
Přidat komentář
12.8.2018 16:58 /František Kučera Srpnový pražský sraz spolku OpenAlt se koná ve čtvrtek – 16. 8. 2018 od 19:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát jsou tématem srazu databáze prezentaci svého projektu si pro nás připravil Standa Dzik. Dále bude prostor, abychom probrali nápady na využití IoT a sítě The Things Network, případně další témata.
Přidat komentář
16.7.2018 1:05 /František Kučera Červencový pražský sraz spolku OpenAlt se koná již tento čtvrtek – 19. 7. 2018 od 18:00 v Kavárně Ideál (Sázavská 30, Praha), kde máme rezervovaný salonek. Tentokrát bude přednáška na téma: automatizační nástroj Ansible, kterou si připravil Martin Vicián.
Přidat komentář
Více ...
Přidat zprávičku
 Poslední diskuze
31.7.2023 14:13 /
Linda Graham iPhone Services
30.11.2022 9:32 /
Kyle McDermott Hosting download unavailable
13.12.2018 10:57 /
Jan Mareš Re: zavináč
2.12.2018 23:56 /
František Kučera Sraz
5.10.2018 17:12 /
Jakub Kuljovsky Re: Jaký kurz a software by jste doporučili pro začínajcího kodéra?
Více ...
|