Описание : в этой статье вы узнаете об оконных функциях 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 и их синтаксисе. В следующих статьях вы узнаете больше о каждой оконной функции и ее приложениях более подробно.