Sestavování složitých výpočtů

Reporting in SQL

Tyler Pernes

Learning & Development Consultant

Přístupy

Okenní funkce
Vrstvené výpočty

Okenní funkce

Odkazuje na ostatní řádky v tabulce.

Okenní funkce

Odkazuje na ostatní řádky v tabulce.

Okenní funkce

Odkazuje na ostatní řádky v tabulce.

Syntaxe okenních funkcí

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

Syntaxe okenních funkcí

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

Syntaxe okenních funkcí

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

PARTITION BY = rozsah výpočtu
ORDER BY = pořadí řádků při výpočtu

Příklady okenních funkcí

Celkový počet bronzových medailí

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

Příklady okenních funkcí

Bronzové medaile podle země

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

Typy okenních funkcí

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

Typy okenních funkcí

LAG() a LEAD()

Typy okenních funkcí

LAG() a LEAD()

Typy okenních funkcí

ROW_NUMBER() a RANK()

Okenní funkce nad agregací

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

Okenní funkce nad agregací

Výsledný dotaz

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

Okenní funkce nad agregací

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.

Vrstvené výpočty

Agregace existující agregace
Využívá poddotaz

Příklad vrstveného výpočtu

Krok 1: Celkový počet bronzových medailí podle země

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

Krok 2: Převod na poddotaz a výběr maxima

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

Plánování složitých výpočtů

Řazení pro okenní funkci?
Dvě agregace s vrstveným výpočtem?

Lass uns üben!

Reporting in SQL