LINUXSOFT.cz Přeskoč levou lištu
Uživatel: Heslo:  
   CZUKPL

> 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 23983×

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:

  1. Z "ostrých" dat se pořídí kopie
  2. 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)
  3. Výsledek se předloží člověku (nebo programu), který s tím pak pracuje jako s daty "jen pro čtení"
  4. Po ukončení analýz se data zničí (nejsou již nejspíš aktuální)
  5. 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

pridej.cz

 

DISKUZE

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ů

15.1.2018 0:51 /František Kučera

První letošní pražský sraz se koná již tento čtvrtek 18. ledna od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5). Vítáni jsou všichni příznivci svobodného softwaru a hardwaru, ESP32, DIY, CNC, SDR nebo dobrého piva. Prvních deset účastníků srazu obdrží samolepku There Is No Cloud… just other people's computers. od Free Software Foundation.


Přidat komentář

14.11.2017 16:56 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt – tradičně první čtvrtek před třetím pátkem v měsíci: 16. listopadu od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

12.11.2017 11:06 /Redakce Linuxsoft.cz
PR: 4. ročník odborné IT konference na téma Datová centra pro business proběhne již ve čtvrtek 23. listopadu 2017 v konferenčním centru Vavruška, v paláci Charitas, Karlovo náměstí 5, Praha 2 (u metra Karlovo náměstí) od 9:00. Konference o návrhu, budování, správě a efektivním využívání datových center nabídne odpovědi na aktuální a často řešené otázky, např Jaké jsou aktuální trendy v oblasti datových center a jak je využít pro vlastní prospěch? Jak zajistit pro firmu či jinou organizaci odpovídající služby datových center? Podle jakých kritérií vybrat dodavatele služeb? Jak volit součásti infrastruktury při budování či rozšiřování vlastního datového centra? Jak efektivně spravovat datové centrum? Jak eliminovat možná rizika? apod.
Přidat komentář

13.9.2017 8:00 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt – tentokrát netradičně v pondělí: 18. září od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

3.9.2017 20:45 /Redakce Linuxsoft.cz
PR: Dne 21. září 2017 proběhne v Praze konference "Mobilní řešení pro business". Hlavní tématy konference budou: nejnovější trendy v oblasti mobilních řešení pro firmy, efektivní využití mobilních zařízení, bezpečnostní rizika a řešení pro jejich omezení, správa mobilních zařízení ve firmách a další.
Přidat komentář

15.5.2017 23:50 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě DIY, CNC, SDR nebo morseovka? Přijď na sraz spolku OpenAlt, který se bude konat ve čtvrtek 18. května od 18:00 v Radegastovně Perón (Stroupežnického 20, Praha 5).
Přidat komentář

12.5.2017 16:42 /Honza Javorek
PyCon CZ, česká konference o programovacím jazyce Python, se po dvou úspěšných ročnících v Brně bude letos konat v Praze, a to 8. až 10. června. Na konferenci letos zavítá např. i Armin Ronacher, známý především jako autor frameworku Flask, šablon Jinja2/Twig, a dalších projektů. Těšit se můžete na přednášky o datové analytice, tvorbě webu, testování, tvorbě API, učení a mentorování programování, přednášky o rozvoji komunity, o použití Pythonu ve vědě nebo k ovládání nejrůznějších zařízení (MicroPython). Na vlastní prsty si můžete na workshopech vyzkoušet postavit Pythonem ovládaného robota, naučit se učit šestileté děti programovat, efektivně testovat nebo si v Pythonu pohrát s kartografickým materiálem. Kupujte lístky, dokud jsou.
Přidat komentář

2.5.2017 9:20 /Eva Rázgová
Putovní konference československé Drupal komunity "DrupalCamp Československo" se tentokrát koná 27. 5.2017 na VUT FIT v Brně. Můžete načerpat a vyměnit si zkušenosti z oblasti Drupalu 7 a 8, UX, SEO, managementu týmového vývoje, využití Dockeru pro Drupal a dalších. Vítáni jsou nováčci i experti. Akci pořádají Slovenská Drupal Asociácia a česká Asociace pro Drupal. Registrace na webu .
Přidat komentář

   Více ...   Přidat zprávičku

> Poslední diskuze

16.1.2018 1:08 / Ivan Pittner
verejna ip od o2 ubuntu

15.1.2018 17:26 / Mira Harvalik
Re: Jak udělat HTML/Javascript swiping gallery do mobilu?

30.12.2017 20:16 / Michal Knoll
odmocnina

31.8.2017 12:11 / Jaromir Obr
Re: ukůládání dat ze souboru

30.7.2017 11:12 / Jaromir Obr
Národní znaky

Více ...

ISSN 1801-3805 | Provozovatel: Pavel Kysilka, IČ: 72868490 (2003-2018) | mail at linuxsoft dot cz | Design: www.megadesign.cz | Textová verze