Agggregatie

• Overzicht
• Tellen van temperatuurmetingen
• Oplossing
• Gemiddelde met NULL
• Oplossing
• Wat doet deze query?
• Oplossing
• Ordering bij aaneenschakelen
• Kernpunten

Overzicht

Lesstof: 10 min
Oefeningen: 10 min

Vragen

• Hoe kan ik sommen, gemiddelden en andere samenvattende waarden berekenen?

Doelstellingen

• Ontdefinieer aggregatie en geef voorbeelden van het gebruik ervan.

• Query’s schrijven die geaggregeerde waarden berekenen.

• De uitvoering traceren van een query die aggregatie uitvoert.

• Uitleggen hoe ontbrekende gegevens worden behandeld tijdens aggregatie.

We willen nu bereiken en gemiddelden berekenen voor onze gegevens.We weten hoe we alle data uit de tabel Visited moeten selecteren:

SELECT dated FROM Visited;

-null-

maar om ze te combineren, moeten we een aggregatiefunctie gebruiken, zoals min of max.Elk van deze functies neemt een reeks records als invoer, en produceert een enkel record als uitvoer:

SELECT min(dated) FROM Visited;

SELECT max(dated) FROM Visited;

min en max zijn slechts twee van de aggregatiefuncties die in SQL zijn ingebouwd.Drie andere zijn avg,count,en 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';

We hebben hier count(reading) gebruikt,maar we hadden evengoed quant of een ander veld in de tabel kunnen tellen, of zelfs count(*) kunnen gebruiken, aangezien de functie niet om de waarden zelf geeft, maar alleen om hoeveel waarden er zijn.

SQL laat ons verschillende aggregaties tegelijk doen.We kunnen bijvoorbeeld het bereik vinden van gevoelige zoutgehalte metingen:

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

We kunnen ook geaggregeerde resultaten combineren met onbewerkte resultaten, hoewel de output u zou kunnen verrassen:

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

Waarom komt de naam van Lake voor en niet die van Roerich of Dyer?Het antwoord is dat wanneer het een veld moet aggregeren, maar niet verteld wordt hoe, de database manager een werkelijke waarde kiest uit de input set.Hij kan de eerste gebruiken die verwerkt is, de laatste, of iets heel anders.

Een ander belangrijk feit is dat wanneer er geen waarden zijn om te aggregeren – bijvoorbeeld wanneer er geen rijen zijn die voldoen aan de WHERE-clausule – het resultaat van de aggregatie “weet niet” is in plaats van nul of een andere willekeurige waarde:

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

Een laatste belangrijke eigenschap van aggregatie functies is dat ze inconsistent zijn met de rest van SQL op een zeer nuttige manier.Als we twee waarden optellen, en een van hen is null, dan is het resultaat null.Bij uitbreiding, als we sum gebruiken om alle waarden in een set op te tellen, en een van die waarden is null, dan zou het resultaat ook null moeten zijn.Het is echter veel nuttiger voor aggregatie functies om null waarden te negeren en alleen die waarden te combineren die niet null zijn.Dankzij dit gedrag kunnen we onze query’s schrijven als:

SELECT min(dated) FROM Visited;

in plaats van altijd expliciet te moeten filteren:

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

Het heeft niet altijd zin om alle records in één keer te registreren.Stel bijvoorbeeld dat we vermoeden dat er een systematische vertekening in onze gegevens zit, en dat de stralingsmetingen van sommige wetenschappers hoger zijn dan die van anderen.We weten dat dit niet werkt:

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

omdat de databankbeheerder één enkele willekeurige naam van de wetenschapper selecteert in plaats van voor elke wetenschapper afzonderlijk samen te tellen.Aangezien er slechts vijf wetenschappers zijn, zouden we vijf queries van de vorm kunnen schrijven:

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

maar dit zou vervelend zijn, en als we ooit een gegevensverzameling zouden hebben met vijftig of vijfhonderd wetenschappers, is de kans klein dat we al die queries goed krijgen.

Wat we moeten doen is de database manager vertellen dat hij de uren van elke wetenschapper apart moet aggregeren met een GROUP BY clausule:

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

