6.9. Funkce SQL

6.9.1. Příprava / opakování

1	Stáhněte si přes FTP z fileserveru pomocí účtu anonym soubor db07.zip a extrahujte jej tak, aby rozbalené soubory byly ve složce c:\temp\db07	FileZilla
2	Otevřte si v Excelu sešit odpovědi.xls, do kterého budete zapisovat výsledky hledání žlutých řádků podle zadání níže	Excel
3	Otevřte v textovém editoru soubor citaty.sql a doplňte příkazy pro vytvoření tabulky citaty a indexu index_autor. Všechny příkazy navíc obalte do transakce (viz vzor.sql).	PSPad
4	Spusťte příkazový řádek a přejděte v něm do složky db07	cmd, cd
5	Vytvořte ve složce db07 novou databázi db07.sqlite spuštěním: c:\programy\sqlite\sqlite3 db07.sqlite	cmd, sqlite
6	Proveďte připravené SQL příkazy načtením souboru citaty.sql	.read
7	Ověřte funkčnost databáze - najděte citáty od Rooseveltů (použijte operátor like)	select ...
8	Omezte výsledek na prvních 10 citátů	... limit ...
9	Uspořádajte výsledky podle času (sloupce datum)	... order by ...

6.9.2. Matematické funkce

• funkce vrací hodnotu vypočítanou z dat předaných v závorce -  http://www.sqlite.org/lang_aggfunc.html, http://www.sqlite.org/lang_corefunc.html 

count() - vrací počet hodnot

• select count(*) from tbl; - celkový počet řádků tabulky
• select count(sloupec) from tbl; - počet řádků s nenulovou hodnotu v uvedeném sloupci
• pokud je třeba duplicitní hodnoty počítat jen jednou, před názvem sloupce se použije distinct:
  • select count(distinct sloupec) from tbl;

10	Vypište celkový počet řádků tabulky citaty příkazem select count(*) from citaty;
11	Vypište počet citátů s uvedeným rokem 1995 (... datum like ...)	xlsx
12	Vypište počet autorů citátů (... count(distinct autor) ...)	xlsx

random() - vrací náhodné celé číslo

• funkci lze použít např. pro výběr náhodného řádku

13

Vypište náhodný citát příkazem select * from citaty order by random() limit 1;

sum() - součet

• při použití matematických funkcí lze používat pro výpočty i operátory - např. pro aritmetický průměr sum()/count()

14	Vypočítejte celkový součet hodnocení citátů u autora Goethe: select sum(hodnoceni) from citaty where ...;	xlsx
15	Vypočítejte průměrné hodnocení citátů u stejného autora (sum/count) - všimněte si zaokrouhlení (výsledek je celé číslo)

avg() - aritmetický průměr

round(x,y) - zaokrouhlení čísla x na y desetinných míst

16

Vypočítejte průměrné hodnocení citátů u stejného autora pomocí funkce avg a výsledek zaokrouhlete na dvě desetinná místa

xlsx

min(), max() - minimum, maximum

6.9.3. Alias - AS

• slouží pro nové pojmenování sloupce - např. právě vytvořeného
• SELECT a,b*b AS bsq FROM table ORDER BY b;  - obr.

Cvičení

17	Nastavte si výpis ve sloupcích včetně záhlaví (.headers on, .mode column) a zopakujte poslední prováděný příkaz - v názvu sloupce je použitá funkce
18	Vypisovaný sloupec pomocí aliasu nazvěte "prumer": select round(avg(hodnoceni),2) as prumer from citaty ...;

6.9.4. Seskupování řádků - GROUP BY

• diagram1 - GROUP BY sloupec - seskupí řádky se stejnou hodnotou
• výraz bývá nejčastěji název sloupce - např. SELECT * FROM tbl GROUP BY a;
• pak řádky, které mají stejnou hodnotu v uvedeném sloupci jsou na výstupu seskupeny do 1 řádku - obr., obr.2
• pro skupiny lze počítat pomocí funkcí součty (SUM), průměry (AVG), MIN, MAX, počty řádků (COUNT) apod.
• např. SELECT a, AVG(b) FROM tbl GROUP BY a;
• pozn. pokud je ve výstupu sloupec, u kterého není jasná sloučená hodnota, vybere se hodnota z náhodného řádku skupiny - obr. - vypisovat takové sloupce nemá smysl
• pozn. pokud se při použití funkcí nepoužije GROUP BY, pak se za skupinu považuje celá tabulka (např. SELECT SUM(b) FROM tbl; vrátí jen součet)

