Базы данных и SQL на уровне аналитика

Для бизнес- и системного аналитика (00.01 Кто такой аналитик (роли BA, SA, FA), 00.02 Бизнес-анализ vs Системный анализ vs Требования) уверенное чтение схем и написание запросов позволяют проверять гипотезы до разработки, уточнять требования по реальным данным и вести диалог с backend-командой на одном языке. Эта заметка задаёт необходимый минимум: от чтения реляционной схемы до запросов уровня SELECT … JOIN … GROUP BY … HAVING — без роли администратора баз данных (DBA). На практике вы чаще работаете с read-only доступом к реплике или витрине данных, а не с продуктовой OLTP-базой напрямую.

1. Суть темы

SQL (Structured Query Language) для аналитика — способ проверить, что данные в системе соответствуют требованиям, спецификации и интеграционным контрактам. Вы не проектируете индексы и не настраиваете кластер, но должны свободно читать ER-схему, находить аномалии в данных и формулировать требования к миграциям и отчётам, которые строит команда.

Граница темы:

Аналитику не нужно быть DBA, но достаточно уровня: читать схему, писать SELECT с JOIN и агрегатами, понимать NULL, диалект проектной СУБД и ограничения доступа к данным.

Зачем это нужно на проекте:

• Проверка гипотез — оценить объёмы, качество и структуру данных до старта разработки.
• Уточнение требований — увидеть реальную схему и зафиксировать бизнес-правила, которых нет в интервью (02.04.01 Верификация и валидация (V&V)).
• Трассировка — сопоставить сущности из SRS и user story с таблицами и полями (02.03.03 Спецификация требований (SRS, Use Case, User Story), 02.03.04 Трассировка требований (концепция)).
• Валидация отчётов и API — сверить выдачу сервиса с прямым запросом к источнику.
• Legacy и миграции — исследовать существующую модель перед изменениями (10.02 Разбор кейса "Миграция legacy-системы").

2. Ключевые понятия

Реляционная модель: таблицы и связи

Реляционная база данных хранит данные в таблицах (отношениях): строки — записи, столбцы — атрибуты.

Типы связей — подробнее в 03.03.01 ER-диаграммы (сущность-связь):

• 1:1 — редко; обычно для вынесения редко используемых полей.
• 1:M — самый частый; одна запись родителя — много дочерних (Customer → Order).
• M:N — через промежуточную таблицу с двумя FK (Order ↔ Product через OrderItem).

Нормализация и денормализация (витрины, звёзды) — в 03.03.02 Логическая и физическая модель данных; здесь достаточно знать, что OLTP-схемы обычно нормализованы, а аналитические витрины — намеренно упрощены. Примеры SQL ниже — на нормализованной OLTP-модели. На витрине или денормализованной таблице те же вопросы задаёте по словарю данных (03.03.03 Словарь данных (описание)): JOIN может не понадобиться, но имена полей и бизнес-правила сверяйте так же внимательно.

Рис. OLTP-схема для запросов ниже. У PRODUCT есть текущая price, у ORDER_ITEM — unit_price: это снимок цены на момент заказа. Сумма заказа считается по order_item.unit_price, а не по актуальной цене каталога. ORDER.deleted_at — мягкое удаление: без фильтра такие строки попадают в отчёт. PAYMENT может отсутствовать, пока заказ не оплачен.

Витрина по тем же продажам устроена иначе: факт и измерения, без цепочки ORDER → ORDER_ITEM → PRODUCT. Запрос к витрине короче, но имена и правила всё равно сверяйте со словарём.

SELECT c.segment, SUM(f.line_amount) AS revenue
FROM fact_order_item f
JOIN dim_customer c ON c.customer_key = f.customer_key
JOIN dim_date d ON d.date_key = f.date_key
WHERE d.calendar_date >= CURRENT_DATE - INTERVAL '1 month'
GROUP BY c.segment;

Типы данных и NULL

При валидации форматов из 02.04.02 Критерии приёмки (Acceptance Criteria) обращайте внимание на тип столбца:

NULL — отсутствие значения, не ноль и не пустая строка. Агрегаты (SUM, AVG, COUNT(col)) игнорируют NULL; COUNT(*) считает все строки. Сравнение col = NULL всегда ложно — используйте IS NULL / IS NOT NULL.

Как читать чужую схему

На legacy-проектах схема редко совпадает с учебной:

• Naming — tbl_order, ord_dt, префиксы модулей; сверяйте с 10.01.14 Шаблон глоссария и словаря данных.
• Связи без FK — логическая связь есть, ограничение в БД не объявлено; проверяйте «осиротевшие» строки запросом.
• Soft delete — deleted_at IS NULL вместо физического удаления; без фильтра отчёт завышен.
• Audit-поля — created_at, updated_by; помогают трассировать изменения.
• Системный каталог — список таблиц и столбцов:

-- PostgreSQL / стандарт SQL
SELECT table_name, column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name = 'order_item'
ORDER BY ordinal_position;

Поиск зависимостей по полю: системный каталог + текстовый поиск по миграциям, view и хранимым процедурам.

Категории команд SQL

В повседневной работе аналитик опирается на DML, прежде всего SELECT. DDL обычно не пишет — достаточно читать миграции и 10.01.03 Шаблон спецификации БД сервиса. TCL нужен на уровне понимания транзакций в OLTP: изменения фиксируются COMMIT. На read-only реплике или витрине вы обычно видите уже зафиксированные данные других транзакций — это не то же самое, что уровень изоляции в вашей сессии на prod. Детали изоляции и поведения реплик — в документации СУБД проекта. На тестовых средах INSERT/UPDATE выполняйте только с согласованием и планом отката (05.04 DevOps и непрерывная поставка — влияние на анализ).

Диалекты SQL

Примеры ниже — в стиле PostgreSQL: кавычки для зарезервированных имён ("order"), INTERVAL '1 month', LIMIT. В MySQL — обратные кавычки; в MSSQL — TOP, DATEADD, квадратные скобки для имён. Перед переносом запроса между СУБД проверяйте функции дат, строк и пагинацию по документации проекта.

Практический SQL: каркас запроса

SELECT [DISTINCT] столбцы_или_выражения
FROM основная_таблица
[JOIN другие_таблицы ON условие_связи]
[WHERE условие_фильтрации_строк]
[GROUP BY столбцы_группировки]
[HAVING условие_на_группы]
[ORDER BY столбец [ASC|DESC]]
[LIMIT количество];

Логический порядок выполнения (важен при отладке): FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT.

Простая выборка:

SELECT id, order_date
FROM "order"
WHERE customer_id = 42
  AND deleted_at IS NULL
ORDER BY order_date DESC;

JOIN. INNER JOIN — только совпадающие строки; LEFT JOIN — все строки слева, справа NULL при отсутствии пары; FULL OUTER JOIN и CROSS JOIN — реже. При LEFT JOIN + агрегатах (COUNT, SUM) строки без совпадения всё равно участвуют в группе — частый источник «лишних» нулей в отчётах; проверяйте, нужен ли фильтр WHERE right.id IS NOT NULL.

Заказы без оплаты на схеме выше — как раз LEFT JOIN к PAYMENT: платёж необязателен, внутреннее соединение такие заказы спрячет.

SELECT o.id, o.status
FROM "order" o
LEFT JOIN payment p ON p.order_id = o.id
WHERE o.deleted_at IS NULL
  AND p.id IS NULL;

-- Сумма каждого заказа по снимку цены в позиции
SELECT o.id, o.order_date, SUM(oi.quantity * oi.unit_price) AS total
FROM "order" o
JOIN order_item oi ON o.id = oi.order_id
WHERE o.deleted_at IS NULL
GROUP BY o.id, o.order_date
ORDER BY total DESC;

DISTINCT. После JOIN без GROUP BY строки могут дублироваться — SELECT DISTINCT или группировка убирает повторы. Типичный кейс: список уникальных клиентов с заказом за период.

