7 ноя 2024

Оконные функции: что это такое и как они работают в SQL

15 мин

Хотите научиться работать с оконными функциями в SQL? Наша статья расскажет вам о том, что они собой представляют, какие виды оконных функций существуют и как их использовать (на примерах). Подробнее читайте в нашей стать.

Оконные функции — инструмент SQL, помогающий производить сложные вычисления над группами строк, связанных с текущей. Они помогают быстрее проводить комплексную аналитику и манипулировать данными. При этом нет необходимости писать сложные запросы. Рассмотрим, что такое оконные функции, изучим их виды, разберем примеры использования.

Что такое оконные функции?
Как работают оконные функции?
Виды оконных функций
Примеры использования оконных функций
Заключение

Что такое оконные функции?

Оконная функция позволяет выполнять вычисления для набора строк таблицы без их объединения. Похожие вычисления можно произвести и с использованием агрегатной функции. Однако оконная функция не группирует все строки в одну выходную строку. Она сохраняет свои отдельные идентификаторы. Окном называют набор строк в таблице. Там может находиться более одного окна в зависимости от того, как вы задаете запрос.

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

выполнения вычислений, требующих контекста: рассчитывайте промежуточные итоги, рейтинги, средние значения и другие показатели, учитывая конкретный контекст или подмножество строк в наборе результатов;
сравнения строки с соседними строками: анализируйте связи данных между текущей строкой и соседними строками в одном наборе результатов;
избегания самообъединений: вместо использования самосоединений для сравнения строки с другими используйте оконные функции для эффективных и более читаемых запросов;
упрощения сложных запросов: вместо написания сложных и вложенных запросов для достижения конкретных аналитических результатов лучше использовать оконные функции.

Как работают оконные функции?

Они действуют как «окна», которые состоят из большого количества строк в таблице. Одна такая функция вычисляется для каждой строки в таблице, учитывая текущую строку и строки внутри «окон». Они определяются с помощью OVER(). Там можно указать различные параметры: например, изменить размер «окон» или порядок их сортировки.

Виды оконных функций

Разделения оконных функций SQL на категории нет, но часто их делят на два или три типа. Базовая классификация разделяет оконные функции на агрегатные и встроенные.

Основная характеристика агрегатных видов заключается в том, что они повторно используют существующие простые агрегатные функции (такие, как COUNT() или SUM()), изменяя при этом способ определения агрегирования и формат результатов. При этом встроенные функции имеют новые имена и не могут применяться в другом контексте.

Встроенные оконные функции SQL иногда делятся на два разных типа: функции ранжирования и функции значения. Функции окна ранжирования используются для простого присвоения номеров существующим строкам в соответствии с некоторыми требованиями. Функции окна значений подходят для копирования данных из строк в другие строки в определенных окнах.

Aggregate window functions — точно такие же, как и стандартные агрегатные функции. Наиболее популярные из них — AVG(), MAX(), MIN(), SUM() и COUNT(). При использовании в обычных обстоятельствах эти функции либо применяются ко всему набору данных (например, AVG() может вернуть среднее их всех данных в столбце), либо работают в паре с оператором GROUP BY, чтобы функция применялась к подмножествам или строкам.

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

SUM(). Рассчитывает сумму указанного поля для указанной группы (например, города, региона, страны) или для всей таблицы, если группа не указана.Пример обычной агрегатной функции SUM(): она подсчитывает сумму заказа для каждого города. Здесь SUM группирует несколько строк в одну выходную строку, в результате чего отдельные строки теряют свою идентичность.

SELECT city, SUM(order_amount) total_order_amount
FROM [dbo].[Orders] GROUP BY city

sql

В оконной функции строки сохраняют идентичность, а также отображают агрегированное значение для каждой строки. В приведенном ниже примере запрос делает то же самое, а именно — агрегирует данные для каждого города и показывает сумму общей суммы заказов для каждого из них. Однако теперь запрос вставляет еще один столбец для общей суммы заказа. Столбец с пометкой grand_total будет новым.

SELECT order_id, order_date, customer_name, city, order_amount
,SUM(order_amount) OVER(PARTITION BY city) as grand_total
FROM [dbo].[Orders]

