Оконные функции

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

Вот простой пример оконной функции в SQL, использующий функцию ROW_NUMBER():

SELECT employee_id, salary,
       ROW_NUMBER() OVER (
                    PARTITION BY department_id 
                    ORDER BY salary DESC) 
       AS row_num
FROM employees;

В этом примере мы используем оконную функцию ROW_NUMBER(), чтобы пронумеровать сотрудников в каждом отделе в порядке убывания их зарплат. Фраза OVER (PARTITION BY department_id ORDER BY salary DESC) указывает базе данных на то, как разделить строки на группы (по department_id) и упорядочить их в каждой группе (по убыванию зарплаты).

Оконные функции - первое знакомство

Особенности оконных функций:

Другое название - аналитические оконные функции.
Нужны относительно редко.

Пример оконной функции:

SUM(n_albums) OVER (......)

Какие задачи решают оконные функции?

Сценарий 1: Детальные данные + агрегация

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

Что нужно, сделать, чтобы посчитать?

Нужно посчитать общее количество альбомов.
Далее от общего числа альбомов можно посчитать процент количества альбомов.

    SELECT d.band_id, d.name, d.year, 
	       d.n_albums, a.total,
		   ROUND(d.n_albums * 100.0 / a.total, 2) as pct_albums
      FROM band_extended as d
CROSS JOIN (SELECT SUM(n_albums) as total 
			  FROM band_extended 
			 WHERE band_id IN (93, 192, 303)) as a
     WHERE d.band_id IN (93, 192, 303);

А теперь сделаем тоже самое, только с помощью оконных функций

SELECT d.band_id, d.name, d.year, d.n_albums, 
	   SUM(d.n_albums) OVER() as total,
       ROUND(d.n_albums * 100.00 / SUM(d.n_albums) OVER(), 2) as pct_albums
  FROM band_extended as d
 WHERE d.band_id IN (93, 192, 303);

SELECT d.band_id, d.name, d.year, d.n_albums: Эта часть запроса выбирает столбцы band_id, name, year и n_albums из таблицы band_extended.
SUM(d.n_albums) OVER() as total: В этом выражении SUM(d.n_albums) вычисляет сумму значений столбца n_albums по всем строкам результатов запроса. Фраза OVER() используется для указания, что сумма должна быть вычислена для всех строк (или "окно") результатов запроса. Результат суммы будет добавлен в каждую строку как столбец с именем total.
ROUND(d.n_albums * 100.00 / SUM(d.n_albums) OVER(), 2) as pct_albums: Здесь мы сначала вычисляем процентное отношение столбца n_albums к общей сумме n_albums (с помощью SUM(d.n_albums) OVER()), затем округляем результат до двух десятичных знаков. Результат будет добавлен в каждую строку как столбец с именем pct_albums.
FROM band_extended as d: Этот запрос выбирает данные из таблицы band_extended и использует ее псевдоним d.
WHERE d.band_id IN (93, 192, 303): Это условие фильтрации, которое выбирает только строки, где значение band_id находится в списке (93, 192, 303).

Фраза OVER() в контексте агрегатных функций (например, SUM()) используется для определения "окна", в пределах которого должны быть вычислены агрегатные функции. Если в скобках после OVER() не указано никаких аргументов, то агрегатная функция вычисляется для всех строк запроса.

Добавляем PARTITION BY

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

PARTITION BY - позволяет выполнять агрегацию данных не по всем строкам, а по отдельным группам строк.

Различные функции агрегации:

SUM(n_album) OVER() - сумма
AVG(n_album) OVER() - среднее значение
COUNT(*) OVER() - подсчет количества строк
COUNT(n_album) OVER() - подсчет количества строк, где эта колонка или выражение отличается от NULL
MIN(n_album) OVER() - минимальное значение
MAX(n_album) OVER() - максимальное значение

ПРИМЕР 1 - Количество лет с первого альбома

Задача: для каждого альбома найти количество лет с момента выхода альбома до выхода текущего альбома.

Какой должен получится результат:

!!! Оконные функции работают не с отдельными строками, а вычисляют агрегатные значения для многих строк !!!

Для начала найдем минимальный год выпуска альбома для каждой группы:

SELECT band_id, MIN(year) as min_year
FROM album_small
GROUP BY band_id