SELECT DISTINCT c.id, c.name
FROM customer c
JOIN "order" o ON c.id = o.customer_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '1 month';

WHERE и HAVING. WHERE — фильтр строк до группировки; HAVING — фильтр групп после GROUP BY.

SELECT customer_id, COUNT(*) AS order_count
FROM "order"
WHERE order_date >= CURRENT_DATE - INTERVAL '1 month'
GROUP BY customer_id
HAVING COUNT(*) > 3;

Подзапросы, EXISTS, NOT IN. Для проверки «есть ли связанные записи» предпочтительнее EXISTS — корректно работает с NULL:

-- Товары без ни одного заказа
SELECT product_name
FROM product p
WHERE NOT EXISTS (
  SELECT 1 FROM order_item oi WHERE oi.product_id = p.id
);

NOT IN (SELECT …) ломается, если подзапрос вернёт хотя бы один NULL — результат станет пустым. Либо фильтруйте NULL в подзапросе, либо используйте NOT EXISTS.

UNION / UNION ALL. Объединение результатов двух запросов с одинаковым числом столбцов — сверка справочников, поиск расхождений между системами:

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

SELECT email FROM customer WHERE source = 'crm'
UNION
SELECT email FROM customer_staging WHERE import_batch = 42;

UNION убирает дубликаты; UNION ALL — быстрее, если дубликаты допустимы.

CASE — явная классификация в запросе; ниже та же агрегация, что в примерах выше:

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_item
  GROUP BY order_id
)
SELECT o.id,
       CASE
         WHEN ot.total >= 10000 THEN 'VIP'
         WHEN ot.total >= 1000  THEN 'Regular'
         ELSE 'New'
       END AS segment
FROM "order" o
JOIN order_totals ot ON o.id = ot.order_id;

CTE (Common Table Expression) — читаемая декомпозиция сложного запроса:

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_item
  GROUP BY order_id
)
SELECT o.id, o.order_date, ot.total
FROM "order" o
JOIN order_totals ot ON o.id = ot.order_id
WHERE ot.total > 1000;

Оконные функции — расчёт по «окну» строк без сворачивания результата:

WITH order_with_total AS (
  SELECT o.id AS order_id,
         o.customer_id,
         SUM(oi.quantity * oi.unit_price) AS total
  FROM "order" o
  JOIN order_item oi ON o.id = oi.order_id
  GROUP BY o.id, o.customer_id
)
SELECT customer_id,
       order_id,
       total,
       RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rank_in_customer
FROM order_with_total;

Полезны ROW_NUMBER(), RANK(), LAG(), LEAD() — проверка последовательностей, пропусков номеров, сравнение с предыдущим периодом.

Представления (views)

View — сохранённый SELECT, скрывающий сложный JOIN или маскирующий чувствительные столбцы. При чтении схемы отличайте физические таблицы от view: изменение базовых таблиц может сломать отчёты, ETL и downstream-потребителей, завязанных на view (03.03.02 Логическая и физическая модель данных, 10.01.03 Шаблон спецификации БД сервиса). Если события уходят из БД в шину — см. 03.05.04 Асинхронное взаимодействие (очереди, Kafka, Events).

Безопасность и доступ к данным

Минимум для работы с данными на проекте:

• Запрашивайте read-only доступ; SELECT * на prod без согласования — риск утечки и нагрузки.
• PII (персональные данные) — не выгружайте в открытые артефакты; используйте маскирование или тестовые среды (08.06 Юридические и регуляторные требования РФ (PCI-DSS), 08.07 Основы кибербезопасности для системного аналитика).
• Разделяйте prod / stage / dev; INSERT и UPDATE только там, где это разрешено политикой команды.
• Не склеивайте SQL из строк пользовательского ввода в скриптах выгрузки — риск injection; параметризованные запросы и политики доступа — зона 08.07 Основы кибербезопасности для системного аналитика.

Инструменты

