Calcule complexe

Raportare în SQL

Tyler Pernes

Learning & Development Consultant

Abordări

Funcții de fereastră
Calcule stratificate

Funcții de fereastră

Referențiază alte rânduri din tabel.

Funcții de fereastră

Referențiază alte rânduri din tabel.

Funcții de fereastră

Referențiază alte rânduri din tabel.

Sintaxa funcțiilor de fereastră

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

Sintaxa funcțiilor de fereastră

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

Sintaxa funcțiilor de fereastră

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

PARTITION BY = domeniul calculului
ORDER BY = ordinea rândurilor în calcul

Exemple de funcții de fereastră

Total medalii de bronz

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

Exemple de funcții de fereastră

Medalii de bronz pe țară

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

Tipuri de funcții de fereastră

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

Tipuri de funcții de fereastră

LAG() și LEAD()

Tipuri de funcții de fereastră

LAG() și LEAD()

Tipuri de funcții de fereastră

ROW_NUMBER() și RANK()

Funcție de fereastră pe o agregare

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

Funcție de fereastră pe o agregare

Interogare finală

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

Funcție de fereastră pe o agregare

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.

Calcule stratificate

Agregarea unei agregări existente
Utilizează o subinterogare

Exemplu de calcul stratificat

Pasul 1: Total medalii de bronz pe țară

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

Pasul 2: Transformare în subinterogare și extragerea maximului

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

Planificarea calculelor complexe

Ordonare pentru funcția de fereastră?
Două agregări cu calcul stratificat?

Să exersăm!

Raportare în SQL