Общие Табличные Выражения (CTE) в MySQL

Введение

В этой лабораторной работе вы научитесь использовать Общие Табличные Выражения (Common Table Expressions, CTE) в MySQL. CTE помогают упростить сложные запросы, разбивая их на логичные, читаемые шаги. CTE создает временный именованный набор результатов, на который вы можете ссылаться в рамках одного SQL-запроса.

Вы начнете с настройки базы данных и создания простого CTE для фильтрации данных сотрудников. Затем вы изучите рекурсивные CTE для работы с иерархическими данными, такими как организационная структура. Наконец, вы научитесь объединять CTE с таблицей для выполнения более продвинутого анализа данных.

Настройка базы данных и простого CTE

На этом первом шаге вы настроите среду базы данных и определите простое Общее Табличное Выражение (CTE). CTE определяются с использованием предложения WITH и действуют как временное представление (view), которое существует только в течение одного запроса. Это делает сложные запросы более читаемыми и управляемыми.

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

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

sudo mysql -u root

После подключения вы увидите приглашение MySQL (mysql>). Теперь создайте базу данных с именем labex_db и переключитесь на нее.

CREATE DATABASE IF NOT EXISTS labex_db;
USE labex_db;

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

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

INSERT INTO employees (employee_id, first_name, last_name, department, salary) VALUES
(1, 'John', 'Doe', 'Sales', 60000.00),
(2, 'Jane', 'Smith', 'Marketing', 75000.00),
(3, 'Robert', 'Jones', 'Sales', 55000.00),
(4, 'Emily', 'Brown', 'IT', 90000.00),
(5, 'Michael', 'Davis', 'Marketing', 70000.00);

Теперь, когда таблица готова, давайте определим простое CTE для выбора только сотрудников из отдела 'Sales'.

WITH SalesEmployees AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE department = 'Sales'
)
SELECT * FROM SalesEmployees;

Разберем этот запрос:

• WITH SalesEmployees AS (...): Это определяет CTE с именем SalesEmployees.
• Запрос внутри скобок выбирает сотрудников из таблицы employees, где department равен 'Sales'.
• SELECT * FROM SalesEmployees;: Это основной запрос, который извлекает все данные из нашего временного набора результатов SalesEmployees.

Вы должны увидеть следующий вывод, перечисляющий только сотрудников из отдела продаж:

+-------------+------------+-----------+----------+
| employee_id | first_name | last_name | salary   |
+-------------+------------+-----------+----------+
|           1 | John       | Doe       | 60000.00 |
|           3 | Robert     | Jones     | 55000.00 |
+-------------+------------+-----------+----------+
2 rows in set (0.00 sec)

Это подтверждает, что ваше первое CTE работает правильно. Пожалуйста, оставайтесь в оболочке MySQL для следующего шага.

Создание рекурсивного CTE для иерархических данных

Рекурсивные CTE являются мощным инструментом для запросов иерархических данных, таких как организационные структуры или вложенные категории. Рекурсивный CTE ссылается сам на себя для обработки древовидной структуры уровень за уровнем. Он состоит из "якорного члена" (начальная точка) и "рекурсивного члена" (итерация).

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

Сначала, оставаясь в оболочке MySQL, создайте таблицу employee_hierarchy. Эта таблица включает manager_id, который ссылается на employee_id другого сотрудника, создавая иерархию.

CREATE TABLE IF NOT EXISTS employee_hierarchy (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT
);

INSERT INTO employee_hierarchy (employee_id, employee_name, manager_id) VALUES
(1, 'David', NULL),
(2, 'Emily', 1),
(3, 'Frank', 1),
(4, 'Grace', 2),
(5, 'Henry', 2),
(6, 'Ivy', 3),
(7, 'Jack', 3);

В этой структуре 'David' является руководителем высшего уровня, потому что его manager_id равен NULL.

Теперь напишите рекурсивный CTE для обхода этой иерархии. Требуется ключевое слово RECURSIVE.

WITH RECURSIVE EmployeeHierarchyCTE AS (
    -- Якорный член: выбирает руководителя высшего уровня
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employee_hierarchy
    WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсивный член: объединяется сам с собой для поиска подчиненных
    SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
    FROM employee_hierarchy e
    INNER JOIN EmployeeHierarchyCTE h ON e.manager_id = h.employee_id
)
SELECT * FROM EmployeeHierarchyCTE ORDER BY level, employee_name;

Проанализируем этот запрос:

• Якорный член: Первое выражение SELECT находит корень иерархии (где manager_id равен NULL) и присваивает ему level 0.
• Рекурсивный член: Второе выражение SELECT объединяет employee_hierarchy с самим CTE (EmployeeHierarchyCTE). Оно находит всех сотрудников, чьи manager_id совпадают с employee_id, уже находящимся в CTE, и увеличивает level.
• UNION ALL: Этот оператор объединяет результаты из якорного и рекурсивного членов.

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

+-------------+---------------+------------+-------+
| employee_id | employee_name | manager_id | level |
+-------------+---------------+------------+-------+
|           1 | David         |       NULL |     0 |
|           2 | Emily         |          1 |     1 |
|           3 | Frank         |          1 |     1 |
|           4 | Grace         |          2 |     2 |
|           5 | Henry         |          2 |     2 |
|           6 | Ivy           |          3 |     2 |
|           7 | Jack          |          3 |     2 |
+-------------+---------------+------------+-------+
7 rows in set (0.00 sec)

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

Соединение CTE с таблицей

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

Оставаясь в оболочке MySQL, выполните следующий запрос. Он определяет CTE с именем AvgSalaryByDepartment, а затем объединяет его с таблицей employees.

WITH AvgSalaryByDepartment AS (
    SELECT
        department,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT
    e.employee_id,
    e.first_name,
    e.department,
    e.salary,
    a.avg_salary
FROM
    employees e
JOIN
    AvgSalaryByDepartment a ON e.department = a.department;

Вот разбивка:

• CTE AvgSalaryByDepartment рассчитывает среднюю зарплату для каждого отдела.
• Основной запрос затем объединяет таблицу employees (с псевдонимом e) с этим CTE (с псевдонимом a) по столбцу department.
• Это позволяет отображать зарплату каждого сотрудника рядом со средней зарплатой его отдела.

Ожидаемый вывод будет следующим:

+-------------+------------+------------+----------+--------------+
| employee_id | first_name | department | salary   | avg_salary   |
+-------------+------------+------------+----------+--------------+
|           1 | John       | Sales      | 60000.00 | 57500.000000 |
|           2 | Jane       | Marketing  | 75000.00 | 72500.000000 |
|           3 | Robert     | Sales      | 55000.00 | 57500.000000 |
|           4 | Emily      | IT         | 90000.00 | 90000.000000 |
|           5 | Michael    | Marketing  | 70000.00 | 72500.000000 |
+-------------+------------+------------+----------+--------------+
5 rows in set (0.00 sec)

Вы можете вручную проверить результаты. Например, средняя зарплата для отдела 'Sales' составляет (60000 + 55000) / 2 = 57500, что соответствует выводу. Это подтверждает правильность вашего запроса.

Вы успешно объединили CTE с таблицей. Теперь вы можете выйти из оболочки MySQL.

exit;

Резюме

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

Вы научились:

• Определять простой CTE с использованием предложения WITH для фильтрации данных и улучшения читаемости запросов.
• Создавать рекурсивный CTE для навигации и отображения иерархических данных из таблицы.
• Объединять CTE с таблицей для комбинирования агрегированных результатов с подробными данными на уровне строк для более сложного анализа.

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