Agregace

• Přehled
• Počet teplotních údajů
• Řešení
• Průměr s NULL
• Řešení
• Co dělá tento dotaz?
• Řešení
• Pořadí při spojování
• Klíčové body

Přehled

Výuka: 10 min
Cvičení: 10 min

Otázky

• Jak vypočítat součty, průměry a další souhrnné hodnoty?

Cíle

• Definice agregace a příklady jejího použití.

• Napsat dotazy, které počítají agregované hodnoty.

• Trasovat provádění dotazu, který provádí agregaci.

• Vysvětlit, jak se při agregaci pracuje s chybějícími daty.

Nyní chceme pro naše data vypočítat rozsahy a průměry.Víme, jak vybrat všechna data z tabulky Visited:

SELECT dated FROM Visited;

data

-null-

ale abychom je spojili,musíme použít agregační funkcijako je min nebo max.Každá z těchto funkcí přijímá jako vstup sadu záznamů a jako výstup vytváří jeden záznam:

SELECT min(dated) FROM Visited;

min(datum)

SELECT max(dated) FROM Visited;

max(datum)

min a max jsou jen dvě z agregačních funkcí zabudovaných v SQL.Další tři jsou avg,count a sum:

SELECT avg(reading) FROM Survey WHERE quant = 'sal';

avg(čtení)

SELECT count(reading) FROM Survey WHERE quant = 'sal';

count(čtení)

.

SELECT sum(reading) FROM Survey WHERE quant = 'sal';

sum(čtení)

Zde jsme použili count(reading),ale stejně dobře jsme mohli spočítat quantnebo jakékoli jiné pole v tabulce, nebo dokonce použít count(*), protože funkci nezajímají samotné hodnoty, jen kolik jich je.

SQL nám umožňuje provést několik agregací najednou.Můžeme například zjistit rozsah rozumných měření salinity:

SELECT min(reading), max(reading) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

min(údaj)	max(údaj)
0.05	0.21

Můžeme také kombinovat agregované výsledky s nezpracovanými výsledky,i když výstup vás možná překvapí:

SELECT person, count(*) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;

.

osoba	počet(*)
lake	7

Proč se objevuje Lakeovo jméno a ne Roerichovo nebo Dyerovo?Odpověď je, že když má agregovat pole,ale není mu řečeno jak,správce databáze vybere aktuální hodnotu ze vstupní množiny.Může použít první zpracovanou, poslední nebo něco úplně jiného.

Dalším důležitým faktem je, že pokud neexistují žádné hodnoty k agregaci – například pokud neexistují žádné řádky splňující klauzuli WHERE – výsledkem agregace je „nevím“, nikoli nula nebo jiná libovolná hodnota:

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant = 'missing';

osoba	max(čtení)	sum(čtení)
-null-	-null-	-null-

Jednou z posledních důležitých vlastností agregačních funkcí je, že jsou velmi užitečným způsobem nekonzistentní se zbytkem jazyka SQL.Pokud sečteme dvě hodnoty a jedna z nich je null, výsledek je null.V důsledku toho, pokud použijeme sum pro sečtení všech hodnot v množině a některá z těchto hodnot je null, výsledek by měl být také null.Je však mnohem užitečnější, aby agregační funkce ignorovaly null hodnoty a kombinovaly pouze ty, které nejsou null.Toto chování nám umožňuje psát dotazy jako:

SELECT min(dated) FROM Visited;

min(datum)

namísto toho, abychom museli vždy explicitně filtrovat:

SELECT min(dated) FROM Visited WHERE dated IS NOT NULL;

min(dated)

Agregace všech záznamů najednou nemá vždy smysl.Předpokládejme například, že máme podezření, že v našich datech existuje systematické zkreslení a že údaje některých vědců o radiaci jsou vyšší než u jiných.Víme, že to nefunguje:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad';

osoba	count(čtení)	round(avg(čtení), 2)
roe	8	6.56

protože správce databáze vybírá jedno libovolné jméno vědce, místo aby agregoval pro každého vědce zvlášť.Protože vědců je pouze pět,mohli bychom napsat pět dotazů ve tvaru:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'AND person = 'dyer';

person	count(reading)	round(avg(reading), 2)
dyer	2	8.81

ale to by bylo zdlouhavé,a kdybychom někdy měli soubor dat s padesáti nebo pěti sty vědci,šance, že všechny tyto dotazy položíme správně, je malá.

To, co musíme udělat, je říct správci databáze, aby agregoval hodiny pro každého vědce zvlášť pomocí klauzule GROUP BY:

SELECT person, count(reading), round(avg(reading), 2)FROM SurveyWHERE quant = 'rad'GROUP BY person;

person	count(reading)	round(avg(reading), 2)
dyer	2	8.81
jezero	2	1.82
pb	3	6.66
roe	1	11.25

GROUP BY dělá přesně to, co naznačuje jeho název:seskupuje všechny záznamy se stejnou hodnotou zadaného pole dohromady, aby agregace mohla zpracovat každou dávku zvlášť.Protože všechny záznamy v každé dávce mají stejnou hodnotu pro person,nezáleží již na tom, že správce databáze vybere libovolnou, kterou zobrazí vedle agregovaných hodnot reading.

