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

Введение

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

Вы начнете с создания примерной таблицы employees, а затем определите простое CTE для выбора сотрудников из отдела 'Sales'. Это продемонстрирует базовый синтаксис и использование CTE для разбиения сложных запросов на более мелкие и управляемые части.

Skills Graph

Определение простого CTE с использованием предложения WITH

На этом этапе вы научитесь определять простое Общее табличное выражение (Common Table Expression, CTE) с использованием предложения WITH в MySQL. CTE - это временные именованные наборы результатов, на которые можно ссылаться в рамках одного оператора SELECT, INSERT, UPDATE или DELETE. Они полезны для разбиения сложных запросов на более мелкие и управляемые части, что улучшает читаемость и поддерживаемость кода.

Понимание CTE

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

Создание примерной таблицы

Сначала создадим простую таблицу с именем employees для работы. Выполните следующие SQL - инструкции в своей среде MySQL. Вы можете открыть командную строку MySQL, просто введя mysql в терминале.

mysql

Затем создайте таблицу:

CREATE TABLE 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);

Теперь выйдите из клиента MySQL:

exit

Определение простого CTE

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

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

Пояснение:

• WITH SalesEmployees AS (...): Это определяет CTE с именем SalesEmployees.
• SELECT employee_id, first_name, last_name, salary FROM employees WHERE department = 'Sales': Это подзапрос, который выбирает информацию о сотрудниках из таблицы employees, где отдел - 'Sales'.
• SELECT employee_id, first_name, last_name, salary FROM SalesEmployees: Это основной запрос, который выбирает все столбцы из CTE SalesEmployees.

Выполнение запроса

Для выполнения запроса вы можете сохранить его в файл с именем sales_employees.sql в каталоге ~/project с помощью nano:

nano ~/project/sales_employees.sql

Вставьте SQL - код в файл, сохраните его (Ctrl+O) и выйдите (Ctrl+X).

Затем выполните SQL - файл с использованием командной строки MySQL:

mysql < ~/project/sales_employees.sql

Ожидаемый результат:

Результатом должен быть таблица, содержащая идентификатор сотрудника, имя, фамилию и зарплату всех сотрудников из отдела 'Sales':

+-------------+------------+-----------+----------+
| 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 для фильтрации данных из таблицы. В следующих шагах вы научитесь более продвинутым техникам, таким как рекурсивные CTE и объединение CTE с таблицами.

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

На этом этапе вы научитесь писать рекурсивное Общее табличное выражение (Common Table Expression, CTE) для запроса иерархических данных в MySQL. Рекурсивные CTE особенно полезны для обхода древовидных структур, таких как организационные схемы, файловые системы или иерархии категорий.

Понимание рекурсивных CTE

Рекурсивное CTE - это CTE, которое ссылается на себя. Оно состоит из двух частей:

1. Аккордный член (Anchor Member): Это базовый случай, который инициализирует CTE. Это обычное выражение SELECT, которое определяет начальную точку рекурсии.
2. Рекурсивный член (Recursive Member): Эта часть ссылается на само CTE, позволяя CTE итерироваться по иерархическим данным. Он объединяется с аккордным членом с помощью UNION ALL.

Создание примерной таблицы

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

CREATE TABLE employee_hierarchy (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES employee_hierarchy(employee_id)
);

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);

В этой таблице:

• employee_id - уникальный идентификатор каждого сотрудника.
• employee_name - имя сотрудника.
• manager_id - employee_id менеджера сотрудника. У верхнего менеджера значение manager_id равно NULL.

Теперь выйдите из клиента MySQL:

exit

Написание рекурсивного CTE

Теперь напишем рекурсивное CTE для извлечения всей иерархии под конкретным менеджером. В этом примере мы извлечем иерархию под Дэвидом (employee_id = 1).

WITH RECURSIVE EmployeeHierarchyCTE AS (
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employee_hierarchy
    WHERE manager_id IS NULL  -- Anchor member: Select the top-level manager

    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  -- Recursive member: Join with the CTE itself
)
SELECT employee_id, employee_name, manager_id, level
FROM EmployeeHierarchyCTE
ORDER BY level, employee_name;

