SQL Окна: Руководство по функциям

Оконные функции SQL позволяют выполнять вычисления над набором строк (окном) без уменьшения количества строк в результате, в отличие от агрегатных функций с GROUP BY. Окно определяется партициями и сортировкой.

Введение

Рассмотрим пример с таблицей оценок учеников:

CREATE TABLE StudentGrades (
    name VARCHAR,
    subject VARCHAR,
    grade INT
);

INSERT INTO StudentGrades (name, subject, grade) VALUES
('Петя', 'Математика', 5),
('Петя', 'Физика', 4),
('Петя', 'Химия', 4),
('Петя', 'Биология', 3),
('Маша', 'Математика', 4),
('Маша', 'Физика', 5),
('Маша', 'Химия', 3),
('Маша', 'Биология', 4);

Проверим данные:

SELECT * FROM StudentGrades;

Основные понятия

Агрегатные функции (например, AVG()) с GROUP BY уменьшают число строк, группируя их. Оконные функции этого не делают. Например, сравним агрегатную функцию AVG() с группировкой:

SELECT name, AVG(grade) AS average_grade
FROM StudentGrades
GROUP BY name;

и аналогичный запрос с оконной функцией:

SELECT name, AVG(grade) OVER (PARTITION BY name) AS average_grade
FROM StudentGrades;

Порядок выполнения запроса с оконными функциями:

1. Выборка таблиц и объединения (FROM).
2. Фильтрация (WHERE).
3. Группировка (GROUP BY).
4. Выборка столбцов и вычисление оконных функций (SELECT).
5. Сортировка (ORDER BY).

Партиции создаются после группировки, если она используется.

Синтаксис

Синтаксис оконных функций:

function_name(column) OVER (PARTITION BY column1, column2 … ORDER BY column3, column4 …)

• function_name: имя функции.
• column: столбец, к которому применяется функция.
• PARTITION BY: определение партиций.
• ORDER BY: сортировка внутри партиций.

Оконные функции можно использовать в SELECT или в ключевом слове WINDOW, присваивая окну псевдоним:

SELECT 
    name,
    ROW_NUMBER() OVER w AS row_num,
    RANK() OVER w AS rank_num,
    DENSE_RANK() OVER w AS dense_rank_num
FROM StudentGrades
WINDOW w AS (PARTITION BY name ORDER BY grade DESC);

Классы оконных функций

Оконные функции делятся на три класса:

• Агрегирующие: SUM(), AVG(), COUNT(), MIN(), MAX().
• Ранжирующие: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), PERCENT_RANK(), CUME_DIST().
• Смещения: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), NTH_VALUE().

Агрегирующие функции

Пример:

SELECT 
    name, 
    SUM(grade) OVER (PARTITION BY name) AS sum_grade,
    AVG(grade) OVER (PARTITION BY name) AS avg_grade,
    COUNT(*) OVER (PARTITION BY name) AS count_grade,
    MIN(grade) OVER (PARTITION BY name) AS min_grade,
    MAX(grade) OVER (PARTITION BY name) AS max_grade
FROM StudentGrades;

Ранжирующие функции

Для ранжирующих функций ORDER BY обязателен:

SELECT 
    name, 
    grade,
    ROW_NUMBER() OVER (PARTITION BY name ORDER BY grade DESC) AS row_num,
    RANK() OVER (PARTITION BY name ORDER BY grade DESC) AS rank_num,
    DENSE_RANK() OVER (PARTITION BY name ORDER BY grade DESC) AS dense_rank_num
FROM StudentGrades;

ROW_NUMBER() присваивает порядковый номер. RANK() присваивает ранг, пропуская номера при одинаковых значениях. DENSE_RANK() аналогичен RANK(), но не пропускает номера. NTILE(n) делит партицию на n групп.

Функции смещения

Функции смещения позволяют обращаться к предыдущим или последующим значениям. Пример с LAG() и LEAD():

CREATE TABLE Grades_Quartal (
    name VARCHAR,
    quartal VARCHAR,
    subject VARCHAR,
    grade INT
);

INSERT INTO Grades_Quartal (name, quartal, subject, grade) VALUES
('Петя', '1', 'Математика', 4),
('Петя', '2', 'Математика', 4),
('Петя', '3', 'Математика', 5),
('Петя', '4', 'Математика', 3);

SELECT 
    name,
    quartal,
    grade,
    LAG(grade, 1, 0) OVER (ORDER BY quartal) AS prev_grade,
    LEAD(grade, 1, 0) OVER (ORDER BY quartal) AS next_grade
FROM Grades_Quartal;

FIRST_VALUE() и LAST_VALUE() возвращают первое и последнее значение в партиции. NTH_VALUE(n) возвращает n-ое значение.

Оконные функции — мощный инструмент SQL для анализа данных.