LabEx
ВходРегистрация

Написание расширенных запросов PostgreSQL

PostgreSQLBeginner
Практиковаться сейчас

Введение

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

Лабораторная работа проведет вас через определение и использование Common Table Expressions (CTE) (общие табличные выражения) для улучшения читаемости и модульности запросов. Кроме того, вы примените оконные функции, такие как ROW_NUMBER, для выполнения вычислений по наборам строк. Наконец, вы освоите группировку и фильтрацию данных с помощью предложений GROUP BY и HAVING, чтобы извлекать значимую информацию из ваших наборов данных.

Написание подзапроса в предложении WHERE

В этом шаге вы узнаете, как использовать подзапрос в предложении WHERE SQL-запроса. Подзапрос, также известный как внутренний запрос или вложенный запрос (nested query), — это запрос, встроенный внутрь другого запроса. Подзапросы используются для возврата данных, которые будут использоваться в основном запросе в качестве условия для дальнейшего ограничения извлекаемых данных.

Понимание подзапросов в предложении WHERE

Подзапрос в предложении WHERE обычно используется для сравнения значения столбца с результатом подзапроса. Подзапрос выполняется первым, и его результат затем используется внешним запросом.

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

SELECT column1, column2
FROM table_name
WHERE column_name IN (SELECT column_name FROM another_table WHERE condition);

Здесь подзапрос (SELECT column_name FROM another_table WHERE condition) возвращает набор значений. Внешний запрос затем выбирает строки из table_name, где column_name находится в этом наборе.

Сценарий:

Предположим, у вас есть две таблицы: employees и departments. Таблица employees содержит информацию о сотрудниках, включая их employee_id, employee_name и department_id. Таблица departments содержит информацию об отделах, включая их department_id и department_name.

Мы хотим найти всех сотрудников, работающих в отделе 'Sales' (продаж).

Шаг 1: Создайте таблицы и вставьте данные

Сначала подключитесь к базе данных PostgreSQL, используя пользователя postgres:

sudo -u postgres psql

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

CREATE TABLE departments (
    department_id SERIAL PRIMARY KEY,
    department_name VARCHAR(50)
);

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

INSERT INTO departments (department_name) VALUES
('Sales'),
('Marketing'),
('Engineering');

Теперь создайте таблицу employees:

CREATE TABLE employees (
    employee_id SERIAL PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INTEGER REFERENCES departments(department_id)
);

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

INSERT INTO employees (employee_name, department_id) VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3),
('Eve', 2);

Шаг 2: Напишите подзапрос

Теперь давайте напишем запрос для поиска всех сотрудников, работающих в отделе 'Sales', используя подзапрос в предложении WHERE.

SELECT employee_name
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name = 'Sales');

Объяснение:

  • Подзапрос (SELECT department_id FROM departments WHERE department_name = 'Sales') выбирает department_id из таблицы departments, где department_name равен 'Sales'. В этом случае он вернет 1.
  • Внешний запрос затем выбирает employee_name из таблицы employees, где department_id находится в наборе, возвращенном подзапросом (который равен 1).

Шаг 3: Выполните запрос и просмотрите результаты

Выполните запрос в вашем терминале psql. Вы должны увидеть следующий вывод:

 employee_name
---------------
 Alice
 Charlie
(2 rows)

Это показывает, что Alice и Charlie — сотрудники, работающие в отделе 'Sales'.

Шаг 4: Использование EXISTS с подзапросами

Другой способ использования подзапросов в предложении WHERE — с оператором EXISTS. Оператор EXISTS проверяет наличие строк в подзапросе. Он возвращает true, если подзапрос возвращает какие-либо строки, и false в противном случае.

Вот пример использования EXISTS для достижения того же результата:

SELECT employee_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.department_id = e.department_id
    AND d.department_name = 'Sales'
);

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

Объяснение:

  • Подзапрос SELECT 1 FROM departments d WHERE d.department_id = e.department_id AND d.department_name = 'Sales' проверяет, есть ли отдел с именем 'Sales', у которого тот же department_id, что и у текущего сотрудника.
  • Если подзапрос возвращает какие-либо строки (что означает, что есть отдел 'Sales' с тем же department_id), оператор EXISTS возвращает true, и выбирается имя сотрудника.

Выполните запрос в вашем терминале psql. Вы должны увидеть тот же вывод, что и раньше:

 employee_name
---------------
 Alice
 Charlie
(2 rows)

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

Определение и использование CTE

В этом шаге вы узнаете, как определять и использовать Common Table Expression (CTE) (общее табличное выражение) в PostgreSQL. CTE — это временный именованный результирующий набор, на который можно ссылаться в пределах одного оператора SELECT, INSERT, UPDATE или DELETE. CTE полезны для разбиения сложных запросов на более простые и читаемые части.

Понимание CTE

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

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

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

