Geavanceerde T-SQL window functions: een uitgebreide gids met voorbeelden

Titel: Geavanceerde T-SQL window functions: een uitgebreide gids met voorbeelden

Inleiding

Transact-SQL (T-SQL), de eigen uitbreiding van Microsoft op de SQL-taal, biedt een flinke set tools om databases te beheren op SQL Server en Azure SQL Database. Window functions krijgen daarbij veel aandacht, omdat je er ingewikkelde berekeningen mee maakt over sets rijen die bij de huidige rij horen. In dit artikel bekijk ik een paar geavanceerde T-SQL window functions en laat ik met praktische voorbeelden zien hoe je ze gebruikt.

Wat zijn window functions?

Window functions rekenen over een groep, of “window”, rijen die iets te maken hebben met de rij die nu verwerkt wordt. Met deze functies los je ingewikkelde queries op waarvoor je anders self-joins, cursors of andere constructies nodig hebt. De belangrijkste soorten window functions zijn ranking-, aggregatie- en distributiefuncties.

Geavanceerde window functions en hoe je ze gebruikt

1. LEAD en LAG:

Met deze functies lees je data uit een andere rij zonder dat je de tabel aan zichzelf hoeft te joinen. LEAD geeft de waarde uit een volgende rij, LAG haalt de waarde uit een voorgaande rij.

Voorbeeld:

1
SELECT
2
  OrderID,
3
  OrderDate,
4
  LAG(OrderDate, 1) OVER (ORDER BY OrderDate) AS PreviousOrderDate
5
FROM Orders;

Dit voorbeeld toont bij elke order in de tabel Orders de datum van de vorige order.

2. FIRST_VALUE en LAST_VALUE:

FIRST_VALUE en LAST_VALUE geven de eerste en de laatste waarde van het opgegeven window frame.

Voorbeeld:

1
SELECT
2
  OrderID,
3
  OrderDate,
4
  FIRST_VALUE(OrderDate) OVER (ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS FirstOrderDate
5
FROM Orders;

Dit voorbeeld toont bij elke order in de tabel Orders de datum van de eerste order in het window.

3. CUME_DIST:

CUME_DIST berekent de cumulatieve verdeling van een waarde in een groep waarden. Het is het aantal rijen met een waarde kleiner dan of gelijk aan de waarde van de huidige rij, gedeeld door het totale aantal rijen.

Voorbeeld:

1
SELECT
2
  OrderID,
3
  OrderDate,
4
  CUME_DIST() OVER (ORDER BY OrderDate) AS OrderDatePercentile
5
FROM Orders;

Dit voorbeeld toont het percentiel van elke orderdatum in de tabel Orders.

4. PERCENT_RANK:

PERCENT_RANK berekent de relatieve rang van een rij binnen een groep rijen.

Voorbeeld:

1
SELECT
2
  OrderID,
3
  OrderDate,
4
  PERCENT_RANK() OVER (ORDER BY OrderDate) AS OrderDatePercentRank
5
FROM Orders;

Dit voorbeeld toont de procentuele rang van elke orderdatum in de tabel Orders.

5. NTILE:

De functie NTILE verdeelt de rijen van een gesorteerde partitie over een opgegeven aantal groepen.

Voorbeeld:

1
SELECT
2
  OrderID,
3
  OrderDate,
4
  NTILE(4) OVER (ORDER BY OrderDate) AS Quartile
5
FROM Orders;

Dit voorbeeld verdeelt de orders in de tabel Orders in kwartielen op basis van de orderdatum.

Conclusie

Geavanceerde window functions in T-SQL zijn krachtige tools voor data-analyse en -transformatie. Als je deze functies begrijpt en gebruikt, schrijf je als developer of data-analist efficiëntere en schonere code, zonder ingewikkelde joins of subqueries. Met wat begrip en oefening pak je met deze window functions zelfs de lastigste datamanipulatie makkelijk aan.