Оконные функции в SQL
Оконные функции SQL - это набор функций который позволяет агрегировать, ранжировать, а так-же смещать значения в рамках обозначенных партиций (окон). При этом
важной особенностью оконных функций является сохранение количества строк после применения агрегационных функций, в отлиции от метода агрегирования с
использованием GROUP BY. Определяет оконные функции оператор OVER(), в нем мы прописываем условия
разбивки данных на партиции и сортировку, которая крайне важна для ранжирующих оконных функций.
Функции ранжирования:
ROW_NUMBER() - позволяет пронумеровать строки по порядку начиная с единицы. (пример: 1, 2, 3, 4, 5) RANK() - возвращает ранг строки. Он вычисляется как единица плюс количество предшествующих строк. При одинаковых значениях присваивается единый ранг. (пример: 1, 2, 2, 4, 5) DENSE_RANK() - так же возврашает ранг строки, но вычисляется как единица плюс количество уникальных предшествующих рангов. (пример: 1, 2, 2, 3, 4)
CREATE TABLE salary
(
id INT PRIMARY KEY AUTO_INCREMENT,
employee VARCHAR(50),
department VARCHAR(30),
salary INT
);
INSERT INTO salary(employee, department, salary) VALUES
('Carlos Boolman', 'Finance', 98000),
('Logan Evans', 'Marketing', 78400),
('Laura White', 'Marketing', 78400),
('Ethan Walker', 'Finance', 63000),
('Mary Wilson', 'Marketing', 46800);
SELECT employee, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM salary;
Теперь для понимая работы команды PARTITION BY выполним тот же запрос, но с указанием разбивки данных при расчете оконных функций по партициям.
SELECT employee, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM salary;
Пустые значения NULL в ранжирующих функциях будут иметь одинаковый ранг
Функции агрегации:
SUM() - Возвращает сумму значений столбца в рамках партиции. COUNT() - Возвращает количество значений столбца в рамках партиции. MIN() - Возвращает минимальное значение столбца в рамках партиции. MAX() - Возвращает максимальное значение столбца в рамках партиции. AVG() - Возвращает среднее арифметическое значение столбца в рамках партиции. Для примеров использования функций агрегации будем использовать таблицу "salary" созданную в разделе "Функции ранжирования":
SELECT employee, department, salary,
SUM(salary) OVER (PARTITION BY department) AS sum,
COUNT(salary) OVER (PARTITION BY department) AS count,
MIN(salary) OVER (PARTITION BY department) AS min,
MAX(salary) OVER (PARTITION BY department) AS max,
AVG(salary) OVER (PARTITION BY department) AS avg
FROM salary ORDER BY department, salary;
Функции смещения:
FIRST_VALUE() - Возвращает первое значение столбца в рамках партиции согласно порядка сортировки данных. LAST_VALUE() - Возвращает последнее значение столбца в рамках партиции согласно порядка сортировки данных. LEAD() - Возвращает следующее значение столбца в рамках партиции согласно порядка сортировки данных. LAG() - Возвращает предыдущее значение столбца в рамках партиции согласно порядка сортировки данных.
CREATE TABLE sales
(
id INT PRIMARY KEY AUTO_INCREMENT,
seller VARCHAR(50),
date DATE,
sales INT
);
INSERT INTO sales(seller, date, sales) VALUES
('Carlos Boolman', '2024-05-10', 81200),
('Carlos Boolman', '2024-05-11', 96400),
('Carlos Boolman', '2024-05-12', 79300),
('Mary Wilson', '2024-05-10', 63100),
('Mary Wilson', '2024-05-11', 46800),
('Mary Wilson', '2024-05-12', 68700);
SELECT seller, date, sales,
FIRST_VALUE(sales) OVER (PARTITION BY seller ORDER BY date) AS first_value,
LAST_VALUE(sales) OVER (PARTITION BY seller ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value, LEAD(sales) OVER (PARTITION BY seller ORDER BY date) AS lead, LAG(sales) OVER (PARTITION BY seller ORDER BY date) AS lag FROM sales;
В функцию LAST_VALUE() мы добавили дополнительные условия ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,
потому что СУБД при получении последней строки обходит массив строк поступательно и за последнюю строку воспринимает текущую, для того чтобы
заставить СУБД при команде LAST_VALUE() получать последнее значение в рамках партиции нам необходимы условия расширяюшие массив
значений до всей партиции.
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_value, LEAD(sales) OVER (PARTITION BY seller ORDER BY date) AS lead, LAG(sales) OVER (PARTITION BY seller ORDER BY date) AS lag FROM sales;