Оконные функции SQL: что это, для чего нужны, примеры

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

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

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

Оконные функции в SQL — это способ делать расчеты по набору строк, не склеивая их в одну, как это происходит при использовании GROUP BY. Они работают «поверх» данных: вычисляют сумму, среднее, порядковый номер строки, процент от общего и другие штуки — и все это без потери самих строк. Каждая строка остается на месте, просто к ней добавляется результат вычисления. Например, можно узнать, сколько заказов сделал клиент до текущего заказа, или какое среднее значение у зарплат в отделе, не сгруппировав все строки в одну.

Главная особенность оконных функций в том, что они считают результат не по всей таблице сразу, а по отдельным «окнам» — группам строк, которые можно настроить через PARTITION BY и отсортировать с помощью ORDER BY. За все это отвечает оператор OVER. Именно он определяет границы окна: что попадает в расчет, а что нет.

Для чего нужны оконные функции

Оконные функции нужны тогда, когда обычные агрегаты вроде SUM() или AVG() уже не справляются. Они незаменимы, если нужно:

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

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

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

Оконные функции в SQL всегда строятся по одному принципу: сначала указывается сама функция (например, SUM, AVG, ROW_NUMBER), потом через OVER() задаются условия, по которым эта функция будет работать.

Общий шаблон записи выглядит так:

<название функции>(аргументы) OVER ([PARTITION BY ...] [ORDER BY ...] [frame])

Подробнее:

• <название функции> — имя оконной функции, например ROW_NUMBER(), RANK(), SUM() и другие;
• (аргументы) — это могут быть столбцы или выражения, на основе которых будет идти расчет;
• OVER() — самое важное. Указывает, что это не обычная агрегация, а расчет в пределах окна;
• PARTITION BY — разбивает строки на группы. В каждой группе функция будет работать отдельно;
• ORDER BY — задает порядок строк внутри группы, что особенно важно для нумерации (ROW_NUMBER, RANK);
• frame (ROWS или RANGE) — уточняет, какие именно строки относительно текущей участвуют в расчетах.

Элементы PARTITION BY, ORDER BY и frame внутри OVER() можно использовать вместе или по отдельности. Все они необязательные, но иногда без них результат будет не тот, который нужен.

PARTITION BY

PARTITION BY нужен, чтобы разбить таблицу на независимые части — партиции. Оконная функция будет работать отдельно внутри каждой такой группы.

Если вы хотите посчитать сумму продаж в каждом отделе, сохраняя все строки, функция будет выглядеть так:

SELECT

  department,

  sale_date,

  sales,

  SUM(sales) OVER (PARTITION BY department) AS total_sales

FROM sport_sales;

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

ORDER BY

ORDER BY внутри OVER() определяет порядок строк для расчётов. Без него функции, которые зависят от порядка (ROW_NUMBER, RANK, LEAD, LAG), не будут работать как надо.

Например, пронумеровать покупки по дате продажи можно следующим образом:

SELECT

  sale_date,

  department,

  sales,

  ROW_NUMBER() OVER (ORDER BY sale_date) AS row_num

FROM sport_sales;

Если же добавить несколько полей в ORDER BY, сначала будет сортировка по первому полю, потом по второму и так далее.

ROWS и RANGE

Часто нужно контролировать, сколько строк участвует в расчетах относительно текущей. Для этого служат ROWSи RANGE. ROWS работает с физическим положением строк. RANGE работает по значениям в столбце сортировки.

Например, если вы хотите посчитать сумму продаж для текущей строки плюс строки до и после, то пропишите:

