This is an old revision of the document!
Table of Contents
Modul SQLTable - příklady SQL dotazů
Protože modul je docela komplikovaný a jeho možnosti opravdu široké, ukážeme jeho možnosti na několika příkladech.
Vylovenost pražských městských částí
Na příkladu ukážeme:
- relativně přehledný zápis složitějšího SQL dotazu, včetně možného komentáře
- volba sloupce pro řazení řádků tabulky, zde navíc bude sloupec obsahovat číselnou hodnotu (procenta), takže musíme řádky řadit číselně a ne abecedně
- definici zarovnání vypisované hodnoty do sloupce - podle typografických pravidel mají být v tabulkách číselné hodnoty řazeny se stejnými řády pod sebou. To zde vyřešíme výpisem na jedno desetinné místo a zarovnáním vpravo
- zobrazení procent a histogramu - protože jednotlivé hodnoty procent nejsou počítány ze stejného základu, není možné použít metodu z modulu CustomizedTable, kdy výsledek SQL dotazu obsahuje počet a nasčítáním této hodnoty ze všech řádků se získá základ. Zde každá městská část má jiné množství keší, proto SQL dotaz musí rovnou vracet procentuální hodnotu a procentuální šířku sloupce v histogramu
V tabulce chceme výpis následujících hodnot:
- počet nalezených keší
- počet procent keší, které jsme už našli
- histogram, který tuto procentuální hodnotu zobrazí graficky
- jméno městské části
- řádky seřadíme podle procent
Postup a vysvětlivky
Napřed mezi použité moduly zařadíme modul SQLTable
a spustíme úpravu parametrů modulu. Pro nás je nejdůležitější parametr SQL
. Ten totiž bude obsahovat vlastně všechny informace potřebné pro výběr dat (což je pochopitelné a očekávané), ale také informace pro vytvoření tabulky.
Modul tabulku vytváří ze sloupců SQL dotazu. Řádky obsahující datové hodnoty jsou plněny daty, ale formátování sloupců, jejich pojmenování a význam obsahu je definován ve jméně sloupce v SQL. SQL bude následující:
SELECT B.pocet "Count", (0.+substr(""||(100.*B.pocet/A.pocet),1,4)) "%=Right", (100.*B.pocet/A.pocet) "Histogram explicit", A.Jmeno Name FROM -- pocet nalezitelnych kesi (SELECT COUNT(gtv.value) AS pocet,gtv.value AS Jmeno FROM geocache gc, geotag gt, geotagcategory gtc, geotagvalue gtv WHERE gt.id=gc.id AND (gc.cachestatus<2 OR gc.dtfound>0) AND gtc.key=gt.ptrkat AND gtc.value='CZ Mestska cast' AND gtv.key=gt.ptrvalue AND gtv.value LIKE 'Praha%' GROUP BY gtv.value ORDER BY gtv.value ) A , -- pocet nalezenych kesi (SELECT COUNT(gtv.value) AS pocet,gtv.value AS Jmeno FROM geocache gc, geotag gt, geotagcategory gtc, geotagvalue gtv WHERE gc.dtfound>0 AND gt.id=gc.id AND gtc.key=gt.ptrkat AND gtc.value='CZ Mestska cast' AND gtv.key=gt.ptrvalue AND gtv.value LIKE 'Praha%' GROUP BY gtv.value ORDER BY gtv.value ) B WHERE A.Jmeno=B.Jmeno ORDER BY "%=Right" DESC
a přesně takto může být zapsán jako parametr SQL.
Hlavni SELECT používá 2 tabulky A a B, které vzniknou jako vnořený SELECT. Tabulka A obsahuje pro každou městskou část počet všech keší, které může uživatel najít. Jde o keše, které nejsou archivované nebo sice archivované jsou, ale už je našel. Tabulka B pak obsahuje jen pocet nalezených keší.
Nás ale zajímá hlavně 1. řádek.
- první sloupec je pojmenován
Count
. Při tvorbě tabulky bude toto jméno sloupce nahrazeno podle souboru Common\ColumnNames_CS.ini na správné české pojmenování - druhý sloupec je pojmenován
%=Right
. To už je zajímavější. To, co je před znakem=
je skutečné jméno sloupce a bude na něj aplikován postup z prvního sloupce. Protože takto pojmenovaný sloupec v souboru neexistuje, ponese tabulka v zahlaví sloupce text%
. Jen poznamenám, že stejného jména by se dosáhlo pojmenovanám sloupcePercent
- text za znakem
=
pak udává formátovací parametrRight
, hodnoty budou tedy zarovnány vpravo - třetí sloupec se jmenuje
Histogram explicit
a to znamená, že bude zobrazen jako histogram. Hodnoty uvedené v tomto sloupci jsou procentuální velikostí sloupce histogramu - čtvrtý sloupec nese jméno
Name
a bude s ním zacházeno stejně jako se sloupcem prvním
První keše nalezené v každém státu
Další příklad vezmeme již rychleji. Chceme tabulku seřazenou podle data (a času) nálezu, vždy jen jednu kešku z každého státu.
SELECT id, cachetype "Type icon", Name, country "Country flag", dtfound -- ||dttime "Found datetime" --- tento radek je pro datum a cas "Found date" --- tento radek je pro detum bez casu FROM ( SELECT gc.id, gc.name, gc.cachetype, gc.country, gc.dtfound, substr("0000"||gc.dtfoundtime,-4,4) dttime FROM geocache gc WHERE gc.dtfound>0 ORDER BY gc.dtfound || dttime DESC ) GROUP BY country ORDER BY dtfound ASC, dttime ASC
V SQL jsou 2 řádky s komentářem. Jde o alternativní možnost výpisu: buďto jen datum nálezu nebo datum a čas nálezu.
Pojmenování sloupců vyjadřuje jak budeme zobrazovat získané hodnoty, přičemž zarovnání hodnot nebudeme měnit a necháme je implicitní.
Seskupení podle státu (sloupec country) zajistí použití jen jedné keše z každého státu.
Důležité je řazení. Protože v databázi jsou datum a čas nálezu uloženy odděleně, musíme je pro řazení spojit. Navíc je čas uložen jako číslo HHMM bez levostranných nul, proto musíme levostranné nuly doplnit, aby by čas do řazení uvažován správně. O to se postará funkce substr() aplikovaná na textový řetěz času doplněný zleva o dostatečný počet nul.
Pokud nemáte v databázi u nálezů uváděn čas, bylo by vhodné
- pro řazení použít položku
LogID
z tabulkygeolog
nebo - použit tabulku
temp.Stator
, která již má keše seřazené podle toho, jak má uživatel nastaveno v konfiguraci