Tworzenie złożonych obliczeń

Raportowanie w SQL

Tyler Pernes

Learning & Development Consultant

Podejścia

Funkcje okna
Obliczenia warstwowe

Funkcje okna

Odwołuje się do innych wierszy w tabeli.

Funkcje okna

Odwołuje się do innych wierszy w tabeli.

Funkcje okna

Odwołuje się do innych wierszy w tabeli.

Składnia funkcji okna

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

Składnia funkcji okna

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

Składnia funkcji okna

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

PARTITION BY = zakres obliczeń
ORDER BY = kolejność wierszy podczas obliczeń

Przykłady funkcji okna

Łączna liczba medali brązowych

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

Przykłady funkcji okna

Medale brązowe według kraju

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

Rodzaje funkcji okna

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

Rodzaje funkcji okna

LAG() and LEAD()

Rodzaje funkcji okna

LAG() and LEAD()

Rodzaje funkcji okna

ROW_NUMBER() and RANK()

Funkcja okna na agregacji

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

Funkcja okna na agregacji

Ostateczne zapytanie

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

Funkcja okna na agregacji

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.

Obliczenia warstwowe

Agregacja istniejącej agregacji
Wykorzystuje podzapytanie

Przykład obliczeń warstwowych

Krok 1: Łączna liczba medali brązowych na kraj

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

Krok 2: Przekształć w podzapytanie i oblicz maksimum

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

Planowanie złożonych obliczeń

Kolejność dla funkcji okna?
Dwie agregacje z obliczeniem warstwowym?

Czas na ćwiczenia!

Raportowanie w SQL