SQL-commando's voor data scientists

Als data scientist of data-analist staat data vanzelfsprekend centraal in je werk. Er zijn veel bronnen waaruit je je data kunt halen. Vaak staat de data in een SQL-database; in dat geval is inzicht in SQL-querycommando's essentieel om je rol goed te kunnen vervullen. In dit artikel maak je kennis met een aantal van de basale commando's, tot aan meer geavanceerde bewerkingen die je goed van pas komen als data-analist of data scientist.

Om de commando's hierboven te begrijpen, gebruiken we de open-source DVD Rental-database om te laten zien hoe je deze commando's kunt toepassen. Je vindt het entity-relationshipdiagram (ERD) hier.

Voor dit artikel hebben we de database lokaal opgezet en alle queries uitgevoerd met pgAdmin4, dus we gebruiken PostgreSQL. Veel, zo niet alle, commando's zouden ook op andere SQL-servers moeten werken. De installatie van PostgreSQL en het inrichten van de database zelf vallen buiten de scope van dit artikel; we raden je aan andere bronnen te bekijken, zoals DataCamps Beginner's Guide to PostgreSQL voor advies over deze processen.

Eenvoudige data-opvraging

SELECT FROM

Het simpelste voorbeeld van opvraging is alle inhoud uit een specifieke tabel in de database ophalen. Als we alle filmcategorieën in de tabel 'category' willen weten, kun je dit commando invoeren:

SELECT *
FROM category

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

Hier hebben we de tabelnaam achter FROM gespecificeerd. Omdat we alle inhoud in de tabel willen, hebben we na SELECT een * gebruikt om aan te geven dat we alles willen.

DISTINCT

Er zijn gevallen waarin we alleen unieke waarden willen weten. Wat als we bijvoorbeeld alle MPAA-ratings uit de tabel film willen weten? Met DISTINCT kies je alleen unieke waarden:

SELECT DISTINCT(rating)
FROM film

Wat het volgende oplevert:

Data-opvraging met eenvoudige voorwaarden

WHERE

Er zijn ook situaties waarin we alleen specifieke records willen ophalen die aan bepaalde voorwaarden voldoen. Met de WHERE-clausule geef je voorwaarden op die moeten worden voldaan voordat de data wordt teruggegeven. In wezen filteren we records op basis van de voorwaarde. Wat als we bijvoorbeeld alleen films willen weten die de rating PG-13 hebben?

SELECT title
FROM film
WHERE rating = 'PG-13'

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

Daarnaast kunnen we de lijst bepalen van films met een rental rate van 4.5 of hoger:

SELECT title, rental_rate
FROM film
WHERE rental_rate >= 4.5

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

ORDER BY

Het ordenen van het resultaat helpt vaak om de data beter te begrijpen. Hoe zit het met dezelfde lijst van PG-13-films als hierboven, maar dan geordend op filmlengte van lang naar kort, inclusief de bijbehorende lengte? Met ORDER BY is dat eenvoudig te doen:

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

LIMIT

Soms zijn we bovendien alleen geïnteresseerd in een beperkt aantal records. Hier zijn we eigenlijk alleen geïnteresseerd in de top 10 langste PG-13-films, dus we kunnen de LIMIT-clausule gebruiken.

SELECT title, length
FROM film
WHERE rating = 'PG-13'
ORDER BY length DESC
LIMIT 10

Wat het volgende oplevert:

Aggregaties

GROUP BY & COUNT( )

Aggregaties worden vaak gebruikt om een samenvatting van de dataset te krijgen om inzichten te verwerven. Aggregaties worden vaak gebruikt met de clausule GROUP BY. Als we bijvoorbeeld willen weten hoeveel verhuurtransacties elke klant tot nu toe heeft gedaan, kunnen we het aantal verhuurtransacties tellen en de resultaten sorteren met de meeste bovenaan.

SELECT customer_id, COUNT(rental_id)
FROM rental
GROUP BY customer_id
ORDER BY COUNT(rental_id) DESC

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

SUM( )

En hoeveel geld heeft elke klant uitgegeven? Dat kunnen we doen met de aggregatiefunctie SUM. We willen hier de grootste betaler bovenaan.

SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

AVG( ) & HAVING

We kunnen extra voorwaarden toevoegen met HAVING nadat je een gegroepeerde aggregatie hebt berekend. We willen bijvoorbeeld weten welke MPAA-ratingcategorieën films hebben die gemiddeld langer zijn dan 115 minuten.

