Функция окна SQL
Расчленение оконной функции
Оконная функция — это мощная функция SQL, которая позволяет пользователям выполнять вычисления и анализ подмножества строк в запросе. Оконная функция выполняет вычисления с данными без агрегирования текущих строк.
Оконные функции вычисляют результат на основе скользящей оконной рамы. Рамка окна — это набор строк, связанных с текущей строкой. Связь заключается в том, что все строки имеют одно и то же значение для всех терминов предложения PARTITION BY в определении окна. Рамка окна оценивается отдельно в каждом разделе. Предложение Partition By разделяет строки на несколько групп.
Синтаксис
SELECT <column list>,
<window_function> OVER (
PARTITION BY <>
ORDER BY <>
<window_frame>
) window_alias
FROM <table_name>
OR
SELECT <column list> OVER <window_definition_name>
FROM <table_name>
WINDOW window_definition_name AS (
PARTITION BY <>
ORDER BY <>
<window_frame>
)
Пример
SELECT city, month,
SUM(amount) OVER (
PARTITION BY city
ORDER BY month
RANGE UNBOUNDED PRECEDING
) as total_sale
FROM Sales;
OR
SELECT city, month, SUM(amount) OVER window_definition_name
FROM Sales
WINDOW window_definition_name AS (
PARTITION BY city
ORDER BY month
RANGE UNBOUNDED PRECEDING
)
Вышеупомянутая оконная функция понимается следующим образом:
Примечание
- Предложение PARTITION BY необязательно, если мы не указываем предложение PARTITION BY, функция обрабатывает все строки как один раздел
- Предложение FRAME (ROWS | RANGE) дополнительно определяет подмножество текущего раздела.
Положение оконной функции в общем логическом порядке операторов:
Итак, из вышеизложенного мы знаем, что оконная функция применяется непосредственно перед запуском части выбора. К этому времени сервер базы данных выполнил все шаги, необходимые для оценки запроса, включая объединение, фильтрацию, группировку и сортировку, и результирующий набор завершен и готов к возврату вызывающей стороне. Оконная функция применяется к этому набору результатов, пока он все еще хранится в памяти.
В отличие от агрегатной функции оконная функция не сворачивает строки.
В спецификации окна мы можем предоставить три разных предложения фрейма:
ROWS BETWEEN – строки на расстоянии N от текущих строк в разделе.
RANGE – строки на основе их значения по сравнению с текущей строкой.
>ГРУППЫ — подсчитывает все группы связанных строк в окне (в Postgres).
[БЕЗ ГРАНИЦ] ПРЕДЫДУЩАЯ | ТЕКУЩАЯ РЯДКА | [БЕЗ ГРАНИЦ] СЛЕДУЮЩИЕ
Мы можем использовать следующие оконные функции:
Агрегатная функция
СЧЁТ, МАКС., МИН., СУММ., СРЕДН.
Функции ранжирования
ROW_NUMBER, РАНГ, DENSE_RANK
Функции распределения
PERCENT_RANK, CUME_DIST, NTILE
Позиционные функции
ОПЕРЕЖЕНИЕ, ЗАПАЗДАНИЕ, NTH_VALUE, FIRST_VALUE, LAST_VALUE
Синтаксис функции опережения/запаздывания.
LEAD | LAG ( expression, offset, deafult_value) OVER
Эти две функции полезны при сравнении годовых и квартальных показателей.
Аналитические функции
NTILE
Примечание. Мы не можем использовать оконную функцию в предложении where для фильтрации данных.
Проблема
185. Три лучших зарплаты отдела
Таблица сотрудников
+--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | name | varchar | | salary | int | | departmentId | int | +--------------+---------+
Таблица отдела
+-------------+---------+ | Column Name | Type | +-------------+---------+ | id | int | | name | varchar | +-------------+---------+
Руководители компании заинтересованы в том, чтобы узнать, кто зарабатывает больше всего денег в каждом из отделов компании. Высокооплачиваемый в отделе – это сотрудник, чья зарплата входит в три самых уникальных зарплаты в этом отделе. Напишите SQL-запрос, чтобы найти сотрудников с высокими доходами в каждом из отделов. Возвратите таблицу результатов в любом порядке.
Пример
Input: Employee table: +----+-------+--------+--------------+ | id | name | salary | departmentId | +----+-------+--------+--------------+ | 1 | Joe | 85000 | 1 | | 2 | Henry | 80000 | 2 | | 3 | Sam | 60000 | 2 | | 4 | Max | 90000 | 1 | | 5 | Janet | 69000 | 1 | | 6 | Randy | 85000 | 1 | | 7 | Will | 70000 | 1 | +----+-------+--------+--------------+ Department table: +----+-------+ | id | name | +----+-------+ | 1 | IT | | 2 | Sales | +----+-------+ Output: +------------+----------+--------+ | Department | Employee | Salary | +------------+----------+--------+ | IT | Max | 90000 | | IT | Joe | 85000 | | IT | Randy | 85000 | | IT | Will | 70000 | | Sales | Henry | 80000 | | Sales | Sam | 60000 | +------------+----------+--------+ Explanation: In the IT department: - Max earns the highest unique salary - Both Randy and Joe earn the second-highest unique salary - Will earns the third-highest unique salary In the Sales department: - Henry earns the highest salary - Sam earns the second-highest salary - There is no third-highest salary as there are only two employees
Решение:
Сначала мы присваиваем ранг всем окладам в отделе. Мы достигаем этого, используя оконную функцию, как показано ниже:
SELECT
departmentId, name, salary,
DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY Salary desc) as r
FROM Employee
Вывод вышеуказанного запроса выглядит следующим образом:
+--------------+-------+--------+----+ | departmentId | name | salary | r | +--------------+-------+--------+----+ | 1 | Max | 90000 | 1 | | 1 | Joe | 85000 | 2 | | 1 | Randy | 60000 | 2 | | 1 | Will | 90000 | 3 | | 1 | Janet | 69000 | 4 | | 2 | Henry | 85000 | 1 | | 2 | Sam | 70000 | 2 | +--------------+-------+--------+----+
Получив этот результат, мы просто отфильтровываем строки с рангом ‹ 4 (1, 2, 3).
WITH ranked_salary_for_each_dept AS (
SELECT departmentId, name, salary, DENSE_RANK() OVER (PARTITION BY departmentId ORDER BY Salary desc) as r
FROM Employee
)
select d.Name as "Department", a.Name as "Employee", a.Salary as "Salary"
from ranked_salary_for_each_dept a
join Department d on a.departmentId = d.Id
where a.r < 4
Счастливого Окна!!
