Groeperen en samenvatten

Inhoud

Groeperen en samenvatten#

Het resultaat van selectie en projectie bevat de gegevens van afzonderlijke rijen in de tabel. Maar soms willen je de gegevens van meerdere rijen samenvatten, bijvoorbeeld in een aantal, gemiddelde, minumum of maximum. Deze samenvatting kan betrekking hebben op de hele tabel, maar ook op groepen in deze tabel.

We behandelen hier de volgende onderdelen van de SELECT-opdracht:

samenvattingsfuncties (count, sum, avg, min, max;
groeperen, met GROUP BY <kolom(men)>;
filteren van groepen, met HAVING <conditie>.

Maak verbinding met de database#

De eerste stap is om verbinding te maken met de database.

Als je Basthon gebruikt. Laad het notebook tegelijk met de database (door het gebruik van de juiste URL). Je kunt de database ook later laden via het “File” menu. Zorg ervoor dat de database gekoppeld is vóór het uitvoeren van de SQL-opdrachten.

Als je Jupyter Notebook gebruikt. In dat geval heb je onderstaande opdracht nodig.

%LOAD data/top2000.db

Samenvatten#

De eenvoudigste samenvatting van reeks rijen is het aantal. In dit geval bestaat de resultaat-kolom uit de functie voor het tellen van de rijen in de tabel die aan de WHERE`-voorwaarde voldoen.

SELECT count(titel)
FROM top2000;

SELECT count(titel)
FROM top2000
WHERE top = 2018;

Opmerking In dit geval tellen we het aantal malen dat een rij voorkomt met een niet-leeg titel veld. In principe kun je hiervoor elk veld gebruiken dat in elke rij een waarde heeft. Je kunt ook count(*) gebruiken: dan weet je zeker dat je alle rijen telt.

Samenvattingsfuncties#

SQL heeft de volgende functies voor het samenvatten van rijen: COUNT (aantal); SUM (som); MIN (minimum); MAX (maximum); AVG (gemiddelde).

Wat is het vroegste publicatie-jaar van een nummer in de top2000?

SELECT min(jaar)
FROM top2000;

Wat is het vroegste publicatie-jaar van een nummer van Queen in de top2000?

SELECT min(jaar)
FROM top2000
WHERE artiest = "Queen";

(variaties op bovenstaande? nog een ander voorbeeld?)

Groeperen#

Zoals gezegd wil je niet altijd de hele tabel in één waarde samenvatten, maar verschillende groepen in de tabel. Bijvoorbeeld: wat is het vroegste publicatie jaar per top2000?

Opmerking: als je in de vraag de formulering per X tegenkomt, dan is er meestal sprake van groepering. Je wilt in het antwoord dan niet alleen de samenvatting, maar ook X als kolom.

SELECT top, min(jaar), titel
FROM top2000
GROUP BY top;

Wat is de laagste notering van Queen per top, en met welk nummer?

SELECT top, max(positie), titel
FROM top2000
WHERE artiest = "Queen"
GROUP BY top;

Opmerking. Alleen in het geval van min en max heeft het zin om andere velden in het resultaat op te nemen. (Ga na waarom.)

Uitzoeken… in principe kunnen we meerdere rijen zijn met de minimum of maximum waarde. Welke wordt dan gekozen als je de andere kolommen wilt weergeven?

Wat is het aantal top-noteringen per nummer van Queen?

SELECT titel, count(titel)
FROM top2000
WHERE artiest = "Queen"
GROUP BY titel;

Filteren van de groepering#

Je kunt de resultaten van een groepering filteren met een conditie met behulp van HAVING. Alleen die groepen die aan de conditie voldoen blijven over.

Bijvoorbeeld: wat zijn de nummers van Queen met meer dan 3 topnoteringen per nummer (titel)?

SELECT titel, count(titel)
FROM top2000
WHERE artiest = 'Queen'
GROUP BY titel
HAVING count(titel) > 3;

(variatie op de bovenstaande? - minder dan 3; )

(nog een opdracht met HAVING)

Opmerking De conditie van HAVING formuleer je met de kolommen van het resultaat van de samenvatting, dus de kolommen die achter de SELECT staan - niet met de kolommen van de oorspronkelijke tabel(len) achter FROM.

Gesorteerd resultaat#

Je kunt de groepen in het resultaat sorteren, met ORDER BY. Dit ORDER BY-deel komt na de groepering (GROUP BY): het heeft immers betrekking op het resultaat van de groepering.

SELECT titel, count(titel)
FROM top2000
WHERE artiest = 'Queen'
GROUP BY titel
HAVING count(titel) < 3
ORDER BY count(titel);

Groeperen en samenvatten - voorbeelden#

SELECT titel, artiest, jaar, count(titel) aantal
FROM top2000
WHERE jaar < 1957
GROUP BY titel
ORDER BY aantal DESC;

(Als je het meest populaire nummer wilt bepalen, kun je deze tabel beperken tot 1. Daar heb je geen “max” functie voor nodig.)

Andere functies voor het samenvatten van waarden zijn: sum, avg, min, max, prod.

Wat is het oudste nummer in de top2000? (Hiervoor is het voldoende om de nummers op jaar te ordenen, en de eerste te nemen - LIMIT 1.)

SELECT titel, artiest, jaar
FROM top2000
ORDER BY jaar ASC
LIMIT 5;

Hoe vaak komt dit oudste nummer in de top2000 voor?

(Kunnen we dit nog op een andere manier formuleren, zonder de gegevens van het vorige resultaat in te vullen?)

SELECT *
FROM top2000
WHERE titel = "We'll Meet Again";