GROUP BY doet precies wat zijn naam impliceert: alle records met dezelfde waarde voor het opgegeven veld samenvoegen zodat de aggregatie elke batch afzonderlijk kan verwerken.Aangezien alle records in elke batch dezelfde waarde hebben voor person, maakt het niet langer uit dat de databasebeheerder een willekeurige waarde kiest om weer te geven naast de samengevoegde reading-waarden.

Net zoals we op meerdere criteria tegelijk kunnen sorteren, kunnen we ook op meerdere criteria groeperen.Om bijvoorbeeld de gemiddelde waarde per wetenschapper en gemeten hoeveelheid te verkrijgen, voegen wij gewoon een ander veld toe aan de GROUP BY-clausule:

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

Merk op dat we quant hebben toegevoegd aan de lijst van getoonde velden, aangezien de resultaten anders niet veel zin zouden hebben.

Laten we nog een stap verder gaan en alle velden verwijderen waarvan we niet weten wie de meting heeft verricht:

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

Bij nader inzien, deze query:

1. selecteerde records uit de Survey tabel waar het person veld niet null was;

2. groepeerde deze records in subsets zodat de person en quant waarden in elke subset hetzelfde waren;

3. ordende die deelverzamelingen eerst op person, en dan binnen elke deelgroep op quant; en

4. telde het aantal records in elke deelverzameling, berekende de gemiddelde reading in elk, en koos een person en quant waarde uit elk (het maakt niet uit welke, omdat ze allemaal gelijk zijn).

Tellen van temperatuurmetingen

Hoeveel temperatuurmetingen heeft Frank Pabodie gedaan, en wat was hun gemiddelde waarde?

Oplossing

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

telling(meting)	avg(meting)
2	-20.0

Gemiddelde met NULL

Het gemiddelde van een reeks waarden is de som van de waarden gedeeld door het aantal waarden.Betekent dit dat de functie avg 2,0 of 3,0 teruggeeft wanneer de waarden 1,0, null, en 5,0 gegeven zijn?

Oplossing

Het antwoord is 3,0.NULL is geen waarde; het is de afwezigheid van een waarde.U kunt dit bevestigen door deze code uit te voeren:

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

Wat doet deze query?

We willen het verschil berekenen tussen elke individuele stralingsmeting en het gemiddelde van alle stralingsmetingen.We schrijven de query:

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

Wat levert dit eigenlijk op, en kun je bedenken waarom?

Oplossing

De query levert slechts één rij resultaten op, terwijl we eigenlijk een resultaat voor elk van de metingen willen. De functie avg() levert slechts één waarde op, en omdat deze eerst wordt uitgevoerd, wordt de tabel gereduceerd tot één rij.De reading waarde is gewoon een willekeurige waarde.

Om te bereiken wat we wilden, zouden we twee query’s moeten uitvoeren:

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

Dit levert de gemiddelde waarde op (6.5625), die we vervolgens in een tweede query kunnen invoegen:

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

Dit levert op wat we willen, maar we kunnen dit combineren in één query met behulp van subquery’s.

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

Op deze manier hoeven we niet twee query’s uit te voeren.

Samengevat is wat we hebben gedaan avg(reading) vervangen door (SELECT avg(reading) FROM Survey WHERE quant='rad') in de oorspronkelijke query.

Ordering bij aaneenschakelen

De functie group_concat(field, separator)eenschakelt alle waarden in een veld aan elkaar met behulp van het opgegeven scheidingsteken (of ‘,’ als het scheidingsteken niet is opgegeven).Gebruik dit om een één-regelige lijst van namen van wetenschappers te produceren, zoals:

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

Kunt u een manier vinden om de lijst op achternaam te ordenen?

Kernpunten

• Gebruik aggregatiefuncties om meerdere waarden te combineren.

• Agggregatiefuncties negeren null waarden.

• Samentelling vindt plaats na filtering.

• Gebruik GROUP BY om deelverzamelingen afzonderlijk te combineren.

• Als voor een veld geen aggregatiefunctie is opgegeven, kan de query een willekeurige waarde voor dat veld retourneren.