Bygga komplexa beräkningar

Rapportering i SQL

Tyler Pernes

Learning & Development Consultant

Metoder

Fönsterfunktioner
Skiktade beräkningar

Fönsterfunktioner

Refererar till andra rader i tabellen.

Fönsterfunktioner

Refererar till andra rader i tabellen.

Fönsterfunktioner

Refererar till andra rader i tabellen.

Syntax för fönsterfunktioner

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

Syntax för fönsterfunktioner

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

Syntax för fönsterfunktioner

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

PARTITION BY = beräkningens intervall
ORDER BY = radernas ordning vid beräkning

Exempel på fönsterfunktioner

Totalt antal bronsmedaljer

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           |
+-------------+-------------+---------------+

Exempel på fönsterfunktioner

Bronsmedaljer 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             |
+-------------+-------------+---------------+

Typer av fönsterfunktioner

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

Typer av fönsterfunktioner

LAG() och LEAD()

Typer av fönsterfunktioner

LAG() och LEAD()

Typer av fönsterfunktioner

ROW_NUMBER() och RANK()

Fönsterfunktion på en aggregering

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            |
+----------+-------------+---------------+

Fönsterfunktion på en aggregering

Slutlig fråga

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

Fönsterfunktion på en aggregering

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.

Skiktade beräkningar

Aggregera en befintlig aggregering
Använder en underfråga

Exempel på skiktad beräkning

Steg 1: Totalt antal bronsmedaljer per land

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

Steg 2: Gör om till underfråga och ta max

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

Planera komplexa beräkningar

Ordning för fönsterfunktionen?
Två aggregeringar med en skiktad beräkning?

Nu kör vi en övning!

Rapportering i SQL