Тут 5 строк превратились в 2. Но нам нужно прописать эти данные для всех 5 строк. Поэтому нам тут помогут оконные функции с оператором PARTITION BY

SELECT album_id, name, band_id, year,
MIN(year) OVER(PARTITION BY band_id) as min_year
FROM album_small

SELECT album_id, name, band_id, year: Эта часть запроса выбирает столбцы album_id, name, band_id и year из таблицы album_small.

MIN(year) OVER(PARTITION BY band_id) as min_year: Это выражение использует оконную функцию. Фраза "OVER(PARTITION BY band_id)" говорит СУБД о том, что мы хотим разбить результаты запроса на группы (партиции) по значениям столбца band_id, и для каждой группы вычислить минимальное значение столбца year. Результат этого вычисления будет добавлен в каждую строку как столбец с именем min_year.

FROM album_small: Этот запрос выбирает данные из таблицы album_small.

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

А теперь найдем разницу между первым альбомом и текущим альбомом:

SELECT album_id, name, band_id, year,
MIN(year) OVER(PARTITION BY band_id) as min_year,
year - MIN(year) OVER(PARTITION BY band_id) as years_since_1st
-- добавили эту колонку
FROM album_small
ORDER BY band_id, years_since_1st

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

SELECT d.album_id, d.name, d.band_id, d.year,
       a.min_year, d.year - a.min_year as years_since_1st
FROM album_small as d
INNER JOIN (
            SELECT band_id, MIN(year) as min_year
            FROM album_small
            GROUP BY band_id) as a
ON a.band_id = d.band_id

Кумулятивные суммы (Накопительные суммы, Накопительный итог)

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

В качестве примера возьмем таблицу album_sales и посмотрим альбомы группы Roxette. Для каждого альбома есть сумма продаж конкретного альбома - колонка total_sales.

Теперь посчитаем кумулятивную сумма продаж альбомов - это сумма продаж текущего альбома + всех предыдущих альбомов.

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

Как получились эти цифры:

SELECT album_id, name, band_id, year, total_sales,
       SUM(total_sales) OVER (ORDER BY year
							  ROWS BETWEEN UNBOUNDED PRECEDING
							  AND CURRENT ROW) as cumulative_sales
FROM album_sales
WHERE band_id = 1811

SUM(total_sales) - суммируем значение колонки total_sales

ORDER BY year - нужно для того, чтобы в накопительной сумме понимать, в каком порядке следует отсортировать данные т.к. сортировка строк очень важна.

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW - эта фраза указывает на то, какие строки учувствуют в агрегации данных:

UNBOUNDED PRECEDING - все предыдущие строки
CURRENT ROW - текущая строка

Таким образом, фраза в запросе ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW переводится буквально так: СТРОКИ МЕЖДУ: ВСЕ ПРЕДЫДУЩИЕ И ТЕКУЩАЯ СТРОКА.

Почему же ОКОННЫЕ ФУНКЦИИ называются ОКОННЫМИ?

Когда мы вычисляем оконную функцию, мы используем данные из нескольких строк, так называемое окошко из строк. Это окошко задается с помощью выражения ROWS BETWEEN. А если ROWS BETWEEN не указано, то по умолчанию окно равно всей таблице.

А если в таблице не одна группа, а несколько?

SELECT album_id, name, band_id, year, total_sales,
       SUM(total_sales) OVER (PARTITION BY band_id
							  ORDER BY year
							  ROWS BETWEEN UNBOUNDED PRECEDING
							  AND CURRENT ROW) as cumulative_sales
FROM album_sales
WHERE band_id IN (192, 93)
ORDER BY band_id, year

и тд

!!!ВАЖНО!!! Особенности ORDER BY для оконных функций

Замечания по ORDER BY

1. ORDER BY по ошибке можно забыть

Если бы забыли вписать ORDER BY в запросе с группой Roxette, то мы бы получили случайный вывод:

2. ORDER BY оконной функции может отличаться от финального ORDER BY в конце запроса

SELECT album_id, name, band_id, year, total_sales,
       SUM(total_sales) OVER (ORDER BY year
							  ROWS BETWEEN UNBOUNDED PRECEDING
							  AND CURRENT ROW) as cumulative_sales
FROM album_sales
WHERE band_id = 1811
ORDER BY name

