Veeg om het menu te tonen

Laten we kort de belangrijkste typen vensterfuncties verkennen die in SQL worden gebruikt.

Aggregatiefuncties

Dit zijn de standaard aggregatiefuncties (AVG, SUM, MAX, MIN, COUNT) die in een venstercontext worden toegepast. We hebben dit type vensterfunctie al gebruikt in het vorige hoofdstuk.

Rangschikkingsfuncties

Rangschikkingsfuncties in SQL zijn een type vensterfunctie waarmee je een rang aan elke rij binnen een partitie van een resultaatset kunt toekennen. Deze functies zijn bijzonder nuttig voor het uitvoeren van geordende berekeningen en analyses.

RANK(): kent een unieke rang toe aan elke verschillende rij binnen de partitie op basis van de ORDER BY-clausule. Rijen met gelijke waarden krijgen dezelfde rang, waarbij er gaten in de rangschikking ontstaan;
DENSE_RANK(): vergelijkbaar met RANK(), maar zonder gaten in de rangschikkingsvolgorde;
NTILE(n): verdeelt de rijen in een geordende partitie in n groepen en kent een groepsnummer toe aan elke rij.

Voorbeeld

We rangschikken de verkopen op basis van het Amount voor elke ProductID in oplopende volgorde door gebruik te maken van de functie DENSE_RANK():


              12345678
            
SELECT 
    sales_id,
    product_id,
    sales_date,
    amount,
    DENSE_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS dense_rank_amount
FROM 
    Sales;

De resultaat-tabel bevat alle informatie uit de hoofdtafel en een extra kolom die de rang van elke verkoop voor het betreffende product weergeeft.

Waardevergelijkingsfuncties

Waardevergelijkings-vensterfuncties in SQL worden gebruikt om waarden in de huidige rij te vergelijken met waarden in andere rijen binnen dezelfde partitie.
Deze functies zijn vooral nuttig voor taken waarbij trends worden geanalyseerd, berekeningen worden uitgevoerd op basis van aangrenzende rijen, of specifieke rijwaarden binnen een gedefinieerd venster worden geraadpleegd. Er zijn verschillende waardevergelijkingsfuncties in SQL:

LAG(): haalt de waarde op uit een vorige rij in de resultaatset zonder dat een self-join nodig is;
LEAD(): haalt de waarde op uit een volgende rij in de resultaatset zonder dat een self-join nodig is;
FIRST_VALUE(): retourneert de waarde van de eerste rij in het vensterframe;
LAST_VALUE(): retourneert de waarde van de laatste rij in het vensterframe.

Voorbeeld

We gebruiken de waardevergelijkings-vensterfunctie LAG() om de verandering in verkoopbedrag ten opzichte van de vorige verkoop voor elk product te berekenen:


              1234567891011
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS amount_change
FROM
    Sales
ORDER BY
    product_id, sales_date;

Querybeschrijving

Belangrijkste onderdelen van de query:

SELECT-clausule: Geeft de kolommen aan die uit de tabel worden opgehaald.
- Kolomnamen: Bevat kolommen zoals SalesID, ProductID, SalesDate en Amount om de relevante gegevens voor elke verkoop weer te geven.
- Vensterfunctie: Maakt gebruik van de LAG()-functie om de waarde van de vorige rij voor een specifieke kolom binnen een partitie op te halen. Een extra berekende kolom toont het verschil tussen de huidige en vorige waarden.

Syntax van de vensterfunctie:

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
- column_name: De kolom waarvan de vorige waarde wordt opgehaald.
- offset: Het aantal rijen terug vanaf de huidige rij om de waarde op te halen (standaard is 1).
- default_value: De waarde die wordt geretourneerd als de offset buiten de grenzen van de partitie valt (optioneel).
- PARTITION BY partition_column: Verdeelt het resultaat in partities waarop de vensterfunctie wordt toegepast, zodat de functie binnen elke partitie afzonderlijk werkt.
- ORDER BY order_column: Geeft de volgorde van de rijen binnen elke partitie aan, zodat de vensterfunctie de rijen in een logische volgorde verwerkt.

Hierdoor kunnen we eenvoudig informatie over verkoopverschillen voor elk specifiek product ophalen zonder subqueries of opgeslagen procedures te gebruiken.
We kunnen ook verschillen voor alle verkopen berekenen zonder te partitioneren met de volgende query:


              123456789
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (ORDER BY sales_date) AS amount_change
FROM
    Sales;

