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

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

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

Оконные функции можно разделить на 3 группы:

• Агрегатные оконные функции
• Ранжирующие оконные функции
• Оконные функции смещения

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

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

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

MySQL
SELECT id,
	home_type,
	price,
	SUM(price) OVER(PARTITION BY home_type) AS 'Sum',
	COUNT(price) OVER(PARTITION BY home_type) AS 'Count',
	AVG(price) OVER(PARTITION BY home_type) AS 'Avg',
	MAX(price) OVER(PARTITION BY home_type) AS 'Max',
	MIN(price) OVER(PARTITION BY home_type) AS 'Min'
FROM Rooms;

Ранжирующие оконные функции

Ранжирующие оконные функции — это функции, которые ранжируют значение для каждой строки в окне.

В ранжирующих функциях под ключевым словом OVER обязательным идёт указание условия ORDER BY, по которому будет происходить сортировка ранжирования.

• ROW_NUMBER — возвращает номер строки, используется для нумерации;
• RANK — возвращает ранг каждой строки. Вот как это работает:
  • Сортировка: во-первых, строки сортируются по одному или нескольким столбцам. Эти столбцы указываются в ORDER BY в конструкции OVER.
  • Присвоение рангов: каждой уникальной строке или группе строк, имеющих одинаковые значения в столбцах сортировки, присваивается ранг. Ранг начинается с 1.
  • Одинаковые значения: если у нескольких строк одинаковые значения в столбцах сортировки, они получают одинаковый ранг. Например, если две строки занимают второе место, обе получают ранг 2.
  • Пропуск рангов: после группы строк с одинаковым рангом, следующий ранг увеличивается на количество строк в этой группе. Например, если две строки имеют ранг 2, следующая строка получит ранг 4, а не 3.
  • Продолжение сортировки: этот процесс продолжается до тех пор, пока не будут присвоены ранги всем строкам в наборе результатов.
• DENSE_RANK — возвращает ранг каждой строки. Но в отличие от функции RANK, она для одинаковых значений возвращает ранг, не пропуская следующий;

MySQL
SELECT id,
	home_type,
	price,
	ROW_NUMBER() OVER(PARTITION BY home_type ORDER BY price) AS 'row_number',
	RANK() OVER(PARTITION BY home_type ORDER BY price) AS 'rank',
	DENSE_RANK() OVER(PARTITION BY home_type ORDER BY price) AS 'dense_rank'
FROM Rooms;

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

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

• LAG — обращается к данным из предыдущих строк окна.

  Имеет три аргумента: столбец, значение которого необходимо вернуть, количество строк для смещения (по умолчанию 1), значение, которое необходимо вернуть, если после смещения возвращается значение NULL.

• LEAD — обращается к данным из следующих строк. Аналогично LAG имеет 3 аргумента.

• FIRST_VALUE — возвращает первое значение в окне. В качестве аргумента принимает столбец, значение которого необходимо вернуть.

• LAST_VALUE — возвращает последнее значение в окне. В качестве аргумента принимает столбец, значение которого необходимо вернуть

MySQL
SELECT id,
	home_type,
	price,
	LAG(price) OVER(PARTITION BY home_type ORDER BY price) AS 'lag',
	LAG(price, 2) OVER(PARTITION BY home_type ORDER BY price) AS 'lag_2',
	LEAD(price) OVER(PARTITION BY home_type ORDER BY price) AS 'lead',
	FIRST_VALUE(price) OVER(PARTITION BY home_type ORDER BY price) AS 'first_value',
	LAST_VALUE(price) OVER(PARTITION BY home_type ORDER BY price) AS 'last_value'
FROM Rooms;