Stejně jako můžeme řadit podle více kritérií najednou, můžeme také seskupovat podle více kritérií.Chceme-li například získat průměrný údaj podle vědce a měřeného množství,stačí přidat další pole do klauzule GROUP BY:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyGROUP BY person, quant;

osoba	množství	počet(čtení)	okolo(avg(čtení), 2)
-null-	sal	1	0.06
-null-	temp	1	-26.0
dyer	rad	2	8.81
dyer	sal	2	0,11
lake	rad	2	1.82
jezero	sal	4	0,11
jezero	temp	1	-16.0
pb	rad	3	6,66
pb	temp	2	-20.0
roe	rad	1	11,25
roe	sal	2	32.05

Všimněte si, že jsme do seznamu zobrazených polí přidali quant,protože jinak by výsledky nedávaly příliš smysl.

Půjdeme ještě o krok dál a odstraníme všechny záznamy,u nichž nevíme, kdo měření provedl:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyWHERE person IS NOT NULLGROUP BY person, quantORDER BY person, quant;

osoba	číslo	count(čtení)	round(avg(čtení), 2)
dyer	rad	2	8.81
dyer	sal	2	0.11
jezero	rad	2	1,82
jezero	sal	4	0.11
jezero	temp	1	-16.0
pb	rad	3	6,66
pb	temp	2	-20.0
roe	rad	1	11,25
roe	sal	2	32.05

Při bližším pohledu,tento dotaz:

1. vybral záznamy z tabulky Survey,kde pole person nebylo nulové;

2. seskupil tyto záznamy do podmnožin tak, aby hodnoty person a quant v každé podmnožině byly stejné;

3. seřadil tyto podskupiny nejprve podle person a pak v rámci každé podskupiny podle quant; a

4. spočítal počet záznamů v každé podskupině, vypočítal průměr reading v každé z nich a vybral z každé hodnoty person a quant (nezáleží na tom, které, protože všechny jsou stejné).

Počet teplotních údajů

Kolik teplotních údajů zaznamenal Frank Pabodie a jaká byla jejich průměrná hodnota?

Řešení

SELECT count(reading), avg(reading) FROM Survey WHERE quant = 'temp' AND person = 'pb';

count(reading)	avg(reading)
2	-20. Jaké jsou výsledky?0

Průměr s NULL

Průměr souboru hodnot je součet hodnotpodělený počtem hodnot.Znamená to, že funkce avg vrátí 2,0 nebo 3,0při zadání hodnot 1,0, null a 5,0?“

Řešení

Odpověď je 3,0.NULL není hodnota, je to nepřítomnost hodnoty.Jako taková není do výpočtu zahrnuta.

To můžete potvrdit, když provedete tento kód:

SELECT AVG(a) FROM ( SELECT 1 AS a UNION ALL SELECT NULL UNION ALL SELECT 5);

Co dělá tento dotaz?

Chceme vypočítat rozdíl mezi každým jednotlivým odečtem zářenía průměrem všech odečtů záření.Napíšeme dotaz:

SELECT reading - avg(reading) FROM Survey WHERE quant = 'rad';

Co to vlastně vyprodukuje a napadá vás proč?“

Řešení

Dotaz vyprodukuje pouze jeden řádek výsledků, i když ve skutečnosti chceme výsledek pro každé z měření.“ Funkce avg() vyprodukuje pouze jednu hodnotu, a protože je spuštěna jako první, je tabulka redukována na jeden řádek.Hodnota reading je prostě libovolná.

Abychom dosáhli toho, co jsme chtěli, museli bychom spustit dva dotazy:

SELECT avg(reading) FROM Survey WHERE quant='rad';

Tímto způsobem získáme průměrnou hodnotu (6.5625), kterou pak můžeme vložit do druhého dotazu:

SELECT reading - 6.5625 FROM Survey WHERE quant = 'rad';

Tím získáme to, co chceme, ale můžeme to spojit do jednoho dotazu pomocí poddotazů.

SELECT reading - (SELECT avg(reading) FROM Survey WHERE quant='rad') FROM Survey WHERE quant = 'rad';

Tímto způsobem nemusíme provádět dva dotazy.

Shrnem to, co jsme udělali, je, že jsme v původním dotazu nahradili avg(reading) za (SELECT avg(reading) FROM Survey WHERE quant='rad').

Pořadí při spojování

Funkce group_concat(field, separator)spojí všechny hodnoty v poli pomocí zadaného znaku oddělovače(nebo ‚,‘, pokud oddělovač není zadán).Použijte ji k vytvoření jednořádkového seznamu jmen vědců,například:

William Dyer, Frank Pabodie, Anderson Lake, Valentina Roerich, Frank Danforth

Můžete najít způsob, jak seznam seřadit podle příjmení?

Klíčové body

• Pro spojení více hodnot použijte agregační funkce.

• Agregační funkce ignorují hodnoty null.

• Agregace probíhá až po filtrování.

• K oddělenému kombinování podmnožin použijte GROUP BY.

• Pokud není pro pole zadána žádná agregační funkce, může dotaz pro toto pole vrátit libovolnou hodnotu.

.