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

> MySQL (34) - větvení kódu a pivotní tabulky

Znát podmíněné příkazy v MySQL může přijít vhod. Zejména na šílenosti typu pivotních tabulek a souhrnů. Což je dnešní téma seriálu MySQL.

19.8.2005 07:00 | Petr Zajíc | Články autora | přečteno 23155×

Komerční sdělení: Pořádáme Kurzy MySQL

Situaci, kdy je potřeba provést nebo neprovést určitý úsek kódu v závislosti na splnění podmínky budete znát spíše z "klasických" programovacích jazyků. Jak ale uvidíme v dnešním díle seriálu o MySQL, jde to použít i v dotazech, a leckdy to má docela opodstatnění. Tak či tak, znát příkazy pro rozbočení kódu může být docela užitečné.

Větvení kódu v MySQL

Především je dobře si uvědomit, kdy se asi budou podobné hrátky s příkazy používat. Bude to v situacích, kde chceme vrátit (či změnit) data v závislosti na nějaké podmínce - nejčastěji v závislosti na datech jiných. Tak nám například jeden a tentýž dotaz může vrátit různá data na základě toho, jaká hodnota je uvedena ve sloupci a podobně. Pro některé typy dotazů je to nepostradatelné.

Příkaz IF

Příkaz IF je obdobou ternárního operátoru, jaký známe z většiny programovacích jazyků. Obsahuje tři části. Vyhodnotí se podmínka (1. část), jestliže je splněna, je vrácen jako výsledek výraz ve druhé části, jestliže ne je vrácen výraz ve třetí části. Mohlo by to vypadat nějak takto:

select jmeno, if (plat>15000,'nadprůměrný','podprůměrný') as plat from pracovnik

V případě jednoduchých rozhodování tento příkaz přes svoji úspornost jistě postačí. Může to ušetřit následné zpracování "řádek po řádku" v nějaké aplikaci, která bude tato data konzumovat. Samozřejmě, že podobnou konstrukci lze použít nejen v příkazu SELECT, ale třeba i v UPDATE.

Příkaz IFNULL

Zvláštním případem jednoduchého větvení budiž příkaz IFNULL. Ten má dva argumenty. Pokud ten první je rovnen hodnotě NULL, je vrácen druhý, jinak první. To se často hodí v případě, kdy sloupec smí obsahovat hodnoty NULL, ale my je v daném případě chceme nahradit nějakou jinou hodnotou. Kdyby směl sloupec plat obsahovat hodnoty NULL a my je chtěli nahradit nulou, šli bychom na to takto:

SELECT jmeno, IFNULL(plat,0) AS plat FROM pracovnik

Což je mimochodem rovnocenné zápisu

SELECT jmeno, IF(plat is null,0,plat) AS plat FROM pracovnik

Co k tomu dodat? Snad jen že existuje i funkce NULLIF, která funguje poněkud jinak a v praxi se téměř nepoužívá. Její popis byste našli v manuálu k MySQL. Ano, a ještě to, že některé DBMS mají funkci IFNULL nazvánu ISNULL, takže se mi to neustále plete.

Příkaz CASE

Case umí větvit kód na více částí a navíc má dvě syntaktické formy. Tudíž je nejsložitější z uvedených příkazů pro větvení. Zase toho ale nejvíc umí a poměrně často se používá. První forma je tzv. "rozhodovací" a vypadá takto:

SELECT JMENO,
CASE
  WHEN PLAT < 12000 THEN 'málo'
  WHEN PLAT > 12000 AND PLAT < 15000 THEN 'středně'
  WHEN PLAT > 15000 THEN 'ujde to'
END as PLAT
FROM pracovnik

Druhá, "prohledávací" pak vypadá nějak takhle

SELECT JMENO,
CASE STATUS
  WHEN 1 THEN 'zaměstnán'
  WHEN 2 THEN 'propuštěn'
  WHEN 3 THEN 've zkušební době'
  ELSE 'neznámý'
END
from pracovnik

Jak vidíte, její typické použití je právě ve spojení s "překladem" jednoduchých "stavových" informací na jejich srozumitelné protějšky.

Praxe

Pro použití těchto konstrukcí v praxi bych měl několik tipů, které se Vám možná budou hodit. Především - s nástroji pro větvení kódu je třeba zacházet opatrně. Mějte na paměti, že použití mnoha (zejména vnořených) konstrukcí CASE nebo IF může kód poněkud znepřehlednit. V příkladech "prohledávacího" typu CASE je navíc možné stavové informace uložit do samostatné tabulky a potom to spojit pomocí relace - odpadá tak nutnost úpravy v kódu v případě, kdy by se přidávala nová větev (nový stav).

Ještě jeden tip z praxe - kdekoli je to možné, hodí se zdrojové kódy SQL obsahující větvení formátovat, aby byly hezky čitelné. Sám jsem se o to v příkladech výše snažil.

Pivotní tabulky

Pomocí příkazu CASE se lehce dají vytvořit pivotní tabulky, tedy takové pohledy na data, které shrnují "řádkové" výsledky do sloupců. Než bych to dlouhosáhle vysvětloval, raději to předvedu na příkladu. Mějme tabulku vystavených faktur

