ИТ Блог. Администрирование серверов на основе Linux (Ubuntu, Debian, CentOS, openSUSE)
Суббота, 7 декабря, 2024
Сегодня у нас 1 праздник:
7 декабря 2004 года вышел в свет Thunderbird версии 1.0, хотя его корни уходят в 2003 год, когда он отделился от проекта Mozilla. Изначально программа была призвана разрушить монополию проприетарных почтовых клиентов, предложив альтернативу с открытым исходным кодом, безопасную и настраиваемую.

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

MySQL. Математические функции

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

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одержит одно из следующих действий:

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

BETWEEN frame_boundary_1 AND frame_boundary_2

 

 

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

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

Exit mobile version