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

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

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

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

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

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

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

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

MySQL 8.1
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, она не пропускает ранги и после группы одинаковых значений ранг увеличивается на единицу, а не на количество строк. Например, если две строки имеют ранг 2, следующая строка получит ранг 3, а не 4.

MySQL 8.1
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 — возвращает последнее значение в окне. В качестве аргумента принимает столбец, значение которого необходимо вернуть

  При использовании ORDER BY рамки окна по умолчанию устанавливаются от начала партиции до текущей строки (RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). Из-за этого LAST_VALUE будет возвращать значение текущей строки, а не последней строки всей партиции. Чтобы получить действительно последнее значение партиции, необходимо явно расширить границы окна: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

MySQL 8.1
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 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS "last_value"
FROM Rooms;