admin

Översikt

Undervisning: 10 min
Övningar: 10 min
Frågor

  • Hur kan jag beräkna summor, medelvärden och andra sammanfattande värden?

Mål

  • Definiera aggregering och ge exempel på dess användning.

  • Skriv frågor som beräknar aggregerade värden.

  • Spåra utförandet av en fråga som utför aggregering.

  • Förklara hur saknade data hanteras vid aggregering.

Vi vill nu beräkna intervall och medelvärden för våra data.Vi vet hur man väljer alla datum från tabellen Visited:

SELECT dated FROM Visited;

dated

-null-

men för att kombinera dem måste vi använda en aggregeringsfunktion som min eller max.Var och en av dessa funktioner tar en uppsättning poster som indata och producerar en enda post som utdata:

SELECT min(dated) FROM Visited;

min(daterad) 

SELECT max(dated) FROM Visited;

max(daterad)

min och max är bara två av de aggregeringsfunktioner som är inbyggda i SQL.Tre andra är avg, count och sum:

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

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

count(reading)

. 

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

sum(reading)

Vi använde count(reading) här,men vi kunde lika gärna ha räknat quant eller något annat fält i tabellen, eller till och med använt count(*), eftersom funktionen inte bryr sig om själva värdena, utan bara om hur många värden det finns.

SQL låter oss göra flera aggregeringar samtidigt.Vi kan till exempel hitta intervallet för känsliga salthaltsmätningar:

SELECT min(reading), max(reading) FROM Survey WHERE quant = 'sal' AND reading <= 1.0;
min(mätning) max(mätning)
0,05 0.21

Vi kan också kombinera aggregerade resultat med råa resultat,även om resultatet kan överraska dig:

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

Varför förekommer Lake i stället för Roerich eller Dyer?Svaret är att när databashanteraren måste aggregera ett fält, men inte får veta hur den ska göra, väljer den ett faktiskt värde från inmatningsuppsättningen.Den kan använda det första som behandlas, det sista eller något helt annat.

Ett annat viktigt faktum är att när det inte finns några värden att aggregera – till exempel när det inte finns några rader som uppfyller WHERE-klausulen – är aggregeringens resultat ”vet inte ”snarare än noll eller något annat godtyckligt värde:

SELECT person, max(reading), sum(reading) FROM Survey WHERE quant = 'missing';
person max(läsning) sum(läsning)
-noll- -null- -null-

En sista viktig egenskap hos aggregeringsfunktioner är att de är inkonsekventa med resten av SQL på ett mycket användbart sätt.Om vi adderar två värden och ett av dem är ogiltigt blir resultatet ogiltigt.Om vi använder sum för att addera alla värden i en uppsättning och något av dessa värden är ogiltigt blir resultatet också ogiltigt.Det är dock mycket mer användbart att aggregeringsfunktioner ignorerar ogiltiga värden och endast kombinerar de värden som inte är ogiltiga.Detta beteende gör att vi kan skriva våra frågor som:

SELECT min(dated) FROM Visited;

min(daterad)

i stället för att alltid behöva filtrera explicit:

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

min(dated)

Aggregering av alla poster på en gång är inte alltid meningsfullt.Anta till exempel att vi misstänker att det finns en systematisk bias i våra data, och att vissa forskares strålningsmätningar är högre än andra.Vi vet att detta inte fungerar:

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

eftersom databashanteraren väljer en enda godtycklig forskares namn i stället för att aggregera separat för varje forskare.Eftersom det bara finns fem forskare kan vi skriva fem förfrågningar av följande form:

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

Men detta skulle vara tråkigt, och om vi någonsin hade en datamängd med femtio eller femhundra vetenskapsmän är chansen liten att vi skulle få alla dessa frågor rätt.

Vad vi behöver göra är att be databashanteraren att sammanställa timmarna för varje forskare separat med hjälp av en GROUP BY-klausul:

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
sjö 2 1.82
pb 3 6.66
roe 1 11.25

GROUP BY gör exakt vad namnet antyder: grupperar alla poster med samma värde för det angivna fältet tillsammans så att aggregeringen kan behandla varje batch separat.Eftersom alla poster i varje sats har samma värde för person spelar det inte längre någon roll att databashanteraren väljer ett godtyckligt värde som visas vid sidan av de aggregerade reading-värdena.

