📌Формальное определение:
Оконные функции — мощный инструмент языка SQL, позволяющий проводить сложные вычисления по группам строк, которые связаны с текущей строкой.
🧐Углубимся в теорию
Классификация оконных функций:
1. Агрегирующие (sum, avg, min, max, count)
2. Ранжирующие (row_number, rank, dense_rank)
3. Смещения (lag, lead, first_value, last_value); функции смещения используются с указанием поля.
Ранжирующие функции:
row_number() – нумеруем каждую строку окна последовательно с шагом 1.
rank() – ранжируем каждую строку окна с разрывом в нумерации при равенстве значений.
dense_rank() – ранжируем каждую строку окна без разрывов в нумерации при равенстве значений.
Функции смещения:
lag(attr, offset (сдвиг), default_value(дефолтное значение в случае, если наша строка окажется первой)) – предыдущее значение со сдвигом.
lead(attr, offset, default_value) – следующее значение со сдвигом.
first_value(attr) – первое значение в окне с первой по текущую строку.
last_value(attr) – последнее значение в окне с первой по текущую строку.
💡Оконные функции нашли свое применение во многих компаниях. В LinkedIn оконные функции используют для расчета «силы» профиля (ранжирование пользователей по активности). А в Uber с их помощью анализируют динамику цен в режиме реального времени.
Ключевые компоненты:
1. OVER() — задает «окно»
-- Средняя зарплата ВО ВСЕЙ таблице (окно = все строки)
SELECT name, salary, AVG(salary) OVER() AS avg_salaryFROM employees;
-- Средняя зарплата ПО ОТДЕЛАМ (окно = строки отдела)
SELECT name,
department,
salary,
AVG(salary) OVER(PARTITION BY department) AS dept_avg
FROM employees;
3. ORDER BY — сортировка внутри окна
-- Накопительная сумма зарплат по дате приема
SELECT name,
hire_date,
salary,
SUM(salary) OVER(ORDER BY hire_date) AS running_total
FROM employees;
💻 Задача для практики:
«Найти сотрудников, чья зарплата выше средней по их отделу»
Решение:
SELECT name, department, salary
FROM ( SELECT
name,
department,
salary,
AVG(salary) OVER(PARTITION BY department) AS dept_avg
FROM employees
) t
WHERE salary > dept_avg;
#SQL #ОконныеФункции
@ProdAnalysis