Cursus
Gegevens manipuleren in SQL
4 uur
337K
Rapporten bouwen op basis van een dataset is een essentiële vaardigheid als je met data werkt. Uiteindelijk wil je namelijk cruciale zakelijke vragen kunnen beantwoorden met de data die je tot je beschikking hebt. Vaak worden deze antwoorden gepresenteerd in de vorm van grafieken. Maar soms zijn rapporten in tabelvorm ook nodig. In beide gevallen kan het nodig zijn de data samen te vatten met eenvoudige berekeningen. In SQL kun je data samenvatten/aggregëren met aggregatiefuncties. Met deze functies kun je vragen beantwoorden als:
- Wat is de maximale waarde voor de
some_column_from_the_table? of - Wat zijn de minimale waarden van
some_column_from_the_tablemet betrekking totanother_column_from_the_table?
en nog veel meer.
Laten we beginnen met wat data-aggregatie.
Let op: Om deze tutorial te kunnen volgen, moet je weten hoe je basisqueries schrijft in PostgreSQL (dat je als RDBMS gaat gebruiken). Deze tutorial is een goede opfrisser.
De database instellen
Laten we eerst een PostgreSQL-database opzetten en deze backup terugzetten, die de tabel bevat die je in deze tutorial gaat gebruiken. Als je wilt leren hoe je een databasebackup in PostgreSQL terugzet, kun je het eerste deel van deze tutorial volgen.
Als het gelukt is om de backup te herstellen, zou je een tabel met de naam international_debt in de database moeten zien (je moet wel eerst een database aanmaken als je die nog niet hebt). Laten we snel naar de eerste paar rijen van de tabel kijken (een simpele select-query helpt je daarbij):
De tabel bevat informatie over de schuldstatistieken van verschillende landen wereldwijd voor dit jaar in verschillende categorieën (zie de kolommen indicator_name en indicator_code). De kolom debt toont het bedrag aan schuld (in USD) dat een bepaald land heeft in een specifieke categorie. De data behoort tot het domein economie en wordt vaak gebruikt om de economische situatie van verschillende landen te analyseren. De data is afkomstig van de Wereldbank.
Nu je de database succesvol hebt opgezet, laten we wat eenvoudige queries uitvoeren om meer over de data te weten te komen. Open de tool pgAdmin en ga aan de slag.
Eenvoudige informatie telt
Op de bovenstaande afbeelding zie je dat er veel dubbele vermeldingen zijn voor één land, maar dan voor verschillende categorieën. Een vraag die hier snel uit voortkomt, is:
Welke verschillende landen komen er in de tabel voor?
Als je simpelweg een select-query uitvoert met de kolom country_name, krijg je niet het juiste antwoord op deze vraag, omdat het resultaat duplicaten zal bevatten. Gebruik het keyword DISTINCT om dit te verhelpen.
select distinct country_name from international_debt;
En dit zou zoiets moeten opleveren:
Nu heb je een redelijk antwoord op de bovenstaande vraag. Nog één vraag voordat je doorgaat met aggregatiefuncties:
Hoeveel verschillende soorten schuldindicatoren staan er in de tabel?
De query om dit te beantwoorden lijkt op die hierboven. Je hoeft alleen de kolomnaam te veranderen. Neem het als oefening. Het resultaat zal ongeveer als volgt zijn:
Aggregatiefuncties
Laten we beginnen met een query met een aggregatiefunctie en vandaaruit verder gaan. Onderweg leer je meer over de syntaxis en de constructies die je moet volgen bij het toepassen van aggregatiefuncties in SQL.
select sum(debt) from international_debt;
En het resultaat:
Met de aggregatiefunctie SUM() kun je de som berekenen over een kolom (die numerieke waarden bevat). Met de bovenstaande query kom je te weten wat de totale schuld is die openstaat voor de landen in de tabel.
Let op dat SUM() geen NULL-waarden meeneemt bij het berekenen van de som. Laten we nu het antwoord vinden op de vraag:
Wat is het maximale schuldbedrag?
Hier komt de aggregatiefunctie MAX() goed van pas:
select max(debt) from international_debt;
En het antwoord is:
Net als de functie SUM() houdt MAX() geen rekening met NULL-waarden bij de berekening. Er is ook een vergelijkbare functie MIN(). Laat me via de sectie Comments de minimumwaarde van de kolom debt weten? Het is nu een goed idee om te kijken of er een ongeldige invoer in de kolom debt staat om te verzekeren dat de resultaten tot nu toe kloppen.
Let op dat je deze functies in kleine letters gebruikt, zoals hierboven getoond.
Als je deze query uitvoert: select * from international_debt where debt is null;, zou je een leeg resultaat moeten krijgen. Laten we nu het totaal aantal verschillende landen in de tabel bepalen.
select count(distinct(country_name)) from international_debt;
En je ziet dat er in totaal 124 verschillende landen in de tabel staan. Let goed op de opeenvolging van functies die je in de bovenstaande query toepaste. Ja, dat mag hier, dus meer dan één aggregatiefunctie op een logische manier combineren.
Stel nu dat je de gemiddelde waarde van de kolom debt wilt zien. De functie is AVG():
select avg(debt) from international_debt;
Je ziet de waarde 1306633214.966397971 (USD). Het is verstandig om deze resultaten met geschikte kolomnamen te presenteren. Uit de bovenstaande resultaten zie je dat PostgreSQL de kolomnaam wijzigt naar de naam van de aggregatiefunctie (die in de query is opgenomen) bij het teruggeven van het resultaat. Het is dus een goed idee om deze kolommen een passende alias te geven. Dat kan zo:
select avg(debt) as Average_Debt_By_A_Country from international_debt;
Het resultaat is veel beter te interpreteren:
Laten we dit nu wat complexer maken. Om vragen te kunnen beantwoorden als What are the minimum values of some_column_from_the_table with respect to another_column_from_the_table?, moet je een aggregatiefunctie koppelen aan de GROUP BY-clausule. Laten we kijken hoe.
Aggregatiefuncties + GROUP BY + meer
Stel dat je een rapport wilt maken waarin je de country_name en de som van hun schulden laat zien. Een voorbeeld:
Rapporten zoals dit worden in de echte wereld vaak gebruikt. Wat zou de query zijn om zo'n rapport te krijgen? Je moet de functie SUM() gebruiken op debt. En je moet ook de country_name tonen met de som van de schulden. Voer de volgende query uit:
select country_name, sum(debt) from international_debt;
Levert dit niet de volgende fout op?
ERROR: column "international_debt.country_name" must appear in the GROUP BY clause or be used in an aggregate function
Laten we nu begrijpen wat dit betekent. Wanneer je een aggregatiefunctie gebruikt (zoals SUM()) met een niet-geaggregeerde kolom zoals country_name, moet je de niet-geaggregeerde kolom opnemen in een GROUP BY-clausule. De juiste query is dus:
select country_name, sum(debt) as total_debt from international_debt group by country_name;
En het resultaat is precies goed:
Let op het gebruik van aliassen in de query.
Stel nu dat je dit rapport wilt sorteren op total_debt in aflopende volgorde. Weet je nog, de ORDER BY-clausule? Ja, je kunt aggregatiefuncties ook combineren met ORDER BY:
select country_name, sum(debt) as total_debt from international_debt
group by country_name order by total_debt desc;
Het resultaat zou nu omgekeerd gesorteerd moeten zijn:
Let op de kolom die je in de ORDER BY-clausule gebruikte.
Dan nog een belangrijke vraag:
Wat is het hoogste schuldbedrag per verschillende categorie (in omgekeerd gesorteerde volgorde)?
Hier moet je de functie MAX() gebruiken. De query om deze vraag te beantwoorden zou nu niet moeilijk moeten zijn om te schrijven.
select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc;
En je krijgt een strak rapport:
Je kunt ook het aantal rijen in dit soort rapporten beperken. Stel dat je alleen de top vijf vermeldingen in het bovenstaande rapport wilt opnemen. Dat kan met de LIMIT-clausule.
select indicator_code, max(debt) as maximum_debt from international_debt
group by indicator_code order by maximum_debt desc
limit 5;
Tijd voor het laatste rapport van deze tutorial. Je moet de namen van de landen opnemen in het bovenstaande rapport. Hoe doe je dat? Met de volgende query lukt dat:
select country_name, indicator_code, max(debt) as maximum_debt from international_debt
group by country_name, indicator_code order by maximum_debt desc;
Nog een goed rapport:
In de bovenstaande query heb je de kolom country_name toegevoegd na de SELECT-clausule en ook na GROUP BY. Je kunt dit format uitbreiden tot elk gewenst aantal.
De volgorde van GROUP BY, ORDER BY en LIMIT is erg belangrijk bij het genereren van dit soort rapporten. Als je per ongeluk de volgorde verandert, krijg je fouten. Kijk zelf maar:
select country_name, sum(debt) as total_debt from international_debt
order by total_debt desc group by country_name;
En je krijgt:
ERROR: syntax error at or near "group"
LINE 1: ... from international_debt order by total_debt desc group by c...In de bovenstaande query plaatste je de ORDER BY-clausule vóór GROUP BY, wat niet is toegestaan. Sterker nog, het is ook niet van toepassing wanneer je geen aggregatiefuncties gebruikt. De juiste volgorde is: GROUP BY -> ORDER BY -> LIMIT. Onthoud dit altijd.
Verder aan de slag
Gefeliciteerd! Je hebt het einde van deze tutorial gehaald. In deze tutorial heb je kennisgemaakt met de verschillende aggregatiefuncties in PostgreSQL en hoe je ze gebruikt om nuttige rapporten te genereren. Dit zijn inderdaad cruciale vaardigheden voor een data scientist. Wil je je SQL-vaardigheden op een systematische manier naar een hoger niveau tillen? Volg dan de volgende DataCamp-cursussen:
Onderwerpen
