Aggregering

• Overblik
• Tæller temperaturmålinger
• Løsning
• Middelværdiberegning med NULL
• Løsning
• Hvad gør denne forespørgsel?
• Løsning
• Rækkefølge ved sammenkædning
• Nøglepunkter

Overblik

Undervisning: 10 min
Øvelser: 10 min

Spørgsmål

• Hvordan kan jeg beregne summer, gennemsnit og andre sammenfattende værdier?

Mål

• Definér aggregering og giv eksempler på dens anvendelse.

• Skriv forespørgsler, der beregner aggregerede værdier.

• Spore udførelsen af en forespørgsel, der udfører aggregering.

• Forklar, hvordan manglende data håndteres under aggregering.

Vi vil nu beregne intervaller og gennemsnit for vores data.Vi ved, hvordan vi kan vælge alle datoerne fra tabellen Visited:

SELECT dated FROM Visited;

-null-

men for at kombinere dem skal vi bruge en aggregeringsfunktion som min eller max.Hver af disse funktioner tager et sæt af poster som input og producerer en enkelt post som output:

SELECT min(dated) FROM Visited;

SELECT max(dated) FROM Visited;

min og max er blot to af de aggregeringsfunktioner, der er indbygget i SQL.Tre andre er avg,count og 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';

Vi har brugt count(reading) her,men vi kunne lige så godt have talt quanteller et hvilket som helst andet felt i tabellen,eller endda brugt count(*),da funktionen er ligeglad med selve værdierne,men kun hvor mange værdier der er.

SQL giver os mulighed for at lave flere aggregeringer på en gang.Vi kan f.eks. finde intervallet for fornuftige saltindholdsmålinger:

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

Vi kan også kombinere aggregerede resultater med rå resultater,selv om resultatet måske vil overraske dig:

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

Hvorfor står Lake’s navn i stedet for Roerich’s eller Dyer’s?Svaret er, at når den skal aggregere et felt, men ikke får at vide, hvordan den skal gøre det, vælger databaseadministratoren en faktisk værdi fra indtastningssættet.Den kan bruge den første behandlede værdi, den sidste værdi eller noget helt andet.

Et andet vigtigt faktum er, at når der ikke er nogen værdier at aggregere – f.eks. når der ikke er nogen rækker, der opfylder WHERE-klausulen – er aggregeringens resultat “ved ikke “i stedet for nul eller en anden vilkårlig værdi:

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

Et sidste vigtigt træk ved aggregeringsfunktioner er, at de er inkonsistente med resten af SQL på en meget nyttig måde.Hvis vi lægger to værdier sammen, og den ene af dem er nul, er resultatet nul. i forlængelse heraf, hvis vi bruger sum til at lægge alle værdierne i et sæt sammen, og nogen af disse værdier er nul, bør resultatet også være nul. det er dog meget mere nyttigt, at aggregeringsfunktioner ignorerer nulværdier og kun kombinerer dem, der ikke er nul.Denne adfærd lader os skrive vores forespørgsler som:

SELECT min(dated) FROM Visited;

i stedet for altid at skulle filtrere eksplicit:

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

Det giver ikke altid mening at aggregere alle poster på én gang.Lad os for eksempel antage, at vi har mistanke om, at der er en systematisk skævhed i vores data, og at nogle forskeres strålingsmålinger er højere end andres.Vi ved, at dette ikke virker:

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

fordi databaseadministratoren vælger en enkelt vilkårlig forskers navn i stedet for at aggregerer separat for hver enkelt forsker.Da der kun er fem videnskabsmænd, kan vi skrive fem forespørgsler af følgende form:

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

men det ville være trættende,og hvis vi nogensinde havde et datasæt med halvtreds eller fem hundrede forskere,er chancerne for at vi får alle disse forespørgsler rigtige,små.

Det, vi skal gøre, er at bede databaseadministratoren om at aggregere timerne for hver videnskabsmand separat ved hjælp af en GROUP BY-klausul:

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