Как происходят 2 сортировки в данном запросе:

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

3. Для проверки удобно указывать финальный ORDER BY таким же, как и в оконной функции

Обязательно ли указывать финальный ORDER BY?

На самом деле не все СУБД поддерживают сортировку из оконной функции. То есть сортировка не гарантирована.

Рассмотрим пример, где не одна, а две оконные функции

SELECT album_id, name, band_id, year, total_sales,
       SUM(total_sales) OVER(
		                ORDER BY year 
		                ROWS BETWEEN UNBOUNDED PRECEDING 
		                AND CURRENT ROW) as cumulative_sales,
	   COUNT(*)         OVER(
		                ORDER BY total_sales DESC 
		                ROWS BETWEEN UNBOUNDED PRECEDING 
		                AND CURRENT ROW) as sales_rank
FROM album_sales
WHERE band_id = 1811

COUNT(*) OVER(ORDER BY total_sales DESC 
		                     ROWS BETWEEN UNBOUNDED PRECEDING 
		                     AND CURRENT ROW) as sales_rank

Данная оконная функция использует функцию COUNT(*) для вычисления "рейтинга продаж" на основе общего объема продаж (total_sales). Оконное выражение ORDER BY total_sales DESC указывает на упорядочивание строк по убыванию значения total_sales.
Для каждой строки будет выполнено вычисление начиная от строки с самым высоким значением total_sales (UNBOUNDED PRECEDING) и заканчивая текущей строкой (CURRENT ROW).
Таким образом, функция COUNT(*) будет подсчитывать количество строк, отсортированных по убыванию total_sales, до текущей строки включительно. Результат этого вычисления будет представлять собой "рейтинг" продаж, где более высокие значения total_sales будут иметь более низкий рейтинг, поскольку их позиция в порядке убывания будет выше.

4. Если нужна сортировка данных, ее нужно указывать явно в конце запроса!

5. Если финальная сортировка не нужна - не указываем финальный ORDER BY

6. Будьте аккуратны со значениями NULL.

7. Желательно делать сортировку в оконных функциях однозначной.

Так как вывод будет меняться в зависимости от дня даже по одному запросу, лучше фиксировать сортировку несколькими условиями. Например, добавим в финальный ORDER BY дополнительные условия

SELECT album_id, name, band_id, year, total_sales,
       SUM(total_sales) OVER (PARTITION BY band_id
							  ORDER BY year
							  ROWS BETWEEN UNBOUNDED PRECEDING
							  AND CURRENT ROW) as cumulative_sales
FROM album_sales
WHERE band_id IN (192, 93)
ORDER BY band_id, year, album_id

Мы добавили сортировку по album_id.

Скользящие средние, предыдущие строки и последующие строки

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

Для вычисления скользящего среднего в SQL можно использовать оконные функции, такие как OVER(), в сочетании с функцией AVG().

Варианты ROWS BETWEEN

Для начала вспомним, как раньше вычисляли кумулятивную сумму:

ORDER BY year
ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW

То есть мы брали все предыдущие строки + текущая строка.

Но так же можно брать те строки, которые нам нужны:

ORDER BY year
ROWS BETWEEN 3 PRECEDING
         AND CURRENT ROW

То есть тут берем три предыдущие строки + текущая строка. Такой подход хорош для вычисления скользящих средних, т.е. когда мы двигаемся с некоторым фиксированным окошком и усредняем несколько предыдущих строк.

Так же можно записать еще и так:

ORDER BY year
ROWS BETWEEN 3 PRECEDING
         AND 3 FOLLOWING

Три предыдущие строки + текущая строка + три последующие строки.

ORDER BY year
ROWS BETWEEN 3 CURRENT ROW
         AND UNBOUNDED FOLLOWING

Текущая трока + все, что дальше

Можно поменять этот запрос, изменив порядок сортировки:

ORDER BY year DESC 
ROWS BETWEEN UNBOUNDED PRECEDING
         AND CURRENT ROW

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

Так же можно составить запрос и без текущей строки:

ORDER BY year
ROWS BETWEEN 2 PRECEDING
         AND 1 PRECEDING

2 предыдущие строки

Пример 1 - Пишем запрос на получение среднего значения двух предыдущих ячеек.

