Используйте оконные функции MySQL, чтобы получить больше от ваших данных

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

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

Агрегатные функции используются для возврата одного скалярного значения из набора строк. Некоторые известные агрегатные функции, доступные в MySQL, - это SUM, MIN, MAX, AVG и COUNT. Мы можем использовать эти функции в сочетании с предложением GROUP BY, чтобы получить агрегированное значение.

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

Каковы наиболее популярные оконные функции MySQL?

Ниже перечислены специализированные оконные функции, которые предлагает MySQL:

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

Примеры использования оконных функций в MySQL

Теперь давайте посмотрим, как именно использовать некоторые из упомянутых выше оконных функций.

Создание образцов таблиц базы данных MySQL

Я буду использовать последний экземпляр сервера MySQL с Arctype в качестве клиента SQL. Ниже представлена ​​структура нашей выборочной базы данных:

Мы можем использовать следующий сценарий SQL для создания структуры таблицы с клиентом Arctype:

CREATE TABLE departments (
	dep_id INT (10) AUTO_INCREMENT PRIMARY KEY,
	dep_name VARCHAR (30) NOT NULL,
	dep_desc VARCHAR (150) NULL
);

CREATE TABLE employees (
	emp_id INT (10) AUTO_INCREMENT PRIMARY KEY,
	first_name VARCHAR (20) NOT NULL,
	last_name VARCHAR (25) NOT NULL,
	email VARCHAR (100) NOT NULL,
	phone VARCHAR (20) DEFAULT NULL,
	salary DECIMAL (8, 2) NOT NULL,
	dep_id INT (10) NOT NULL,
	FOREIGN KEY (dep_id) REFERENCES 
		departments (dep_id) 
			ON DELETE CASCADE
			ON UPDATE CASCADE
);

CREATE TABLE evaluations (
    eval_id INT (10) AUTO_INCREMENT PRIMARY KEY,
    emp_id INT (10) NOT NULL,
    eval_date DATETIME NOT NULL,
    eval_name VARCHAR (30) NOT NULL,
    notes TEXT DEFAULT NULL,
    marks DECIMAL (4,2) NOT NULL,
    FOREIGN KEY (emp_id) REFERENCES employees (emp_id)
);

CREATE TABLE overtime (
    otime_id INT (10) AUTO_INCREMENT PRIMARY KEY,
    emp_id INT (10) NOT NULL,
    otime_date DATETIME NOT NULL,
    no_of_hours DECIMAL (4,2) NOT NULL,
    FOREIGN KEY (emp_id) REFERENCES employees (emp_id)
);

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

Сортировать и разбивать результаты на страницы с помощью ROW_NUMBER ()

В нашем примере базы данных таблица сотрудников организована в соответствии с emp_id. Однако, если нам нужно получить отдельный порядковый номер, присвоенный каждой строке, мы можем использовать оконную функцию ROW_NUMBER ().

В следующем примере мы используем функцию ROW_NUMBER () при сортировке каждой строки по размеру заработной платы.

Мы получим следующий результат, если запросим просто с помощью предложения GROUP BY.

SELECT * FROM employees ORDER BY salary DESC;

Мы видим, что порядковый номер был присвоен каждой строке после связывания отдельного номера строки с помощью функции ROW_NUMBER ():

SELECT 
  ROW_NUMBER() OVER( ORDER BY salary DESC) `row_num`,
  first_name,
  last_name,
  salary
FROM
  employees;

РЕЗУЛЬТАТ:

Другое использование функции ROW_NUMBER - для разбивки на страницы. Например, предположим, что нам нужно отобразить сведения о сотрудниках в формате с разбивкой на страницы, при этом каждая страница состоит всего из пяти записей. Этого можно добиться с помощью функции ROW_NUMBER и предложения WHERE, чтобы указать на желаемый набор записей:

WITH page_result AS (
	SELECT
		ROW_NUMBER() OVER( 
			ORDER BY salary DESC
		) `row_num`,
		first_name,
		last_name,
		salary
	FROM
		employees
)
SELECT * FROM page_result WHERE `row_num` BETWEEN 6 AND 10