SELECT

  sale_date,

  department,

  sales,

  SUM(sales) OVER (ORDER BY sale_date ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_sum

FROM sport_sales;

FILTER (WHERE ...)

Иногда хочется посчитать функцию только для части строк. Для этого есть конструкция FILTER (WHERE ...) прямо в оконной функции.

Посчитать сумму только тех продаж, которые больше 100, можно так:

SELECT

  sale_date,

  department,

  sales,

  SUM(sales) FILTER (WHERE sales > 100) OVER (PARTITION BY department) AS big_sales_sum

FROM sport_sales;

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

Чтобы выбрать нужную функцию и использовать ее правильно, стоит понимать, какие вообще бывают оконные функции и в чем между ними разница:

Агрегатные функции

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

К основным агрегатным функциям относятся:

• SUM(column_name) — считает сумму значений в окне;
• AVG(column_name) — вычисляет среднее значение;
• MAX(column_name) — находит максимальное значение;
• MIN(column_name) — находит минимальное значение;
• COUNT(column_name) — считает количество строк.

Все эти функции позволяют проводить аналитику, не разрушая исходные данные.

Допустим, у нас есть таблица заказов, и мы хотим посчитать среднюю, суммарную, максимальную и минимальную стоимость заказа на каждую дату, а заодно узнать, сколько заказов было в день. Причем нам важно сохранить все заказы в результате.

Вот как будет выглядеть запрос:

SELECT

   order_id,

   order_date,

   category,

   amount,

   AVG(amount) OVER (PARTITION BY order_date) AS avg_amount,

   SUM(amount) OVER (PARTITION BY order_date) AS sum_amount,

   MAX(amount) OVER (PARTITION BY order_date) AS max_amount,

   MIN(amount) OVER (PARTITION BY order_date) AS min_amount,

   COUNT(amount) OVER (PARTITION BY order_date) AS count_amount

FROM orders;

А вот что мы получим:

Теперь для каждой строки видно, сколько в среднем заказывали в этот день, какая была суммарная сумма, сколько было заказов и какие минимальные и максимальные заказы были сделаны.

Функции ранжирования

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

Основные функции ранжирования:

• RANK();
• DENSE_RANK();
• ROW_NUMBER().

Разберем каждую из них подробно.

RANK()

RANK() присваивает каждой строке ранг внутри группы, определённой через PARTITION BY. Если у нескольких строк есть одинаковые значения для сортировки, они получают одинаковый ранг. При этом следующий ранг пропускается на количество строк с одинаковым значением.

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

Запрос будет выглядеть так:

SELECT

   employee_id,

   department,

   salary,

   RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank

FROM employees;

Пояснения:

• PARTITION BY department — делит сотрудников по отделам;
• ORDER BY salary DESC — сортирует по зарплате в порядке убывания;
• RANK() — присваивает ранг в каждом отделе по размеру зарплаты.

Пример результата:

У второго и третьего сотрудников одинаковая зарплата, поэтому у них ранг 2. Следующий сотрудник получает ранг 4, а не 3 — потому что учитывается пропуск.

DENSE_RANK()

DENSE_RANK() работает похожим образом на RANK(), но без пропуска рангов. Если у нескольких строк одинаковое значение, следующий ранг будет просто увеличен на 1.

Пример:

SELECT

   employee_id,

   department,

   salary,

   DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_salary_rank

FROM employees;

Пояснения:

• PARTITION BY department — делит сотрудников по отделам;
• ORDER BY salary DESC — сортирует по убыванию зарплаты;
• DENSE_RANK() — присваивает ранги, без пропусков.

Результат:

Получается, что у сотрудников с зарплатой 85000 одинаковый ранг — 2. Следующий сотрудник получает ранг 3, без пропуска.

ROW_NUMBER()

ROW_NUMBER() присваивает каждой строке уникальный порядковый номер внутри группы. Даже если значения одинаковые, номера будут разными.

Пример:

SELECT

   employee_id,

   department,

   salary,

   ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num

FROM employees;

Пояснения:

• PARTITION BY department — разбивает сотрудников по отделам;
• ORDER BY salary DESC — сортирует их по убыванию зарплаты внутри каждого отдела;
• ROW_NUMBER() — нумерует строки подряд.

Пример результата:

Даже если зарплата одинаковая (85000 у двух сотрудников), у них разные порядковые номера — 2 и 3. Номера идут строго по порядку, без пропусков.

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

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

К функциям смещения относятся:

• LEAD();
• LAG();
• FIRST_VALUE();
• LAST_VALUE().

Поговорим о каждой подробнее. 

LEAD()

Функция LEAD() позволяет посмотреть вперед на заданное количество строк и достать оттуда значение. По сути, она отвечает на вопрос: «Что будет следующим?». Например, с ее помощью можно сравнить текущую продажу со следующей или рассчитать разницу между днями.

Пример:

SELECT

   order_id,

   order_date,

   category,

   amount,

   LEAD(amount, 1) OVER (PARTITION BY category ORDER BY order_date) AS next_amount

FROM orders;

Пояснения:

• PARTITION BY category — делит заказы по категориям товаров;
• ORDER BY order_date — сортирует заказы по дате;
• LEAD(amount, 1) — берет значение amount из следующей строки.

Пример результата:

У заказа на 2500 в категории Electronics следующее значение — 2700. У последнего заказа в каждой категории следующего значения нет, поэтому NULL.

LAG()

Функция LAG() делает все наоборот — она позволяет заглянуть назад. Иными словами, отвечает на вопрос: «Что было перед этим?». Удобный вариант, когда нужно, например, посмотреть, сколько клиент тратил в прошлый раз, или сравнить продажи с предыдущим днём.

Пример:

SELECT

   order_id,

   order_date,

   category,

   amount,

   LAG(amount, 1) OVER (PARTITION BY category ORDER BY order_date) AS previous_amount

FROM orders;

Пояснения:

• PARTITION BY category — делит заказы по категориям товаров;
• ORDER BY order_date — сортирует заказы по дате;
• LAG(amount, 1) — берет значение amount из предыдущей строки.

Результат:

У первой строки в каждой категории нет предыдущего значения — поэтому NULL. У остальных строк отображается значение из предыдущего заказа.

FIRST_VALUE()

Функция FIRST_VALUE() вытаскивает первое значение из окна строк, отсортированного по заданному порядку. Она отвечает на вопрос: «С чего всё начиналось?». Чаще всего ее используют, чтобы показать, с чего начался период.

Пример:

SELECT 

  order_id, 

  order_date, 

  category, 

  amount,

  FIRST_VALUE(amount) OVER (PARTITION BY category ORDER BY order_date) AS first_amount

FROM orders;

Пояснения:

• PARTITION BY category — делит строки по категориям товаров;
• ORDER BY order_date — сортирует по дате заказа;
• FIRST_VALUE(amount) — для каждой строки берет первое значение amount в рамках своей группы.

Результат:

В категории Electronics первой была покупка на 2500 — и это значение подтягивается ко всем строкам этой группы. То же самое применимо и для Furniture. 

LAST_VALUE()

А вот LAST_VALUE() вытаскивает последнее значение в окне. Она отвечает на вопрос: «Чем все закончилось?».

Когда нужно показать последнюю покупку клиента, последнее изменение статуса заказа или финальное значение в группе, используется именно эта функция.

Важно! Без правильной настройки окна LAST_VALUE() может вернуть не настоящее последнее значение, а текущее значение строки. Поэтому лучше всегда задавать полный фрейм.

Пример с фреймом:

SELECT

   order_id,

   order_date,

   category,

   amount,

   LAST_VALUE(amount) OVER (

     PARTITION BY category 

     ORDER BY order_date 

     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

   ) AS last_amount

FROM orders;

Пояснения:

• PARTITION BY category — разбивает данные по категориям товаров, чтобы работать отдельно с каждой группой;
• ORDER BY order_date — сортирует заказы внутри каждой категории по дате;
• LAST_VALUE(amount) — берет значение из последней строки в отсортированном порядке для каждой группы;
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — указывает, что нужно видеть все окно целиком: от первой до последней строки. 

Результат:

Теперь в каждой строке видно, каким было последнее значение для категории.

Функции аналитики

Функции аналитики в SQL нужны тогда, когда простой суммы или среднего значения уже мало. Они позволяют понять, как каждая строка соотносится с другими строками в пределах своей группы: какое место занимает, на каком процентиле находится и насколько «высоко» расположена по сравнению с остальными.

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

К основным аналитическим оконным функциям относятся:

• CUME_DIST();
• PERCENT_RANK();
• PERCENTILE_CONT();
• PERCENTILE_DISC().

Посмотрим на каждую подробнее.

CUME_DIST()

Функция CUME_DIST() показывает, у какой доли строк в группе значение меньше или равное текущему. Результат выражается в виде числа от 0 до 1. По сути, это способ понять, насколько «высоко» находится строка в своем наборе данных.

Пример:

SELECT 

  product_id,

  category,

  price,

  CUME_DIST() OVER (PARTITION BY category ORDER BY price) AS cum_dist

FROM products;

Пояснения:

• PARTITION BY category — делит товары по категориям;
• ORDER BY price — сортирует их по цене в порядке возрастания;
• CUME_DIST() — для каждой строки рассчитывает долю товаров с меньшей или равной ценой.

Результат:

Таким образом, у продукта с ценой 700 в категории Electronics 67% товаров стоят меньше или столько же.

PERCENT_RANK()

PERCENT_RANK() — еще один способ оценить положение строки в группе. Он показывает ранг строки среди всех остальных как долю между 0 и 1, но при этом самая первая строка получает 0, а самая последняя — 1.

Пример:

SELECT 

  product_id,

  category,

  price,

  PERCENT_RANK() OVER (PARTITION BY category ORDER BY price) AS percent_rank

FROM products;

Пояснения:

• PARTITION BY category — делит товары на группы по категориям;
• ORDER BY price — сортирует товары в порядке возрастания цены;
• PERCENT_RANK() — рассчитывает относительное положение каждой строки среди всех в группе.

Результат:

Первая строка в группе всегда имеет percent_rank = 0. Последняя — 1. Остальные строки получают значение, рассчитанное по формуле: (Ранг строки – 1)  / (Общее количество строк – 1). 

PERCENTILE_CONT()

Функция PERCENTILE_CONT() нужна для вычисления непрерывного процентиля. Она ищет значение в данных, которое соответствует заданному проценту. Если точного значения нет, SQL аккуратно интерполирует его между двумя ближайшими.

Проще говоря: PERCENTILE_CONT() может возвращать значения между строками.

Например, найдем медианную цену товара в каждой категории (0.5 — это медиана, или 50-й процентиль):

SELECT 

  category,

  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) OVER (PARTITION BY category) AS median_price

FROM products;

Пояснения:

• PARTITION BY category — делит данные на группы по категориям товаров;
• ORDER BY price — сортирует товары по цене;
• PERCENTILE_CONT(0.5) — ищет медиану (50-й процентиль) в каждой группе.

Результат:

Для Electronics медиана — 700 (оно есть в данных). Для Furniture медиана — 450, хотя такого значения в таблице нет. SQL рассчитал медиану как среднее между ближайшими ценами (300 и 600).

PERCENTILE_DISC()

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

Пример:

SELECT 

  category,

  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY price) OVER (PARTITION BY category) AS median_price_disc

FROM products;

Пояснения:

• PARTITION BY category — делит данные на группы по категориям товаров;
• ORDER BY price — сортирует товары по цене;
• PERCENTILE_DISC(0.5) — ищет медиану (50-й процентиль) в каждой группе, возвращая реальное значение из списка.

Результат:

Здесь в категории Furniture медианой становится товар за 300, а не усредненное значение.

Заключение

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

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