19	Vypište témata citátů (bez duplicit - použijte distinct)
20	Vypište počet témat (pomocí count)	xlsx
21	Vypište témata včetně počtu citátů u každého tématu: select tema, count(tema) ... group by tema;
22	Ověřte nastavení parametrů sqlite příkazem .show (musí být zapnuto zobrazování ve sloupcích - columns - a zobrazování záhlaví - headers)
23	Zjistěte pomocí příkazu .help jak se nastavuje šířka zobrazených sloupců a nastavte šířku prvního sloupce na 20 znaků. Znovu proveďte poslední select a ověřte, že témata jsou vypsána bez zkrácení
24	K výpisu přidejte další sloupec s průměrným hodnocením každého tématu zaokrouhleným na 2 desetinná místa ... round(avg(...),2) ...
25	Pro poslední sloupec si nadefinujte alias prumer ... as prumer ...
26	Stejný výpis uspořádejte od nejlepšího hodnocení k nejhoršímu (sestupně) ... order by prumer desc;

6.9.5. Filtrování po seskupení - HAVING

• používá se pro filtrování výpisu vzniklého seskupením po GROUP BY (narozdíl od WHERE, který filtruje řádky před seskupením) - obr.
• v podmínce za HAVING lze využívat výsledky funkcí

27	Naposled použitý výpis omezte (filtrujte) jen na průměry větší než 5: ... group by tema having prumer > 5 ...
28	Zapněte si výpis příkazů (.echo on) a výstup do souboru temata.txt (.output temata.txt) a proveďte znovu předchozí příkaz pro výpis do souboru. Poté si přesměrujte výstup zpátky na obrazovku (.output stdout)

6.9.6. Funkce pro datum a čas

• http://www.sqlite.org/lang_datefunc.html
• formát ukládání data a času - typ TEXT: YYYY-MM-DD HH:MM:SS
• funkce date(time, modifier) vrací datum - time=vstupní datum a čas, modifier - posun času (např. '+1 month') - viz Modifiers na odkazu výše
• podobně funkce time() vrací čas , datetime() vrací datum i čas

29	Vypište aktuální čas: select time('now');
30	Výpis je třeba opravit podle časového pásma: select time('now', 'localtime');
31	Vypište datum za 1 měsíc: select date('now','+1 month');
32	Podobně vypište datum za 250 dnů	xlsx

• funkce julianday() - juliánské datum - počet dnů od 1.ledna roku 4713 př. n. l. - wiki

33	Počet dnů mezi dvěma daty (zde od vypuknutí 2. sv. války): select julianday('now')-julianday('1939-09-01');
34	Vypište zaokrouhlený počet dnů do prvního máje	xlsx

• univerzální formátování data a času: strftime( format, time, modifier, modifier... )
• format - řetězec se šablonou (viz http://www.sqlite.org/lang_datefunc.html), time - vstupní čas

35

Vypište dnešní datum v běžně používané podobě: select strftime('%d.%m.%Y','now','localtime');

• aliasy:
  • CURRENT_TIME - akt. čas HH:MM:SS = time('now')
  • CURRENT_DATE - YYYY-MM-DD = date('now')
  • CURRENT_TIMESTAMP - YYYY-MM-DD HH:MM:SS = datetime('now')
• aliasy lze použít při definici struktury tabulky jako DEFAULT hodnoty - automaticky přiřazované hodnoty (např. DEFAULT CURRENT_TIMESTAMP)

6.9.7. Textové funkce

upper() - vrací velká písmena (jen ASCII), lower() - malá písmena

substr(x,y,z) - podřetězec vstupního x od písmena y (první písmeno=1) v délce z

length() - délka řetězce

36	Zapněte si režim výpisu seznam (.mode list)
37	Vypište z tabulky citáty nejkratší citát: select citat from citaty order by length(citat) limit 1;
38	Vypište z tabulky citáty nejdelší citát a do Excelu zapište délku tohoto citátu	xlsx
39	Vypište autora s nejkratším jménem
40	Zapište do Excelu největší délku jména autora	xlsx
41	Vypište z tabulky citáty rok nejstaršího citátu (použijte funkci substr) a zapište jej do Excelu	xlsx
42	Uložte a zavřete otevřené dokumenty a celou složku db07 (se soubory db07.sqlite, citaty.sql, odpovědi.xls, temata.txt) uložte na fileserver	Filezilla