create table faktury (datum date, castka decimal(10,2));
insert into faktury (datum, castka) values('20040101', 2200);
insert into faktury (datum, castka) values('20040201', 2600);
insert into faktury (datum, castka) values('20040301', 2500);
insert into faktury (datum, castka) values('20040501', 3800);
insert into faktury (datum, castka) values('20040701', 4200);
insert into faktury (datum, castka) values('20040806', 2100);
insert into faktury (datum, castka) values('20040901', 2300);
insert into faktury (datum, castka) values('20040906', 2800);
insert into faktury (datum, castka) values('20041001', 3100);
insert into faktury (datum, castka) values('20050101', 4000);
insert into faktury (datum, castka) values('20050201', 5000);
insert into faktury (datum, castka) values('20050301', 5500);
insert into faktury (datum, castka) values('20050501', 4200);
insert into faktury (datum, castka) values('20050701', 4800);
insert into faktury (datum, castka) values('20050806', 6100);
insert into faktury (datum, castka) values('20050901', 3200);
insert into faktury (datum, castka) values('20050906', 2200);
insert into faktury (datum, castka) values('20051001', 8500);

a šéfa. Ten jednoho krásného dne přijde s tím, že chce výsledky ve formě tabulky, kde každý řádek bude obsahovat data za jeden rok a sloupce budou obsahovat součty pohybů za jednotlivé měsíce. Tento požadavek se v mnoha obměnách opakuje v nejrůznějších aplikacích, takže následující postup se docela vylatí znát. Dotaz bude vypadat nějak takto:

select rok,
sum(case when mesic=1 then castka else 0 end) as 'leden',
sum(case when mesic=2 then castka else 0 end) as 'únor',
sum(case when mesic=3 then castka else 0 end) as 'březen',
sum(case when mesic=4 then castka else 0 end) as 'duben',
sum(case when mesic=5 then castka else 0 end) as 'květen',
sum(case when mesic=6 then castka else 0 end) as 'červen',
sum(case when mesic=7 then castka else 0 end) as 'červenec',
sum(case when mesic=8 then castka else 0 end) as 'srpen',
sum(case when mesic=9 then castka else 0 end) as 'září',
sum(case when mesic=10 then castka else 0 end) as 'říjen',
sum(case when mesic=11 then castka else 0 end) as 'listopad',
sum(case when mesic=12 then castka else 0 end) as 'prosinec'
from
(
select year(datum) as rok, month(datum) as mesic, sum(castka) as castka
from faktury group by year(datum), month(datum)
) as soucty
group by rok

Všimněte si, že data za jednotlivé měsíce jsou nejprve sloučena v poddotazu, a ten je ve "vnějším" dotazu použit jako zdroj. Výhoda tohoto přístupu je jasná - dotaz seskupí data pro libovolný počet let a navíc v jednom měsíci může být libovolný počet pohybů (včetně žádného). Jen je ten kód trochu nudný na psaní.

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.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ář

1.5.2017 20:31 /Pavel `Goldenfish' Kysilka
PR: 25.5.2017 proběhne v Praze konference na téma Firemní informační systémy. Hlavními tématy jsou: Informační systémy s vlastní inteligencí, efektivní práce s dokumenty, mobilní přístup k datům nebo využívání cloudu.
Přidat komentář

15.4.2017 15:20 /František Kučera
Máš rád svobodný software a hardware nebo se o nich chceš něco dozvědět? Zajímá tě IoT a radiokomunikace? Přijď na sraz spolku OpenAlt, který se bude konat ve středu 19. dubna od 18:30 v Šenkovně (Sokolská 60, Praha 2).
Přidat komentář

5.3.2017 19:12 /Redakce Linuxsoft.cz
PR: 23. března proběhne v Praze konferenci na téma Cloud computing v praxi. Hlavními tématy jsou: Nejžhavější trendy v oblasti cloudu a cloudových řešení, Moderní cloudové služby, Infrastruktura současných cloudů, Efektivní využití cloudu, Nástrahy cloudových řešení a jak se jim vyhnout.
Přidat komentář

27.2.2017 22:12 /František Kučera
Pozvánka na 137. sraz OpenAlt – Praha: Tentokrát jsme si pro vás připravili neobvyklou akci. Ve středu 1.3. v 17:30 nás přivítá sdružení CZ.NIC ve svých prostorách v Milešovské ulici číslo 5 na Praze 3, kde si pro nás připravili krátkou prezentaci jejich činnosti. Následně navštívíme jejich datacentrum pod Žižkovskou věží. Provedou nás prostory, které jsou běžnému smrtelníkovi nedostupné!
Po ukončení prohlídky se všchni odebereme do hostince U vodoucha, Jagelonská 21, Praha 3 pochutnat si na některém z vybraných piv či dát si něco na zub. Rezervaci máme od 19:30, heslo je OpenAlt.
Ale pozor! Do prostor datového centra máme omezený přístup, dostane se tam pouze 10 lidí! Takže kdo přijde dříve, ten má přednost, a občanky s sebou! Kdo nebude chtít na prohlídku datového centra, může se pomalu přesunout do hostince U vodoucha a u nepřeberné nabídky piv počkat na ostatní.
Přidat komentář

18.1.2017 0:49 /František Kučera
Členové a příznivci spolku OpenAlt se pravidelně schází v Praze a Brně. Fotky z pražských srazů za uplynulý rok si můžete prohlédnout na stránkách spolku. Příští sraz se koná už 19. ledna – tentokrát je tématem ergonomie ovládání počítače – tzn. klávesnice, myši a další zařízení. Také budete mít příležitost si prohlédnout pražský hackerspace Brmlab.
Přidat komentář

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

> Poslední diskuze

15.6.2017 9:34 / Ondřej Havlas
php,

10.6.2017 10:39 / Temple
sell home for cash

11.5.2017 23:32 / lelo
Re: Problém se správcem balíčků

11.5.2017 5:45 / davd mašek
Re: Problém se správcem balíčků

10.5.2017 22:54 / lelo
Re: Problém se správcem balíčků

Více ...

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