Здесь cte_name — это имя, которое вы даете CTE. Оператор SELECT внутри круглых скобок определяет результирующий набор CTE. Внешний оператор SELECT затем запрашивает CTE, как если бы это была обычная таблица.

Сценарий:

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

Шаг 1: Проверьте таблицы и данные

Убедитесь, что таблицы employees и departments существуют и содержат данные из предыдущего шага. Вы можете проверить это, выполнив следующие запросы в вашем терминале psql:

SELECT * FROM departments;
SELECT * FROM employees;

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

Шаг 2: Определите CTE

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

WITH EmployeeDepartments AS (
    SELECT
        e.employee_name,
        d.department_name
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.department_id
)
SELECT employee_name, department_name
FROM EmployeeDepartments;

Объяснение:

  • Предложение WITH EmployeeDepartments AS (...) определяет CTE с именем EmployeeDepartments.
  • Оператор SELECT внутри круглых скобок объединяет таблицу employees (с псевдонимом e) с таблицей departments (с псевдонимом d) по столбцу department_id.
  • Внешний оператор SELECT затем извлекает employee_name и department_name из CTE EmployeeDepartments.

Шаг 3: Выполните запрос и просмотрите результаты

Выполните запрос в вашем терминале psql. Вы должны увидеть следующий вывод:

 employee_name | department_name
---------------+-----------------
 Alice         | Sales
 Bob           | Marketing
 Charlie       | Sales
 David         | Engineering
 Eve           | Marketing
(5 rows)

Это показывает имена всех сотрудников и соответствующие названия их отделов.

Шаг 4: Использование CTE для фильтрации

Вы также можете использовать CTE для фильтрации данных. Например, давайте найдем всех сотрудников, работающих в отделе 'Sales', используя CTE EmployeeDepartments.

WITH EmployeeDepartments AS (
    SELECT
        e.employee_name,
        d.department_name
    FROM
        employees e
    JOIN
        departments d ON e.department_id = d.department_id
)
SELECT employee_name
FROM EmployeeDepartments
WHERE department_name = 'Sales';

Объяснение:

  • Этот запрос аналогичен предыдущему, но он добавляет предложение WHERE к внешнему оператору SELECT, чтобы отфильтровать результаты и включить только сотрудников, работающих в отделе 'Sales'.

Выполните запрос в вашем терминале psql. Вы должны увидеть следующий вывод:

 employee_name
---------------
 Alice
 Charlie
(2 rows)

Это показывает, что Alice и Charlie — сотрудники, работающие в отделе 'Sales'.

Теперь вы успешно определили и использовали CTE для объединения таблиц и фильтрации данных. CTE могут значительно улучшить читаемость и поддерживаемость сложных SQL-запросов.

Применение оконных функций (например, ROW_NUMBER)

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

Понимание оконных функций

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

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

SELECT
    column1,
    column2,
    WINDOW_FUNCTION(column3) OVER (
        [PARTITION BY column4]
        [ORDER BY column5]
    ) AS alias_name
FROM
    table_name;
  • WINDOW_FUNCTION: Имя оконной функции (например, ROW_NUMBER, RANK, SUM, AVG).
  • OVER(): Указывает окно, над которым работает функция.
  • PARTITION BY: Разделяет строки на разделы (partitions), и оконная функция применяется к каждому разделу независимо.
  • ORDER BY: Определяет порядок строк внутри каждого раздела.
  • alias_name: Псевдоним для вычисленного результата оконной функции.

Сценарий:

Продолжая работу с таблицами employees и departments из предыдущих шагов, давайте используем оконную функцию ROW_NUMBER() для присвоения уникального ранга каждому сотруднику в пределах их соответствующих отделов на основе их employee_name.

Шаг 1: Примените оконную функцию ROW_NUMBER()

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

SELECT
    employee_name,
    department_id,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
FROM
    employees;

Объяснение:

  • ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name): Это оконная функция.
    • ROW_NUMBER(): Присваивает уникальное последовательное целое число каждой строке в окне.
    • PARTITION BY department_id: Разделяет строки на разделы на основе department_id. Это означает, что ранжирование будет выполняться отдельно для каждого отдела.
    • ORDER BY employee_name: Указывает порядок, в котором строки в каждом разделе ранжируются. В этом случае сотрудники ранжируются в алфавитном порядке по их employee_name.
  • employee_rank: Это псевдоним, присвоенный результату оконной функции.

Шаг 2: Выполните запрос и просмотрите результаты

Выполните запрос в вашем терминале psql. Вы должны увидеть следующий вывод:

 employee_name | department_id | employee_rank
---------------+---------------+---------------
 Alice         |             1 |             1
 Charlie       |             1 |             2
 Bob           |             2 |             1
 Eve           |             2 |             2
 David         |             3 |             1
(5 rows)

Это показывает имя каждого сотрудника, его идентификатор отдела и его ранг в пределах его отдела. Например, Alice занимает 1-е место в отделе 1, а Charlie - 2-е место в отделе 1.

