SQLite CTE Запросы

Введение

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

Skills Graph

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

На этом шаге вы создадите базу данных SQLite и таблицу employees (сотрудники). Эта таблица будет хранить информацию о сотрудниках, которую вы будете использовать на следующих шагах для практики запросов CTE.

Сначала откройте свой терминал в LabEx VM (виртуальной машине). Ваш путь по умолчанию - /home/labex/project.

Теперь давайте создадим базу данных SQLite с именем company.db. Выполните следующую команду, чтобы создать файл базы данных и открыть инструмент командной строки SQLite:

sqlite3 company.db

Вы увидите приглашение, указывающее на то, что вы находитесь внутри оболочки SQLite:

SQLite version 3.x.x
Enter ".help" for usage hints.
sqlite>

Далее создайте таблицу с именем employees для хранения основной информации о сотрудниках. Эта таблица будет иметь четыре столбца: id, name (имя), department (отдел) и salary (зарплата). Введите следующую SQL-команду в командной строке sqlite> и нажмите Enter:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT,
    department TEXT,
    salary INTEGER
);

Эта команда настраивает таблицу employees, где:

• id - это целое число, которое служит первичным ключом для каждого сотрудника.
• name - это текстовое поле для имени сотрудника.
• department - это текстовое поле для отдела сотрудника.
• salary - это целочисленное поле для зарплаты сотрудника.

Вы не увидите никакого вывода, если команда выполнится успешно.

Вставка данных в таблицу

Теперь, когда вы создали таблицу employees (сотрудники), давайте добавим в нее некоторые данные. Мы вставим пять записей о сотрудниках в таблицу.

Вставьте пять записей о сотрудниках в таблицу employees, выполняя эти команды одну за другой в командной строке sqlite>:

INSERT INTO employees (name, department, salary) VALUES ('Alice', 'Sales', 50000);
INSERT INTO employees (name, department, salary) VALUES ('Bob', 'Marketing', 60000);
INSERT INTO employees (name, department, salary) VALUES ('Charlie', 'Sales', 55000);
INSERT INTO employees (name, department, salary) VALUES ('David', 'Engineering', 70000);
INSERT INTO employees (name, department, salary) VALUES ('Eve', 'Marketing', 62000);

Эти команды добавляют пять строк в таблицу employees.

• INSERT INTO employees (name, department, salary) указывает, что вы вставляете данные в столбцы name (имя), department (отдел) и salary (зарплата) таблицы employees.
• VALUES ('Alice', 'Sales', 50000) предоставляет значения для вставки для каждой записи.

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

SELECT * FROM employees;

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

1|Alice|Sales|50000
2|Bob|Marketing|60000
3|Charlie|Sales|55000
4|David|Engineering|70000
5|Eve|Marketing|62000

Этот вывод показывает id, name (имя), department (отдел) и salary (зарплата) для каждой записи. Команда SELECT * извлекает все столбцы из указанной таблицы.

Определение простого CTE (Common Table Expression)

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

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

Базовый синтаксис:

WITH
    cte_name AS (
        SELECT column1, column2
        FROM table1
        WHERE condition
    )
SELECT column1, column2
FROM cte_name
WHERE another_condition;

Давайте определим CTE для выбора сотрудников из отдела 'Sales' (продажи). Выполните следующий SQL-запрос в командной строке sqlite>:

WITH
    SalesEmployees AS (
        SELECT id, name, salary
        FROM employees
        WHERE department = 'Sales'
    )
SELECT id, name, salary
FROM SalesEmployees
WHERE salary > 52000;

Этот запрос сначала определяет CTE с именем SalesEmployees, который выбирает id, name (имя) и salary (зарплата) всех сотрудников в отделе 'Sales' (продажи). Затем основной запрос выбирает id, name (имя) и salary (зарплата) из CTE SalesEmployees, где зарплата больше 52000.

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

3|Charlie|55000

Это показывает, что Чарли - единственный сотрудник в отделе продаж с зарплатой более 52000.

Создание рекурсивного CTE (Common Table Expression)

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

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

• Anchor Member (Якорный элемент): Начальный оператор SELECT, который определяет базовый случай или отправную точку рекурсии.
• Recursive Member (Рекурсивный элемент): Оператор SELECT, который ссылается на сам CTE. Эта часть выполняет рекурсивный шаг, основываясь на результатах предыдущей итерации.