Пояснение:

• WITH RECURSIVE EmployeeHierarchyCTE AS (...): Это определяет рекурсивное CTE с именем EmployeeHierarchyCTE.
• Аккордный член (Anchor Member):
  • SELECT employee_id, employee_name, manager_id, 0 AS level FROM employee_hierarchy WHERE manager_id IS NULL: Это выбирает верхнего менеджера (Дэвида) и присваивает ему уровень 0.
• Рекурсивный член (Recursive Member):
  • 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: Это объединяет таблицу employee_hierarchy с EmployeeHierarchyCTE по manager_id, чтобы найти всех сотрудников, которые подчиняются кому - то в CTE. Он увеличивает уровень для каждого уровня иерархии.
• UNION ALL: Это объединяет результаты аккордного и рекурсивного членов.
• SELECT employee_id, employee_name, manager_id, level FROM EmployeeHierarchyCTE ORDER BY level, employee_name: Это основной запрос, который выбирает все столбцы из EmployeeHierarchyCTE и сортирует результаты по уровню и имени сотрудника.

Выполнение запроса

Для выполнения запроса вы можете сохранить его в файл с именем employee_hierarchy.sql в каталоге ~/project с помощью nano:

nano ~/project/employee_hierarchy.sql

Вставьте SQL - код в файл, сохраните его (Ctrl+O) и выйдите (Ctrl+X).

Затем выполните SQL - файл с использованием командной строки MySQL:

mysql < ~/project/employee_hierarchy.sql

Ожидаемый результат:

Результатом должен быть таблица, показывающая иерархию сотрудников под Дэвидом:

+-------------+---------------+------------+-------+
| 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 для запроса иерархических данных и извлечения всей иерархии под конкретным узлом.

Объединение CTE с таблицей

На этом этапе вы научитесь объединять Общее табличное выражение (Common Table Expression, CTE) с таблицей в MySQL. Объединение CTE с таблицей позволяет комбинировать результаты CTE с данными из другой таблицы, что позволяет выполнять более сложные запросы и анализ.

Понимание объединения CTE

Объединение CTE с таблицей аналогично объединению двух таблиц. Вы задаете условие объединения с использованием ключевого слова JOIN и предложения ON. CTE рассматривается как виртуальная таблица в рамках запроса.

Использование существующих таблиц

Мы продолжим использовать таблицы employees и employee_hierarchy, созданные на предыдущих этапах. Если вы пропустили эти этапы, создайте таблицы с использованием SQL - скриптов, предоставленных на этапах 1 и 2.

Создание CTE для расчета средней зарплаты по отделам

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

WITH AvgSalaryByDepartment AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT * FROM AvgSalaryByDepartment;

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

Объединение CTE с таблицей employees

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

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

Пояснение:

• WITH AvgSalaryByDepartment AS (...): Это определяет CTE, как и ранее.
• SELECT e.employee_id, e.first_name, e.last_name, e.department, e.salary, a.avg_salary FROM employees e JOIN AvgSalaryByDepartment a ON e.department = a.department: Это выбирает информацию о сотрудниках из таблицы employees и среднюю зарплату из CTE AvgSalaryByDepartment. Предложение JOIN соединяет эти две таблицы на основе столбца department.

Выполнение запроса

Для выполнения запроса вы можете сохранить его в файл с именем employee_avg_salary.sql в каталоге ~/project с помощью nano:

nano ~/project/employee_avg_salary.sql

Вставьте SQL - код в файл, сохраните его (Ctrl+O) и выйдите (Ctrl+X).

Затем выполните SQL - файл с использованием командной строки MySQL:

mysql < ~/project/employee_avg_salary.sql

Ожидаемый результат:

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