Для просмотра схемы и выполнения запросов достаточно универсального клиента (DBeaver, DataGrip) или встроенного средства СУБД (pgAdmin, SSMS). Если на проекте принято строить дашборды через SQL — команда может использовать Metabase или Redash; ваша задача как аналитика — проверка данных для требований и V&V, а не роль отчётника.

3. Как применяется в работе аналитика

Краткий чеклист перед выводами по данным:

1. К какой среде подключены (prod, реплика, витрина)?
2. Учтены ли soft delete и статусные фильтры?
3. Суммы и количества — по правильным столбцам (снимок vs текущее значение)?
4. Есть ли NULL, влияющие на NOT IN или LEFT JOIN?
5. Результат согласуется с AC и контрактом API?

4. Типовые ошибки и ограничения

Ограничения этой заметки: здесь нет углубления в индексы и чтение плана (EXPLAIN), уровни изоляции транзакций и сравнение диалектов PostgreSQL/MSSQL/MySQL — это зона документации СУБД и DBA. Требования к latency и объёму выборок формулируйте как NFR (08.02 Нефункциональные требования (НФТ) — классификация и стандарты).

Связи с другими разделами базы

• 03.03.01 ER-диаграммы (сущность-связь) — проектирование таблиц и связей.
• 03.03.02 Логическая и физическая модель данных — нормализация, физическая схема, витрины.
• 03.03.03 Словарь данных (описание) — документирование структуры БД.
• 02.03.03 Спецификация требований (SRS, Use Case, User Story) — сущности в спецификации vs таблицы.
• 02.03.04 Трассировка требований (концепция) — увязка требований с объектами БД.
• 02.04.01 Верификация и валидация (V&V) — SQL как способ проверки требований.
• 02.04.02 Критерии приёмки (Acceptance Criteria) — форматы данных и правила в AC.
• 08.03 Тестирование требований (статическое тестирование) — SQL в ревью спецификации.
• 07.02 Работа с API (практика) — сверка данных БД с ответами API.
• 08.02 Нефункциональные требования (НФТ) — классификация и стандарты — производительность и надёжность хранения.
• 08.07 Основы кибербезопасности для системного аналитика — PII, injection, политики доступа.
• 10.01.03 Шаблон спецификации БД сервиса — артефакт описания схемы.
• 10.01.14 Шаблон глоссария и словаря данных — именование полей и терминов.
• 10.02 Разбор кейса "Миграция legacy-системы" — исследование существующих схем.
• 05.04 DevOps и непрерывная поставка — влияние на анализ — миграции схемы и среды.

Источники и дополнительные материалы

Книги и стандарты

• Алан Бьюли. «Изучаем SQL» (Learning SQL, 3-е изд., O'Reilly, 2020) — базовый курс с примерами для разных СУБД.
• Энтони Молинаро, Роберт де Грааф. «SQL. Сборник рецептов» (SQL Cookbook, 2-е изд., O'Reilly, 2020) — типовые задачи аналитика.
• К. Дж. Дейт. «Введение в системы баз данных» (8-е издание) — реляционная теория.
• Скотт Амблер, Прамодкумар Дж. Садаладж. «Рефакторинг баз данных. Эволюционное проектирование» — эволюция схем.

Интерактивные тренажёры

• SQL Academy — русскоязычный тренажёр.
• Stepik: «Интерактивный тренажёр по SQL» — практические упражнения.
• Mode Analytics SQL Tutorial — SQL для анализа данных.
• PostgreSQL Exercises — задачи на PostgreSQL.

Документация

• PostgreSQL Documentation (актуальная) — справочник по синтаксису и функциям.
• PostgreSQL: EXPLAIN — чтение плана запроса (обзорный уровень).
• MySQL Reference Manual — для проектов на MySQL.
• Microsoft SQL Server — техническая документация — для проектов на MSSQL.

Инструменты

• DBeaver — универсальный SQL-клиент.
• pgAdmin — клиент для PostgreSQL.
• Metabase — дашборды и SQL-запросы.
• Redash — SQL-дашборды для команд.
• DataGrip — IDE для SQL.