Побудова складних обчислень

Звітування в SQL

Tyler Pernes

Learning & Development Consultant

Підходи

Віконні функції
Багатошарові обчислення

Віконні функції

Посилається на інші рядки в таблиці.

Віконні функції

Посилається на інші рядки в таблиці.

Віконні функції

Посилається на інші рядки в таблиці.

Синтаксис віконних функцій

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

Синтаксис віконних функцій

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

Синтаксис віконних функцій

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

PARTITION BY = діапазон обчислення
ORDER BY = порядок рядків під час обчислення

Приклади віконних функцій

Усього бронзових медалей

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

Приклади віконних функцій

Бронзові медалі країни

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

Типи віконних функцій

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

Типи віконних функцій

LAG() і LEAD()

Типи віконних функцій

LAG() і LEAD()

Типи віконних функцій

ROW_NUMBER() і RANK()

Віконна функція на агрегації

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

Віконна функція на агрегації

Фінальний запит

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

Віконна функція на агрегації

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.

Багатошарові обчислення

Агрегуйте вже наявну агрегацію
Використовуйте підзапит

Приклад багатошарових обчислень

Крок 1: Усього бронзових медалей на країну

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

Крок 2: Перетворіть на підзапит і візьміть максимум

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

Планування складних обчислень

Порядок для віконної функції?
Дві агрегації з багатошаровим обчисленням?

Давайте потренуємось!

Звітування в SQL