Оконные функции в SQL: что это и зачем они нужны

Оконные функции в 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);

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

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

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

<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.

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

Читайте также:

Какие базы данных существуют и для чего их используют: SQL, NoSQL, OLAP

PARTITION BY

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

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

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

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

Этот запрос выводит в столбце 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;

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

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

Читайте также:

SQL-запросы: основные команды для управления базами данных

ORDER BY

Ключевое слово 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;

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

ROWS или RANGE

Ключевые слова 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;

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

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

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

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

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

Читайте также:

Что такое SQL: как устроен, зачем нужен и как с ним работать

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

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 начиная от самой первой записи до текущей даты, включая все строки с датами, равными дате текущей.

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

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;

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

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

Читайте также:

NULL, ноль и undefined в SQL: чем различаются и как с ними работать

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

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

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

Функция 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;

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

Функция 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. Вот как это отражается в таблице:

Функция 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;

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

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

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

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

Функция 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):

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

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

Функции 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;

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

Читайте также:

Система управления базами данных: что это такое и зачем она нужна

В заключение

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

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