+-------------+------------+-----------+-----------+----------+------------+
| employee_id | first_name | last_name | department | salary   | avg_salary |
+-------------+------------+-----------+-----------+----------+------------+
|           2 | Jane       | Smith     | Marketing | 75000.00 | 72500.00   |
|           5 | Michael    | Davis     | Marketing | 70000.00 | 72500.00   |
|           4 | Emily      | Brown     | IT        | 90000.00 | 90000.00   |
|           1 | John       | Doe       | Sales     | 60000.00 | 57500.00   |
|           3 | Robert     | Jones     | Sales     | 55000.00 | 57500.00   |
+-------------+------------+-----------+-----------+----------+------------+
5 rows in set (0.00 sec)

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

Тестирование вывода запросов CTE

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

Почему нужно тестировать запросы CTE?

Тестирование запросов CTE необходимо по нескольким причинам:

• Правильность: Гарантирует, что CTE возвращают правильные данные на основе определенной логики.
• Целостность данных: Проверяет, что преобразования и вычисления данных в CTE точны.
• Производительность: Помогает выявить возможные узкие места в производительности запросов CTE.
• Поддержка: Упрощает понимание и поддержку сложных запросов, разбивая их на более мелкие, тестируемые части.

Стратегии тестирования

Существует несколько стратегий для тестирования запросов CTE:

1. Ручной осмотр: Просмотрите вывод запросов CTE и сравните его с ожидаемыми результатами. Это подходит для небольших наборов данных и простых запросов.
2. Автоматизированное тестирование: Напишите скрипты или программы для автоматического выполнения запросов CTE и сравнения вывода с заранее определенными тестовыми случаями. Это более эффективно для больших наборов данных и сложных запросов.
3. Модульное тестирование: Тестируйте отдельные CTE в изоляции, чтобы убедиться, что они работают правильно, прежде чем интегрировать их в более крупные запросы.

Тестирование простого CTE (Этап 1)

На этапе 1 вы создали простой CTE для выбора сотрудников из отдела "Sales". Чтобы протестировать этот запрос, вы можете вручную проверить вывод и убедиться, что он содержит только сотрудников из отдела "Sales".

Выполните следующий запрос еще раз:

mysql < ~/project/sales_employees.sql

Вывод должен быть следующим:

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

Убедитесь, что Джон Доу и Роберт Джонс действительно работают в отделе Sales.

Тестирование рекурсивного CTE (Этап 2)

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

Выполните следующий запрос еще раз:

mysql < ~/project/employee_hierarchy.sql

Вывод должен быть следующим:

+-------------+---------------+------------+-------+
| 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)

Убедитесь, что Дэвид находится на уровне 0, Эмили и Фрэнк подчиняются Дэвиду (уровень 1), а Грейс, Генри, Айви и Джек подчиняются Эмили или Фрэнку (уровень 2).

Тестирование объединения CTE (Этап 3)

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

Выполните следующий запрос еще раз:

mysql < ~/project/employee_avg_salary.sql

Вывод должен быть следующим:

+-------------+------------+-----------+-----------+----------+------------+
| employee_id | first_name | last_name | department | salary   | avg_salary |
+-------------+------------+-----------+-----------+----------+------------+
|           2 | Jane       | Smith     | Marketing | 75000.00 | 72500.00   |
|           5 | Michael    | Davis     | Marketing | 70000.00 | 72500.00   |
|           4 | Emily      | Brown     | IT        | 90000.00 | 90000.00   |
|           1 | John       | Doe       | Sales     | 60000.00 | 57500.00   |
|           3 | Robert     | Jones     | Sales     | 55000.00 | 57500.00   |
+-------------+------------+-----------+-----------+----------+------------+
5 rows in set (0.00 sec)

Убедитесь, что средняя зарплата в отделе Marketing равна (75000 + 70000) / 2 = 72500, средняя зарплата в отделе IT равна 90000, а средняя зарплата в отделе Sales равна (60000 + 55000) / 2 = 57500.

Заключение

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

Резюме

В этом практическом занятии вы научились определять и использовать Общие табличные выражения (Common Table Expressions, CTE) в MySQL. Первый этап заключался в создании примерной таблицы employees, а затем в определении простого CTE с именем SalesEmployees с использованием предложения WITH. Это CTE выбирало сотрудников из отдела "Sales", демонстрируя, как CTE могут разбивать сложные запросы на более мелкие и управляемые части, улучшая читаемость.

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