Aggregáció

• Áttekintés
• Hőmérsékleti mérések számolása
• megoldás
• átlagolás NULL-al
• megoldás
• Mit csinál ez a lekérdezés?
• Megoldás
• Sorrend az összekapcsoláskor
• Kulcspontok

Áttekintés

Tanítás: 10 perc
Gyakorlatok: 10 perc

Kérdések

• Hogyan számolhatok összegeket, átlagokat és egyéb összegző értékeket?

Célok

• Meghatározza az összesítés fogalmát és példákat hoz a használatára.

• Az összesített értékeket kiszámító lekérdezések írása.

• Az összesítést végző lekérdezés végrehajtásának nyomon követése.

• Magyarázza, hogyan kezeli a hiányzó adatokat az összesítés során.

Az adatainkhoz most tartományokat és átlagokat szeretnénk kiszámítani.Tudjuk, hogyan válasszuk ki az összes dátumot a Visited táblázatból:

SELECT dated FROM Visited;

-null-

de ahhoz, hogy egyesítsük őket,olyan aggregáló függvényt kell használnunk, mint a min vagy a max.Mindegyik függvény bemenetként egy rekordhalmazt fogad el,és kimenetként egyetlen rekordot állít elő:

SELECT min(dated) FROM Visited;

SELECT max(dated) FROM Visited;

min és max csak két az SQL-be beépített aggregációs függvények közül.Három másik a avg,count és sum:

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

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

.

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

Itt a count(reading)-t használtuk,de ugyanilyen könnyen megszámolhattuk volna quantvagy bármely más mezőt a táblázatban,vagy akár count(*)-t is használhattunk volna,mivel a függvényt nem érdeklik maguk az értékek,csak az,hogy hány érték van.

A SQL lehetővé teszi számunkra, hogy egyszerre több aggregációt végezzünk.Így például megtalálhatjuk az érzékeny sótartalom mérések tartományát:

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

Az összesített eredményeket is kombinálhatjuk a nyers eredményekkel,bár a kimenet meglepő lehet:

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

Miért szerepel Lake neve Roerich vagy Dyer helyett?A válasz az, hogy amikor aggregálnia kell egy mezőt,de nem mondják meg neki, hogy hogyan,az adatbázis-kezelő kiválaszt egy aktuális értéket a bemeneti halmazból.Használhatja az első feldolgozottat, az utolsót, vagy valami teljesen mást.

Egy másik fontos tény, hogy amikor nincsenek aggregálandó értékek – például amikor nincsenek a WHERE záradéknak megfelelő sorok -, akkor az aggregálás eredménye “nem tudom”-ot ad, nem pedig nullát vagy más tetszőleges értéket:

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

Az aggregációs függvények egy utolsó fontos tulajdonsága, hogy nagyon hasznos módon ellentmondásban vannak az SQL többi részével.Ha összeadunk két értéket,és az egyikük null,az eredmény null.Kiterjesztve,ha a sum-t használjuk egy halmaz összes értékének összeadására,és bármelyik érték null,az eredménynek szintén nullnak kell lennie.Sokkal hasznosabb azonban,hogy az aggregációs függvények figyelmen kívül hagyják a null értékeket,és csak azokat kombinálják,amelyek nem nullák.Ez a viselkedés lehetővé teszi, hogy lekérdezéseinket így írjuk meg:

SELECT min(dated) FROM Visited;

ahelyett, hogy mindig explicit módon kellene szűrnünk:

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

Az összes rekord egyszerre történő összevonásának nem mindig van értelme.Tegyük fel például, hogy azt gyanítjuk, hogy adatainkban szisztematikus torzítás van, és hogy egyes tudósok sugárzási mérései magasabbak, mint másoké.Tudjuk, hogy ez nem működik:

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

mert az adatbázis-kezelő egyetlen tetszőleges tudós nevét választja ki, ahelyett, hogy minden egyes tudós esetében külön-külön összesítené.Mivel csak öt tudós van,öt lekérdezést írhatunk a következő formában:

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

de ez fárasztó lenne,és ha valaha is lenne egy adathalmazunk ötven vagy ötszáz tudósból,kicsi az esélye annak,hogy minden ilyen lekérdezést jól fogunk találni.

Azt kell tennünk, hogy megmondjuk az adatbázis-kezelőnek, hogy az egyes tudósok óráit külön-külön összesítse egy GROUP BY záradékkal:

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

