Mít spoustu dat je hezká věc, ale občas bychom spíše potřebovali udělat z nich nějaký "výcuc". O tom je dnešní díl seriálu o MySQL.
10.6.2005 06:00 | Petr Zajíc | přečteno 51427×
Již víme, že tabulky se v dotazech dají spojovat. Dosud jsme ale
pracovali způsobem, při němž se vždy ve výsledku dotazu zobrazily
všechny (nebo vybrané) řádky z jedné či více tabulek. To může pro
základní práci s databází sice stačit, ale většinou je to málo. Pojďme
se dnes začít zabývat seskupováním záznamů.
Abychom byli od začátku přesní - seskupování záznamů je něco jiného
než spojování tabulek. Při spojování tabulek totiž pracujete se dvěma
nebo více tabulkami s cílem najít záznamy, které spolu vzájemně
souvisejí. (Exaktně řečeno lze spojit tabulku i samu se sebou, je to
však speciální případ a probereme to později samostatně). Seskupování
naproti tomu je proces, který probíhá s cílem zjistit něco o skupině
nějak souvisejících záznamů. "Skupina nějak souvisejících záznamů"
přitom může být docela dobře v jediné tabulce. V praxi bychom mohli
najít mnoho případů seskupování:
Představme si například následující tabulku, která by mohla
vzniknout jako záznam z nějakého toho logu návštěvnosti webu. Ukládá
datum návštěvy, operační systém uživatele, jeho prohlížeč, stránku,
kterou navštívil a čas, který tím strávil:
create table logtable
(datum datetime, system varchar(20),
prohlizec varchar(20), stranka varchar(20), doba_prohlizeni int);
insert into logtable (datum, system, prohlizec, stranka, doba_prohlizeni)
values ('20050609132500','windows','ie6','index.php',5);
insert into logtable (datum, system, prohlizec, stranka, doba_prohlizeni)
values ('20050609132510','windows','ie6','data.php',7);
insert into logtable (datum, system, prohlizec, stranka, doba_prohlizeni)
values ('20050609132740','linux','firefox','index.php',9);
insert into logtable (datum, system, prohlizec, stranka, doba_prohlizeni)
values ('20050609132810','linux','firefox','formular.php',35);
insert into logtable (datum, system, prohlizec, stranka, doba_prohlizeni)
values ('20050609132810','unix','opera','index.php',6);
insert into logtable (datum, system, prohlizec, stranka, doba_prohlizeni)
values ('20050609132850','linux','firefox','data.php',15);
insert into logtable (datum, system, prohlizec, stranka, doba_prohlizeni)
values ('20050609132930','unix','opera','data.php',18);
insert into logtable (datum, system, prohlizec, stranka, doba_prohlizeni)
values ('20050610082500','linux','opera','index.php',44);
insert into logtable (datum, system, prohlizec, stranka, doba_prohlizeni)
values ('20050610091106','unix','firefox','formular.php',20);
Pozn.: Omlouvám se za délku, ale
pokud si to budete chtít vyzkoušet, nějaká ta data je při testech
seskupování záznamů třeba mít po ruce.
Seskupování záznamů probíhá tak, že se za příkaz SELECT uvede klauzule GROUP BY [název pole,[název pole...]], která přikáže serveru data před jejich vrácením seskupit. V nejjednodušším případě to může vypadat nějak takto:
select * from logtable
group by system;
Pokud si to zkoušíte, asi nejste z výsledku příliš nadšeni. MySQL
totiž vybere z celé tabulky vždy první záznam o systému, který tu ještě
nebyl, a ten vrátí. Ve výsledku tedy bude jeden řádek s linuxem, jeden
s unixem a jeden s windows. Ze seskupování se ale dá vyzískat mnohem
víc, když se použijí
Agregační funkce umožňují "něco" vypočítat z řádků, které se právě
seskupují. Přestože těchto funkcí existuje celá řada, v praxi pro
většinu běžných úloh typicky stačí znát pouze dvě z nich:
Obě teď můžeme předvést na příkladu. Pomocí agregační funkce COUNT můžeme z protokolu například zjistit, kolik přístupů zaznamenala ta která stránka:
select stranka, count(*)
from logtable group by stranka;
A pomocí SUM můžeme třeba vypátrat, kolik času na jednotlivých stránkách naši návštěvníci tráví:
select stranka,
sum(doba_prohlizeni) from logtable group by stranka;
K agregačním funkcím se váže celá řada postřehů a zásad, které je dobře znát. Tak například za klauzulí GROUP BY nemusí být uveden jeden sloupec, ale může jich být hned několik. Není to takový nesmysl, jak by se mohlo na první pohled zdát. Můžeme třebas náš příkad s funkcí COUNT chtít rozšířit tak, aby vracel počet shlédnutí stránky podle stránky a prohlížeče, jímž se na tuto stránku přistupovalo. V tom případě bude vrácen počet přístupů pro každou kombinaci stránky a prohlížeče:
select stranka,
prohlizec, count(*) from logtable group by stranka, prohlizec;
Dále, nic nám nebrání sestavit dotaz, v němž bude jak klauzule WHERE, tak i GROUP BY (ve skutečnosti je to celkem častý případ). Jestliže nás bude zajímat jen chování uživatelů Firexofu, můžeme předchozí příklad přepsat použitím:
select stranka,
prohlizec, count(*) from logtable where prohlizec = 'firefox' group by
stranka, prohlizec;
Konečně, jednou seskupené řádky lze řadit. Uvědomme si, že řazení
probíhá až PO seskupení záznamů. To má dva praktické důsledky:
select stranka,
sum(doba_prohlizeni) from logtable group by stranka order by
sum(doba_prohlizeni) desc;
Důvod je prostý - MySQL nesmí mít v klauzuli ORDER BY výraz. Řešení jsou dvě, buď se dá použít číslo řazeného sloupce, nebo si výraz SUM (doba_prohlizeni) nazvat aliasem. Oba dotazy níže již projdou a dělají totéž:
select stranka,
sum(doba_prohlizeni) as doba from logtable group by stranka order by
doba desc;
select stranka, sum(doba_prohlizeni) from logtable group by stranka
order by 2 desc;
V příštím díle se můžete těšit na další informace ohledně seskupování záznamů.