РЕЗУЛЬТАТ:

Использование PARTITION BY в оконной функции MySQL

Использование предложения PARTITION BY позволяет нам разделить сотрудников по отделам. Следующий запрос можно использовать для получения шкалы заработной платы сотрудников, разделенных по каждому отделу.

SELECT
	dep_name,
	ROW_NUMBER() OVER (
		PARTITION BY dep_name 
        ORDER BY salary DESC
	) `row_num`,
	first_name,
	last_name,
	salary,
	email
FROM 
	employees AS emp
	INNER JOIN departments AS dep
		ON dep.dep_id = emp.dep_id

РЕЗУЛЬТАТ:

Мы можем дополнительно расширить этот запрос, чтобы получить наиболее высокооплачиваемого сотрудника каждого отдела, извлекая строку, в которой row_num равно единице. (Поскольку мы разделили сотрудников по каждому отделу, ROW_NUMBER запускает новую последовательность для каждого раздела)

SELECT
	ROW_NUMBER() OVER (
		ORDER BY dep_name DESC
	) `row_num`, 
	dep_name, 
	first_name,
	last_name,
	salary,
	email
FROM
(
	SELECT
	dep_name,
	ROW_NUMBER() OVER (
		PARTITION BY dep_name 
        ORDER BY salary DESC
	) `row_num`,
	first_name,
	last_name,
	salary,
	email
	FROM 
		employees AS emp
		INNER JOIN departments AS dep
			ON dep.dep_id = emp.dep_id
) AS highest_paid
WHERE
	`row_num` = 1

РЕЗУЛЬТАТ:

Сравнение значений строк с помощью LAG ()

Функция LAG позволяет пользователям получать доступ к предыдущим строкам, используя указанное смещение. Этот вид функции полезен, когда нам нужно сравнить значения предыдущих строк с текущей строкой. В нашем наборе данных есть таблица с именем «оценки», которая включает ежегодные оценки сотрудников. Используя LAG, мы можем определить производительность каждого сотрудника и определить, улучшились они или нет.

Во-первых, давайте напишем запрос к таблице «оценок», чтобы определить основной вывод функции LAG. В этом запросе мы разделим сотрудников по emp_id (идентификатор сотрудника) и упорядочим этот раздел по eval_date (дата оценки).

SELECT 
	emp_id,
	DATE(eval_date) AS `date`,
	eval_name,
	marks,
	LAG(marks) OVER (
		PARTITION BY emp_id ORDER BY eval_date
	) AS previous
FROM
	evaluations;

РЕЗУЛЬТАТ:

Из приведенного выше набора результатов мы видим, что функция LAG возвращает соответствующее предыдущее значение для столбца «метки». Затем нам необходимо дополнительно уточнить этот набор данных, чтобы получить числовой процент для определения производительности сотрудников в годовом исчислении.

WITH emp_evaluations AS (
	SELECT 
		emp_id,
		YEAR(eval_date) AS `year`,
		eval_name,
		marks,
		LAG(marks,1,0) OVER (
        	PARTITION BY emp_id 
            ORDER BY eval_date
        ) AS previous
	FROM
		evaluations
)
SELECT
	emp_id,
	`year`,
	eval_name,
	marks,
	previous,
	IF (previous = 0, '0%',
		CONCAT(ROUND((marks - previous)*100/previous, 2), '%')
	) AS difference
FROM
	emp_evaluations;

В приведенном выше запросе мы определили общее табличное выражение (CTE) для получения результатов начального запроса LAG, называемого emp_evaluations. Есть несколько отличий от исходного запроса.

Во-первых, здесь мы извлекаем только значение года из поля eval_date DATETIME, а во-вторых, мы определили смещение и значение по умолчанию (1 как смещение и 0 как значение по умолчанию) в функции LAG. Это значение по умолчанию будет заполнено, когда нет предыдущих строк, например, в начале каждого раздела.

