SQL на практике: первые запросы в PostgreSQL

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

Изначальный пост сопровождался скриншотами установки. Здесь их нет, поэтому обновлённая версия не обещает пошаговый установщик для каждой ОС. Это самостоятельная лабораторная работа по SQL с коротким вариантом запуска PostgreSQL через Docker.

Подготовить стенд

Если PostgreSQL уже доступен, переходи к созданию таблиц. Если установлен Docker, временный локальный стенд можно подготовить так:

docker run --name sql-practice \
  -e POSTGRES_PASSWORD=postgres \
  -p 127.0.0.1:5432:5432 \
  -d postgres:16

docker exec sql-practice pg_isready -U postgres

Дождись ответа accepting connections; до этого повтори последнюю проверку. Затем открой клиент:

docker exec -it sql-practice psql -U postgres

Команды ниже выполняются внутри psql. Для рабочего окружения пароль, сеть, роли и хранение данных нужно настраивать отдельно; этот контейнер предназначен только для локальной практики.

Модель данных

Возьмём отделы и сотрудников. Имена объектов записаны в snake_case, чтобы PostgreSQL не требовал кавычек вокруг идентификаторов.

CREATE TABLE departments (
  department_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  department_name text NOT NULL UNIQUE
);

CREATE TABLE employees (
  employee_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name text NOT NULL,
  department_id integer NOT NULL REFERENCES departments(department_id),
  salary numeric(12, 2) NOT NULL CHECK (salary >= 0)
);

Выполняй упражнение в новой учебной базе без этих таблиц. Следующая вставка предполагает, что последовательность отделов ещё не использовалась и выдаст ID 1–3. В прикладном коде такие ID получают через RETURNING или поиск по устойчивому ключу, а не угадывают. Наполним таблицы тестовыми данными:

INSERT INTO departments (department_name) VALUES
  ('IT'),
  ('Finance'),
  ('Marketing');

INSERT INTO employees (full_name, department_id, salary) VALUES
  ('Иван Иванов', 1, 90000),
  ('Анна Петрова', 1, 110000),
  ('Олег Сидоров', 2, 95000),
  ('Елена Волкова', 2, 105000),
  ('Мария Орлова', 3, 85000);

SELECT, фильтрация и сортировка

Начнём с явного списка столбцов. В прикладном запросе он обычно полезнее SELECT *: контракт результата виден сразу.

SELECT employee_id, full_name, salary
FROM employees
ORDER BY employee_id;

Найдём сотрудников с зарплатой от 100 000 и отсортируем по убыванию:

SELECT full_name, salary
FROM employees
WHERE salary >= 100000
ORDER BY salary DESC, full_name;

Условие WHERE фильтрует строки до формирования результата, а ORDER BY задаёт порядок выдачи.

JOIN: соединяем сотрудников с отделами

В employees хранится внешний ключ department_id, а название отдела лежит в departments.

SELECT
  e.full_name,
  d.department_name,
  e.salary
FROM employees AS e
JOIN departments AS d
  ON d.department_id = e.department_id
ORDER BY d.department_name, e.full_name;

Обычный JOIN здесь равнозначен INNER JOIN: в результат попадут строки, для которых найдено соответствие с обеих сторон. Ограничение внешнего ключа не позволит создать сотрудника с несуществующим отделом.

Агрегация и GROUP BY

Посчитаем численность и среднюю зарплату по отделам:

SELECT
  d.department_name,
  COUNT(*) AS employee_count,
  ROUND(AVG(e.salary), 2) AS average_salary
FROM employees AS e
JOIN departments AS d
  ON d.department_id = e.department_id
GROUP BY d.department_id, d.department_name
ORDER BY average_salary DESC, d.department_name;

Если в SELECT рядом с агрегатом выводится обычный столбец, он должен входить в GROUP BY либо однозначно зависеть от сгруппированного ключа. Для первого знакомства проще перечислять группируемые поля явно.

Фильтр по уже сформированным группам задаётся через HAVING:

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) >= 2;