sql

MIN(). Найдет минимальное значение для указанной группы или для всей таблицы. Например, мы ищем наименьший заказ для каждого города и будем использовать следующий запрос.

SELECT order_id, order_date, customer_name, city, order_amount
,MIN(order_amount) OVER(PARTITION BY city) as minimum_order_amount
FROM [dbo].[Orders]

sql

MAX() определит наибольшее значение указанного поля для указанной группы строк или для всей таблицы.

SELECT order_id, order_date, customer_name, city, order_amount
,MAX(order_amount) OVER(PARTITION BY city) as maximum_order_amount
FROM [dbo].[Orders]

sql

Ranking window functions используются для присвоения номеров строкам в зависимости от определенного порядка. В отличие от агрегатных они не имеют эквивалентов, не использующих окна. Более того, попытка найти те же результаты, но без использования аналитических функций, потребует нескольких вложенных запросов и будет неэффективной. К этому виду относятся:

ROW_NUMBER()
RANK()
DENSE_RANK()
PERCENT_RANK()
NTILE()

ROW_NUMBER(). Функции присваивают каждой записи уникальный номер строки. Номер строки будет сброшен для каждого раздела, если указано PARTITION BY.

SELECT order_id,order_date,customer_name,city, order_amount,
ROW_NUMBER() OVER(PARTITION BY city ORDER BY order_amount DESC) [row_number]
FROM [dbo].[Orders]

sql

RANK(). Используется для присвоения уникального ранга каждой записи на основе указанного значения. (зарплаты, суммы заказа). Если две записи имеют одинаковое значение, функция RANK() присвоит обеим одинаковый ранг и пропустит следующий. Если в ранге 2 есть два одинаковых значения, обеим записям будет присвоен один и тот же ранг 2, затем будет пропущен ранг 3, а следующая запись получит ранг 4.

SELECT order_id,order_date,customer_name,city,
RANK() OVER(ORDER BY order_amount DESC) [Rank]
FROM [dbo].[Orders]

sql

DENSE_RANK(). Функция не пропускает ни одного ранга. Если найдены две идентичные записи, DENSE_RANK() присвоит обеим записям одинаковый ранг.

SELECT order_id,order_date,customer_name,city, order_amount,
DENSE_RANK() OVER(ORDER BY order_amount DESC) [Rank]
FROM [dbo].[Orders]

sql

PERCENT_RANK(). Еще одна функция, которая использует RANK() для расчета окончательного рейтинга. Значения ранжирования в случае PERCENT_RANK рассчитываются по следующей формуле: (ранг — 1)/(строки — 1). По этой причине все значения ранжирования масштабируются по количеству строк и остаются в диапазоне от 0 до 1. Кроме того, строкам с первым значением всегда присваивается значение ранжирования 0.

SELECT  from_user, 
        COUNT(*) as total_emails, 
        PERCENT_RANK() OVER (ORDER BY count(*) desc)
FROM google_gmail_emails 
GROUP BY from_user

sql

NTILE(). Она поможет определить, в какой процентиль (или квартиль, или любое другое подразделение) попадает данная строка. Это означает, что если у вас 100 строк и вы хотите создать 4 квартиля на основе указанного поля значений, вы можете легко это сделать и посмотреть, сколько строк попадает в каждый квартиль.

В приведенном ниже запросе мы указали, что хотим создать четыре квартиля на основе суммы заказа. Затем мы хотим увидеть, сколько заказов попадает в каждый квартиль.

SELECT order_id,order_date,customer_name,city, order_amount,
NTILE(4) OVER(ORDER BY order_amount) [row_number]
FROM [dbo].[Orders]

sql

Функции окна значений используются для поиска первого, последнего, предыдущего и следующего значений. Можно использовать следующие функции: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().

LAG(). Позволяет получить доступ к данным из предыдущей строки в том же наборе результатов без использования объединений SQL. В приведенном ниже примере вы можете увидеть, как с помощью функции LAG мы нашли дату предыдущего заказа.

