Несчастные не имеют друзей (Драйден).

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

Главное меню » Базы данных » База данных MySQL » MySQL. Оконные функции

16.09.2019

Описание : в этой статье вы узнаете об оконных функциях MySQL и их полезных приложениях для решения задач аналитических запросов.

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

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

CREATE TABLE sales(
sales_employee VARCHAR(50) NOT NULL,
fiscal_year INT NOT NULL,
sale DECIMAL(14,2) NOT NULL,
PRIMARY KEY(sales_employee,fiscal_year)
);

INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('AndreyEx',2019,50000),
('AndreyEx',2017,44000),
('AndreyEx',2018,35000),
('Angel',2019,35000),
('Angel',2018,30000),
('Angel',2017,28000),
('Master',2019,32000),
('Master',2018,27000),
('Master',2017,25000);

SELECT
*
FROM
sales

Вероятно, легче понять оконные функции, если начать с агрегатных функций.

Агрегирующие функции суммируют данные из нескольких строк в одну строку результатов. Например, следующая функция SUM() возвращает общие продажи всех сотрудников за записанные годы:

SELECT
SUM(sale)
FROM
sales;

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

SELECT
fiscal_year,
SUM(sale)
FROM
sales
GROUP BY
fiscal_year;

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

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

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

SELECT
fiscal_year,
sales_employee,
sale,
SUM(sale) OVER (PARTITION BY fiscal_year) total_sales
FROM
sales;

В этом примере функция SUM() работает как оконная функция, которая работает с набором строк, определенных содержимым предложения OVER. Набор строк, к которым SUM() применяется функция, называется окном.

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

Обратите внимание , что окно функции выполняются на наборе результатов после того, как все JOIN, WHERE, GROUP BY и положения HAVING и до ORDER BY, LIMIT и SELECT DISTINCT.

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

Общий синтаксис вызова оконной функции следующий:

window_function_name(expression)
OVER (
[partition_defintion]
[order_definition]
[frame_definition]
)

В этом синтаксисе:

Сначала укажите имя оконной функции, а затем выражение.
Во-вторых, укажите предложение OVER, которое имеет три возможных элемента: определение раздела, определение порядка и определение фрейма.

Открывающая и закрывающая скобки после предложения OVER являются обязательными, даже без выражения, например:

window_function_name(expression) OVER()

Синтаксис partition_clause

partition_clause разбивает строки на части или перегородки. Два раздела разделены границей раздела.

Функция окна выполняется внутри разделов и повторно инициализируется при пересечении границы раздела.

Синтаксис partition_clause выглядит следующим образом:

PARTITION BY <expression>[{,<expression>...}]

Вы можете указать одно или несколько выражений в предложении PARTITION BY. Несколько выражений разделяются запятыми.

Синтаксис order_by_clause

order_by_clause имеет следующий синтаксис:

ORDER BY <expression> [ASC|DESC], [{,<expression>...}]

Предложение ORDER BY определяет порядок упорядочения строк в разделе. Можно упорядочить данные внутри раздела по нескольким ключам, каждый ключ определяется выражением. Несколько выражений также разделяются запятыми.

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

Синтаксис frame_clause

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

frame_unit {<frame_start>|<frame_between>}

Фрейм определяется относительно текущей строки, что позволяет фрейму перемещаться внутри раздела в зависимости от положения текущей строки в его разделе.

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

frame_start и frame_between определяют границы фрейма.

frame_start cодержит одно из следующих действий:

UNBOUNDED PRECEDING: рамка начинается с первого ряда раздела.
N PRECEDING: физический N строк перед первой текущей строкой. N может быть буквальным числом или выражением, которое оценивается как число.
CURRENT ROW: строка текущего расчета

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

BETWEEN frame_boundary_1 AND frame_boundary_2

Каждый фрейм frame_boundary_1 и frame_boundary_2 может содержать одно из следующего:

frame_start: как упоминалось ранее.
UNBOUNDED FOLLOWING: фрейм заканчивается в последнем ряду раздела.
N FOLLOWING: физический N строк после текущей строки.

Если вы не укажете frame_definition в предложении OVER, то MySQL по умолчанию использует следующий фрейм:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

MySQL. Список оконных функций

В следующей таблице показаны оконные функции в MySQL:

Имя	Описание
CUME_DIST	Вычисляет совокупное распределение значения в наборе значений.
DENSE_RANK	Присваивает ранг каждой строке в своем разделе на основе предложения ORDER BY. Он присваивает одинаковый ранг строкам с одинаковыми значениями. Если две или более строки имеют одинаковый ранг, то в последовательности ранжированных значений не будет пробелов.
FIRST_VALUE	Возвращает значение указанного выражения относительно первой строки в рамке окна.
LAG	Возвращает значение N-й строки перед текущей строкой в разделе. Возвращает NULL, если предшествующей строки не существует.
LAST_VALUE	Возвращает значение указанного выражения относительно последней строки в рамке окна.
LEAD	Возвращает значение N-й строки после текущей строки в разделе. Возвращает NULL, если никакой последующей строки не существует.
NTH_VALUE	Возвращает значение аргумента из N-й строки рамки окна
NTILE	Распределяет строки для каждого раздела окна в указанное количество ранжированных групп.
PERCENT_RANK	Вычисляет процентильный ранг строки в разделе или наборе результатов
RANK	Аналогична функции DENSE_RANK() за исключением того, что в последовательности ранжированных значений есть пробелы, когда две или более строки имеют одинаковый ранг.
ROW_NUMBER	Назначает последовательное целое число каждой строке в своем разделе

В этой статье вы узнали о оконных функциях MySQL и их синтаксисе. В следующих статьях вы узнаете больше о каждой оконной функции и ее приложениях более подробно.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.

Просмотров поста: 1 572