GROUP BY pontosan azt teszi, amit a neve is sugall:a megadott mezőre azonos értékkel rendelkező rekordokat csoportosítja, hogy az aggregálás az egyes tételeket külön-külön feldolgozhassa.Mivel az egyes tételekben minden rekordnak ugyanaz az person értéke,már nem számít, hogy az adatbázis-kezelő egy tetszőleges értéket választ ki az összesített reading értékek mellett.

Amint ahogy egyszerre több kritérium szerint is rendezhetünk, úgy csoportosíthatunk is több kritérium szerint.Ha például az átlagos értéket szeretnénk megkapni a tudós és a mért mennyiség szerint,csak hozzáadunk egy másik mezőt a GROUP BY záradékhoz:

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

Megjegyezzük, hogy a megjelenített mezők listáját kiegészítettük a quant-mal,mivel az eredményeknek másképp nem sok értelme lenne.

Menjünk egy lépéssel tovább, és távolítsuk el az összes olyan bejegyzést, ahol nem tudjuk, hogy ki végezte a mérést:

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

Nézzük meg közelebbről,ezt a lekérdezést:

1. kiválasztotta a Survey táblából azokat a rekordokat, amelyekben a person mező nem volt nulla;

2. ezeket a rekordokat részhalmazokba csoportosította, hogy a person és quant értékek minden részhalmazban azonosak legyenek;

3. rendezte ezeket az alcsoportokat először person szerint,majd minden alcsoporton belül quant szerint;és

4. megszámolta a rekordok számát minden alcsoportban,kiszámította az átlagos reading értéket mindegyikben,és kiválasztott egy person és egy quant értéket mindegyikből(nem számít, hogy melyiket,mivel mindegyik egyenlő).

Hőmérsékleti mérések számolása

Hány hőmérsékleti mérést rögzített Frank Pabodie,és mennyi volt az átlagértékük?

megoldás

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

szám(mérés)	átlag(mérés)
2	-20.0

átlagolás NULL-al

Egy értékkészlet átlaga az értékek összegeosztva az értékek számával.Ez azt jelenti, hogy a avg függvény 2,0 vagy 3,0 értéket ad vissza, ha az 1,0, null és 5,0 értékeket kapjuk?

megoldás

A válasz 3,0.NULL nem egy érték, hanem egy érték hiánya.Mint ilyen, nem szerepel a számításban.

Ezt a következő kód végrehajtásával erősítheti meg:

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

Mit csinál ez a lekérdezés?

Az egyes sugárzási értékek és az összes sugárzási érték átlaga közötti különbséget szeretnénk kiszámítani.Megírjuk a lekérdezést:

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

Mit eredményez ez valójában, és el tudja képzelni, hogy miért?

Megoldás

A lekérdezés csak egy sornyi eredményt ad, holott valójában minden egyes leolvasásra egy eredményt szeretnénk.A avg() függvény csak egyetlen értéket ad, és mivel először ez fut le, a táblázat egyetlen sorra csökken.A reading érték egyszerűen egy tetszőleges érték.

Hogy elérjük, amit akartunk, két lekérdezést kellene futtatnunk:

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

Ez az átlagértéket (6.5625), amelyet aztán beilleszthetünk egy második lekérdezésbe:

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

Ez azt eredményezi, amit szeretnénk, de ezt egyetlen lekérdezéssé kombinálhatjuk az alkérdések segítségével.

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

Így nem kell két lekérdezést végrehajtanunk.

Összefoglalva, amit tettünk, hogy az eredeti lekérdezésben a avg(reading) helyett (SELECT avg(reading) FROM Survey WHERE quant='rad') szerepel.

Sorrend az összekapcsoláskor

A group_concat(field, separator)függvény egy mező összes értékét összekapcsolja a megadott elválasztó karakter (vagy ‘,’, ha az elválasztó karakter nincs megadva) használatával.Használja ezt a tudósok neveinek egysoros listájának előállításához,például:

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

Megoldható a lista vezetéknév szerinti rendezése?

Kulcspontok

• Az aggregációs függvények segítségével több értéket kombinálhat.

• Az aggregációs függvények figyelmen kívül hagyják a null értékeket.

• Az összesítés a szűrés után történik.

• Használja a GROUP BY-t a részhalmazok külön-külön történő egyesítéséhez.

• Ha egy mezőhöz nincs megadva összesítő függvény, a lekérdezés tetszőleges értéket adhat vissza az adott mezőre.