SELECT order_id,customer_name,city, order_amount,order_date,
--in below line, 1 indicates check for previous row of the current row
LAG(order_date,1) OVER(ORDER BY order_date) prev_order_date
FROM [dbo].[Orders]

sql

LEAD (). Функция помогает получить доступ к данным из следующей строки.

SELECT order_id,customer_name,city, order_amount,order_date,
--in below line, 1 indicates check for next row of the current row
LEAD(order_date,1) OVER(ORDER BY order_date) next_order_date
FROM [dbo].[Orders]

sql

FIRST_VALUE() и LAST_VALUE(). Помогут идентифицировать первую и последнюю запись в разделе или всей таблице, если PARTITION BY не указан.

Можно найти город, где сделали первый заказ, и город, куда был доставлен последний заказ. Предложение ORDER BY обязательное для функций FIRST_VALUE() и LAST_VALUE().

SELECT order_id,order_date,customer_name,city, order_amount,
FIRST_VALUE(order_date) OVER(PARTITION BY city ORDER BY city) first_order_date,
LAST_VALUE(order_date) OVER(PARTITION BY city ORDER BY city) last_order_date
FROM [dbo].[Orders]

sql

Примеры использования оконных функций

Расчет скользящего среднего. Этот элемент SQL-запроса рассчитывает скользящее среднее объема продаж, которое может быть полезным показателем при анализе тенденций продаж. Скользящее среднее — расчет, который усредняет определенное количество точек данных за определенный период времени, при этом «окно» точек данных «перемещается» вперед по мере поступления новых данных.

AVG(SUM(f.SalesAmount)) OVER (
      PARTITION BY c.EnglishProductCategoryName
      ORDER BY d.CalendarYear, d.CalendarQuarter
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
   ) AS MovingAvg

sql

AVG(SUM(f.SalesAmount)): основной расчет скользящего среднего. Он берет сумму столбца SalesAmount из таблицы FactInternetSales (которая называется «f»), а затем вычисляет среднее значение. Результатом этого расчета будет скользящая средняя.

OVER: это ключевое слово используется для указания оконной функции, которая позволяет вычислять нужный показатель.

PARTITION BY c.EnglishProductCategoryName: это предложение делит данные на секции на основе значений в столбце EnglishProductCategoryName из таблицы DimProductCategory. Это означает, что скользящее среднее будет рассчитываться отдельно для каждой категории товаров.

ORDER BY d.CalendarYear, d.CalendarQuarter: это предложение упорядочивает данные в каждой секции по значениям в столбцах CalendarYear и CalendarQuarter из таблицы DimDate. Это гарантирует, что скользящее среднее рассчитывается в хронологическом порядке.

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: в этом предложении указывается диапазон строк, по которым должно рассчитываться скользящее среднее. В частности, он включает текущую строку и две предыдущие строки.

AS MovingAvg: это присваивает результату расчета скользящего среднего название «MovingAvg», которое будет включено в результаты запроса.

PERCENT_RANK для расчета процентиля SQL оценок учащихся. Общее табличное выражение используется для хранения оценок учащихся. Функция PERCENT_RANK поможет найти самую высокую и самую низкую оценку.

WITH Student
   	AS (SELECT Marks
       	FROM(VALUES(75), (59), (69), (88), (72), (86), (60), (67), (49), (81), (93)) Val(Marks)),
   	Percentiles
   	AS (SELECT Marks,
                  PERCENT_RANK() OVER(
                  ORDER BY Marks) AS Percent_Rank
       	FROM Student)
   	SELECT *
   	FROM Percentiles;

sql

Самая низкая оценка (49) получает нулевое значение Percent_Rank. Наивысшую оценку (93) получает значение Percent_Rank value one.

Заключение

Оконная функция не группирует строки в одну строку, а создает новый выходной столбец.
Оконную функцию можно разбить на группы и легко организовать с помощью ключевых слов PARTITION BY и ORDER BY. PARTITION BY разделяет результаты агрегатной функции между различными группами данных. ORDER BY упорядочивает данные.
Window functions SQL часто используют аналитики данных и специалисты, которые работают с большими объемами информации.
Оконные функции SQL эффективны. Их синтаксис немного отличается от стандартного SQL, но вполне логичен.