Creare calcoli complessi

Reportistica in SQL

Tyler Pernes

Learning & Development Consultant

Approcci

Funzioni finestra
Calcoli a strati

Funzioni finestra

Fa riferimento ad altre righe della tabella.

Funzioni finestra

Fa riferimento ad altre righe della tabella.

Funzioni finestra

Fa riferimento ad altre righe della tabella.

Sintassi delle funzioni finestra

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

Sintassi delle funzioni finestra

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

Sintassi delle funzioni finestra

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

PARTITION BY = ambito del calcolo
ORDER BY = ordine delle righe durante il calcolo

Esempi di funzioni finestra

Totale medaglie di bronzo

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

Esempi di funzioni finestra

Bronzi per paese

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

Tipi di funzioni finestra

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

Tipi di funzioni finestra

LAG() e LEAD()

Tipi di funzioni finestra

LAG() e LEAD()

Tipi di funzioni finestra

ROW_NUMBER() e RANK()

Funzione finestra su un'aggregazione

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

Funzione finestra su un'aggregazione

Query finale

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

Funzione finestra su un'aggregazione

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.

Calcoli a strati

Aggregare un'aggregazione esistente
Usare una sottoquery

Esempio di calcoli a strati

Passo 1: Bronzi totali per paese

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

Passo 2: Trasforma in sottoquery e prendi il max

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

Progettare calcoli complessi

Ordinamento per la funzione finestra?
Due aggregazioni con un calcolo a strati?

Esercitiamoci!

Reportistica in SQL