Якорный и рекурсивный элементы объединяются с помощью оператора UNION ALL. Рекурсия продолжается до тех пор, пока рекурсивный элемент не вернет пустой набор результатов.

Сначала давайте создадим таблицу с именем employees_hierarchy (иерархия сотрудников) со следующей структурой:

CREATE TABLE employees_hierarchy (
    id INTEGER PRIMARY KEY,
    name TEXT,
    manager_id INTEGER,
    title TEXT
);

Эта таблица представляет иерархию сотрудников, где manager_id ссылается на id менеджера сотрудника. Выполните приведенную выше команду в командной строке sqlite>.

Затем вставьте несколько примеров данных в таблицу employees_hierarchy:

INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Alice', NULL, 'CEO');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Bob', 1, 'VP of Marketing');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Charlie', 1, 'VP of Sales');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('David', 2, 'Marketing Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Eve', 3, 'Sales Manager');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Frank', 4, 'Marketing Specialist');
INSERT INTO employees_hierarchy (name, manager_id, title) VALUES ('Grace', 5, 'Sales Representative');

Здесь Алиса - генеральный директор (нет менеджера), Боб и Чарли подчиняются Алисе, Дэвид подчиняется Бобу, Ева подчиняется Чарли, Фрэнк подчиняется Дэвиду, а Грейс подчиняется Еве. Выполните приведенные выше команды в командной строке sqlite>.

Теперь давайте создадим рекурсивный CTE для извлечения всей иерархии под Алисой (генеральным директором). Выполните следующий SQL-запрос:

WITH RECURSIVE
    EmployeeHierarchy(id, name, manager_id, title, level) AS (
        -- Anchor member: Select the CEO
        SELECT id, name, manager_id, title, 0 AS level
        FROM employees_hierarchy
        WHERE manager_id IS NULL

        UNION ALL

        -- Recursive member: Select employees reporting to the current level
        SELECT e.id, e.name, e.manager_id, e.title, eh.level + 1
        FROM employees_hierarchy e
        JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
    )
SELECT id, name, title, level
FROM EmployeeHierarchy;

Этот запрос определяет рекурсивный CTE с именем EmployeeHierarchy (ИерархияСотрудников). Якорный элемент выбирает генерального директора (где manager_id равен NULL). Рекурсивный элемент объединяет таблицу employees_hierarchy с CTE EmployeeHierarchy, чтобы найти сотрудников, которые подчиняются сотрудникам, выбранным на предыдущем уровне. Столбец level отслеживает глубину в иерархии.

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

1|Alice|CEO|0
2|Bob|VP of Marketing|1
3|Charlie|VP of Sales|1
4|David|Marketing Manager|2
5|Eve|Sales Manager|2
6|Frank|Marketing Specialist|3
7|Grace|Sales Representative|3

Это показывает всю иерархию сотрудников, где level указывает уровень подчинения.

Интеграция CTE (Common Table Expression) в сложные запросы

На этом шаге вы узнаете, как интегрировать CTE (Common Table Expression) (общее табличное выражение) в более сложные запросы в SQLite. Вы увидите, как использовать несколько CTE в одном запросе.

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

Давайте создадим новую таблицу с именем department_salaries (зарплаты отделов) со следующей структурой:

CREATE TABLE department_salaries (
    department TEXT,
    total_salary INTEGER
);

Эта таблица будет хранить общую зарплату для каждого отдела. Выполните приведенную выше команду в командной строке sqlite>.

Теперь давайте используем CTE для расчета общей зарплаты для каждого отдела и вставим результаты в таблицу department_salaries. Выполните следующий SQL-запрос:

WITH
    DepartmentTotalSalaries AS (
        SELECT department, SUM(salary) AS total_salary
        FROM employees
        GROUP BY department
    )
INSERT INTO department_salaries (department, total_salary)
SELECT department, total_salary
FROM DepartmentTotalSalaries;

SELECT * FROM department_salaries;

Этот запрос сначала определяет CTE с именем DepartmentTotalSalaries (ОбщиеЗарплатыОтделов), который вычисляет общую зарплату для каждого отдела, используя таблицу employees (сотрудники). Затем он вставляет результаты из CTE DepartmentTotalSalaries в таблицу department_salaries. Наконец, он выбирает все данные из таблицы department_salaries, чтобы отобразить результаты.

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

Sales|105000
Marketing|122000
Engineering|70000

Это показывает общую зарплату для каждого отдела.

Итог

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