SELECT album_id, name, band_id, year, total_sales,
       ROUND(AVG(total_sales) OVER(ORDER BY year
					          ROWS BETWEEN 2 PRECEDING
					           AND 1 PRECEDING
					  ), 2) as avg2_sales
FROM album_sales
WHERE band_id = 1811
ORDER BY year;

Такое усреднение называется скользящим средним.

Так же можно брать только одну предыдущую строку:

MAX(total_sales) OVER(ORDER BY year
                 ROWS BETWEEN 1 PRECEDING
                          AND 1 PRECEDING)

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

Так как такой частный случай (одна предыдущая строка) встречается чаще других, для нее придумали специальную функцию LAG.

LAG(total_sales, 1) OVER(ORDER BY year)

LAG(total_sales, 1) - в скобках указываем столбец, из которого нужно брать предыдущие значения. А через запятую, количество строк, насколько нужно отступить назад.

Если нужно выбрать одну следующую строку

MAX(total_sales) OVER(ORDER BY year
                 ROWS BETWEEN 1 FOLLOWING
                          AND 1 FOLLOWING)

Или же воспользоваться специальной функцией по типа LAG - LEAD, которая выбирает следующую строку

LEAD(total_sales, 1) OVER(ORDER BY year)

ИНТЕРЕНСЫЙ МОМЕНТ:
Функцию LEAD можно переписать через функцию LAG, поменяв порядок сортировки на противоположный
LAG(total_sales, 1) OVER(ORDER BY year DESC)

ВАЖНЫЙ МОМЕНТ:
Не все базы данных поддерживают LAG и LEAD

Пример 2 - Применение функции LAG

Рядом с датой выходя текущего альбома напишем выход предыдущего

  SELECT album_id, name, band_id, year,
         LAG(year, 1) OVER(ORDER BY year) as previous_year
    FROM album
   WHERE band_id = 1811
ORDER BY year;

А теперь посчитаем, сколько прошло лет с момента выхода предыдущего альбома до текущего.

  SELECT album_id, name, band_id, year,
         LAG(year, 1) OVER(ORDER BY year) as previous_year,
		 year - LAG(year, 1) OVER(ORDER BY year) as year_diff
    FROM album
   WHERE band_id = 1811
ORDER BY year;

Нумерация и ранжирование

Допустим, нам нужно пронумеровать альбомы по суммам продаж. Для самого продаваемого 1, для менее продаваемого 2 и тд. На примере группы TESLA.

SELECT * FROM album_sales
WHERE band_id = 2454
ORDER BY total_sales DESC

ROWS_NUMBER() - Нумерация

Чтобы выполнить такую нумерацию, есть специальная оконная функция ROW_NUMBER()

SELECT a.*,
       ROW_NUMBER() OVER(ORDER BY total_sales DESC, album_id) as rn
FROM album_sales as a
WHERE band_id = 2454
ORDER BY total_sales DESC, album_id

ROW_NUMBER() OVER(ORDER BY total_sales DESC, album_id) as rn:

ORDER BY total_sales DESC, album_id - нужно указать обязательно, потому что нумерация будет разная в зависимости от того, как мы отсортируем данные. Сортируем по album_id потому что третья и четвертая строка имеют одну и ту же сумму продаж.

RANK()

Для того, чтобы четко понимать, кому достанется третье место по продажам (2 альбома с одинаковым количеством продаж), используем функцию RANK()

SELECT a.*,
       ROW_NUMBER() OVER(ORDER BY total_sales DESC, album_id) as rn,
	   RANK() OVER(ORDER BY total_sales DESC) as rank
FROM album_sales as a
WHERE band_id = 2454
ORDER BY total_sales DESC, album_id

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

В нашем примере у третьего и четвертого альбомов функция RANK() вернула 2 третьих значения и потом сразу пятое.

DENSE_RANK()

Но если мы хотим, чтобы нумерация шла как положено в функции RANK() и не было пропусков - надо использовать функцию DENSE_RANK()

SELECT a.*,
       ROW_NUMBER() OVER(ORDER BY total_sales DESC, album_id) as rn,
	   RANK() OVER(ORDER BY total_sales DESC) as rank,
	   DENSE_RANK() OVER(ORDER BY total_sales DESC) as d_rank
FROM album_sales as a
WHERE band_id = 2454
ORDER BY total_sales DESC, album_id