SELECT rating, AVG(length)
FROM film
GROUP BY rating
HAVING AVG(length) > 115

Wat het volgende oplevert:

MIN( ) & alias

Groeperen is niet altijd nodig bij een aggregatie. Als je de kortste filmlengte wilt weten, kunnen we simpelweg de functie MIN gebruiken. We willen ook dat de kolom 'shortest_movie_length' heet; dat kan door een alias toe te wijzen met 'AS'.

SELECT MIN(length) AS shortest_movie_length
FROM film

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

Je kunt ook een alias gebruiken voor tabelnamen. Dit is handig wanneer je met lange tabelnamen werkt, vooral bij het joinen van tabellen (zie hieronder). Let ook op welk soort alias je gebruikt, want dat kan de leesbaarheid van de code sterk beïnvloeden.

Er zijn andere nuttige aggregatiefuncties; je kunt er meer over leren in de Introduction to SQL-cursus.

Joins

INNER JOIN (JOIN)

Je hebt waarschijnlijk gemerkt dat je maar beperkte dingen kunt doen door naar één tabel te kijken. We willen vaak data uit meerdere tabellen bekijken. Daarvoor gebruiken we JOINs. We kunnen tabellen joinen op basis van een gemeenschappelijke sleutel. Bijvoorbeeld, als we films zoeken die niet Engelstalig zijn:

SELECT film.title, language.name
FROM film
JOIN language
    ON film.language_id = language.language_id
WHERE language.name != 'English'

(Let op: in deze database zijn er geen films in een andere taal dan Engels. Je krijgt met bovenstaande query een lege tabel.)

Je kunt ook meerdere tabellen joinen. Hieronder willen we bijvoorbeeld de categorieën en hun bijbehorende gemiddelde rating weten. We hebben de waarden ook geordend op gemiddelde rating, met de hoogste eerst.

SELECT category.name, AVG(film.rental_rate) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

Er zijn veel soorten joins: INNER JOIN, LEFT JOIN, RIGHT JOIN, OUTER JOIN, CROSS JOIN. Hier gebruikten we JOIN, wat standaard een INNER JOIN is waarbij je alleen records terugkrijgt als ze in beide tabellen aanwezig zijn. Wil je meer leren over de verschillen tussen deze joins, bekijk dan de cursus Joining Data in SQL.

Gegevenstypen veranderen

CAST( )

In het bovenstaande voorbeeld waarin we geldbedragen berekenden, heb je misschien gemerkt dat SQL de waarden alleen als nummers ziet. We kunnen deze omzetten naar geldbedragen met de functie CAST:

SELECT customer_id, CAST(SUM(amount) AS money)
FROM payment
GROUP BY customer_id
ORDER BY SUM(amount) DESC

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

Geld is niet het enige waarnaar we waarden kunnen omzetten. We kunnen nummers omzetten naar floats, tekst en zelfs naar datum en tijd.

ROUND( )

Op dezelfde manier kunnen we ook afronden. In het bovenstaande voorbeeld waarin we de gemiddelde rental rate berekenden, zitten veel decimalen in de waarden. We kunnen de functie ROUND gebruiken om af te ronden op twee decimalen:

SELECT category.name, ROUND(AVG(film.rental_rate), 2) AS average_rating
FROM film
JOIN film_category
    ON film.film_id = film_category.film_id
JOIN category
    ON film_category.category_id = category.category_id
GROUP BY category.name
ORDER BY average_rating DESC

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

Complexe voorwaarden

CASE-statement

Je kunt je voorstellen dat je door veel van de bovenstaande functies en clausules te combineren behoorlijk complexe queries kunt uitvoeren. Toch zijn er situaties waarin de basis niet volstaat. Stel dat we een kolom met categorieën op basis van filmlengte willen toevoegen. We kunnen films labelen als 'Short Film' als ze korter zijn dan 50 minuten, 'Long Film' als ze langer zijn dan 150 minuten, en 'Medium Film' voor de rest. Dat kan met een CASE-statement:

SELECT title, length,
CASE
    WHEN length < 50 THEN 'Short Film'
    WHEN length > 150 THEN 'Long Film'
    ELSE 'Medium Film'
END AS Movie_Length_Category
FROM film

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

