Complexe berekeningen bouwen

Rapporteren in SQL

Tyler Pernes

Learning & Development Consultant

Aanpakken

Windowfuncties
Gelaagde berekeningen

Windowfuncties

Verwijst naar andere rijen in de tabel.

Windowfuncties

Verwijst naar andere rijen in de tabel.

Windowfuncties

Verwijst naar andere rijen in de tabel.

Syntaxis van windowfuncties

SUM(value) OVER (PARTITION BY field ORDER BY field)

Syntaxis van windowfuncties

SUM(value) OVER (PARTITION BY field ORDER BY field)

Syntaxis van windowfuncties

SUM(value) OVER (PARTITION BY field ORDER BY field)

PARTITION BY = bereik van de berekening
ORDER BY = volgorde van rijen tijdens de berekening

Voorbeelden van windowfuncties

Totaal bronzen medailles

SELECT 
    country_id, 
    athlete_id, 
    SUM(bronze) OVER () AS total_bronze
FROM summer_games;

+-------------+-------------+---------------+
| country_id  | athlete_id  | total_bronze  |
|-------------|-------------|---------------|
| 11          | 77505       | 141           |
| 11          | 11673       | 141           |
| 14          | 85554       | 141           |
| 14          | 76433       | 141           |
+-------------+-------------+---------------+

Voorbeelden van windowfuncties

Bronzen medailles per land

SELECT 
    country_id, 
    athlete_id, 
    SUM(bronze) OVER (PARTITION BY country_id) AS total_bronze
FROM summer_games

+-------------+-------------+---------------+
| country_id  | athlete_id  | total_bronze  |
|-------------|-------------|---------------|
| 11          | 77505       | 12            |
| 11          | 11673       | 12            |
| 14          | 85554       | 5             |
| 14          | 76433       | 5             |
+-------------+-------------+---------------+

Typen windowfuncties

SUM()
AVG()
MIN()
MAX()

Typen windowfuncties

LAG() en LEAD()

Typen windowfuncties

LAG() en LEAD()

Typen windowfuncties

ROW_NUMBER() en RANK()

Windowfunctie op een aggregatie

original_table
+----------+-----------+---------+ 
| team_id  | player_id | points  |
|----------|-----------|---------|    
| 1        | 4123      | 3       |
| 1        | 5231      | 6       |
| 2        | 8271      | 5       |
+----------+-----------+---------+

desired_report
+----------+-------------+---------------+ 
| team_id  | team_points | league_points |
|----------|-------------|---------------|    
| 1        | 9           | 43            |
| 2        | 12          | 43            |
| 3        | 22          | 43            |
+----------+-------------+---------------+

Windowfunctie op een aggregatie

Definitieve query

SELECT
    team_id,
    SUM(points) AS team_points,
    SUM(SUM(points)) OVER () AS league_points
FROM original_table
GROUP BY team_id;

Windowfunctie op een aggregatie

SELECT
    team_id,
    SUM(points) AS team_points,
    SUM(points) OVER () AS league_points
FROM original_table
GROUP BY team_id;

ERROR: points must be an aggregation or appear in a GROUP BY statement.

Gelaagde berekeningen

Aggregeer op een bestaande aggregatie
Maakt gebruik van een subquery

Voorbeeld van gelaagde berekeningen

Stap 1: Totaal bronzen medailles per land

SELECT country_id, SUM(bronze) as bronze_medals
FROM summer_games
GROUP BY country_id;

Stap 2: Zet om naar subquery en neem de max

SELECT MAX(bronze_medals)
FROM
  (SELECT country_id, SUM(bronze) as bronze_medals
  FROM summer_games
  GROUP BY country_id) AS subquery;

Complexe berekeningen plannen

Sortering voor windowfunctie?
Twee aggregaties met een gelaagde berekening?

Laten we oefenen!

Rapporteren in SQL