Построение сложных вычислений

Отчётность в 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