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

Введение

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

Вы начнете с создания образца набора данных, а затем будете использовать такие функции, как ROW_NUMBER(), SUM(), AVG() и LAG() в сочетании с предложением OVER() для выполнения продвинутого анализа данных. Эта лабораторная работа предоставляет практические пошаговые примеры, которые помогут вам понять и применить эти мощные функции.

Настройка базы данных и таблицы

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

Сначала откройте терминал на вашем рабочем столе.

Подключитесь к серверу MySQL как пользователь root. Поскольку это лабораторная среда, вы можете использовать sudo для подключения без пароля.

sudo mysql -u root

После подключения вы увидите приглашение MySQL (mysql>).

Теперь создайте базу данных company и переключитесь на нее. Предложение IF NOT EXISTS предотвращает ошибку, если база данных уже существует.

CREATE DATABASE IF NOT EXISTS company;
USE company;

Далее создайте таблицу employees. Эта таблица будет хранить идентификатор сотрудника, имя, отдел и зарплату.

CREATE TABLE IF NOT EXISTS employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

Теперь вставьте некоторые образцы данных в таблицу employees.

INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
(1, 'Alice', 'Sales', 60000.00),
(2, 'Bob', 'Marketing', 55000.00),
(3, 'Charlie', 'Sales', 65000.00),
(4, 'David', 'IT', 70000.00),
(5, 'Eve', 'Marketing', 62000.00)
ON DUPLICATE KEY UPDATE
    employee_name = VALUES(employee_name),
    department = VALUES(department),
    salary = VALUES(salary);

Чтобы убедиться, что данные были вставлены правильно, вы можете просмотреть все строки из таблицы employees.

SELECT * FROM employees;

Вывод должен отобразить пять вставленных вами записей:

+-------------+---------------+------------+----------+
| employee_id | employee_name | department | salary   |
+-------------+---------------+------------+----------+
|           1 | Alice         | Sales      | 60000.00 |
|           2 | Bob           | Marketing  | 55000.00 |
|           3 | Charlie       | Sales      | 65000.00 |
|           4 | David         | IT         | 70000.00 |
|           5 | Eve           | Marketing  | 62000.00 |
+-------------+---------------+------------+----------+
5 rows in set (0.00 sec)

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

Ранжирование строк с помощью ROW_NUMBER()

Функция ROW_NUMBER() присваивает уникальное последовательное целое число каждой строке в пределах раздела (partition) набора результатов. Она часто используется для ранжирования и пагинации.

Базовый синтаксис: ROW_NUMBER() OVER (ORDER BY column_name [ASC|DESC])

OVER(): Это предложение определяет окно (набор строк) для функции.
ORDER BY: Это предложение, внутри OVER(), указывает порядок, в котором присваиваются номера строк.

Продолжая работу в оболочке MySQL, вы теперь будете использовать ROW_NUMBER() для ранжирования сотрудников по их зарплате в порядке убывания.

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS salary_rank
FROM
    employees;

Этот запрос добавляет столбец salary_rank, нумеруя сотрудников от самой высокой к самой низкой зарплате.

+---------------+------------+----------+-------------+
| employee_name | department | salary   | salary_rank |
+---------------+------------+----------+-------------+
| David         | IT         | 70000.00 |           1 |
| Charlie       | Sales      | 65000.00 |           2 |
| Eve           | Marketing  | 62000.00 |           3 |
| Alice         | Sales      | 60000.00 |           4 |
| Bob           | Marketing  | 55000.00 |           5 |
+---------------+------------+----------+-------------+
5 rows in set (0.00 sec)

Как вы можете видеть, Дэвид имеет ранг 1, потому что у него самая высокая зарплата. Это демонстрирует, как ROW_NUMBER() может использоваться для создания простого ранжирования.

Расчет накопительной суммы с помощью SUM()

Накопительная сумма (running total) — это сумма последовательности чисел, которая обновляется по мере добавления каждого нового числа. В SQL это можно рассчитать с помощью SUM() OVER().

Синтаксис: SUM(column_name) OVER (ORDER BY column_name [ASC|DESC])

Эта функция суммирует значения столбца в порядке, указанном предложением ORDER BY.

Теперь давайте рассчитаем накопительную сумму зарплат в порядке employee_id.

SELECT
    employee_name,
    salary,
    SUM(salary) OVER (ORDER BY employee_id) AS running_total
FROM
    employees;

Результат покажет зарплату каждого сотрудника и кумулятивную сумму до этой строки.

