Заметки бизнес-аналитика
Главная › Системный анализ

Данные и SQL

На странице

ER-модель

  • Сущность — объект предметной области: Клиент, Заказ, Товар.
  • Атрибут — свойство сущности: имя, дата, цена.
  • Первичный ключ (PK) — уникально идентифицирует запись, не может быть NULL.
  • Внешний ключ (FK) — ссылается на PK другой таблицы и защищает целостность связи.

Уровни модели

  • Концептуальная — сущности и связи, без деталей. Согласуется с бизнесом.
  • Логическая — добавлены атрибуты, ключи, кратности, нормализация. Не привязана к СУБД.
  • Физическая — конкретные таблицы, типы данных, индексы под выбранную СУБД.

Связи

ТипПримерКак реализуется
1:1Пользователь — профильFK с ограничением UNIQUE в одной из таблиц
1:NКлиент — заказыFK customer_id в таблице заказов
M:NЗаказы — товарыСвязующая таблица order_items(order_id, product_id, qty, price)
Связь «многие ко многим» в реляционной базе напрямую не реализуется: всегда нужна промежуточная таблица с двумя внешними ключами. Это один из самых частых вопросов.

Нормализация

  • 1НФ — значения атомарны (нет списков в одной ячейке), у строк есть ключ.
  • 2НФ — 1НФ, и каждый неключевой атрибут зависит от всего составного ключа. Название товара зависит только от товара, поэтому не хранится в позициях заказа.
  • 3НФ — 2НФ, и нет транзитивных зависимостей: неключевые атрибуты не зависят друг от друга. Город клиента не хранится в заказе.

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

Транзакции и ACID

  • Atomicity — транзакция выполняется целиком или не выполняется вовсе.
  • Consistency — после транзакции данные удовлетворяют всем ограничениям.
  • Isolation — параллельные транзакции не мешают друг другу. Уровни: Read Uncommitted, Read Committed, Repeatable Read, Serializable.
  • Durability — зафиксированные данные не теряются при сбое.

Шпаргалка по SQL

Схема для примеров: customers(id, name, city), orders(id, customer_id, created_at, status, amount).

Выборка и фильтрация

SELECT name, city
FROM customers
WHERE city IN ('Москва', 'Казань')
  AND name LIKE 'А%'
ORDER BY name
LIMIT 10;

Сравнение с NULL через = не работает, используйте IS NULL / IS NOT NULL.

Агрегаты, GROUP BY и HAVING

-- Клиенты, сделавшие больше 3 оплаченных заказов в 2026 году
SELECT customer_id, COUNT(*) AS cnt, SUM(amount) AS total
FROM orders
WHERE status = 'PAID' AND created_at >= '2026-01-01'
GROUP BY customer_id
HAVING COUNT(*) > 3
ORDER BY total DESC;

WHERE фильтрует строки до группировки, HAVING — группы после. Агрегатную функцию в WHERE писать нельзя.

Логический порядок выполнения: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. Поэтому псевдоним из SELECT нельзя использовать в WHERE.

JOIN

  • INNER JOIN — только строки, у которых есть пара в обеих таблицах.
  • LEFT JOIN — все строки левой таблицы, справа NULL, если пары нет.
  • RIGHT JOIN — зеркально LEFT.
  • FULL JOIN — все строки обеих таблиц.
  • CROSS JOIN — декартово произведение.
-- Сумма заказов по городам
SELECT c.city, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.city;

-- Клиенты без единого заказа (классическая задача)
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Подзапросы и CTE

-- Заказы дороже среднего
SELECT * FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);

-- То же через CTE, плюс EXISTS
WITH big AS (
  SELECT customer_id FROM orders WHERE amount > 10000
)
SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM big b WHERE b.customer_id = c.id);

CASE и UNION

SELECT id,
       CASE WHEN amount >= 10000 THEN 'крупный'
            WHEN amount >= 3000  THEN 'средний'
            ELSE 'мелкий' END AS segment
FROM orders;

UNION объединяет результаты и убирает дубликаты, UNION ALL оставляет дубликаты и работает быстрее.

Оконные функции

-- Последний заказ каждого клиента
SELECT * FROM (
  SELECT o.*,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

-- Накопительная выручка по дням
SELECT created_at::date AS day,
       SUM(amount) AS revenue,
       SUM(SUM(amount)) OVER (ORDER BY created_at::date) AS running_total
FROM orders
GROUP BY created_at::date;

ROW_NUMBER даёт уникальные номера, RANK — одинаковые ранги с пропусками (1, 1, 3), DENSE_RANK — без пропусков (1, 1, 2). LAG/LEAD берут значение из предыдущей или следующей строки.

Изменение данных

  • DELETE удаляет строки по условию, можно откатить, срабатывают триггеры.
  • TRUNCATE быстро очищает всю таблицу, без условия.
  • DROP удаляет саму таблицу вместе со структурой.

Индекс ускоряет поиск и сортировку по столбцу, но замедляет вставку и обновление и занимает место.

Где тренироваться

  • sql-academy.org — теория и задачи с автопроверкой.
  • sql-ex.ru — классический русскоязычный тренажёр.
  • Stepik — «Интерактивный тренажёр по SQL».
  • Песочницы: dbfiddle.uk, sqlize.online.

Вопросы для самопроверки

Как найти записи из одной таблицы, которых нет в другой?

LEFT JOIN и условие WHERE правая.id IS NULL, либо NOT EXISTS. Вариант с NOT IN опасен: если в подзапросе есть NULL, результат будет пустым.

Чем COUNT(*) отличается от COUNT(column)?

COUNT(*) считает все строки, COUNT(column) — только строки, где значение столбца не NULL. COUNT(DISTINCT column) считает уникальные непустые значения.

Как реализовать связь «студенты — курсы»?

Таблицы students и courses плюс связующая таблица enrollments(student_id, course_id, enrolled_at) с составным первичным ключом (student_id, course_id) и двумя внешними ключами.

Конспекты статей по теме

Подробные конспекты полезных статей. В конце каждого есть ссылка на оригинал и автора.

← BPMN, UML и C4Интеграции и REST API →