GeoGet

Complete geocaching solutions

User Tools

Site Tools


user:skript:stator:modules:stator-sqlexample

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.

Délka SQL dotazu jedo verze 1.3.2.11 včetně omezena na cca 4000 znaků. Ve formuláři je její překročení signalizováno podbarvením textu se SQL dotazem.

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 počet 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 záhlaví sloupce text %. Jen poznamenám, že stejného jména by se dosáhlo pojmenováním sloupce Percent
  • text za znakem = pak udává formátovací parametr Right, 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 tabulky geolog nebo
  • použit tabulku temp.Stator, která již má keše seřazené podle toho, jak má uživatel nastaveno v konfiguraci

Keše nad 1000 m.n.m souhrnná tabulka

Příklad pro ty kteří rádi lezou po horách - souhrnná tabulka která zobrazí kolik keší nad 1000 m.n.m jste v kterém státě ulovili, procento na stát, nejvyšší odlov a průměr dosažených výšek

SELECT 
  gc.[country] AS 'Country flag', 
  gc.[country] AS 'Country', 
  COUNT(gc.[id]) AS 'hi_count=Center',
  (COUNT(gc.[id])*100.00)/(SELECT COUNT(*)
    FROM geocache igc
    INNER JOIN geotag igt ON igt.[id] = igc.[id]
    INNER JOIN geotagcategory igtc ON igtc.[VALUE] IN ("Elevation") AND igtc.[KEY] = igt.[ptrkat]
    INNER JOIN geotagvalue igtv ON igt.[ptrvalue] = igtv.[KEY]
    WHERE igc.[dtfound] > 0 AND CAST(igtv.[VALUE] AS INT) > 999
   ) AS 'hi_proc=Center',
  MAX(CAST(gtv.[VALUE] AS INT)) AS 'hi_max=Center',
  SUM(CAST(gtv.[VALUE] AS INT))/COUNT(gc.[id]) AS 'hi_prum=Center'
FROM geocache gc
INNER JOIN geotag gt ON gt.[id] = gc.[id]
INNER JOIN geotagcategory gtc ON gtc.[VALUE] IN ("Elevation") AND gtc.[KEY] = gt.[ptrkat]
INNER JOIN geotagvalue gtv ON gt.[ptrvalue] = gtv.[KEY]
WHERE gc.[dtfound] > 0 AND CAST(gtv.[VALUE] AS INT) > 999
GROUP BY gc.[country]
ORDER BY COUNT(gc.[id]) DESC

Pojmenování sloupců je potřeba doplnit do ColumnNames.ini ve vašem profilu
hi_count=Počet keší
hi_proc=%
hi_max=Nejvýše m.n.m.
hi_prum=Průměr m.n.m.

Řazení je podle počtu keší ve státech
Ukázka na mém profilu na záložce TOP tabulky

Dokumentace modulů Statoru

PageDateDescriptionTags
Modul Badges 2016/01/06 19:01 stator Modul Badges FIXME doplnit popis samotného modulu Dokumentace modulů Statoru stator stator_modul ,
Modul BookmarkComposer 2020/10/28 12:00 stator Modul BookmarkComposer FIXME doplnit popis modulu Použití tohoto modulu závisí na správném otagování keší. Je možné postupovat ručně nebo si pro budou… ,
Modul CalcBG 2021/04/19 11:30 stator Modul CalcBG Popis modulu Výpočetní modul, který vyhodnocuje informace o získaných odznacích pro modul Badges. Je použit automaticky, ale obsahuje i… ,
Modul CalcGSAPI 2019/06/05 14:32 stator Modul CalcGSAPI Popis modulu Výpočetní modul, který vyhodnocuje informace z GSAPI. Je použit automaticky. Jeden suvenýr do textu Modul kromě vytvo… ,
Modul CalcMain 2019/06/05 14:33 stator Modul CalcMain Popis modulu Hlavní výpočetní modul Statoru. Používají ho další moduly, jako Badges, Numbers a FTFPodium a mnoho dalších. Pokud je jaký… ,
Modul CalcTrackable 2015/12/29 22:07 stator Modul CalcTrackable Popis modulu FIXME Modul využívá dat stažených pomocí GeoJarry, která “přechroustá” a předá k dalšímu použití. Seznam globálníc… ,
Modul CwgList 2015/12/30 08:18 stator Modul CwgList Modul je určen pro zobrazování CWG (ale i SQ, vizitky a další typy) a data načítá z pluginu CwgList. Parametry modulu Základní funkce a… ,
Modul MapPinboard 2016/03/01 00:07 stator Modul MapPinboard Modul pro puntíkování nálezů do mapy. Nahrazuje původní modul MapCzDot a má mnohem širší možnosti nastavení. Především výběr území, k… ,
Modul MyOwnLife 2016/02/18 00:00 stator Modul MyOwnLife FIXME Parametry modulu * TagXTF obsahuje jméno kategorie tagu (implicitně OnMyCache???), do které uživatel zadává nicky FTFkařů na … ,
Modul Numbers 2020/10/28 11:59 stator Modul Numbers Popis modulu FIXME Modul vytváří tabulku se zajímavými čísly rozdělenou do několika tématických částí. Pokud nemáte v částil s Logy uv… ,
Modul QTHMap 2020/10/28 11:58 Modul QTHMap Modul je určený pro zobrazení pokrytí dané země QTH lokátory a jejich vyplnění kešemi. Defakto lovením keší v dané zemi se jednotlivé obdélníky ve… ,
Modul SetFilter 2020/10/28 12:05 stator Modul SetFilter Popis modulu Běžně moduly do svého výstupu používají všechny nalezené keše. Pomocí modulu SetFilter1.4.1.12, je možné zadat podmínku a… ,
Modul SQLTable - příklady SQL dotazů 2020/10/28 12:04 stator 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.… ,
Moduly CustomizedTable, SQLTable 2020/10/28 12:03 stator Moduly CustomizedTable, SQLTable Moduly umožňují vytvářet uživatelsky široce konfigurovatelné seznamy nálezů, případně i jiných dat z databáze GeoGetu.… ,
Stator - moduly pro generování statistik 2019/06/13 20:32 stator Stator - moduly pro generování statistik Na této stránce je základní seznam všech modulů pro plugin Stator a jejich stručný popis. Naprostá většina mod… , , , , ,
user/skript/stator/modules/stator-sqlexample.txt · Last modified: 2020/10/28 12:04 by mikrom