Затем мы запрашиваем набор результатов emp_evaluations, чтобы вычислить разницу между «отметками» и «предыдущим» столбцом для каждой строки.

Здесь мы определили условие ЕСЛИ, чтобы идентифицировать пустые предыдущие значения (предыдущее = 0) и отображать их как отсутствие разницы (0%) или иным образом вычислять разницу. Без этого условия IF первая строка каждого раздела будет отображаться как нулевое значение. В результате этот запрос предоставит следующий форматированный вывод.

Присвоение рангов строкам с помощью DENSE_RANK ()

Функцию DENSE_RANK можно использовать для присвоения рангов строкам в разделах без каких-либо пробелов. Если целевой столбец имеет одинаковое значение в нескольких строках, DENSE_RANK присвоит одинаковый ранг каждой из этих строк.

В предыдущем разделе мы определили результаты работы сотрудников в годовом исчислении. Теперь предположим, что мы предлагаем бонус наиболее продвинутому сотруднику в каждом отделе. В этом случае мы можем использовать DENSE_RANK для присвоения ранга разнице в производительности сотрудников.

Во-первых, давайте изменим запрос в разделе функции LAG, чтобы создать представление из результирующего набора данных. Поскольку нам просто нужно запросить (ВЫБРАТЬ) данные здесь, представление MySQL было бы идеальным решением. Мы изменили оператор SELECT в emp_evaluations, чтобы включить в него соответствующий отдел, имя и фамилию, объединив таблицы оценок, сотрудников и отделов.

CREATE VIEW emp_eval_view AS
	WITH emp_evaluations AS (
		SELECT 
			eval.emp_id AS `empid`,
			YEAR(eval.eval_date) AS `eval_year`,
			eval.eval_name AS `evaluation`,
			eval.marks AS `mark`,
			LAG(eval.marks,1,0) OVER (
				PARTITION BY eval.emp_id 
                ORDER BY eval.eval_date
			) AS `previous`,
			dep.dep_name AS `department`,
			emp.first_name AS `first_name`,
			emp.last_name AS `last_name`
		FROM
			evaluations AS eval
			INNER JOIN employees AS emp ON emp.emp_id = eval.emp_id
			INNER JOIN departments AS dep ON dep.dep_id = emp.dep_id
	)
	SELECT
		empid,
		first_name,
		last_name,
		department,
		`eval_year`,
		evaluation,
		mark,
		previous,
		IF (previous = 0, '0%',
			CONCAT(ROUND((mark - previous)*100/previous, 2), '%')
		) AS difference
	FROM
		emp_evaluations;

РЕЗУЛЬТАТ:

Затем, используя это представление (emp_eval_view), мы используем функцию DENSE_RANK для присвоения ранга каждой строке, разделенной по отделам и упорядоченной по разнице в порядке убывания. Кроме того, мы выбираем только записи, относящиеся к указанному году (eval_year = 2020).

SELECT
	empid,
	first_name,
	last_name,
	department,
	`eval_year`,
	evaluation,
	difference AS 'improvement',
	DENSE_RANK() OVER (
		PARTITION BY Department
		ORDER BY Difference DESC
	) AS performance_rank
FROM 
	emp_eval_view 
WHERE 
	`eval_year` = 2020

РЕЗУЛЬТАТ:

Наконец, мы можем отфильтровать приведенный выше набор результатов, чтобы определить наиболее результативного сотрудника в каждом отделе, используя предложение WHERE для получения первой записи рейтинга (performance_rank = 1), как показано ниже.

SELECT *
FROM (
	SELECT
		empid,
		first_name,
		last_name,
		department,
		`eval_year`,
		evaluation,
		difference AS 'improvement',
		DENSE_RANK() OVER (
			PARTITION BY Department
			ORDER BY Difference DESC
		) AS performance_rank
	FROM 
		emp_eval_view 
	WHERE 
		`eval_year` = 2020
) AS yearly_performance_data
WHERE 
	performance_rank = 1

РЕЗУЛЬТАТ:

Как видно из приведенного выше набора результатов, компания может использовать эту функцию DENSE_RANK для выявления наиболее эффективных или недостаточно эффективных сотрудников и отделов. Эти виды показателей имеют решающее значение для процессов бизнес-аналитики, и вся заслуга в MySQL Windows-функциях.

Используйте FIRST_VALUE и LAST_VALUE (), чтобы получить первое и последнее значения из раздела

Функция FIRST_VALUE позволяет пользователям получать первое значение из упорядоченного раздела, в то время как LAST_VALUE получает противоположное, последнее значение набора результатов. Эти функции можно использовать для нашего набора данных, чтобы определить сотрудников, которые работали сверхурочно в каждом отделе с наименьшей или большей частью.

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

В следующем операторе SQL мы определили общее табличное выражение для расчета сверхурочных, затраченных каждым сотрудником за каждый месяц, с использованием агрегатной функции SUM. Затем, используя оконную функцию FIRST_VALUE, мы получаем объединенные данные (имя и фамилия со значением сверхурочной работы) сотрудника, который отработал меньше всего сверхурочных в определенном отделе. Это разделение выполняется с помощью оператора PARTITION BY.

WITH overtime_details AS (
	SELECT
		MONTHNAME(otime.otime_date) AS `month`,
		dep.dep_name AS `dep_name`,
		emp.emp_id AS `emp_id`,
		emp.first_name AS `first_name`,
		emp.last_name AS `last_name`,
		SUM(otime.no_of_hours) AS `overtime`
	FROM
		overtime AS otime
		INNER JOIN employees AS emp ON emp.emp_id = otime.emp_id
		INNER JOIN departments AS dep ON dep.dep_id = emp.dep_id
	GROUP BY `month`, emp.emp_id
	ORDER BY `month`, emp.emp_id ASC
)
SELECT
	dep_name,
	emp_id,
	first_name,
	last_name,
	`month`,
	overtime,
	FIRST_VALUE (CONCAT(first_name,' ',last_name,' - ',overtime)) OVER (
			PARTITION BY dep_name
			ORDER BY overtime
		) least_overtime
FROM 
	overtime_details;

Это даст набор результатов, аналогичный приведенному ниже, с указанием сотрудника, который сделал меньше всего с течением времени.

LAST_VALUE ()
Мы можем использовать оконную функцию LAST_VALUE, чтобы получить сотрудника, который отработал больше всего сверхурочных в каждом отделе. Синтаксис и логика идентичны оператору SQL FIRST_VALUE, но с добавлением «предложения кадра» для определения подмножества текущего раздела, в котором должна применяться функция LAST_VALUE.

Мы используем:

RANGE BETWEEN 
	UNBOUNDED PRECEDING AND 
	UNBOUNDED FOLLOWING

в качестве фреймового предложения. По сути, это информирует ядро ​​базы данных о том, что кадр начинается с первой строки и заканчивается последней строкой набора результатов. (В нашем запросе это относится к каждому разделу)

WITH overtime_details AS (
	SELECT
		MONTHNAME(otime.otime_date) AS `month`,
		dep.dep_name AS `dep_name`,
		emp.emp_id AS `emp_id`,
		emp.first_name AS `first_name`,
		emp.last_name AS `last_name`,
		SUM(otime.no_of_hours) AS `overtime`
	FROM
		overtime AS otime
		INNER JOIN employees AS emp ON emp.emp_id = otime.emp_id
		INNER JOIN departments AS dep ON dep.dep_id = emp.dep_id
	GROUP BY `month`, emp.emp_id
	ORDER BY `month`, emp.emp_id ASC
)
SELECT
	dep_name,
	emp_id,
	first_name,
	last_name,
	`month`,
	overtime,
	LAST_VALUE (CONCAT(first_name,' ',last_name,' - ',overtime)) OVER (
			PARTITION BY dep_name
			ORDER BY overtime
			RANGE BETWEEN
				UNBOUNDED PRECEDING AND
				UNBOUNDED FOLLOWING
		) most_overtime
FROM 
	overtime_details;

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

Заключение

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