SQL pro pokročilé - 1.díl

Při uvedení článku o SQL Injection jsem zjistil, že velmi mnoho čtenářů by se o SQL rádo dozvědelo více. Proto jsem se rozhodl napsat pár článků o pokročilých technikách SQL, které se v učebnicích příliš nepublikují, a ukázat pár vzorových příkladů.

Dnešním tématem budiž dočasné tabulky a triky s nimi.

Dočasná tabulka, neboli TEMPORARY TABLE, se až na jednu podstatnou vlastnost neliší od normální tabulky. Tou vlastností je, jak už název napovídá, dočasnost. Tabulka je po ukončení spojení mezi PHP a MySQL databází nenávratně smazána. V praxi to znamená, že tabulka existuje po dobu, kdy máme platný identifikátor na MySQL spojení. Tento identifikátor vrací funkce Mysql_connect.

Uvedu příklad z praxe:

Mám dvě tabulky rozdílné struktury obsahující data o předpovědi počasí pro lyžařská střediska. Potřebuji vytvořit seznam TOP 10 středisek s nejvyšší sněhovou pokrývkou.

Vypadalo by to asi takto:


( SELECT  p.`vyska_snehu`,s.`stredisko`
          FROM `predpoved1` AS p
          LEFT JOIN `stredisko` AS s ON s.`id_stredisko` = p.`id_stredisko`  
          WHERE (TO_DAYS(NOW())=TO_DAYS(`datum`))
          GROUP BY p.`id_stredisko` 
)
 UNION
( SELECT p.`vyska_snehu`,s.`stredisko`
          FROM `predpoved2` AS p
          LEFT JOIN `stredisko` AS s ON s.`id_stredisko` = p.`id_stredisko`  
          WHERE (TO_DAYS(NOW())=TO_DAYS(`datum`))
          GROUP BY p.`id_stredisko` 
)
ORDER BY `vyska_snehu` DESC LIMIT 10

Jak vidíte, načteme data z tabulky předpovědi a seskupíme řádky pomocí id_střediska. Tím získáme jeden záznam pro každé středisko - vždy ten nejnovější. Tento select složíme s druhým selectem, který musí mít stejnou strukturu výstupu.

Tento kód je vcelku dobrý, bohužel má ale jednu chybu: Přestane správně fungovat ve chvíli, kdy se v obou tabulkách předpovědí objeví totéž středisko. A pokud možno ještě budeme dostávat trochu odlišná data do každé předpovědi. Pak se nám může stát, že v seznamu TOP 10 středisek bude některé středisko dvakrát...

Jak to řešit? Pomocí dočasných tabulek - TEMPORARY TABLE.

Začneme vytvořením:

CREATE TEMPORARY TABLE `top` (vyska_snehu INT, stredisko VARCHAR(100))

Jak vidíte, je to téměř totožné jako vytváření běžných tabulek.

Následuje mírné přepsání selectů:

INSERT INTO `top` ( SELECT  p.`vyska_snehu`,s.`stredisko`
          FROM `predpoved1` AS p
          LEFT JOIN `stredisko` AS s ON s.`id_stredisko` = p.`id_stredisko`  
          WHERE (TO_DAYS(NOW())=TO_DAYS(`datum`))
          GROUP BY p.`id_stredisko` 
)
 UNION
( SELECT p.`vyska_snehu`,s.`stredisko`
          FROM `predpoved2` AS p
          LEFT JOIN `stredisko` AS s ON s.`id_stredisko` = p.`id_stredisko`  
          WHERE (TO_DAYS(NOW())=TO_DAYS(`datum`))
          GROUP BY p.`id_stredisko` 
)
ORDER BY `vyska_snehu` DESC

Opět žádná novinka, pouze vkládáme výsledek selectů do naší dočasné tabulky. Jediný rozdíl je odstranění LIMIT na konci dotazu.

A nakonec přijde select z dočasné tabulky:

SELECT * FROM `top` GROUP BY stredisko ORDER BY 1 DESC LIMIT 10

Tím je problém vyřešen. Data vložíme do dočasné tabulky a tam je poté opět seskupíme podle středisek. Žádné duplicity už tedy nevzniknou...

Hodnocení

Komentáře

2007-01-21 18:13:17

[imp]I když jsi se evidentně snažil, tak se ti to nepovedlo. Nerad to říkám, ale je to ten nejhorší článek o SQL co jsem kdy četl. Všechny funkce a výrazy nevysvětluješ, rozborem se dál nezabýváš atd. Já osobně jsem toho z článku moc nepochopil. Do příště bych ti doporučil více preciznosti a trpělivosti, snaž se ten článek napsat tak, aby jsi vše vysvětlil, tak jak by vysvětleno mělo být. [/imp]

[2] Dundee
2007-01-21 19:22:21

Problém bude možná v tom, že jsem to bral jako článek pro pokročilé. Proto vysvětluji jen věci, které mi nepřijdou pro pokročilého programátora samozřejmé... Pokusím se to příště vysvětlit více dopodrobna...

[3] trta
2007-01-29 19:59:29

No, nejhorsi clanek to jiste neni, spis je to prilis konkretni a obavam se, ze pokud nekdo ze ctenaru nesestavoval dotaz pro slouceni predpovedi dvou stredisek :) nijak zvlast chytrej z toho nebude...
Spis bych se zminil o tom, kdy je obecne vhodny pouzit docasny tabulky (zvyseni efektivnosti/prehlednosti) atp... Taky by bylo dobry upozornit na to, jak se docasny tabulky chovaj pri pouziti perzistentniho pripojeni a dat si na to bacha....

[4] Dundee
2007-01-30 11:49:51

Přiznávám, není to moc dobrej článek. Psal jsem to asi příliš narychlo a moc pozdě v noci :) Příště se pokusím to víc promyslet a zobecnit...

2008-02-24 20:13:55

Nevím, ale myslím, že zas tak strašnej ten článek nebyl. V podstatě nejdůležitější jeho sdělení je, že existuje TEMPORARY TABLE a že se s tím dá velmi efektivně pracovat. Pro mě užitečné, díky ;)

Komentáře již nelze přidávat