Шаг 3: Использование оконных функций с CTE

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

WITH RankedEmployees AS (
    SELECT
        employee_name,
        department_id,
        ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY employee_name) AS employee_rank
    FROM
        employees
)
SELECT
    employee_name,
    department_id,
    employee_rank
FROM
    RankedEmployees;

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

Выполните запрос в вашем терминале psql. Вы должны увидеть тот же вывод, что и раньше.

Группировка и фильтрация с помощью GROUP BY и HAVING

В этом шаге вы узнаете, как использовать предложения GROUP BY и HAVING в PostgreSQL для группировки строк и фильтрации сгруппированных результатов. Предложение GROUP BY группирует строки с одинаковыми значениями в указанных столбцах в сводные строки, например, для нахождения количества сотрудников в каждом отделе. Предложение HAVING используется для фильтрации этих сгруппированных строк на основе указанного условия.

Понимание GROUP BY и HAVING

  • GROUP BY: Это предложение группирует строки с одинаковыми значениями в одном или нескольких столбцах в сводную строку. Обычно вы используете агрегатные функции (например, COUNT, SUM, AVG, MIN, MAX) для вычисления значений для каждой группы.
  • HAVING: Это предложение фильтрует группы, созданные предложением GROUP BY. Оно похоже на предложение WHERE, но работает с группами, а не с отдельными строками.

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

SELECT
    column1,
    aggregate_function(column2)
FROM
    table_name
WHERE
    condition
GROUP BY
    column1
HAVING
    aggregate_function(column2) condition;
  • column1: Столбец для группировки.
  • aggregate_function(column2): Агрегатная функция, применяемая к column2 для каждой группы.
  • WHERE: Фильтрует строки до группировки.
  • GROUP BY: Группирует строки на основе значений в column1.
  • HAVING: Фильтрует группы после группировки, на основе результата агрегатной функции.

Сценарий:

Продолжая работу с таблицами employees и departments, давайте используем GROUP BY и HAVING, чтобы найти отделы, в которых работает более одного сотрудника.

Шаг 1: Группировка по отделу и подсчет сотрудников

Сначала давайте напишем запрос для группировки сотрудников по отделам и подсчета количества сотрудников в каждом отделе.

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
GROUP BY
    d.department_name;

Объяснение:

  • SELECT d.department_name, COUNT(e.employee_id) AS employee_count: Это выбирает название отдела и количество идентификаторов сотрудников для каждого отдела.
  • FROM employees e JOIN departments d ON e.department_id = d.department_id: Это объединяет таблицы employees и departments по столбцу department_id.
  • GROUP BY d.department_name: Это группирует строки по названию отдела, поэтому функция COUNT() будет подсчитывать количество сотрудников в каждом отделе.

Шаг 2: Выполните запрос и просмотрите результаты

Выполните запрос в вашем терминале psql. Вы должны увидеть следующий вывод:

 department_name | employee_count
-----------------+----------------
 Engineering     |              1
 Marketing       |              2
 Sales           |              2
(3 rows)

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

Шаг 3: Фильтрация с помощью HAVING

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

SELECT
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM
    employees e
JOIN
    departments d ON e.department_id = d.department_id
GROUP BY
    d.department_name
HAVING
    COUNT(e.employee_id) > 1;

Объяснение:

  • Этот запрос такой же, как и предыдущий, но он добавляет предложение HAVING:
    • HAVING COUNT(e.employee_id) > 1: Это фильтрует группы, чтобы включить только отделы, в которых количество идентификаторов сотрудников больше 1.

Шаг 4: Выполните запрос и просмотрите результаты

Выполните запрос в вашем терминале psql. Вы должны увидеть следующий вывод:

 department_name | employee_count
-----------------+----------------
 Marketing       |              2
 Sales           |              2
(2 rows)

Это показывает только отделы, в которых работает более одного сотрудника (Marketing и Sales).

Теперь вы успешно использовали предложения GROUP BY и HAVING для группировки строк и фильтрации сгруппированных результатов на основе условия.

Резюме

В этой лабораторной работе вы научились писать расширенные запросы PostgreSQL. Вы начали с использования подзапросов (subqueries) в предложении WHERE для фильтрации данных на основе результатов другого запроса. Это включало понимание синтаксиса и применения подзапросов для сравнения значений столбцов с набором значений, возвращаемых внутренним запросом.

Затем вы перешли к определению и использованию Common Table Expressions (CTE) (общие табличные выражения) для улучшения читаемости и модульности запросов. CTE позволяют разбивать сложные запросы на более простые, более управляемые части.

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

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

Связанные PostgreSQL Курсы
PostgreSQL для начинающих

PostgreSQL для начинающих

databasepostgresql
Практические лабораторные работы по продвинутому использованию PostgreSQL

Практические лабораторные работы по продвинутому использованию PostgreSQL

databasepostgresql