Samma som vi kan sortera efter flera kriterier samtidigt, kan vi också gruppera efter flera kriterier.För att få fram den genomsnittliga avläsningen efter vetenskapsman och uppmätt kvantitet är det bara att lägga till ytterligare ett fält i GROUP BY-klausulen:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyGROUP BY person, quant;
person kvant count(reading) round(avg(reading), 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
sjö sal 4 0.11
sjö temp 1 -16.0
pb rad 3 6.66
pb temp 2 -20.0
roe rad 1 11.25
roe sal 2 32.05

Notera att vi har lagt till quant i listan över fält som visas, eftersom resultaten inte skulle vara särskilt logiska annars.

Vi kan gå ett steg längre och ta bort alla poster där vi inte vet vem som utförde mätningen:

SELECT person, quant, count(reading), round(avg(reading), 2)FROM SurveyWHERE person IS NOT NULLGROUP BY person, quantORDER BY person, quant;
person kvant räknar(läsning) rundar(avg(läsning), 2)
dyer rad 2 8.81
dyer sal 2 0.11
sjö rad 2 1.82
sjö sal 4 0.11
lake temp 1 -16.0
pb rad 3 6.66
pb temp 2 -20.0
roe rad 1 11.25
roe sal 2 32.05

Om man tittar närmare på denna fråga:

  1. väljer ut poster från tabellen Survey där fältet person inte är noll;

  2. grupperar dessa poster i undergrupper så att värdena person och quant i varje undergrupp är desamma;

  3. ordnade dessa undergrupper först efter person och sedan inom varje undergrupp efter quant, och

  4. räknade antalet poster i varje undergrupp, beräknade genomsnittet reading i varje och valde ett person– och quant-värde från var och en av dem (det spelar ingen roll vilka, eftersom de alla är lika).

Räkna temperaturavläsningar

Hur många temperaturavläsningar registrerade Frank Pabodie och vilket var deras medelvärde?

Lösning

SELECT count(reading), avg(reading) FROM Survey WHERE quant = 'temp' AND person = 'pb';
räkning(avläsning) avg(avläsning)
2 -20.0

Medelvärdeberäkning med NULL

Genomsnittet av en uppsättning värden är summan av värdena dividerat med antalet värden.Betyder detta att funktionen avg returnerar 2,0 eller 3,0 när den får värdena 1,0, null och 5,0?

Lösning

Svaret är 3,0.NULL är inte ett värde; det är frånvaron av ett värde.Det ingår alltså inte i beräkningen.

Du kan bekräfta detta genom att köra den här koden:

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

Vad gör den här frågan?

Vi vill beräkna skillnaden mellan varje enskild strålningsmätning och genomsnittet av alla strålningsmätningar.Vi skriver frågan:

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

Vad ger detta egentligen och kan du tänka dig varför?

Lösning

Frågan ger bara en rad med resultat när vi egentligen vill ha ett resultat för var och en av avläsningarna.avg()-funktionen ger bara ett enda värde, och eftersom den körs först, reduceras tabellen till en enda rad.Värdet reading är helt enkelt ett godtyckligt värde.

För att uppnå det vi ville skulle vi behöva köra två förfrågningar:

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

Detta ger medelvärdet (6.5625), som vi sedan kan infoga i en andra fråga:

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

Detta ger det vi vill ha, men vi kan kombinera detta till en enda fråga med hjälp av underfrågor.

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

På så sätt behöver vi inte utföra två förfrågningar.

Sammanfattningsvis är det vi har gjort att ersätta avg(reading) med (SELECT avg(reading) FROM Survey WHERE quant='rad') i den ursprungliga förfrågan.

Ordning vid sammankoppling

Funktionen group_concat(field, separator)samkör alla värden i ett fält med hjälp av det angivna separatortecknet (eller ’,’ om separatorn inte är angiven).Använd den här funktionen för att producera en enradig lista över vetenskapsmännens namn, till exempel:

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

Kan du hitta ett sätt att ordna listan efter efternamn?

Nyckelpunkter

  • Använd aggregeringsfunktioner för att kombinera flera värden.

  • Aggregeringsfunktioner ignorerar null värden.

  • Aggregering sker efter filtrering.

  • Använd GROUP BY för att kombinera delmängder separat.

  • Om ingen aggregeringsfunktion har angetts för ett fält kan frågan returnera ett godtyckligt värde för det fältet.

admin

Lämna ett svar

Din e-postadress kommer inte publiceras.

lg