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

> MySQL (24) - Seskupujeme záznamy

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 | Články autora | přečteno 48330×

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ů.

Seskupování 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í:

  • Aplikace vyhodnocující přístup k internetovým stránkám bude chtít vědět, kolik přístupů byl zaznamenáno v jednotlivých dnes provozu webu
  • Aplikace pro fakturaci bude chtít zjistit, jaká je celková cena bez DPH, DPH a cena s daní po sečtení všech položek faktury
  • Aplikace pro statistiku v meteorologii bude chtít vědět, jaká byla průměrná teplota pro dnešní den v několika předcházejících desetiletích
  • Krásný příklad - budete chtít zjistit z databáze zaměstnanců, kdo má vyšší plat, než je průměr ve vaší firmě.

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

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:

  • Agregační funkce COUNT, která zjistí počet právě seskupovaných řádků
  • Agregační funkce SUM, která zjistí součet položek v právě seskupovaných řádcích

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:

  1. Klauzule ORDER BY je v příkazu SELECT vždy až na posledním místě. Logicky to odpovídá pořadí, v němž dotaz bude zpracováván. Takže, z nám známých rozšíření příkazu SQL je zatím pořadí toto: SELECT ... FROM ... JOIN ... WHERE ... GROUP BY ... ORDER BY. Začátečníkovi to sice může připadat poněkud náročné na zapamatování, ale je to intuitivní.
  2. Jelikož probíhá řazení až nakonec, můžeme řadit podle výsledku agregační funkce! Nebyl by tedy problém seřadit například stránky sestupně podle doby, kterou na nich uživatelé tráví. Ovšem pozor, sytaxe, která vás v této souvislosti asi napadne nejdřív, nefunguje:
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ů.

Verze pro tisk

pridej.cz

 

DISKUZE

MySQL 5.O 27.4.2008 23:02 David Dejvidof Jíra




Příspívat do diskuze mohou pouze registrovaní uživatelé.
> Vyhledávání software
> Vyhledávání článků

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

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

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

> Poslední diskuze

15.12.2017 15:11 / Petit
freehold nj

15.12.2017 15:06 / Petit
nj freehold

5.12.2017 11:50 / Thomas
kitchen renovations

18.9.2017 14:37 / Rojas
high security vault

15.9.2017 7:33 / Wilson
new zealand childcare jobs

Více ...

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