ARCHIV
PerlPHPGimpZačínáme s wi-fi64bit LinuxProgramování C/C++Linux v příkazechHTMLPostgreSQLOkenní managery
Software (10844)
Distribuce (131)
Skripty (697)
Menu
Diskuze
Informace
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 26798×
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í.
Předchozí Celou kategorii (seriál) Další
MySQL (2) - Instalujeme databázi MySQL
MYSQL (3) Instalujeme MySQL podruhé
MySQL (4) - něco terminologie
MySQL (5) - tajuplné SQL
MySQL (6) - Ukládáme řetězce
MySQL (7) - hrátky s čísly
MySQL (8) - Ukládání datumů
MySQL (9) - Další datové typy
MySQL (10) - tvorba databáze. Základy DDL
MySQL (11) - vytváříme tabulky
MySQL (12) - tipy k tvorbě tabulek
MySQL (13) - Vkládáme data
MySQL (14) - Upravujeme data
MySQL (15) - Odstraňujeme data
MySQL (16) - Tipy a triky k manipulaci s daty
MySQL (17) - vybíráme data
MySQL (18) - Filtrujeme data
MySQL (19) - Řadíme data
MySQL (20) - spojení více tabulek
MySQL (21) - klauzule JOIN
MySQL (22) - tipy a triky ke spojování tabulek
MySQL (23) - relace 1:N a N:N
MySQL (24) - Seskupujeme záznamy
MySQL (25) - hrátky se seskupenými záznamy
MySQL (26) - Poddotazy
MySQL (27) - Složitější dotazy
MySQL (28) - Dotazy pro pokročilé
MySQL (29) - Vracení nejvyšších záznamů
MySQL (30) - průběžné součty
MySQL (31) - Indexy
MySQL (32) - ještě k indexům
MySQL (33) - Příkaz UNION
MySQL (35) - vestavěné funkce
MySQL (36) - Regulární výrazy
MySQL (37) - použití fulltextového vyhledávání
MySQL (38) - Fulltext a praxe
MySQL (39) - typy tabulek v MySQL
MySQL (40) - další typy tabulek
MySQL (41) - Transakce
MySQL (42) - ještě k transakcím
MySQL (43) - Uložené procedury
MySQL (44) - parametry uložených procedur
MySQL (45) - větvení kódu uložených procedur
MySQL (46) - Triggery
MySQL (47) - Triggery a praxe
MySQL (48) - UDF
MySQL (49) - pohledy
MySQL (50) - Pohledy podruhé
MySQL (51) - Metadata
MySQL (52) - A co zálohování?
MySQL (53) - SELECT INTO OUTFILE
MySQL (54) - zálohování MySQL z webu
MySQL (55) - zálohování MySQL z pohledu správce
MySQL (56) - Obnova zálohovaných dat
MySQL (57) - Ach, ta čeština
MySQL (58) - čeština v praxi
MySQL (59) - české řazení
MySQL (60) - řádkový klient
MySQL (61) - Oprávnění
MySQL (62) - Oprávnění podruhé
MySQL (63) - jemné nastavení práv
MySQL (64) - nad dotazy čtenářů
MySQL (65) - Ladíme server
MySQL (66) - Ještě k ladění serveru
MySQL - (67)
MySQL (68) - Závěr
MySQL (69) - Prepared Statements
Předchozí Celou kategorii (seriál) Další
|
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ář
Poslední diskuze31.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?