Meetreeksen en administratieve data

Datamodellering
Lesbrief 5:
Rekenen en tellen met SQL
Rekenen en tellen met SQL
Met behulp van SQL functies kan men tellen, optellen, maxima en minima en
gemiddelden bepalen van een selectie gegevens.
Je kunt ook berekeningen uitvoeren. Ik zal aan de hand van de KNMI database
met meetwaarden van De Bilt tussen 01/01/1901 en 25/01/2005 (knmi.mdb) en
de (uitgebreide) bestellingen database van de vorige lesbrieven (winkel.mdb) een
en ander laten zien. Je kunt ze vinden in de materiaal folder. Deze databases heb
je ook zelf al gemaakt in de vorige lessen.
Je kunt kijken op http://www.w3schools.com/sql/ voor een SQL tutorial, waarin
ook voorbeelden staan van rekenfuncties.
De volgende rekenfuncties passeren de revue:
COUNT, MAX, MIN, AVG, SUM
En de z.g. aggregatie functie GROUP BY al of niet met HAVING.
De algemene syntax is
SELECT functie(tabelnaam.veldnaam) AS alias FROM tabelnaam;
De exacte betekenis zal je zien aan de hand van onderstaande voorbeelden. Je
hoeft niet per se iets voor alias in te vullen. Probeer al deze voorbeelden uit op de
gegeven databases.
Je kunt het beste beginnen in de Query By Example (QBE) mode en dan overgaan
op de SQL view.
Vragen 1 t/m 6 slaan op knmi.mdb en vragen 7 t/m 9 op winkel.mdb.
knmi.mdb
COUNT()
Vb1. Hoeveel regels staan er in de tabel Debilt?
SELECT COUNT(*)AS [totaal aantal meetdagen]
FROM Debilt;
Antwoord: 38290
Vb2. Hoeveel gemiddelde luchtdrukmetingen staan in de tabel Debilt?
SELECT COUNT(PG) AS [aantal luchtdrukmetingen]
FROM Debilt;
Antwoord: 37925
Dat is iets minder omdat de velden waar niets ingevuld staat niet meegeteld
worden en in het begin van de eeuw werden deze metingen nog niet gedaan.
MAX(), MIN()
Vb3. Wat was de hoogst gemeten temperatuur in graden Celsius?
SELECT Max(TX)/10 AS [graden Celsius]
FROM Debilt;
Antwoord: 36,8 graden Celsius
lesbrief 5
Pagina 1 van 8
Datamodellering
Rekenen en tellen met SQL
Vb4. Op welke datum werd deze hoogste temperatuur gemeten?
Deze eenvoudige vraag kan je niet oplossen met
SELECT YYYYMMDD, Max(TX)
FROM Debilt;
Probeer maar. Welke foutmelding krijg je dan?
Dit gaat een stuk beter:
SELECT Debilt.YYYYMMDD AS [heetste dag]
FROM Debilt
WHERE (Debilt.TX)=(SELECT Max(TX)
FROM Debilt);
Je hebt hier een voorbeeld van een genest SELECT statement.
Antwoord: 19470627
En dit is de QBE formulering, waarin je nu ook een regeltje SQL ziet staan:
AVG()
Vb5. Wat was de gemiddelde temperatuur vanaf 1901 in De Bilt en over hoeveel
metingen is dit gemiddelde berekend?
QBE formulering:
lesbrief 5
Pagina 2 van 8
Datamodellering
Rekenen en tellen met SQL
SQL formulering:
SELECT Avg(TG)/10 AS [Gem temp vanaf 1901], Count(TG) AS [aantal metingen]
FROM Debilt;
Antwoord:
Vb5
Gem_temp_vanaf_1901 aantal_metingen
9,43607730477932
38290
SUM()
Vb6. Hoeveel regen in meters is er in totaal gevallen vanaf 1901 in De Bilt en wat
is de gemiddelde regenval per dag in mm op hoeveel regendagen?
SQL formulering:
SELECT Sum(RH)/10000 AS [totale hoeveelheid regen in meters], Count(RH) AS
[totaal aantal regendagen], Avg(RH)/10 AS [daggemiddelde in mm]
FROM Debilt
WHERE (Debilt.RH)>0;
Let op dat hier RH>0 bij moet staan, omdat op dagen dat het kurkdroog was -1
ingevuld staat en die mag je niet meerekenen!
RH is het aantal 0.1 mm’s regenval per etmaal, vandaar die omrekenfactoren
10000 (naar meters) en 10 (naar mm) die je ziet.
QBE formulering:
lesbrief 5
Pagina 3 van 8
Datamodellering
Rekenen en tellen met SQL
Het antwoord is:
Vb6
totale_hoeveelheid_regen_in_meters totaal_aantal_regendagen daggemiddelde_in_mm
78,8974
20519
3,84508991666261
Dus 78,9 meter water in De Bilt vanaf 1901, 20519 regendagen en 3,845 mm
regen gemiddeld per dag. Gelukkig maar dat het water ergens naar toe kan :)
Dus ruwweg krijgt Nederland in een eeuw de hele Noordzee over zich uitgestort.
Je kunt zelf narekenen dat het daggemiddelde AVG(RH) gelijk is aan
SUM(RH)/COUNT(RH)
In de volgende voorbeelden gaan we de functies, die je net gehad hebt loslaten
op een groep records. We doen dit aan de hand van de winkel.mdb database,
die je kunt vinden in de materiaal folder.
Eerst een eenvoudige:
Vb7. Hoeveel artikelen zijn in totaal besteld?
De QBE formulering is:
lesbrief 5
Pagina 4 van 8
Datamodellering
Rekenen en tellen met SQL
De SQL formulering is:
SELECT Sum(Bestelregel.aantal) AS [Totaal aantal artikelen besteld]
FROM Bestelregel;
Het antwoord is 409
GROUP BY
Vb8. Hoeveel artikelen zijn in totaal per klant besteld?
We moeten nu per klant sommeren en doen dit door een GROUP BY clausule toe
te voegen aan het eind.
De QBE formulering is:
lesbrief 5
Pagina 5 van 8
Datamodellering
Rekenen en tellen met SQL
De SQL formulering is:
SELECT Klant.naam, Sum(Bestelregel.aantal) AS [Aantal Bestellingen per
klant]
FROM (Klant INNER JOIN Bestelling ON Klant.kcode = Bestelling.kcode) INNER
JOIN Bestelregel ON Bestelling.bcode = Bestelregel.bcode
GROUP BY Klant.naam;
Het antwoord is:
Samen is dit inderdaad 409
Vb9. Wat is het totaal bestelde bedrag per klant?
Nu moet het aantal artikelen ook nog met de stukprijs vermenigvuldigd worden
alvorens alles gegroepeerd per klant kan worden opgeteld. Dus je gebruikt nu
een vermenigvuldiging binnen de SUM() functie.
Begin in de QBE formulering met het toevoegen van de 4 benodigde tabellen en
de 3 relaties en ga dan over op SQL formulering.
lesbrief 5
Pagina 6 van 8
Datamodellering
Rekenen en tellen met SQL
De SQL formulering is:
SELECT Klant.naam, Sum(Bestelregel.aantal*Artikel.stukprijs) AS [Totaal
bestelde bedrag]
FROM ((Klant INNER JOIN Bestelling ON Klant.kcode = Bestelling.kcode) INNER
JOIN Bestelregel ON Bestelling.bcode = Bestelregel.bcode) INNER JOIN Artikel
ON Bestelregel.acode = Artikel.acode
GROUP BY Klant.naam;
Het antwoord luidt:
De QBE formulering ziet er zo uit:
lesbrief 5
Pagina 7 van 8
Datamodellering
Rekenen en tellen met SQL
Opdrachten
Vind een antwoord op onderstaande vragen met behulp van de juiste SQL
formulering. Gebruik de database knmi.mdb, die je kunt vinden in de
materiaalfolder en winkel2.mdb die je in lesbrief 4 gemaakt hebt.
knmi.mdb:
1. Geef behalve de gemiddelde temperatuur (in graden Celsius) ook de
standaarddeviatie van dit gemiddelde voor de metingen vanaf 1901. (gebruik
StDev())
2. Hoeveel vorstdagen waren er vanaf 1901? Dus dagen waarop de temperatuur
niet boven nul kwam?
3. Wat was de gemiddelde windrichting (in graden over de hele meetperiodew)?
(een gemene!)
4. Geef de laagst gemeten temperatuur (in graden Celsius) en de datum waarop
dat was.
winkel2.mdb:
5. Voor welk bedrag is er per artikel in totaal besteld?
6. Hoeveel aantallen zijn er per artikel besteld?
7. Wie heeft nog nooit iets besteld? (lastig!)
8. Welk artikel is nog nooit besteld?
Redeneer bij vraag 7 als volgt: Geef mij de selectie klantnamen van die klanten,
van wie de klantID NIET in de selectie van klantID’s zit van klanten die ooit wel
iets besteld hebben. Dus je gebruikt een omweg. Bij vraag 8 moet je een
soortgelijke redenering opzetten.
Zet de vragen met hun QBE notaties, SQL formuleringen en de antwoorden in een
word document les5.doc en bewaar dit zorgvuldig.
Einde lesbrief 5
lesbrief 5
Pagina 8 van 8