+---------------+----------+---------------+
| employee_name | salary   | running_total |
+---------------+----------+---------------+
| Alice         | 60000.00 |      60000.00 |
| Bob           | 55000.00 |     115000.00 |
| Charlie       | 65000.00 |     180000.00 |
| David         | 70000.00 |     250000.00 |
| Eve           | 62000.00 |     312000.00 |
+---------------+----------+---------------+
5 rows in set (0.00 sec)

Например, running_total для Боба — это сумма его зарплаты и зарплаты Алисы (60000.00 + 55000.00 = 115000.00). Это полезно для отслеживания кумулятивных метрик, таких как продажи или расходы с течением времени.

Групповые вычисления с PARTITION BY

Предложение PARTITION BY делит набор результатов на разделы (группы) и применяет оконную функцию к каждому разделу независимо. Это полезно для выполнения вычислений внутри определенных категорий.

Синтаксис: function() OVER (PARTITION BY column_name ORDER BY ...)

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

SELECT
    employee_name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept
FROM
    employees;

Этот запрос разделяет данные по department (отделу), а затем ранжирует сотрудников в каждом отделе по зарплате.

+---------------+------------+----------+--------------+
| employee_name | department | salary   | rank_in_dept |
+---------------+------------+----------+--------------+
| David         | IT         | 70000.00 |            1 |
| Eve           | Marketing  | 62000.00 |            1 |
| Bob           | Marketing  | 55000.00 |            2 |
| Charlie       | Sales      | 65000.00 |            1 |
| Alice         | Sales      | 60000.00 |            2 |
+---------------+------------+----------+--------------+
5 rows in set (0.00 sec)

Обратите внимание, что ранжирование начинается заново для каждого отдела. Например, и Ева, и Чарли имеют ранг 1, но в пределах своих соответствующих отделов 'Marketing' и 'Sales'. Это позволяет проводить более детальный анализ по сравнению с глобальным ранжированием.

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

Функция LAG() предоставляет доступ к строке на указанном физическом смещении, которая предшествует текущей строке. Она полезна для сравнения значения в текущей строке со значением в предыдущей строке.

Синтаксис: LAG(expression, offset, default_value) OVER (ORDER BY ...)

expression: Столбец или выражение для получения.
offset: Количество строк, на которое нужно посмотреть назад (по умолчанию 1).
default_value: Значение, которое будет возвращено, если смещение выходит за пределы (например, для первой строки).

Найдем зарплату предыдущего сотрудника в списке, упорядоченном по employee_id.

SELECT
    employee_name,
    salary,
    LAG(salary) OVER (ORDER BY employee_id) AS previous_salary
FROM
    employees;

Этот запрос извлекает зарплату из предыдущей строки. Для первой строки, где предыдущей строки нет, возвращается NULL.

+---------------+----------+-----------------+
| employee_name | salary   | previous_salary |
+---------------+----------+-----------------+
| Alice         | 60000.00 |            NULL |
| Bob           | 55000.00 |        60000.00 |
| Charlie       | 65000.00 |        55000.00 |
| David         | 70000.00 |        65000.00 |
| Eve           | 62000.00 |        70000.00 |
+---------------+----------+-----------------+
5 rows in set (0.00 sec)

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

SELECT
    employee_name,
    salary,
    salary - LAG(salary) OVER (ORDER BY employee_id) AS salary_diff
FROM
    employees;

Этот запрос вычисляет разницу между зарплатой текущего сотрудника и предыдущей.

+---------------+----------+-------------+
| employee_name | salary   | salary_diff |
+---------------+----------+-------------+
| Alice         | 60000.00 |        NULL |
| Bob           | 55000.00 |    -5000.00 |
| Charlie       | 65000.00 |    10000.00 |
| David         | 70000.00 |     5000.00 |
| Eve           | 62000.00 |    -8000.00 |
+---------------+----------+-------------+
5 rows in set (0.00 sec)

Теперь вы попрактиковались в использовании нескольких ключевых оконных функций. Вы можете выйти из оболочки MySQL.

exit;

Резюме

В этой лабораторной работе вы изучили использование оконных функций MySQL. Вы узнали, как присваивать порядковые номера с помощью ROW_NUMBER(), вычислять скользящие суммы с помощью SUM() OVER(), выполнять вычисления для конкретных групп с помощью PARTITION BY и получать доступ к данным из предыдущих строк с помощью LAG().

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