Zoals je ziet, wordt het resultaat geretourneerd wanneer aan de opgegeven voorwaarde is voldaan. Dit lijkt op 'if-else'-statements in programmeertalen.

Subqueries

We willen nu misschien weten welke films de langste speelduur hebben, en wat hun titels zijn. Daarvoor kunnen we de hierboven geïntroduceerde WHERE-clausule gebruiken, maar dan met een complexere voorwaarde:

SELECT title, length
FROM film
WHERE length = (SELECT max(length)
                FROM film)

Wat het volgende oplevert:

Met subqueries kun je complexe queries opbouwen. In dit voorbeeld willen we de lijst met filmtitels die een hogere rental rate hebben dan de gemiddelde rental rate van alle komedies:

SELECT title, rental_rate
FROM film
WHERE rental_rate >
(SELECT AVG(film.rental_rate) AS Average_Rating
FROM film
JOIN film_category AS cat_id
    ON film.film_id=cat_id.film_id
JOIN category AS cat
    ON cat_id.category_id=cat.category_id
GROUP BY cat.name, cat_id.category_id
HAVING cat.name = 'Comedy')

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

In dit voorbeeld bepalen we eerst de gemiddelde rental rate voor komediefilms. De waarde uit deze query (tussen de haakjes hierboven) wordt vervolgens gebruikt als voorwaarde in de WHERE-clausule waarmee we de filmtitels vinden.

Common Table Expressions (CTE's)

In een complexer voorbeeld kunnen we een Common Table Expression (CTE) gebruiken om een tijdelijke tabel op te slaan waaruit we de benodigde informatie halen. In dit voorbeeld willen we het gemiddelde aantal verhuurtransacties weten dat elke medewerker per week verwerkt.

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT staff_id, AVG(rental_numbers)
FROM weekly_rentals
GROUP BY staff_id

Wat het volgende oplevert:

In het bovenstaande geval slaan we het aantal verhuurtransacties per week op in een tijdelijke tabel 'weekly_rentals', waarop we vervolgens een andere query kunnen uitvoeren om het eindresultaat te krijgen.

Windowfuncties

Windowfuncties zijn een andere set SQL-vaardigheden die belangrijk zijn en je naar een hoger niveau kunnen tillen. Hoewel windowfuncties als vaardigheden van gemiddeld tot gevorderd niveau worden beschouwd, vinden veel data-analisten en -scientists ze essentieel voor hun werk. Het is dus de moeite waard om je hierin te verdiepen.

OVER( ) & PARTITION BY Stel dat we het wekelijks aantal verhuurtransacties per medewerker willen achterhalen en het totale aantal verhuurtransacties, samen met de staff ID en het weeknummer. We kunnen de CTE uit het bovenstaande voorbeeld hergebruiken, zoals hieronder:

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER() AS total_rentals
FROM weekly_rentals

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

Met OVER in de code geef je aan dat het om een windowfunctie gaat. Hier zeggen we dat de windowfunctie (hier SUM) op elke rij wordt toegepast. We kunnen de windowfunctie ook gebruiken met PARTITION BY om de functie toe te passen op specifieke 'partities'. Stel dat we nu het totale aantal verhuurtransacties per week willen berekenen in plaats van het totale aantal (naast de andere kolommen). Ook in het onderstaande voorbeeld gebruiken we de eerder gegenereerde CTE.

WITH weekly_rentals AS(
    SELECT
        staff_id,
        DATE_PART('week', payment_date) AS week,
        COUNT(rental_id) AS rental_numbers
    FROM payment
    GROUP BY staff_id, week)

SELECT *, SUM(rental_numbers) OVER(PARTITION BY week) AS total_weekly_rentals
FROM weekly_rentals

Wat het volgende oplevert (alleen de eerste 10 rijen getoond):

De extra kolom bevat het totale wekelijkse aantal verhuurtransacties in plaats van het cumulatieve totaal.

We hebben hier slechts een paar eenvoudige voorbeelden van windowfuncties geïntroduceerd die belangrijk zijn voor data-analisten of -scientists bij veel bedrijven. We moedigen je aan meer te leren over deze commando's met behulp van de gegeven links. Je kunt ook de Intermediate SQL-cursus bekijken om je SQL-vaardigheden te leren of op te frissen. Daarnaast kun je oefenen door een open-source database te nemen en deze verschillende commando's uit te proberen terwijl je nadenkt over relevante vragen om te beantwoorden.