Практика SQL для аналитика строится вокруг повторяющихся задач: соединить таблицы, сгруппировать данные, посчитать доли, найти последние события, применить ранжирование и проверить качество результата. Ниже - безопасный маршрут от CTE и JOIN до оконных функций, подзапросов и оптимизации. Примеры используют таблицы заказов, клиентов и платежей.
Коротко о навыках: какие запросы закрывают 80% задач
- CTE помогают разделять сложный SQL-запрос на понятные этапы.
- JOIN связывают факты с клиентами, товарами, календарём и справочниками.
- GROUP BY, CASE и HAVING превращают события в бизнес-метрики.
- Оконные функции находят первое событие, позицию клиента, накопительный итог и динамику.
- EXISTS и anti-join позволяют искать наличие или отсутствие связанных записей.
- EXPLAIN и проверки качества снижают риск медленных запросов и неверных выводов.
Подготовка данных: CTE как инструмент читаемости и переиспользования
Цель. CTE подходит аналитику, когда запрос состоит из нескольких логических этапов: фильтрации, очистки, агрегации и соединения. Не стоит создавать много вложенных CTE для простого запроса или использовать их как замену индексу и материализованному представлению.
Пример: выручка по клиентам
WITH paid_orders AS (
SELECT customer_id, amount
FROM orders
WHERE status = 'paid'
)
SELECT customer_id, SUM(amount) AS revenue
FROM paid_orders
GROUP BY customer_id
ORDER BY revenue DESC;Входная таблица orders:
| customer_id | amount | status |
|---|---|---|
| 101 | 1200 | paid |
| 101 | 800 | cancelled |
| 102 | 500 | paid |
Ожидаемый результат:
| customer_id | revenue |
|---|---|
| 101 | 1200 |
| 102 | 500 |
Эквивалент без CTE:
SELECT customer_id, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY revenue DESC;Оптимизация: фильтруйте строки как можно раньше, выбирайте только нужные столбцы и проверяйте план выполнения. Поведение CTE зависит от конкретной СУБД и её версии.
Практические JOIN: INNER, LEFT, RIGHT и anti-join на реальных примерах
Цель. JOIN нужен, когда метрика хранится в одной таблице, а описание объекта - в другой. Что понадобится: доступ на чтение, понятные ключи связи, документация по полям и небольшой тестовый набор данных. Для аналитики чаще всего достаточно INNER JOIN и LEFT JOIN; RIGHT JOIN можно заменить перестановкой таблиц.
Пример: клиенты с заказами и клиенты без заказов
SELECT c.customer_id, c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;Входные таблицы:
| customers.customer_id | name | orders.order_id |
|---|---|---|
| 101 | Анна | 5001 |
| 102 | Борис | 5002 |
| 103 | Вера | NULL |
Ожидаемый результат:
| customer_id | name | order_id |
|---|---|---|
| 101 | Анна | 5001 |
| 102 | Борис | 5002 |
| 103 | Вера | NULL |
Anti-join для поиска клиентов без заказов:
SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;Эквивалент через CTE:
WITH active_customers AS (
SELECT DISTINCT customer_id
FROM orders
)
SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN active_customers AS a
ON a.customer_id = c.customer_id
WHERE a.customer_id IS NULL;Оптимизация: проверяйте уникальность ключа и число строк после JOIN. Ошибка в условии связи может размножить строки и завысить сумму.
Группировки и агрегаты: сложные агрегации с CASE и HAVING
Цель. Группировка переводит отдельные события в показатели по клиенту, месяцу, каналу или продукту. Используйте CASE для сегментации, а HAVING - для фильтрации уже рассчитанных групп.
-
Определите зерно результата.
Решите, одна строка будет означать клиента, день, заказ или сочетание измерений. Это предотвращает смешение разных уровней детализации. -
Отфильтруйте факты.
Укажите нужный статус и период до агрегации, если это возможно.- Не фильтруйте агрегат в WHERE.
- Для суммы после GROUP BY используйте HAVING.
-
Добавьте бизнес-классификацию.
CASE может разделить заказы на малые и крупные, а также посчитать отдельные категории. -
Проверьте группы.
Сравните число исходных строк, сумму по всем группам и несколько ручных примеров.
SELECT customer_id,
COUNT(*) AS orders_count,
SUM(amount) AS revenue,
SUM(CASE WHEN amount >= 1000 THEN 1 ELSE 0 END) AS large_orders
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 1000
ORDER BY revenue DESC;Входная таблица orders:
| customer_id | amount | status |
|---|---|---|
| 101 | 1200 | paid |
| 101 | 300 | paid |
| 102 | 700 | paid |
| 102 | 900 | paid |
Ожидаемый результат:
| customer_id | orders_count | revenue | large_orders |
|---|---|---|---|
| 101 | 2 | 1500 | 1 |
| 102 | 2 | 1600 | 0 |
Вариант с CTE:
WITH paid_orders AS (
SELECT *
FROM orders
WHERE status = 'paid'
)
SELECT customer_id, SUM(amount) AS revenue
FROM paid_orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;Эквивалент без CTE - тот же запрос с фильтром status непосредственно в основном SELECT. Оптимизация: не группируйте по описательным полям, если достаточно идентификатора; присоединяйте справочники после агрегации.
Оконные функции в аналитике: ранжирование, накопления и скользящие метрики
Цель. Оконные функции считают показатель по соседним или связанным строкам, не сворачивая их в одну строку. Они особенно полезны для рейтингов, первого заказа, накопительной выручки и сравнения с предыдущим периодом.
SELECT customer_id,
order_date,
amount,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
) AS order_number,
SUM(amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_revenue
FROM orders
WHERE status = 'paid';Входная таблица orders:
| customer_id | order_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2026-01-01 | 500 |
| 101 | 2 | 2026-01-03 | 700 |
| 102 | 3 | 2026-01-02 | 300 |
Ожидаемый результат:
| customer_id | order_date | order_number | running_revenue |
|---|---|---|---|
| 101 | 2026-01-01 | 1 | 500 |
| 101 | 2026-01-03 | 2 | 1200 |
| 102 | 2026-01-02 | 1 | 300 |
Вариант с CTE полезен, если сначала нужно агрегировать заказы по дню:
WITH daily_revenue AS (
SELECT order_date, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY order_date
)
SELECT order_date,
revenue,
SUM(revenue) OVER (ORDER BY order_date) AS running_revenue
FROM daily_revenue;Без CTE оконную функцию можно применить поверх подзапроса. Оптимизация: добавляйте устойчивый tie-breaker, например order_id, если даты повторяются.
Проверка результата перед публикацией
- Указан правильный уровень окна в PARTITION BY.
- Порядок строк в ORDER BY соответствует бизнес-логике.
- При одинаковых датах есть дополнительный уникальный ключ.
- NULL обработан явно, если он возможен.
- Накопительный итог не превышает контрольную сумму исходных данных.
- Количество строк до и после добавления окна совпадает.
- Границы ROWS или RANGE соответствуют требуемой метрике.
Сложные подборки: подзапросы, EXISTS и объединения для бизнес-логики
Цель. Подзапросы и EXISTS помогают выразить условия вроде "клиенты, у которых был хотя бы один оплаченный заказ". UNION пригоден для объединения одинаковых по смыслу наборов.
SELECT c.customer_id, c.name
FROM customers AS c
WHERE EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.customer_id
AND o.status = 'paid'
);Входные данные и результат:
| customer_id | name | paid_order_exists |
|---|---|---|
| 101 | Анна | да |
| 102 | Борис | нет |
| 103 | Вера | да |
Вариант с CTE:
WITH paid_customers AS (
SELECT DISTINCT customer_id
FROM orders
WHERE status = 'paid'
)
SELECT c.customer_id, c.name
FROM customers AS c
JOIN paid_customers AS p
ON p.customer_id = c.customer_id;Ошибки, которые чаще всего искажают результат
- Использование NOT IN, когда подзапрос может вернуть NULL.
- Применение условий правой таблицы в WHERE после LEFT JOIN, превращающее его в INNER JOIN.
- UNION вместо UNION ALL, когда удаление дублей не предусмотрено логикой.
- Соединение таблиц по неуникальному ключу без проверки кардинальности.
- Сравнение дат с разными часовыми поясами.
- Подзапрос, который возвращает несколько строк там, где ожидается одно значение.
- Смешение фильтра по событиям и фильтра по сущностям.
Оптимизация: для проверки существования обычно достаточно EXISTS; не выбирайте лишние столбцы внутри подзапроса. Подтверждайте план и наличие подходящих индексов по полям связи.
Производительность и контроль качества: EXPLAIN, индексы и тесты на данных
Цель. Быстрый и корректный запрос требует двух проверок: как СУБД выполняет его и соответствует ли результат бизнес-правилу. Сначала тестируйте чтение и агрегацию, затем добавляйте JOIN и окна.
Базовая диагностика
EXPLAIN
SELECT customer_id, SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;Контрольный набор:
| Проверка | Ожидаемое условие |
|---|---|
| Дубликаты ключа | Нет неожиданных повторов |
| NULL в ключах | Обработан явно |
| Итоговая сумма | Согласуется с исходными фильтрами |
| Период | Границы дат определены однозначно |
Когда выбрать другой подход
| Подход | Когда уместен |
|---|---|
| CTE | Несколько последовательных этапов и важна читаемость. |
| Подзапрос | Логика локальна и используется один раз. |
| Материализованное представление | Один и тот же тяжёлый расчёт регулярно читается. |
| Предагрегация | Детализация избыточна для итогового отчёта. |
Быстрый режим
- Определите зерно результата и список нужных столбцов.
- Отфильтруйте факты, затем агрегируйте и присоединяйте справочники.
- Для сравнений по строкам используйте оконные функции.
- Проверьте дубли, NULL, период, сумму и количество строк.
- Перед публикацией изучите EXPLAIN на рабочем объёме данных.
Практические ответы на типичные затруднения при написании запросов
Как понять, какой JOIN выбрать?
INNER JOIN оставляет только совпавшие записи, LEFT JOIN сохраняет все строки левой таблицы, а anti-join ищет строки без соответствия. RIGHT JOIN обычно удобнее заменить перестановкой таблиц и использованием LEFT JOIN.
Почему после JOIN сумма стала больше?
Чаще всего ключ связи не уникален, поэтому одна строка размножилась. Проверьте количество строк до соединения, уникальность ключей и кардинальность результата.
Когда использовать CTE?
Используйте CTE для именованных этапов расчёта, повторного обращения к промежуточному набору и упрощения проверки. Для короткого фильтра CTE обычно не нужен.
Чем HAVING отличается от WHERE?
WHERE фильтрует строки до группировки, а HAVING - группы после вычисления агрегатов. Условие по SUM или COUNT размещают в HAVING.
Почему ROW_NUMBER даёт непредсказуемый порядок?
В ORDER BY окна недостаточно уникального поля: при одинаковых значениях СУБД может менять порядок. Добавьте стабильный идентификатор, например order_id.
Как безопасно проверить запрос на больших данных?
Начните с ограниченного периода или тестовой копии, используйте EXPLAIN и не запускайте UPDATE или DELETE без явного условия и резервного плана. Для аналитических запросов сначала применяйте SELECT и проверяйте выборку вручную.
Что изучать после базовых JOIN и окон?
Следующий этап - модель данных, временные интервалы, качество справочников, планы выполнения и повторяемые тесты. Это важнее механического запоминания большого числа конструкций.