Оконная функция

Оконная функция не схлопывает строки. Например, можно пронумеровать сотрудников внутри каждого отдела по зарплате:

SELECT
  full_name,
  department_id,
  salary,
  ROW_NUMBER() OVER (
    PARTITION BY department_id
    ORDER BY salary DESC, employee_id
  ) AS salary_position
FROM employees
ORDER BY department_id, salary_position;

ORDER BY внутри OVER задаёт нумерацию, но не порядок итоговых строк. Поэтому внешний ORDER BY указан отдельно.

INSERT, UPDATE и DELETE

CRUD — это удобная модель операций над данными. В SQL чтению обычно соответствует SELECT, а изменениям — INSERT, UPDATE и DELETE.

Добавим сотрудника и сразу посмотрим созданную строку:

INSERT INTO employees (full_name, department_id, salary)
VALUES ('Сергей Котов', 3, 88000)
RETURNING employee_id, full_name, salary;

Изменим его зарплату:

UPDATE employees
SET salary = 92000
WHERE full_name = 'Сергей Котов'
RETURNING employee_id, full_name, salary;

Удалим учебную запись:

DELETE FROM employees
WHERE full_name = 'Сергей Котов'
RETURNING employee_id, full_name;

Для UPDATE и DELETE сначала полезно выполнить SELECT с тем же WHERE. Так проще заметить слишком широкое условие до изменения данных.

В этом наборе данных имя однозначно указывает на учебную запись. В реальной системе ФИО не уникально: для изменения и удаления используй employee_id, полученный после INSERT.

Транзакция

Транзакция объединяет несколько операторов в одну атомарную единицу. Ниже мы переносим 5 000 между двумя тестовыми зарплатами, проверяем результат и откатываем упражнение:

BEGIN;

UPDATE employees
SET salary = salary + 5000
WHERE employee_id = 1;

UPDATE employees
SET salary = salary - 5000
WHERE employee_id = 2;

SELECT employee_id, full_name, salary
FROM employees
WHERE employee_id IN (1, 2)
ORDER BY employee_id;

ROLLBACK;

ROLLBACK отменяет обе операции. Если результат нужно сохранить, транзакцию завершают COMMIT. Ограничение CHECK (salary >= 0) дополнительно не даст записать отрицательную зарплату, но не заменяет бизнес-проверки.

Что аналитику стоит уметь дальше

После этого стенда я бы двигался в таком порядке:

1. NULL и трёхзначная логика условий;
2. разные виды JOIN и причины размножения строк;
3. подзапросы, CTE и оконные функции;
4. уровни изоляции и конкурентные изменения;
5. индексы и чтение EXPLAIN;
6. роли, права и различия диалектов SQL.

Не обязательно помнить весь синтаксис. Важно уметь сформулировать вопрос к данным, проверить ключи и связи, а затем убедиться, что запрос возвращает именно ожидаемое множество строк.

Продолжить практику можно по официальному учебнику PostgreSQL.

Самопроверка: отдел без сотрудников

Добавь отдел, в котором пока никто не работает:

INSERT INTO departments (department_name) VALUES ('Research');

Теперь нужен отчёт по всем отделам. Предыдущий INNER JOIN новый отдел не покажет. Используем LEFT JOIN:

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

Если выполнял стенд последовательно, у Finance и IT будет по два сотрудника, у Marketing — один, у Research — ноль. Здесь важен COUNT(e.employee_id): он не считает NULL со стороны отсутствующего сотрудника. COUNT(*) посчитает строку самого соединения и даст для пустого отдела единицу.

Следующее упражнение: посчитай только сотрудников с зарплатой от 100 000, но сохрани в результате все отделы. Сравни размещение условия в ON и в WHERE. Во втором случае условие по зарплате уберёт строки без сотрудников, и смысл отчёта изменится. SQL выполняет написанное — не то, что мы имели в виду.

---

Из моего Telegram-канала @it_underside.

Ваш ДПУПП