GROUP BY gør præcis, hvad navnet antyder: grupperer alle poster med den samme værdi for det angivne felt sammen, så aggregering kan behandle hver batch separat.Da alle posterne i hver batch har den samme værdi for person, betyder det ikke længere noget, at databaseadministratoren vælger en vilkårlig værdi til at blive vist ved siden af de aggregerede reading-værdier.

Som vi kan sortere efter flere kriterier på én gang, kan vi også gruppere efter flere kriterier.Hvis vi f.eks. vil få den gennemsnitlige aflæsning efter videnskabsmand og målt mængde, skal vi blot tilføje endnu et felt til GROUP BY-klausulen:

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

Bemærk, at vi har tilføjet quant til listen over felter, der vises,da resultaterne ellers ikke ville give meget mening.

Lad os gå et skridt videre og fjerne alle de poster, hvor vi ikke ved, hvem der har foretaget målingen:

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

Hvis man ser nærmere på denne forespørgsel:

1. valgte poster fra tabellen Survey, hvor feltet person ikke var nul;

2. grupperede disse poster i undergrupper, således at person– og quant-værdierne i hver undergruppe var de samme;

3. ordnede disse undergrupper først efter person og derefter inden for hver undergruppe efter quant;og

4. optalt antallet af poster i hver undergruppe,beregnet den gennemsnitlige reading i hver og valgt en person– og quant-værdi fra hver (det er ligegyldigt hvilke,da de alle er ens).

Tæller temperaturmålinger

Hvor mange temperaturmålinger registrerede Frank Pabodie,og hvad var deres gennemsnitsværdi?

Løsning

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

tælle(aflæsning)	gennemsnit(aflæsning)
2	-20.0

Middelværdiberegning med NULL

Gennemsnittet af et sæt af værdier er summen af værdierne divideret med antallet af værdier.Betyder det, at funktionen avg returnerer 2,0 eller 3,0, når den får værdierne 1,0, null og 5,0?

Løsning

Svaret er 3,0.NULL er ikke en værdi; det er fraværet af en værdi.Det indgår derfor ikke i beregningen.

Du kan bekræfte dette ved at udføre denne kode:

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

Hvad gør denne forespørgsel?

Vi ønsker at beregne forskellen mellem hver enkelt strålingsmåling og gennemsnittet af alle strålingsmålingerne.Vi skriver forespørgslen:

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

Hvad giver det egentlig, og kan du tænke på hvorfor?

Løsning

Spørgslen giver kun én række resultater, når vi i virkeligheden ønsker et resultat for hver enkelt måling.avg()funktionen giver kun en enkelt værdi, og fordi den køres først, reduceres tabellen til en enkelt række.reading-værdien er simpelthen en vilkårlig værdi.

For at opnå det, vi ønskede, skulle vi køre to forespørgsler:

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

Dette giver den gennemsnitlige værdi (6.5625), som vi derefter kan indsætte i en anden forespørgsel:

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

Dette giver det ønskede resultat, men vi kan kombinere dette i en enkelt forespørgsel ved hjælp af underafspørgsler.

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

På denne måde behøver vi ikke at udføre to forespørgsler.

Sammenfattende er det, vi har gjort, at erstatte avg(reading) med (SELECT avg(reading) FROM Survey WHERE quant='rad') i den oprindelige forespørgsel.

Rækkefølge ved sammenkædning

Funktionen group_concat(field, separator)samkæder alle værdierne i et felt ved hjælp af det angivne separatortegn(eller ‘,’, hvis separatoren ikke er angivet).Brug denne funktion til at fremstille en liste på én linje over videnskabsfolks navne,f.eks.:

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

Kan du finde en måde at ordne listen efter efternavn?

Nøglepunkter

• Brug aggregeringsfunktioner til at kombinere flere værdier.

• Aggregeringsfunktioner ignorerer null værdier.

• Aggregering sker efter filtrering.

• Brug GROUP BY for at kombinere delmængder separat.

• Hvis der ikke er angivet nogen aggregeringsfunktion for et felt, kan forespørgslen returnere en vilkårlig værdi for det pågældende felt.