Je ziet dat we de PARTITION BY clausule niet hebben opgenomen in het OVER-blok. Dit betekent dat we niet alleen de vorige waarden voor een specifiek product willen ophalen, maar voor alle verkopen in de tabel.

Was alles duidelijk?

Bedankt voor je feedback!

Sectie 3. Hoofdstuk 3

Vraag AI

Vraag wat u wilt of probeer een van de voorgestelde vragen om onze chat te starten.

Typen Vensterfuncties

Laten we kort de belangrijkste typen vensterfuncties verkennen die in SQL worden gebruikt.

Aggregatiefuncties

Dit zijn de standaard aggregatiefuncties (AVG, SUM, MAX, MIN, COUNT) die in een venstercontext worden toegepast. We hebben dit type vensterfunctie al gebruikt in het vorige hoofdstuk.

Rangschikkingsfuncties

RANK(): kent een unieke rang toe aan elke verschillende rij binnen de partitie op basis van de ORDER BY-clausule. Rijen met gelijke waarden krijgen dezelfde rang, waarbij er gaten in de rangschikking ontstaan;
DENSE_RANK(): vergelijkbaar met RANK(), maar zonder gaten in de rangschikkingsvolgorde;
NTILE(n): verdeelt de rijen in een geordende partitie in n groepen en kent een groepsnummer toe aan elke rij.

Voorbeeld

We rangschikken de verkopen op basis van het Amount voor elke ProductID in oplopende volgorde door gebruik te maken van de functie DENSE_RANK():


              12345678
            
SELECT 
    sales_id,
    product_id,
    sales_date,
    amount,
    DENSE_RANK() OVER (PARTITION BY product_id ORDER BY amount DESC) AS dense_rank_amount
FROM 
    Sales;

De resultaat-tabel bevat alle informatie uit de hoofdtafel en een extra kolom die de rang van elke verkoop voor het betreffende product weergeeft.

Waardevergelijkingsfuncties

LAG(): haalt de waarde op uit een vorige rij in de resultaatset zonder dat een self-join nodig is;
LEAD(): haalt de waarde op uit een volgende rij in de resultaatset zonder dat een self-join nodig is;
FIRST_VALUE(): retourneert de waarde van de eerste rij in het vensterframe;
LAST_VALUE(): retourneert de waarde van de laatste rij in het vensterframe.

Voorbeeld

We gebruiken de waardevergelijkings-vensterfunctie LAG() om de verandering in verkoopbedrag ten opzichte van de vorige verkoop voor elk product te berekenen:


              1234567891011
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (PARTITION BY product_id ORDER BY sales_date) AS amount_change
FROM
    Sales
ORDER BY
    product_id, sales_date;

Querybeschrijving

Belangrijkste onderdelen van de query:

SELECT-clausule: Geeft de kolommen aan die uit de tabel worden opgehaald.
- Kolomnamen: Bevat kolommen zoals SalesID, ProductID, SalesDate en Amount om de relevante gegevens voor elke verkoop weer te geven.
- Vensterfunctie: Maakt gebruik van de LAG()-functie om de waarde van de vorige rij voor een specifieke kolom binnen een partitie op te halen. Een extra berekende kolom toont het verschil tussen de huidige en vorige waarden.

Syntax van de vensterfunctie:

LAG(column_name, offset, default_value) OVER (PARTITION BY partition_column ORDER BY order_column)
- column_name: De kolom waarvan de vorige waarde wordt opgehaald.
- offset: Het aantal rijen terug vanaf de huidige rij om de waarde op te halen (standaard is 1).
- default_value: De waarde die wordt geretourneerd als de offset buiten de grenzen van de partitie valt (optioneel).
- PARTITION BY partition_column: Verdeelt het resultaat in partities waarop de vensterfunctie wordt toegepast, zodat de functie binnen elke partitie afzonderlijk werkt.
- ORDER BY order_column: Geeft de volgorde van de rijen binnen elke partitie aan, zodat de vensterfunctie de rijen in een logische volgorde verwerkt.


              123456789
            
SELECT
    sales_id,
    product_id,
    sales_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sales_date) AS previous_amount,
    amount - LAG(amount, 1) OVER (ORDER BY sales_date) AS amount_change
FROM
    Sales;

Was alles duidelijk?

Bedankt voor je feedback!

Sectie 3. Hoofdstuk 3