Оконные функции в SQL: что это и зачем они нужны
2026-02-21 01:46 Diff
https://skillbox.ru/media/code/okonnye-funktsii-v-sql-chto-eto-i-zachem-oni-nuzhny/

#статьи

  • 26 июн 2024
  • 0

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

Иллюстрация: Оля Ежак для Skillbox Media

Программист, консультант, специалист по документированию. Легко и доступно рассказывает о сложных вещах в программировании и дизайне.

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

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

Всё самое важное об оконных функциях в SQL:

ЭКСПЕРТ

Разработчик-фрилансер. Занимается iOS-разработкой с 2011 года. Вела курс по Swift в Астраханском государственном университете. Программный директор и эксперт по мобильной разработке в Skillbox.

С 2016 года также занималась управлением проектами в мобильной разработке.

Создадим таблицу sport_sales, содержащую продажи магазина спортивных товаров по отделам. Включим в неё следующие поля:

  • id — уникальный идентификатор каждой записи;
  • sale_date — дата продаж по каждому отделу;
  • department — наименование отдела;
  • sales — количество продаж по каждому отделу за день.

Таблицу можно создать с помощью следующего запроса:

CREATE TABLE IF NOT EXISTS sport_sales ( id integer primary key, sale_date date, department varchar(50), sales integer );

А для заполнения таблицы данными используем следующий код:

insert into sport_sales (id, sale_date, department, sales) values (1, 2024-02-01, 'Shoes', 6), (2, 2024-02-01, Clothing', 5), (3, 2024-02-01, Equipment', 10), (4, 2024-02-02, Clothing', 6), (5, 2024-02-02, Clothing', 6), (5, 2024-02-02, Equipment', 4), (6, 2024-02-01, 'Shoes', 4), (7, 2024-02-03, 'Shoes', 8), (8, 2024-02-03, Equipment', 10), (9, 2024-02-01, Clothing', 4);

В результате получим вот такую таблицу базы данных, будем использовать её:

idsale_datedepartmentsales12024-02-01Shoes622024-02-01Clothing532024-02-01Equipment1042024-02-02Clothing652024-02-02Equipment462024-02-02Shoes472024-02-03Shoes882024-02-03Equipment1092024-02-03Clothing4

?️ Технические детали

Синтаксис оконных функций может различаться в разных реализациях SQL. Некоторые функции могут называться иначе или их может не быть вовсе. Запросы в этой статье показаны на примере PL\SQL — диалекта, который используется в базах данных под управлением Oracle DB.

В общем виде синтаксис оконных функций выглядит так:

<window_function>(arguments) OVER ([Partitioning] [Ordering] [Frame])

Детально разберём структуру:

  • <window_function_name> — имя оконной функции, например ROW_NUMBER(), RANK(), DENSE_RANK() и другие.
  • (arguments) — аргументы функции, например имя столбца, по которому происходит расчёт.
  • OVER() — ключевое слово, которое определяет, как оконная функция будет применятся к набору данных.
  • [Partitioning] — определяет критерий, по которому строки делятся на подгруппы. Это необязательный компонент.
  • [Ordering] — указывает порядок строк в каждой подгруппе, что важно для таких функций, как RANK(), ROW_NUMBER() и других. Этот элемент тоже необязателен.
  • [Frame] — задаёт «фрейм» строк относительно текущей. Как правило, используется с ключевыми словами ROWS или RANGE.

Рассмотрим подробнее эти компоненты.

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

Например, рассмотрим, как работает запрос с группировкой GROUP BY, считающий суммарные продажи за период по каждому отделу:

SELECT department, sum(sales) AS sum_sales FROM sport_sales GROUP BY department ORDER BY department;

В результате получим:

departmentsum_salesClothing15Equipment24Shoes18

Этот запрос выводит в столбце sum_sales сумму продаж по каждому отделу.

Теперь используем оконную функцию:

SELECT id, sale_date, department, sales, sum(sale) OVER(PARTITION BY department) AS sum_sales FROM sport_sales4 ORDER BY department, sale_date;

Результат будет ��аким:

idsale_datedepartmentsalessum_sales22024-02-01Clothing51542024-02-02Clothing61592024-02-03Clothing41532024-02-01Equipment102452024-02-02Equipment42482024-02-03Equipment102412024-02-01Shoes61862024-02-02Shoes41872024-02-03Shoes818

Этот запрос выводит продажи каждого отдела в столбце sales и суммарные продажи в столбце sum_sales. Все строки из исходной таблицы сохраняются, причём они сгруппированы по отделам.

Ключевое слово ORDER BY определяет, как данные будут упорядочены при применении оконной функции.

Для определения порядка строк используются ключевые слова ASC и DESC:

  • ASC — сортировка по возрастанию. Это значение по умолчанию. Упорядочивание от наименьшего значения к наибольшему.
  • DESC — сортировка по убыванию. Упорядочивание от наибольшего значения к наименьшему.

ORDER BY может включать несколько колонок. Например, ORDER BY sale_date ASC, sale DESC сначала упорядочивает данные отделов по датам в порядке возрастания, а затем по продажам в порядке убывания.

Составим запрос с использованием функции RANK(), которая присваивает ранг каждой строке в зависимости от значения в столбце sale. Отделу с наибольшими продажами за день присваивается ранг 1, с наименьшими — ранг 3. Значения столбца sale_date сгруппируем по возрастанию, а sales — по убыванию:

SELECT id, sale_date, department, sales, RANK() OVER(PARTITION BY sale_date ORDER BY sale_date ASC, sales DESC) AS rank FROM sport_sales;

Вот какую таблицу получим в итоге:

idsale_datedepartmentsalesrank32024-02-01Equipment10112024-02-01Shoes6222024-02-01Clothing5342024-02-02Clothing6152024-02-02Equipment4262024-02-02Shoes4282024-02-03Equipment10172024-02-03Shoes8292024-02-03Clothing43

Ключевые слова ROWS и RANGE определяют, какие строки окна будут учитываться при выполнении расчётов. Они устанавливают «фрейм» окна — набор строк относительно текущей строки, который будет использоваться для вычислений.

ROWS задаёт границы окна в пределах определённого количества строк до или после текущей строки. Например, ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING указывает, что окно включает строку перед текущей, текущую и строку после. На их основании будет вычисляться функция.

Напишем запрос, который суммирует продажи в текущей, предыдущей и последующей строках и размещает полученные суммы в столбце sum_sales:

SELECT id, sale_date, department, sales, SUM(sale) OVER (ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_sales FROM sport_sales;

В результате получим:

idsale_datedepartmentsalessum_sales12024-02-01Shoes61122024-02-01Clothing52132024-02-01Equipment102142024-02-02Clothing62052024-02-02Equipment41462024-02-02Shoes41672024-02-03Shoes82282024-02-03Equipment102292024-02-03Clothing414

В таблице выше данные сгруппированы по датам, и в столбце sum_sales считаются так:

  • Когда текущая — первая строка, суммируются продажи из первой и второй строки.
  • Когда текущая — вторая строка, суммируются продажи из первой, второй и третьей строки и так далее.

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

То есть, когда вы используете RANGE вместе с ORDER BY, SQL обрабатывает окно для каждой строки, включая в него всё с соответствующими или сопоставимыми значениями столбца, указанного в ORDER BY. Это значит, что, если есть несколько строк с одинаковыми значениями в сортируемом столбце (например, одинаковые даты или отделы), они все будут включены в окно.

Например, рассчитаем кумулятивную (накапливающуюся) сумму продаж по датам. Для этого напишем следующий запрос:

SELECT id, sale_date, department, sales, SUM(sale) OVER ( ORDER BY sale_date RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS cumulative_sales FROM sport_sales;

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

В результате получим:

idsale_datedepartmentsalescumulative_sales12024-02-01Shoes62122024-02-01Clothing52132024-02-01Equipment102142024-02-02Clothing63552024-02-02Equipment43562024-02-02Shoes43572024-02-03Shoes85782024-02-03Equipment105792024-02-03Clothing457

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

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

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

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

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

SELECT id, sale_date, department, sales, AVG(sales) OVER (PARTITION BY sale_date) as avg_s, SUM(sales) OVER (PARTITION BY sale_date) as sum_s, MAX(sales)OVER (PARTITION BY sale_date) as max_s, MIN(sales)OVER (PARTITION BY sale_date) as min_s, COUNT(sales) OVER (PARTITION BY sale_date) as count_s FROM sport_sales;

В результате получим следующую таблицу:

idsale_
datedepartmentsaleavg_ssum_smax_smin_scount_s12024-02-01Shoes67.021105322024-02-01Clothing57.021105332024-02-01Equipment107.021105342024-02-02Clothing64.71464352024-02-02Equipment44.71464362024-02-02Shoes44.71464372024-02-03Shoes87.322104382024-02-03Equipment107.322104392024-02-03Clothing47.3221043

Важно отметить, что агрегатные функции чувствительны к NULL. К примеру, AVG() и SUM() игнорируют такие значения, а COUNT() — считает все строки, даже с NULL.

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

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

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

В примере ниже SQL-запрос присваивает строкам ранги в порядке возрастания числа продаж. Если количество продаж одинаковое (id 5 и 6), то будет присвоен ранг 1, а следующей по порядку строке (id 4) — ранг 3:

SELECT id, sale_date, department, sales, RANK() OVER (PARTITION BY sale_date ORDER BY sales) AS rank_s FROM sport_sales;

Так выглядит таблица с результатом запроса:

idsale_datedepartmentsalesrank_s22024-02-01Clothing5112024-02-01Shoes6232024-02-01Equipment10352024-02-02Equipment4162024-02-02Shoes4142024-02-02Clothing6392024-02-03Clothing4172024-02-03Shoes8282024-02-03Equipment103

Функция DENSE_RANK(). Похожа на RANK(), но работает «плотнее». Это значит, что следующий ранг увеличивается на 1, независимо от количества строк с одинаковым рангом.

Напишем простой запрос с использованием функции DENSE_RANK():

SELECT id, sale_date, department, sales, DENSE_RANK() OVER (PARTITION BY sale_date ORDER BY sales) AS dn_rank_s FROM sport_sales;

В этом примере SQL-запрос также присваивает строкам ранг в порядке возрастания количества продаж. Если количество продаж одинаково (id 5 и 6), то будет присвоен ранг 1, а следующей по порядку строке (id 4) — ранг 2. Вот как это отражается в таблице:

idsale_datedepartmentsalesdn_rank_s22024-02-01Clothing5112024-02-01Shoes6232024-02-01Equipment10352024-02-02Equipment4162024-02-02Shoes4142024-02-02Clothing6292024-02-03Clothing4172024-02-03Shoes8282024-02-03Equipment103

Функция ROW_NUMBER(). Присваивает уникальный порядковый номер каждой строке в разделе окна.

SQL-запрос выглядит следующим образом:

SELECT id, sale_date, department, sale, ROW_NUMBER() OVER (PARTITION BY sale_date ORDER BY department) AS row_n FROM sport_sales;

В результате все данные рассортированы по датам продаж и отмечены уникальными номерами:

idsale_datedepartmentsalesrow_n22024-02-01Clothing5132024-02-01Equipment10212024-02-01Shoes6342024-02-02Clothing6152024-02-02Equipment4262024-02-02Shoes4392024-02-03Clothing4182024-02-03Equipment10272024-02-03Shoes83

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

Основные функции смещения:

Функция LEAD(). Позволяет заглянуть вперёд на определённое количество строк от текущей и получить значение из столбца в этих строках.

К примеру, запрос может выглядеть следующим образом:

SELECT id, sale_date, department, sales, LEAD(sales, 1) OVER (PARTITION BY department ORDER BY sale_date) AS next_day_s FROM sport_sales;

В результате для каждого отдела запрос возвращает продажи текущего дня (sales) и следующего (next_day_s):

idsale_datedepartmentsalesnext_day_s22024-02-01Clothing5642024-02-02Clothing6492024-02-03Clothing4None32024-02-01Equipment10452024-02-02Equipment41082024-02-03Equipment10None12024-02-01Shoes6462024-02-02Shoes4872024-02-03Shoes8None

Функция LAG(). Похожа на LEAD(), но, вместо того чтобы «заглядывать вперёд», эта функция «смотрит назад» на определённое количество строк.

Здесь в каждой строке окна столбец sales покажет продажи текущего, а столбец last_day_s — продажи предыдущего дня для каждого отдела:

idsale_datedepartmentsaleslast_day_s22024-02-01Clothing5None42024-02-02Clothing6592024-02-03Clothing4632024-02-01Equipment10None52024-02-02Equipment41082024-02-03Equipment10412024-02-01Shoes6None62024-02-02Shoes4672024-02-03Shoes84

Функции FIRST_VALUE() и LAST_VALUE(). Эти функции возвращают первое и последнее значение столбца в окне соответственно.

С помощью вот такого запроса можно вывести продажи текущего дня и продажи за первое и третье число для каждого отдела:

SELECT id, sale_date, department, sale, FIRST_VALUE(sale) OVER (PARTITION BY department ORDER BY sale_date) AS first_sale, LAST_VALUE(sale) OVER (PARTITION BY department ORDER BY sale_date) AS last_sale FROM sport_sales;

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

idsale_datedepartmentsalesfirst_salelast_sale22024-02-01Clothing55442024-02-02Clothing65492024-02-03Clothing45432024-02-01Equipment10101052024-02-02Equipment4101082024-02-03Equipment10101012024-02-01Shoes66862024-02-02Shoes46872024-02-03Shoes868

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

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

Data Science с нуля: пробуем профессии на практике за 5 дней

Вы разберётесь в трёх главных направлениях data science: машинном обучении, разработке на Python и визуализации данных. Решите, какая сфера вам ближе, и выполните 4 реальные задачи с данными.

Пройти бесплатно

Попробуйте data science на бесплатном курсе
Пройдите курс по data science и изучите 3 направления в работе с данными. Решите, в какой сфере хотите развиваться дальше, и получите ценные подарки. Пройти курс →