SQL для аналитики данных с нуля: от базовых запросов до бизнес-отчетов

Введение в базы данных и SQL

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

Пока продаж было сто в день, таблица работала отлично. Вы могли легко посчитать выручку за смену или найти самый популярный кофе. Но через год сеть выросла до пятидесяти кофеен. Ежедневно совершаются десятки тысяч транзакций. Файл Excel стал весить сотни мегабайт, он открывается по пять минут, зависает при попытке построить сводную таблицу, а если два менеджера попытаются отредактировать его одновременно — данные могут быть безвозвратно испорчены.

Именно в этот момент бизнес переходит от электронных таблиц к профессиональным инструментам хранения данных.

Что такое база данных и СУБД

Чтобы решить проблему медленной работы и путаницы, информацию переносят в базу данных (БД) — организованную структуру для хранения, изменения и извлечения информации.

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

Однако сам по себе склад не может выдавать товары или вести их учет. Ему нужен заведующий. В мире IT таким заведующим выступает система управления базами данных (СУБД) — специальное программное обеспечение, которое позволяет пользователям и приложениям взаимодействовать с базой данных.

СУБД берет на себя всю черновую работу: она следит за тем, чтобы данные не потерялись при сбое питания, чтобы два пользователя не изменили одну и ту же запись одновременно, и чтобы поиск нужной информации среди миллионов строк занимал доли секунды.

Самые популярные СУБД в аналитике данных — это PostgreSQL, MySQL, Microsoft SQL Server и Oracle. Несмотря на разные названия и компании-разработчики, все они работают по схожим принципам и понимают один и тот же язык запросов.

Реляционная модель данных

Существуют разные способы организации данных, но в бизнесе (особенно в финансах, ритейле и e-commerce) абсолютным стандартом являются реляционные базы данных. Термин происходит от английского слова relation (отношение), но на практике это означает, что данные хранятся в виде связанных таблиц.

В реляционной базе данных вся информация строго структурирована в виде двумерных массивов. Каждая таблица посвящена только одной сущности. Например, у нас будет отдельная таблица для клиентов, отдельная — для товаров, и отдельная — для заказов.

Анатомия таблицы

Любая таблица в реляционной БД состоит из двух фундаментальных элементов:

1. Поля (столбцы) — определяют структуру данных. Каждый столбец имеет свое уникальное имя и описывает один атрибут сущности. Например, в таблице клиентов могут быть поля: Имя, Email, Дата регистрации.
2. Записи (строки) — содержат сами данные. Каждая строка представляет собой один конкретный экземпляр сущности (одного конкретного клиента).

Главное отличие таблицы в базе данных от листа в Excel заключается в жесткости структуры. В Excel вы можете в любой момент написать текст в ячейку, где до этого были только числа, или закрасить ячейку желтым цветом. Реляционная база данных такого не позволит. Она требует строгой дисциплины, и эта дисциплина обеспечивается типами данных.

Типы данных

При создании таблицы для каждого столбца необходимо указать тип данных — правило, которое определяет, какая именно информация может храниться в этой колонке. Это защищает базу от ошибок (например, от попытки записать слово «бесплатно» в колонку с ценой товара).

Рассмотрим основные типы данных, с которыми аналитик сталкивается каждый день:

Целые числа (Integer)

Используются для данных, которые можно посчитать поштучно. Примеры: количество проданных товаров, возраст клиента, количество бонусных баллов. В SQL этот тип обычно обозначается как INT или INTEGER.

Числа с плавающей точкой (Decimal / Numeric)

Используются для точных финансовых расчетов или дробных метрик. Примеры: цена товара, сумма заказа, вес посылки в килограммах. Часто задается с указанием точности, например DECIMAL(10,2). Это означает, что число может состоять максимум из 10 цифр, при этом 2 из них будут находиться после запятой. Если цена товара составляет 1500.50 долл., она идеально впишется в этот тип.

Строки и текст (String / Varchar)

Предназначены для хранения текстовой информации любой длины. Примеры: имя клиента, название товара, промокод, почтовый адрес. Обычно используется тип VARCHAR(n), где n — максимальное количество символов. Например, VARCHAR(50) подойдет для email-адреса, а для длинного отзыва клиента лучше использовать тип TEXT, который вмещает огромные объемы символов.

Дата и время (Date / Timestamp)

Критически важный тип для аналитики, так как большинство бизнес-отчетов привязаны к временным периодам (выручка за месяц, когортный анализ по неделям).

• DATE хранит только дату (год, месяц, день). Пример: 2023-10-25.
• TIMESTAMP или DATETIME хранит дату и точное время вплоть до миллисекунд. Пример: 2023-10-25 14:30:00.

Логический тип (Boolean)

Хранит только два значения: истина (TRUE) или ложь (FALSE). Примеры: подписан ли клиент на рассылку, оплачен ли заказ, активен ли промокод.

Первичные и внешние ключи: как связываются данные

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

Первичный ключ (Primary Key)

Первичный ключ — это столбец (или набор столбцов), который уникально идентифицирует каждую строку в таблице.

Представьте, что в вашей базе есть два клиента с именем Иван Иванов. Если они оба сделают заказ, как система поймет, кому именно начислять бонусные баллы? Имя не подходит для идентификации, так как оно может повторяться. Номер телефона может измениться.

Поэтому каждой записи присваивается уникальный идентификатор (ID). В таблице клиентов это будет customer_id.

Правила первичного ключа:

1. Он должен быть абсолютно уникальным для каждой строки.
2. Он не может быть пустым (не может содержать значение NULL).
3. Он никогда не должен меняться со временем.

Внешний ключ (Foreign Key)

Внешний ключ — это столбец в одной таблице, который ссылается на первичный ключ в другой таблице. Именно внешние ключи создают те самые «реляции» (связи) между данными.

Рассмотрим пример. У нас есть таблица Orders (Заказы). В ней есть свой первичный ключ order_id (номер заказа). Чтобы указать, кто именно сделал этот заказ, мы добавляем в таблицу Orders колонку customer_id. Эта колонка и будет внешним ключом. Она не содержит имени клиента или его адреса — она содержит только число, которое отсылает нас к таблице Customers (Клиенты).

Такой подход называется нормализацией данных. Мы не дублируем информацию. Если Иван Иванов изменит свой номер телефона, нам нужно будет обновить его только в одном месте — в таблице Customers. Все его 50 прошлых заказов в таблице Orders автоматически будут ссылаться на актуальные данные, потому что они связаны через неизменный customer_id.

Типы связей между таблицами

В бизнес-аналитике вы будете встречать три основных типа связей:

1. Один ко многим (One-to-Many). Самый частый случай. Один клиент может сделать много заказов. Одна категория товаров содержит много продуктов. В этом случае внешний ключ всегда находится на стороне «многих» (в таблице заказов).
2. Один к одному (One-to-One). Встречается реже. Например, у одного сотрудника может быть только один пропуск в офис, а у пропуска — только один владелец. Обычно такие данные можно хранить в одной таблице, но их разделяют из соображений безопасности (например, чтобы скрыть паспортные данные в отдельную таблицу с ограниченным доступом).
3. Многие ко многим (Many-to-Many). Один заказ может содержать много разных товаров, и один и тот же товар может присутствовать в тысячах разных заказов. Реляционные базы не умеют связывать таблицы напрямую таким образом. Для этого создается третья, промежуточная таблица (например, Order_Items), которая содержит внешние ключи обеих таблиц.

Что такое SQL и зачем он нужен аналитику

Теперь, когда мы понимаем, как данные хранятся и связываются, возникает вопрос: как их оттуда достать?

Для общения с реляционными базами данных был создан SQL (Structured Query Language — язык структурированных запросов). Это специализированный язык программирования, который позволяет создавать таблицы, добавлять в них данные, изменять их и, самое главное для аналитика, извлекать нужную информацию.

Главная особенность SQL заключается в том, что это декларативный язык. Когда вы пишете код на Python или C++, вы должны пошагово объяснить компьютеру, как получить результат (создай цикл, открой файл, прочитай строку, сравни значение). В SQL вы просто описываете, что вы хотите получить, а СУБД сама решает, как это сделать наиболее эффективным способом.

Пример бизнес-задачи: руководство просит вас узнать общую сумму продаж кофе «Капучино» за сентябрь 2023 года.

Если бы вы делали это в Excel, вам пришлось бы:

1. Открыть огромный файл.
2. Включить фильтр по дате (сентябрь 2023).
3. Включить фильтр по названию (Капучино).
4. Выделить колонку с ценой и посмотреть сумму внизу экрана.

В SQL вы пишете один запрос, который читается почти как обычный английский текст:

SELECT SUM(price)
FROM sales
WHERE product_name = 'Капучино'
  AND sale_date BETWEEN '2023-09-01' AND '2023-09-30';

Этот запрос отправится к СУБД. Она сама найдет нужную таблицу (sales), отфильтрует строки по условиям (WHERE) и сложит значения в колонке цены (SUM). В ответ вы получите не миллион строк, а одну единственную цифру — готовую выручку.

Почему аналитику недостаточно Excel или Python?

1. Объем данных. Excel физически ограничен 1 048 576 строками. Современный бизнес генерирует такой объем данных за несколько дней. Базы данных могут хранить миллиарды строк.
2. Скорость. СУБД используют сложные математические алгоритмы и индексы для поиска. Запрос к таблице на 10 миллионов строк может выполниться за миллисекунды.
3. Единый источник правды. В компании может быть сто аналитиков. Если каждый скачает себе файл Excel, через день у всех будут разные данные. База данных лежит на защищенном сервере, и все аналитики обращаются к одним и тем же актуальным данным в реальном времени.
4. Автоматизация. SQL-запросы можно встроить в BI-системы (Tableau, Power BI, Superset). Вы пишете запрос один раз, подключаете его к дашборду, и руководство каждое утро видит свежие графики без вашего участия.

Жизненный цикл аналитической задачи

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

Шаг 1: Понимание бизнес-требования. Менеджер по маркетингу спрашивает: «Какая рекламная кампания привела нам самых лояльных клиентов в прошлом году?»

Шаг 2: Перевод бизнеса на язык данных. Вы, как аналитик, понимаете, что «лояльный клиент» — это тот, кто сделал больше 5 заказов. Значит, вам нужно найти клиентов с количеством заказов > 5, посмотреть дату их регистрации (прошлый год) и узнать источник их перехода.

Шаг 3: Поиск таблиц. Вы изучаете структуру базы данных (схему) и понимаете, что вам понадобятся три таблицы: Users (пользователи), Orders (заказы) и Marketing_Campaigns (кампании).

Шаг 4: Написание SQL-запроса. Вы пишете код, который объединяет эти три таблицы с помощью внешних ключей (оператор JOIN, который мы подробно изучим в следующих статьях), фильтрует данные по дате и группирует их по названию кампании.

Шаг 5: Анализ и визуализация. СУБД возвращает вам небольшую, агрегированную таблицу (например, 10 строк с названиями кампаний и количеством лояльных клиентов). Вы загружаете этот небольшой результат в Excel, Python или BI-систему, строите красивую диаграмму и отправляете менеджеру.

Как видите, SQL — это мост между сырыми, непонятными терабайтами данных и готовыми бизнес-решениями. Без умения строить этот мост работа аналитика данных просто невозможна. В следующих статьях мы начнем писать наши первые запросы и шаг за шагом разберем все ключевые команды этого мощного языка.

Основы SELECT-запросов: как извлекать данные для бизнес-анализа

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

Любой анализ данных в SQL начинается с команды чтения. В отличие от Excel, где вы сразу видите весь массив на экране, база данных скрывает информацию до тех пор, пока вы явно не попросите её показать. Для этого используется оператор извлечения данных.

Базовая конструкция: SELECT и FROM

Самый простой SQL-запрос состоит из двух обязательных слов (клауз):

1. SELECT (выбрать) — указывает, какие столбцы вы хотите получить.
2. FROM (из) — указывает, из какой таблицы их нужно взять.

Представьте, что перед вами таблица transactions (транзакции), в которой фиксируется каждая продажа в сети кофеен. В ней есть колонки: transaction_id, date, barista_name, product_name, category, price, quantity.

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

SELECT product_name, price
FROM transactions;

СУБД пойдет в таблицу transactions, возьмет оттуда только две указанные колонки и вернет их вам в виде результирующей таблицы (датасета).

Почему SELECT * — плохая привычка

Если вы хотите увидеть абсолютно все колонки, вместо перечисления их названий можно использовать символ звездочки *:

SELECT *
FROM transactions;

На этапе обучения это удобно. Но в реальной бизнес-аналитике использование SELECT * считается дурным тоном по двум причинам:

1. Избыточная нагрузка на сеть и память. В корпоративных базах таблицы могут содержать по 100-200 колонок. Если вам нужна только выручка за день, а вы запрашиваете всю таблицу, база данных будет пересылать по сети гигабайты ненужной информации (например, длинные текстовые отзывы или служебные идентификаторы). Это замедлит работу и вашу, и сервера.
2. Хрупкость отчетов. Если вы подключите такой запрос к дашборду, а завтра разработчики добавят в таблицу новую колонку с техническими логами, ваш дашборд может сломаться из-за изменения структуры входящих данных.

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

Переименование столбцов: псевдонимы (AS)

Часто названия колонок в базе данных технические и некрасивые: txn_dt, prod_nm, amt. Если вы выгружаете данные для презентации руководству, такие заголовки не подойдут.

SQL позволяет переименовывать столбцы «на лету» в момент выгрузки с помощью оператора AS (от англ. alias — псевдоним):

SELECT
    product_name AS "Название товара",
    price AS "Цена (руб.)"
FROM transactions;

Обратите внимание: если ваш псевдоним содержит пробелы или спецсимволы, его обязательно нужно брать в двойные кавычки. Если это одно слово (например, AS revenue), кавычки можно опустить. Переименование никак не меняет саму базу данных — оно влияет только на то, как выглядит результат вашего конкретного запроса.

Уникальные значения: DISTINCT

Часто аналитику нужно узнать не сколько раз купили товар, а какие вообще товары покупали. Например, вы хотите посмотреть список всех категорий меню, которые есть в базе.

Если вы напишете SELECT category FROM transactions;, вы получите миллион строк, где слово «Кофе» повторится 500 000 раз, а «Выпечка» — 300 000 раз. Чтобы оставить только уникальные значения, используется ключевое слово DISTINCT:

SELECT DISTINCT category
FROM transactions;

Результатом будет компактная таблица из нескольких строк: Кофе, Чай, Выпечка, Десерты, Сэндвичи.

Фильтрация данных: WHERE

Мы научились выбирать нужные столбцы. Но главная сила SQL — в умении отсекать ненужные строки. Для этого используется оператор WHERE (где), после которого пишется логическое условие. СУБД проверяет каждую строку таблицы: если условие выполняется (истина), строка попадает в итоговый отчет; если нет (ложь) — отбрасывается.

Математические операторы сравнения

Для числовых данных и дат работают стандартные математические правила. Допустим, мы хотим найти все транзакции, где цена товара строго больше 300 рублей ($p > 300$):

SELECT product_name, price
FROM transactions
WHERE price > 300;

Доступные операторы:

• = — равно
• > — больше
• < — меньше
• >= — больше или равно
• <= — меньше или равно
• <> или != — не равно (синтаксис зависит от конкретной СУБД, но <> является стандартом)

Работа с текстом

При фильтрации по текстовым колонкам искомое значение всегда берется в одинарные кавычки.

SELECT date, product_name, price
FROM transactions
WHERE category = 'Выпечка';

Если вы напишете WHERE category = Выпечка (без кавычек), база данных подумает, что Выпечка — это название другой колонки, и выдаст ошибку.

Логические операторы: AND, OR, NOT

Бизнес-задачи редко бывают простыми. Обычно нужно проверить сразу несколько условий.

AND (И) требует, чтобы выполнялись оба условия одновременно. Найдем дорогие десерты:

SELECT product_name, price
FROM transactions
WHERE category = 'Десерты' AND price >= 400;

OR (ИЛИ) требует, чтобы выполнялось хотя бы одно из условий. Найдем все продажи кофе или чая:

SELECT product_name, category
FROM transactions
WHERE category = 'Кофе' OR category = 'Чай';

NOT (НЕ) инвертирует условие. Найдем все транзакции, кроме продаж воды:

SELECT product_name, price
FROM transactions
WHERE NOT category = 'Вода';

Важный нюанс: оператор AND имеет приоритет над OR (как умножение имеет приоритет над сложением в математике). Если вы комбинируете их, всегда используйте скобки для явного указания логики.

Запрос WHERE category = 'Кофе' OR category = 'Чай' AND price > 300 вернет весь кофе (любой цены) и только тот чай, который дороже 300.

Правильный вариант: WHERE (category = 'Кофе' OR category = 'Чай') AND price > 300.

Продвинутые операторы фильтрации

Чтобы не писать длинные цепочки из OR, в SQL есть элегантные решения.

Оператор IN позволяет проверить, входит ли значение в заданный список. Тот же запрос на поиск кофе и чая можно переписать гораздо чище:

SELECT product_name, category
FROM transactions
WHERE category IN ('Кофе', 'Чай', 'Какао');

Оператор BETWEEN идеально подходит для диапазонов (чисел или дат). Он включает в себя обе границы. Найдем транзакции за первую неделю сентября:

SELECT date, product_name
FROM transactions
WHERE date BETWEEN '2023-09-01' AND '2023-09-07';

Оператор LIKE используется для поиска по текстовому шаблону (паттерну). Это спасение, когда вы не знаете точного названия. В LIKE используются два спецсимвола:

• % (процент) — заменяет любое количество любых символов (даже ноль символов).
• _ (подчеркивание) — заменяет ровно один любой символ.

Например, руководство просит найти продажи всех видов сиропа. В базе они могут называться «Карамельный сироп», «Сироп ваниль», «Доп. сироп малина».

SELECT product_name, price
FROM transactions
WHERE product_name LIKE '%сироп%';

Этот запрос найдет все строки, где слово «сироп» встречается в любом месте названия.

Коварный NULL

В базах данных есть особое состояние — NULL. Это не ноль, не пустая строка и не пробел. NULL означает «данные отсутствуют» или «неизвестно».

Например, если клиент оплатил заказ наличными, колонка bank_card_type будет пустой (NULL).

Вы не можете проверить NULL с помощью знака равенства (= NULL не сработает), потому что нельзя быть «равным неизвестности». Для этого используются специальные операторы IS NULL и IS NOT NULL.

-- Найти транзакции, где не указан бариста
SELECT transaction_id, date
FROM transactions
WHERE barista_name IS NULL;

Сортировка результатов: ORDER BY

По умолчанию база данных возвращает строки в том порядке, в котором ей удобно их читать с жесткого диска. Этот порядок хаотичен. Чтобы упорядочить отчет, применяется ORDER BY.

Сортировать можно по возрастанию — ASC (от англ. ascending, применяется по умолчанию) или по убыванию — DESC (от англ. descending).

Выведем самые дорогие чеки наверх:

SELECT date, product_name, price
FROM transactions
ORDER BY price DESC;

Можно сортировать сразу по нескольким колонкам. Например, сначала выстроим категории по алфавиту, а внутри каждой категории отсортируем товары от самых дорогих к дешевым:

SELECT category, product_name, price
FROM transactions
ORDER BY category ASC, price DESC;

Ограничение количества строк: LIMIT

Когда вы ищете аномалии или строите рейтинги, вам редко нужны все миллионы строк. Чаще всего бизнес просит «Топ-10» или «Топ-5». Оператор LIMIT обрезает итоговую таблицу до указанного количества строк.

LIMIT почти всегда используется в паре с ORDER BY. Если использовать LIMIT без сортировки, вы получите случайные строки.

Найдем 3 самых дешевых товара в меню:

SELECT product_name, price
FROM transactions
ORDER BY price ASC
LIMIT 3;

Порядок выполнения запроса: как мыслит СУБД

Это самая важная концепция для начинающего аналитика. То, в каком порядке мы пишем SQL-запрос, кардинально отличается от того, в каком порядке база данных его выполняет.

Мы пишем запрос так:

1. SELECT (что показать)
2. FROM (откуда)
3. WHERE (как отфильтровать)
4. ORDER BY (как отсортировать)
5. LIMIT (сколько оставить)

Но СУБД читает его иначе. Представьте себя на месте базы данных:

1. FROM: Сначала база должна найти саму таблицу на диске. Она берет все сырые данные.
2. WHERE: Затем она применяет фильтры, отбрасывая лишние строки. На этом этапе она еще видит все колонки.
3. SELECT: Только теперь база «отрезает» ненужные столбцы, оставляя те, что вы запросили (и применяет псевдонимы AS).
4. ORDER BY: Оставшийся компактный набор данных сортируется в памяти.
5. LIMIT: Отсортированный массив обрезается до нужного количества строк и отправляется вам на экран.

Почему это важно знать на практике? Поскольку SELECT (где мы задаем псевдонимы AS) выполняется после WHERE, вы не можете использовать придуманный псевдоним в блоке фильтрации.

Если вы напишете:

SELECT product_name AS name, price
FROM transactions
WHERE name = 'Капучино'; -- ОШИБКА!

База выдаст ошибку, потому что на этапе выполнения WHERE она еще не знает, что такое name. Она знает только оригинальное название product_name.

Бизнес-кейс: собираем всё вместе

Давайте решим реальную задачу.

Ситуация: Маркетолог запустил акцию на осеннее меню. Он просит вас выгрузить топ-5 самых крупных транзакций по продаже напитков, в названии которых есть слово «Тыквенный», совершенных во второй половине октября 2023 года. В отчете должны быть только дата, название и цена, причем колонки должны называться по-русски для вставки в презентацию.

Решение:

SELECT
    date AS "Дата продажи",
    product_name AS "Напиток",
    price AS "Сумма"
FROM transactions
WHERE product_name LIKE '%Тыквенный%'
  AND date BETWEEN '2023-10-15' AND '2023-10-31'
ORDER BY price DESC
LIMIT 5;

Этот шаблон — ваш первый полноценный аналитический инструмент. Меняя названия таблиц, условия в WHERE и колонки в SELECT, вы уже можете ответить на десятки базовых вопросов бизнеса. В следующей статье мы перейдем к агрегации данных: научимся не просто выгружать строки, а считать суммы, средние чеки и конверсии с помощью GROUP BY.

Продвинутая фильтрация и сортировка: подготовка данных для реального бизнеса

Как мы разобрали ранее, базовые операторы извлечения и ограничения строк позволяют отвечать на прямые вопросы к базе данных. Однако в реальной бизнес-аналитике данные редко бывают идеальными. Клиенты опечатываются при вводе email-адресов, менеджеры по продажам по-разному заполняют статусы сделок, а руководству нужны дашборды, которые обновляются автоматически каждый день без переписывания кода.

Чтобы перейти от разовых выгрузок к построению надежных аналитических отчетов, необходимо освоить инструменты трансформации данных «на лету» — прямо внутри условий фильтрации и сортировки.

Укрощение текстовых данных: функции форматирования

Реляционные базы данных мыслят буквально. Для СУБД строки «Иван», «иван» и « Иван » (с пробелом в начале) — это три абсолютно разных значения. Если вы попытаетесь найти все покупки Ивана с помощью строгого равенства, вы потеряете часть данных, что приведет к искажению финансового отчета.

Для решения этой проблемы используются строковые функции — специальные команды, которые преобразуют текст перед тем, как база данных сравнит его с вашим условием.

Приведение к единому регистру

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

Представьте, что вы ищете транзакции по промокоду «SUMMER2023». Клиенты могли ввести его как «summer2023», «Summer2023» или даже «SuMmEr2023».

SELECT date, price, discount
FROM transactions
WHERE LOWER(promocode) = 'summer2023';

В этом запросе СУБД берет значение из колонки promocode, виртуально переводит все его буквы в строчные и только потом сравнивает с эталоном 'summer2023'. Исходные данные в самой таблице при этом не изменяются.

Очистка от мусорных символов

Частая боль аналитика — невидимые пробелы. Пользователь скопировал email из другого документа и случайно захватил пробел в конце. Визуально в таблице это незаметно, но фильтр не сработает.

Функция TRIM() удаляет пробелы (а при необходимости и другие указанные символы) с начала и конца строки.

SELECT user_id, email
FROM users
WHERE TRIM(LOWER(email)) = 'ceo@company.com';

Обратите внимание на вложенность: TRIM(LOWER(email)). SQL выполняет функции изнутри наружу. Сначала текст переводится в нижний регистр, затем отсекаются пробелы по краям, и результат сравнивается с искомым значением. Это золотой стандарт поиска по текстовым полям, введенным вручную.

Аналитический инсайт: Использование функций внутри блока фильтрации делает запрос надежным, но замедляет его выполнение. СУБД вынуждена применить функцию к каждой строке таблицы, прежде чем проверить условие. На таблицах в десятки миллионов строк это может стать проблемой. В таких случаях инженеры данных создают специальные функциональные индексы, но на этапе исследования данных подход с LOWER() и TRIM() абсолютно оправдан.

Динамическая фильтрация дат

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

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

Текущая дата и время

В SQL существует системная переменная CURRENT_DATE (в некоторых диалектах GETDATE() или CURDATE()), которая всегда возвращает сегодняшнюю дату сервера.

Чтобы получить все продажи за сегодня, достаточно написать:

SELECT transaction_id, price
FROM transactions
WHERE date = CURRENT_DATE;

Интервальная арифметика

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

Допустим, маркетолог просит выгрузить список клиентов, которые совершили покупку за последние 7 дней. Запрос будет выглядеть так:

SELECT user_id, date, price
FROM transactions
WHERE date >= CURRENT_DATE - INTERVAL '7 days';

Если сегодня 15 октября, база данных сама вычислит, что CURRENT_DATE - INTERVAL '7 days' равно 8 октября, и вернет все транзакции, начиная с этой даты.

Извлечение частей даты

Иногда бизнес интересует не конкретный период, а сезонность. Например: «Как продается холодный кофе в летние месяцы независимо от года?».

Для этого применяется функция EXTRACT(), которая позволяет «вытащить» нужный компонент из полной даты (год, месяц, день, час).

SELECT date, product_name, price
FROM transactions
WHERE category = 'Холодный кофе'
  AND EXTRACT(MONTH FROM date) IN (6, 7, 8);

Этот запрос пройдется по всей истории компании и выберет продажи только за июнь, июль и август любого года.

Продвинутая логика: управление неизвестностью

В реальных базах данных часто не хватает информации. Как мы помним, отсутствие данных обозначается маркером неизвестности. Но что делать, если эта неизвестность ломает математику отчета?

Рассмотрим классический кейс. В таблице товаров есть колонка regular_price (обычная цена) и discount_price (цена по скидке). Если товар продается без скидки, в колонке discount_price стоит пустота.

Аналитику нужно посчитать потенциальную выручку от продажи 10 единиц каждого товара по его актуальной цене. Если мы просто умножим discount_price на 10, то для товаров без скидки получим пустоту (любая математическая операция с неизвестностью дает неизвестность).

Функция COALESCE

Для элегантного решения таких проблем существует функция COALESCE(). Она принимает список значений через запятую и возвращает первое непустое значение, читая слева направо.

SELECT
    product_name,
    regular_price,
    discount_price,
    COALESCE(discount_price, regular_price) AS actual_price
FROM products;

Как это работает:

1. СУБД смотрит на discount_price.
2. Если там есть число (например, 150), функция возвращает 150 и прекращает работу.
3. Если там пустота, функция переходит к следующему аргументу — regular_price — и возвращает его (например, 200).

Функцию COALESCE можно использовать прямо в блоке фильтрации. Найдем товары, чья актуальная цена (со скидкой или без) меньше 300 рублей:

SELECT product_name
FROM products
WHERE COALESCE(discount_price, regular_price) < 300;

Это избавляет нас от необходимости писать громоздкие конструкции с множеством логических операторов проверки на пустоту.

Кастомная сортировка: выход за рамки алфавита

Стандартная сортировка отлично справляется с числами (от меньшего к большему) и текстом (от А до Я). Но бизнес-логика редко подчиняется алфавиту.

Представьте таблицу support_tickets (обращения в поддержку). У каждого обращения есть текстовый статус: «Новое», «В работе», «Ожидает ответа», «Закрыто».

Если вы примените стандартную сортировку по возрастанию, база данных выстроит статусы по алфавиту:

1. В работе
2. Закрыто
3. Новое
4. Ожидает ответа

Для руководителя отдела поддержки такой отчет бесполезен. Ему нужно видеть сначала «Новые» обращения, затем те, что «В работе», потом «Ожидает ответа», и только в самом конце — «Закрытые».

Оператор CASE WHEN

Чтобы научить базу данных бизнес-логике, используется условный оператор CASE WHEN (аналог функции «ЕСЛИ» в Excel). Он позволяет создать скрытую систему весов прямо в момент сортировки.

SELECT ticket_id, client_name, status
FROM support_tickets
ORDER BY
    CASE status
        WHEN 'Новое' THEN 1
        WHEN 'В работе' THEN 2
        WHEN 'Ожидает ответа' THEN 3
        WHEN 'Закрыто' THEN 4
        ELSE 5
    END ASC;

Как мыслит СУБД при выполнении этого запроса:

1. Она берет первую строку. Видит статус «В работе».
2. Прогоняет его через блок CASE. Статус совпадает со вторым условием, база данных виртуально присваивает этой строке цифру 2.
3. Берет следующую строку. Статус «Новое». Присваивает цифру 1.
4. Когда всем строкам присвоены скрытые числовые веса, СУБД сортирует таблицу по этим цифрам по возрастанию.

В итоговом отчете вы не увидите этих цифр 1, 2, 3, 4 — они существуют только в оперативной памяти сервера в момент сортировки. Вы получите красиво упорядоченный по бизнес-логике список.

Блок ELSE в этой конструкции служит страховкой. Если завтра разработчики добавят новый статус (например, «Передано разработчикам»), о котором ваш запрос не знает, он получит вес 5 и окажется в самом низу списка, не сломав основную сортировку.

Управление неизвестностью при сортировке

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

Допустим, вы строите рейтинг клиентов по количеству накопленных бонусных баллов (loyalty_points). У новых клиентов баллов еще нет, в их ячейках пустота.

SELECT client_name, loyalty_points
FROM clients
ORDER BY loyalty_points DESC;

В зависимости от конкретной СУБД (PostgreSQL, MySQL, Oracle), пустые значения могут оказаться либо в самом верху списка, либо в самом низу. База данных не знает, считать ли пустоту бесконечно большим числом или бесконечно малым.

Если ваша СУБД выводит пустые значения наверх, ваш отчет «Топ лучших клиентов» будет состоять из людей без баллов. Чтобы взять ситуацию под контроль, в SQL существуют модификаторы NULLS FIRST (пустые сначала) и NULLS LAST (пустые в конце).

Правильный аналитический запрос для рейтинга выглядит так:

SELECT client_name, loyalty_points
FROM clients
ORDER BY loyalty_points DESC NULLS LAST;

Теперь на первом месте гарантированно окажется клиент с максимальным количеством баллов, а все новички без баллов скромно разместятся в конце отчета.

Многоуровневая сортировка с вычислениями

Сортировать можно не только по существующим колонкам или условным конструкциям, но и по результатам математических вычислений.

Представьте задачу: нужно вывести список товаров, отсортированный по размеру скидки в рублях. У нас есть только обычная цена и цена со скидкой. Нам не обязательно создавать новую колонку в блоке извлечения данных, мы можем провести вычисление прямо в блоке сортировки:

SELECT product_name, regular_price, discount_price
FROM products
WHERE discount_price IS NOT NULL
ORDER BY (regular_price - discount_price) DESC;

База данных «на лету» вычислит разницу для каждой строки и выстроит товары от самой большой выгоды к самой маленькой.

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

Агрегация данных и GROUP BY: от сырых строк к бизнес-метрикам

В предыдущих материалах мы научились извлекать, фильтровать и сортировать отдельные строки. Однако руководству компании редко нужны списки из сотен тысяч транзакций. Бизнес мыслит категориями итогов: «Какова выручка за месяц?», «Сколько у нас активных клиентов?», «Какой товар приносит наибольшую прибыль?».

Чтобы ответить на эти вопросы, необходимо перейти от работы с отдельными записями к математическим вычислениям над целыми наборами данных. Этот процесс называется агрегацией.

Агрегатные функции: сжатие информации

Агрегатные функции — это специальные SQL-команды, которые принимают на вход множество значений (например, весь столбец таблицы), а возвращают одно итоговое вычисленное значение.

В SQL существует пять базовых агрегатных функций, которые покрывают 90% аналитических задач:

• SUM() — вычисляет сумму всех числовых значений.
• AVG() — находит среднее арифметическое.
• MIN() — возвращает минимальное значение.
• MAX() — возвращает максимальное значение.
• COUNT() — подсчитывает количество строк.

Представьте, что у нас есть таблица sales (продажи), в которой хранятся данные о каждой проданной единице товара. Чтобы узнать общую выручку компании за всё время, мы применяем функцию SUM к столбцу price:

SELECT SUM(price) AS total_revenue
FROM sales;

Вместо миллионов строк база данных вернет ровно одну строку и один столбец с итоговой суммой.

Особенности работы с неизвестностью (NULL)

Важнейшее свойство агрегатных функций — их отношение к пустым значениям. Все агрегатные функции (кроме одной) автоматически игнорируют NULL.

Если вы считаете среднюю оценку товаров AVG(rating), и у половины товаров нет оценок (NULL), СУБД сложит только существующие оценки и разделит их на количество товаров с оценками. Пустые строки не испортят математику, занизив средний балл.

Исключением является функция подсчета строк, у которой есть две вариации:

1. COUNT(column_name) — считает только те строки, где в указанном столбце есть данные (игнорирует NULL).
2. COUNT(*) — считает абсолютно все строки в таблице, независимо от того, заполнены в них ячейки или нет.

Аналитический инсайт: Если вам нужно узнать общее количество зарегистрированных пользователей, используйте COUNT(*). Если нужно узнать, сколько из них указали свой номер телефона — используйте COUNT(phone). Разница между этими двумя числами покажет количество профилей без телефона.

Разделяй и властвуй: оператор GROUP BY

Агрегатные функции сами по себе полезны, но их истинная мощь раскрывается в связке с оператором GROUP BY.

GROUP BY — это команда, которая объединяет строки с одинаковыми значениями в указанных столбцах в виртуальные группы (корзины), чтобы затем применить агрегатную функцию к каждой группе по отдельности.

Представьте, что перед вами гора монет разного номинала. Если применить просто SUM(), вы посчитаете общую сумму всех денег. Но если сначала разложить монеты в кучки по номиналу (сгруппировать), а затем применить COUNT() к каждой кучке, вы узнаете, сколько у вас десятирублевых монет, сколько пятирублевых и так далее.

Рассмотрим задачу: нам нужно узнать выручку не в целом по компании, а в разрезе каждой категории товаров.

SELECT
    category,
    SUM(price) AS category_revenue
FROM sales
GROUP BY category;

Как СУБД выполняет этот запрос:

1. Просматривает столбец category и находит все уникальные значения (например, «Электроника», «Одежда», «Книги»).
2. Создает для каждой категории свою невидимую «корзину» и раскладывает в них соответствующие строки.
3. Внутри каждой корзины берет столбец price и суммирует его значения.
4. Выводит итоговую таблицу, где каждой категории соответствует своя сумма.

Золотое правило группировки

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

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

Почему так происходит? Допустим, вы написали ошибочный запрос:

-- ОШИБОЧНЫЙ ЗАПРОС
SELECT category, product_name, SUM(price)
FROM sales
GROUP BY category;

Вы просите базу данных сгруппировать всё по категориям. В категории «Электроника» лежат сотни разных товаров (ноутбуки, телефоны, наушники). СУБД может посчитать общую сумму для «Электроники», но она не знает, какое именно название товара (product_name) вывести в итоговой строке рядом с этой суммой. Выбрать случайное? Вывести все через запятую? Реляционная логика не допускает двусмысленности, поэтому запрос выдаст ошибку.

Многомерный анализ: группировка по нескольким столбцам

Бизнес-задачи редко ограничиваются одним измерением. Руководству нужно видеть динамику: как продавались разные категории товаров по месяцам.

Мы можем передать в GROUP BY сразу несколько столбцов (или результатов функций) через запятую. В этом случае СУБД создаст группы для каждой уникальной комбинации значений.

SELECT
    EXTRACT(MONTH FROM date) AS sales_month,
    category,
    SUM(price) AS revenue
FROM sales
GROUP BY
    EXTRACT(MONTH FROM date),
    category
ORDER BY
    sales_month ASC,
    revenue DESC;

В результате мы получим детализированный отчет: выручка за январь по электронике, выручка за январь по одежде, выручка за февраль по электронике и так далее.

Фильтрация групп: оператор HAVING

Представьте новую задачу: маркетолог просит вывести список только тех категорий товаров, которые принесли выручку более 1 000 000 рублей.

Логичный порыв новичка — использовать уже знакомый инструмент фильтрации:

-- ОШИБОЧНЫЙ ЗАПРОС
SELECT category, SUM(price)
FROM sales
WHERE SUM(price) > 1000000
GROUP BY category;

Этот код не сработает. Причина кроется в строгом порядке выполнения SQL-запроса сервером. Напомним, что WHERE отрабатывает до блока SELECT. Но теперь в нашем уравнении появился GROUP BY.

Новый порядок выполнения запроса выглядит так:

1. FROM — откуда берем данные.
2. WHERE — отсеиваем лишние сырые строки.
3. GROUP BY — разбиваем оставшиеся строки на группы.
4. HAVING — отсеиваем лишние группы.
5. SELECT — выбираем столбцы для вывода.
6. ORDER BY — сортируем результат.

База данных физически не может отфильтровать данные в WHERE по условию SUM(price), потому что на этапе WHERE математика еще не посчитана! Группировка и суммирование произойдут только на следующем шаге.

Для фильтрации данных после агрегации был создан оператор HAVING.

-- ПРАВИЛЬНЫЙ ЗАПРОС
SELECT category, SUM(price) AS revenue
FROM sales
GROUP BY category
HAVING SUM(price) > 1000000;

Разделение зон ответственности

WHERE и HAVING часто используются в одном запросе, но выполняют разные роли.

Допустим, нам нужны категории с выручкой более миллиона, но считать мы хотим только успешные сделки (статус 'Оплачено').

SELECT category, SUM(price) AS revenue
FROM sales
WHERE status = 'Оплачено'       -- 1. Отбрасываем неоплаченные строки
GROUP BY category               -- 2. Группируем успешные продажи
HAVING SUM(price) > 1000000;    -- 3. Отбрасываем категории с малой суммой

Характеристика	WHERE	HAVING
Что фильтрует?	Отдельные сырые строки	Сгруппированные данные и итоги
Когда выполняется?	До группировки (GROUP BY)	После группировки (GROUP BY)
Можно ли использовать агрегатные функции?	Нет	Да (SUM, AVG, COUNT и т.д.)

Бизнес-шаблоны: считаем ключевые метрики

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

Уникальные клиенты и COUNT(DISTINCT)

Если мы просто напишем COUNT(client_id), мы посчитаем количество транзакций. Но один лояльный клиент мог совершить 10 покупок. Чтобы узнать размер реальной клиентской базы, нам нужно посчитать только уникальные идентификаторы.

Для этого внутри агрегатной функции используется ключевое слово DISTINCT:

SELECT
    EXTRACT(MONTH FROM date) AS month,
    COUNT(DISTINCT client_id) AS unique_customers
FROM sales
GROUP BY EXTRACT(MONTH FROM date);

Средний чек (Average Order Value - AOV)

Средний чек — это отношение общей выручки к количеству уникальных заказов.

Формула расчета: $AOV = \frac{Total Revenue}{Total Orders}$, где $Total Revenue$ — общая сумма продаж, а $Total Orders$ — количество уникальных чеков.

В базе данных один заказ (один чек) часто состоит из нескольких строк, если клиент купил несколько разных товаров. У этих строк будет одинаковый order_id. Поэтому мы не можем просто использовать AVG(price) — это даст нам среднюю стоимость одного товара, а не всего заказа.

Правильный расчет среднего чека в SQL выглядит так:

SELECT
    SUM(price) / COUNT(DISTINCT order_id) AS average_order_value
FROM sales;

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

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

Соединение таблиц с помощью JOIN: собираем бизнес-пазл

В реальных базах данных информация никогда не хранится в одной огромной таблице. Как мы выяснили на первых уроках, процесс нормализации разбивает данные на логические блоки: клиенты хранятся отдельно, заказы — отдельно, а товары — в своей собственной таблице. Это спасает базу от дублирования и аномалий, но создает проблему для аналитика.

Руководству не нужны разрозненные идентификаторы. Бизнес хочет видеть понятный отчет: «Имя клиента — Название товара — Сумма покупки». Чтобы собрать этот пазл воедино, нам нужен механизм горизонтального связывания данных. В SQL за это отвечает семейство операторов JOIN.

Анатомия объединения: как работает JOIN

Оператор JOIN (соединение) позволяет объединить столбцы из двух и более таблиц в один результирующий набор данных на основе логического условия — совпадения значений в определенных столбцах.

Представьте, что у вас есть два списка на бумаге. В левой руке — список заказов, где указан номер клиента (например, Клиент №5). В правой руке — картотека клиентов. Вы берете первую строчку из левого списка, видите «Клиент №5», ищете карточку с номером 5 в правом списке и переписываете его имя рядом с заказом. Именно так физически работает база данных.

Базовый синтаксис соединения выглядит так:

SELECT
    столбцы_для_вывода
FROM таблица_1
JOIN таблица_2
    ON таблица_1.ключ = таблица_2.ключ;

Ключевое слово ON задает условие соединения. Чаще всего это равенство внешнего ключа одной таблицы и первичного ключа другой.

Алиасы таблиц: правило хорошего тона

Когда мы работаем с несколькими таблицами, в них могут быть столбцы с одинаковыми названиями (например, столбец id есть почти везде). Чтобы СУБД поняла, какой именно id вы хотите вывести, необходимо указывать имя таблицы перед столбцом: clients.id.

Писать полные названия таблиц долго и неудобно. Поэтому в профессиональной аналитике всегда используются алиасы таблиц — короткие псевдонимы, которые назначаются в блоке FROM.

SELECT
    c.name,
    o.order_date,
    o.amount
FROM clients AS c
JOIN orders AS o
    ON c.id = o.client_id;

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

INNER JOIN: строгое пересечение

INNER JOIN (внутреннее соединение) — это самый строгий тип объединения. Он возвращает только те строки, для которых нашлось совпадение в обеих таблицах.

Допустим, у нас есть таблица users (зарегистрированные пользователи) и таблица transactions (оплаты).

Если мы используем INNER JOIN, в итоговый отчет попадут только те пользователи, которые совершили хотя бы одну транзакцию. Пользователи без покупок будут отброшены. Транзакции, у которых по какой-то причине нет привязки к пользователю (например, системные списания), также будут отброшены.

SELECT
    u.email,
    t.transaction_id,
    t.amount
FROM users u
INNER JOIN transactions t
    ON u.id = t.user_id;

Примечание: слово INNER можно опустить. Если вы напишете просто JOIN, база данных по умолчанию выполнит именно внутреннее соединение.

Бизнес-кейс для INNER JOIN: анализ среднего чека авторизованных клиентов. Нам не нужны «пустые» профили, нам нужны только реальные факты продаж, связанные с конкретными людьми.

LEFT JOIN: сохраняем основу

В аналитике данных LEFT JOIN (левое внешнее соединение) используется в 80% случаев.

Этот оператор берет все строки из левой таблицы (той, что указана сразу после FROM) и ищет для них совпадения в правой таблице. Если совпадение найдено — данные склеиваются. Если совпадения нет — строка из левой таблицы все равно остается в отчете, а на месте данных из правой таблицы подставляются значения NULL.

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

Если мы используем INNER JOIN, клиенты без покупок просто исчезнут из отчета. Маркетолог их не увидит. Нам нужен LEFT JOIN:

SELECT
    c.client_id,
    c.name,
    SUM(o.amount) AS total_spent
FROM clients c
LEFT JOIN orders o
    ON c.id = o.client_id
GROUP BY
    c.client_id,
    c.name;

Для клиентов, которые ничего не покупали, функция агрегации SUM получит на вход NULL и вернет пустую ячейку. Чтобы сделать отчет красивым, мы можем обернуть сумму в функцию безопасной обработки пустот: COALESCE(SUM(o.amount), 0). Тогда вместо NULL маркетолог увидит понятный 0.

RIGHT JOIN и FULL OUTER JOIN

RIGHT JOIN работает абсолютно зеркально левому соединению: он сохраняет все строки из правой таблицы. На практике аналитики почти никогда его не используют. Человеческий мозг привык читать слева направо, поэтому логичнее поменять таблицы местами в коде и использовать привычный LEFT JOIN, чем ломать голову над RIGHT JOIN.

FULL OUTER JOIN (полное внешнее соединение) — это комбинация левого и правого соединений. Он возвращает вообще все строки из обеих таблиц. Если есть совпадение — склеивает их. Если нет — оставляет NULL с той стороны, где данных не хватило.

Бизнес-кейс для FULL OUTER JOIN: сверка данных (реконсиляция).

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

SELECT
    i.order_id AS internal_order,
    g.order_id AS gateway_order,
    i.amount AS internal_amount,
    g.amount AS gateway_amount
FROM internal_payments i
FULL OUTER JOIN gateway_payments g
    ON i.transaction_id = g.transaction_id
WHERE
    i.order_id IS NULL
    OR g.order_id IS NULL;

Этот запрос покажет «потеряшки»: платежи, которые есть у нас, но нет в шлюзе (возможно, ошибка синхронизации), и платежи, которые прошли в шлюзе, но не записались к нам (критическая ошибка, клиент потерял деньги).

CROSS JOIN: матрица комбинаций

CROSS JOIN (перекрестное соединение) стоит особняком. Ему не нужно условие ON. Он просто берет каждую строку из первой таблицы и соединяет ее с каждой строкой из второй таблицы.

В математике это называется декартово произведение. Если в первой таблице $N$ строк, а во второй $M$ строк, то результат будет содержать $N \times M$ строк.

Зачем это нужно? Чаще всего — для создания аналитических каркасов.

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

Чтобы этого избежать, аналитики сначала создают идеальную сетку с помощью CROSS JOIN:

-- Создаем каркас: 12 месяцев * 3 категории = 36 строк
SELECT
    m.month_name,
    c.category_name
FROM months m
CROSS JOIN categories c;

А затем к этому идеальному каркасу через LEFT JOIN прикручивают реальные продажи. Там, где продаж не было, появятся нули, и график в BI-системе (например, Tableau или PowerBI) отрисуется корректно, с падением линии в ноль, а не с разрывом.

Ловушка аналитика: размножение строк (Cartesian Explosion)

Самая частая и самая опасная ошибка при работе с JOIN — это непреднамеренное дублирование данных, которое приводит к искажению финансовых метрик.

Правило, которое нужно высечь в камне:

JOIN не просто ищет совпадения. Если для одной строки из левой таблицы находится несколько подходящих строк в правой таблице, левая строка будет продублирована столько раз, сколько совпадений найдено.

Рассмотрим классический пример. У нас есть таблица заказов orders и таблица позиций внутри заказа order_items.

Таблица orders:

order_id	client_id	total_price
101	5	5000

Таблица order_items:

item_id	order_id	product_name	price
1	101	Мышь	1000
2	101	Клавиатура	4000

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

SELECT
    SUM(o.total_price) AS revenue
FROM orders o
LEFT JOIN order_items i
    ON o.order_id = i.order_id;

Что сделает база данных? Она возьмет заказ 101 из левой таблицы. Пойдет в правую таблицу. Найдет там две строки с order_id = 101. База данных обязана показать обе позиции, поэтому она продублирует строку заказа.

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

order_id	total_price	product_name
101	5000	Мышь
101	5000	Клавиатура

Теперь функция SUM(total_price) сложит 5000 + 5000. Аналитик отчитается руководству о выручке в 10 000 рублей, хотя реальный заказ был всего на 5 000. Это катастрофа для бизнеса.

Как избежать размножения строк?

1. Понимать гранулярность (уровень детализации) таблиц. Перед объединением всегда задавайте себе вопрос: «Что является уникальной строкой в левой таблице, а что — в правой?». Связь «один-к-одному» безопасна. Связь «один-ко-многим» всегда вызывает дублирование левой стороны.
2. Агрегировать до джоина. Если вам нужна общая сумма заказов, считайте ее до присоединения детальных строк, либо используйте подзапросы (о них мы поговорим в следующих статьях).
3. Проверять себя. Всегда сравнивайте количество строк до JOIN и после. Если вы делаете LEFT JOIN к справочнику (где ключи уникальны), количество строк измениться не должно. Если оно выросло — вы поймали дубль.

Тонкости фильтрации: ON против WHERE

Когда мы используем LEFT JOIN, у нас появляется два места, где можно написать условия фильтрации: в блоке ON и в блоке WHERE. Для новичков они кажутся одинаковыми, но их логика кардинально отличается.

Допустим, мы хотим получить список всех клиентов и их заказы, но только те заказы, которые имеют статус 'Доставлен'.

Вариант 1: Фильтрация в ON

SELECT c.name, o.order_id
FROM clients c
LEFT JOIN orders o
    ON c.id = o.client_id
    AND o.status = 'Доставлен';

Как это работает: СУБД берет всех клиентов. Затем она пытается приклеить к ним заказы. Но приклеивает только доставленные. Если у клиента были только отмененные заказы, они не приклеятся. Но сам клиент из отчета не исчезнет, так как это LEFT JOIN. Напротив его имени просто будет стоять NULL.

Вариант 2: Фильтрация в WHERE

SELECT c.name, o.order_id
FROM clients c
LEFT JOIN orders o
    ON c.id = o.client_id
WHERE o.status = 'Доставлен';

Как это работает: СУБД сначала честно выполняет LEFT JOIN, склеивая клиентов со всеми их заказами (и оставляя NULL для тех, кто ничего не купил). Формируется огромная промежуточная таблица. И только потом в дело вступает WHERE. Он жестко отсекает все строки, где статус не равен 'Доставлен'.

Что произойдет с клиентами, у которых был NULL вместо заказа? Выражение NULL = 'Доставлен' дает ложь. Эти клиенты будут удалены из отчета!

Золотое правило: При использовании LEFT JOIN фильтрация по правой таблице в блоке WHERE фактически превращает ваш LEFT JOIN в INNER JOIN. Если вы хотите сохранить все строки левой таблицы, пишите условия для правой таблицы внутри блока ON.

Умение правильно соединять таблицы — это водораздел между начинающим пользователем базы данных и настоящим аналитиком. JOIN позволяет собирать разрозненные факты в связные бизнес-истории. В следующей статье мы расширим наш арсенал и научимся использовать результаты одних запросов внутри других с помощью подзапросов и CTE.

Подзапросы и Common Table Expressions: многоуровневая логика в SQL

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

Вы не можете написать WHERE amount > AVG(amount), потому что, как мы помним из логического порядка выполнения запроса, фильтрация строк (WHERE) происходит до того, как база данных вычислит агрегаты (GROUP BY и агрегатные функции). Нам нужно сначала посчитать средний чек, запомнить это число, а затем сравнить с ним каждый заказ.

Для решения таких многоходовых задач в SQL используются подзапросы (вложенные запросы) и CTE (Common Table Expressions).

Скалярные подзапросы: вычисление одного значения

Скалярный подзапрос — это запрос внутри основного запроса, который возвращает строго одно значение (одну строку и один столбец). База данных воспринимает его как обычное число или строку.

Чаще всего скалярные подзапросы используются в блоке WHERE для динамической фильтрации.

SELECT
    order_id,
    client_id,
    amount
FROM orders
WHERE amount > (
    SELECT AVG(amount)
    FROM orders
);

Как это работает: СУБД сначала выполняет внутренний запрос (в скобках), получает конкретное число (например, 5000), подставляет его вместо скобок и затем выполняет внешний запрос: WHERE amount > 5000.

Скалярные подзапросы также полезны в блоке SELECT, когда нужно сравнить строку с общим итогом. Например, покажем сумму заказа и её отклонение от среднего чека:

SELECT
    order_id,
    amount,
    (SELECT AVG(amount) FROM orders) AS avg_check,
    amount - (SELECT AVG(amount) FROM orders) AS difference
FROM orders;

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

Подзапросы, возвращающие список (оператор IN)

Если внутренний запрос возвращает один столбец, но несколько строк, мы получаем список значений. Сравнивать конкретное значение со списком через знак равенства нельзя. Для этого используется оператор IN.

Бизнес-кейс: маркетологам нужны email-адреса клиентов, которые покупали товары из категории «Ноутбуки».

Мы могли бы решить это через JOIN таблиц clients, orders и products. Но если клиент купил 5 ноутбуков, JOIN продублирует его email 5 раз (придется использовать DISTINCT). Подзапрос делает логику чище:

SELECT email
FROM clients
WHERE client_id IN (
    SELECT client_id
    FROM orders
    WHERE category = 'Ноутбуки'
);

Здесь внутренний запрос формирует список ID покупателей ноутбуков (например: 10, 45, 89). Внешний запрос просто проверяет, входит ли ID клиента в этот список.

Подзапросы в FROM: производные таблицы

Иногда нам нужно предварительно сгруппировать данные, а затем совершить над ними еще одну операцию. Подзапрос в блоке FROM создает временную виртуальную таблицу, которая существует только в момент выполнения запроса. Это называется производная таблица (Derived Table).

Бизнес-кейс: найти среднее количество заказов на одного клиента. Сначала нужно посчитать заказы для каждого клиента, а затем найти среднее от этих сумм. Вложить агрегатную функцию в агрегатную (AVG(COUNT(order_id))) в SQL нельзя.

SELECT
    AVG(order_count) AS avg_orders_per_client
FROM (
    SELECT
        client_id,
        COUNT(order_id) AS order_count
    FROM orders
    GROUP BY client_id
) AS client_stats;

Золотое правило: любой подзапрос в блоке FROM обязан иметь алиас (в нашем примере это AS client_stats), иначе база данных не поймет, как обращаться к этой виртуальной таблице.

Common Table Expressions (CTE): чистый код аналитика

Подзапросы отлично работают, но у них есть огромный минус — они делают код нечитаемым. Когда подзапросы вкладываются друг в друга (запрос внутри запроса внутри запроса), код начинает читаться изнутри наружу. Это ломает мозг.

Для решения этой проблемы в SQL внедрили CTE (Common Table Expressions) — общие табличные выражения. Они позволяют вынести подзапросы на самый верх кода и дать им понятные имена.

Синтаксис начинается с ключевого слова WITH:

WITH ClientStats AS (
    SELECT
        client_id,
        COUNT(order_id) AS order_count
    FROM orders
    GROUP BY client_id
)
SELECT
    AVG(order_count) AS avg_orders_per_client
FROM ClientStats;

Преимущества CTE для бизнес-аналитики

1. Читаемость сверху вниз. Код читается как книга: сначала мы готовим данные (шаг 1, шаг 2), затем делаем финальную выборку.
2. Переиспользование. Если вам нужна одна и та же производная таблица несколько раз в финальном запросе, с CTE вы пишете её один раз, а обращаетесь к ней сколько угодно.
3. Цепочки логики. Вы можете создавать несколько CTE подряд, разделяя их запятыми. При этом каждое следующее CTE может ссылаться на предыдущее.

Шаблон: Воронка продаж через цепочку CTE

Построение конверсионных воронок — классическая задача аналитика. Посмотрим, как элегантно это решается с помощью CTE.

Задача: посчитать конверсию из регистрации в первую покупку за текущий месяц. Конверсия = Покупатели / Регистрации × 100.

WITH Registrations AS (
    -- Шаг 1: Считаем всех зарегистрированных в этом месяце
    SELECT COUNT(client_id) AS total_users
    FROM clients
    WHERE EXTRACT(MONTH FROM registration_date) = EXTRACT(MONTH FROM CURRENT_DATE)
),
Buyers AS (
    -- Шаг 2: Считаем тех, кто сделал хотя бы один заказ
    SELECT COUNT(DISTINCT client_id) AS total_buyers
    FROM orders
    WHERE EXTRACT(MONTH FROM order_date) = EXTRACT(MONTH FROM CURRENT_DATE)
)
-- Шаг 3: Собираем метрики вместе и считаем конверсию
SELECT
    r.total_users,
    b.total_buyers,
    ROUND(b.total_buyers * 100.0 / r.total_users, 2) AS conversion_rate
FROM Registrations r
CROSS JOIN Buyers b; -- Соединяем две таблицы из одной строки

В этом шаблоне мы разбили сложную бизнес-логику на изолированные, легко тестируемые блоки. Если конверсия выглядит странно, вы можете выделить и выполнить только первый блок SELECT, чтобы проверить количество регистраций.

Коррелированные подзапросы: убийцы производительности

До сих пор мы рассматривали независимые подзапросы. Они выполняются СУБД ровно один раз, их результат кэшируется и используется внешним запросом.

Но существует коррелированный подзапрос — это подзапрос, который ссылается на столбец из внешнего запроса.

Пример: найти клиентов, у которых сумма заказа больше, чем средний чек именно в их городе.

SELECT
    c.name,
    o.amount
FROM clients c
JOIN orders o ON c.id = o.client_id
WHERE o.amount > (
    SELECT AVG(amount)
    FROM orders o2
    JOIN clients c2 ON o2.client_id = c2.id
    WHERE c2.city = c.city -- Ссылка на внешний запрос!
);

Как это работает физически: база данных берет первую строку внешнего запроса (клиент из Москвы), запускает внутренний запрос (считает среднее по Москве), сравнивает. Берет вторую строку (клиент из Казани), заново запускает внутренний запрос (считает среднее по Казани).

Если в таблице миллион строк, внутренний запрос выполнится миллион раз. Это катастрофически замедляет работу базы данных.

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

Подзапросы и CTE — это переход от плоских таблиц к многомерному мышлению. Вы научились создавать виртуальные наборы данных на лету и выстраивать из них логические цепочки. Это фундамент для написания сложных аналитических дашбордов и витрин данных.

Работа с датами и временем в SQL: анализ временных рядов и когорт

Время — самое важное измерение в аналитике данных. Бизнес редко интересуют статичные цифры. Руководители хотят знать: как изменилась выручка по сравнению с прошлым месяцем? Сколько времени проходит от клика до покупки? Как часто клиенты возвращаются через полгода?

Все эти вопросы требуют уверенного владения функциями работы с датами и временем. В базах данных время — это не просто текст, а сложный математический объект, подчиняющийся правилам календарей, високосных годов и часовых поясов.

Анатомия времени в базах данных

Прежде чем писать запросы, необходимо понять, как СУБД хранит время. Использование неправильного типа данных приведет к тому, что отчет за «вчера» захватит часть сегодняшнего дня из-за разницы часовых поясов.

В SQL существует три основные сущности для работы со временем:

1. DATE — календарная дата без привязки ко времени суток (например, 2024-05-15). Идеально подходит для дней рождений, дат найма или фиксации отчетных периодов.
2. TIME — время на часах без привязки к конкретному дню (например, 14:30:00). Используется редко, в основном для расписаний (например, часы работы магазина).
3. TIMESTAMP (или DATETIME) — точная отметка времени, объединяющая дату и время с точностью до долей секунды (например, 2024-05-15 14:30:00.123). Это главный тип данных для аналитика, так как именно им размечаются все транзакции, логи событий и изменения статусов заказов.

Проблема часовых поясов и TIMESTAMPTZ

Представьте, что сервер вашей базы данных находится в Лондоне, а покупатели — в Москве и Нью-Йорке. Клиент из Нью-Йорка делает заказ 31 декабря в 22:00 по местному времени. В Москве в это время уже 1 января 06:00 утра. К какому месяцу и году отнести выручку от этого заказа?

Для решения этой проблемы в продвинутых СУБД (например, PostgreSQL) существует тип данных TIMESTAMPTZ (Timestamp with Time Zone).

Когда вы записываете данные в колонку типа TIMESTAMPTZ, база данных автоматически переводит локальное время события в UTC (Всемирное координированное время) и сохраняет его. При извлечении данных СУБД конвертирует время обратно в часовой пояс, настроенный у клиента (аналитика), выполняющего запрос.

Золотой стандарт аналитики: все сырые данные в базе должны храниться строго в UTC. Конвертация в локальное время (например, московское) должна происходить только на этапе вывода данных в финальный отчет или дашборд.

Округление времени: функция DATE_TRUNC

Аналитику редко нужно анализировать данные с точностью до секунды. Чтобы построить график динамики продаж, транзакции нужно сгруппировать по дням, неделям или месяцам.

Для этого используется функция DATE_TRUNC() (от слова truncate — усекать). Она «сбрасывает» все компоненты даты, которые меньше заданного периода, до их минимальных значений (первое число для дней/месяцев, нули для часов/минут).

Синтаксис в PostgreSQL: DATE_TRUNC('период', столбец_с_датой)

Пример работы функции для даты 2024-08-25 15:45:30:

• DATE_TRUNC('year', ...) вернет 2024-01-01 00:00:00
• DATE_TRUNC('month', ...) вернет 2024-08-01 00:00:00
• DATE_TRUNC('day', ...) вернет 2024-08-25 00:00:00
• DATE_TRUNC('hour', ...) вернет 2024-08-25 15:00:00

Бизнес-кейс: Динамика выручки по месяцам

Построим классический отчет для руководства: суммарная выручка и количество заказов с разбивкой по месяцам.

SELECT
    DATE_TRUNC('month', order_date) AS report_month,
    COUNT(order_id) AS total_orders,
    SUM(amount) AS total_revenue
FROM orders
WHERE status = 'Delivered'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY report_month ASC;

Обратите внимание: мы группируем данные именно по усеченной дате. Все заказы, сделанные с 1 по 31 августа, получат одинаковую метку 2024-08-01 00:00:00 и схлопнутся в одну строку итогового отчета.

Математика времени: вычисление разницы

Вторая по популярности задача — измерение длительности процессов. Сколько дней заказ ехал до клиента? Сколько минут пользователь провел на сайте?

В разных диалектах SQL эта задача решается по-разному.

Подход 1: Прямое вычитание (PostgreSQL)

В PostgreSQL можно просто вычесть одну дату из другой. Результатом будет специальный тип данных — интервал.

SELECT
    order_id,
    delivery_date - order_date AS delivery_time
FROM orders;

Если delivery_date и order_date имеют тип DATE, результатом будет целое число (количество дней). Если это TIMESTAMP, результатом будет интервал (например, 4 days 12:30:00).

Подход 2: Функция DATEDIFF (SQL Server, MySQL)

В других СУБД для вычисления разницы используется функция DATEDIFF(). Она принимает три аргумента: единицу измерения, начальную дату и конечную дату.

-- Пример для SQL Server
SELECT
    order_id,
    DATEDIFF(day, order_date, delivery_date) AS days_to_deliver,
    DATEDIFF(hour, order_date, delivery_date) AS hours_to_deliver
FROM orders;

Бизнес-кейс: Контроль SLA службы доставки

SLA (Service Level Agreement) — это согласованный уровень качества услуг. Допустим, компания обещает доставлять товары не дольше чем за 3 дня. Найдем долю заказов, нарушивших это правило.

WITH DeliveryStats AS (
    SELECT
        order_id,
        EXTRACT(DAY FROM (delivery_date - order_date)) AS delivery_days
    FROM orders
    WHERE delivery_date IS NOT NULL
)
SELECT
    COUNT(order_id) AS total_delivered,
    SUM(CASE WHEN delivery_days > 3 THEN 1 ELSE 0 END) AS sla_breaches,
    ROUND(
        SUM(CASE WHEN delivery_days > 3 THEN 1 ELSE 0 END) * 100.0 / COUNT(order_id),
        2
    ) AS breach_rate_percent
FROM DeliveryStats;

Здесь мы объединили знания из предыдущих уроков: использовали CTE для предварительного расчета дней, а затем применили условную агрегацию (SUM(CASE WHEN...)) для подсчета проблемных заказов.

Форматирование дат для бизнес-пользователей

Сырой формат 2024-08-01 00:00:00 отлично подходит для вычислений, но плохо смотрится в финальных отчетах. Руководителю приятнее видеть «Август 2024» или «01.08.2024».

Для преобразования даты в читаемый текст используется функция TO_CHAR() (в PostgreSQL и Oracle) или DATE_FORMAT() (в MySQL).

Синтаксис: TO_CHAR(дата, 'шаблон_форматирования')

Популярные шаблоны:

• 'YYYY-MM-DD' → 2024-08-25
• 'DD.MM.YYYY' → 25.08.2024
• 'Month YYYY' → August 2024
• 'Dy, DD Mon' → Sun, 25 Aug

SELECT
    TO_CHAR(order_date, 'DD.MM.YYYY HH24:MI') AS formatted_date,
    amount
FROM orders
LIMIT 5;

Важное правило: форматирование даты превращает её в текст (строку). После применения TO_CHAR вы больше не сможете использовать математические операции над этим столбцом или правильно его отсортировать (текст '01.08.2024' при сортировке встанет раньше, чем '25.01.2024', потому что '0' меньше '2'). Поэтому форматирование всегда делается на самом последнем этапе запроса, в финальном SELECT.

Продвинутый шаблон: Когортный анализ (Retention)

Когортный анализ — это метод исследования, при котором пользователи делятся на группы (когорты) по дате первого действия (обычно — первой покупки или регистрации), а затем отслеживается их поведение во времени.

Это главный инструмент для расчета Retention Rate (коэффициента удержания) и LTV (Lifetime Value — пожизненной ценности клиента).

Бизнес-задача: Понять, какой процент клиентов, совершивших первую покупку в определенном месяце, возвращается за новыми покупками в следующие месяцы.

Решение этой задачи требует многоуровневой логики. Разберем её по шагам с помощью CTE.

Шаг 1: Определяем когорту каждого клиента

Нам нужно найти дату самой первой покупки для каждого client_id и округлить её до месяца. Это и будет «имя» когорты.

WITH FirstPurchases AS (
    SELECT
        client_id,
        DATE_TRUNC('month', MIN(order_date)) AS cohort_month
    FROM orders
    GROUP BY client_id
)

Шаг 2: Привязываем когорту ко всем заказам

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

, OrderCohorts AS (
    SELECT
        o.client_id,
        o.order_id,
        o.amount,
        DATE_TRUNC('month', o.order_date) AS order_month,
        fp.cohort_month
    FROM orders o
    JOIN FirstPurchases fp ON o.client_id = fp.client_id
)

Шаг 3: Вычисляем «возраст» заказа (Lifetime Month)

Нам нужно понять, на какой месяц жизни когорты был сделан заказ. Если клиент из когорты «Январь» делает заказ в марте, это 2-й месяц жизни (Январь = 0, Февраль = 1, Март = 2).

В PostgreSQL мы можем вычесть месяцы, используя извлечение года и месяца:

, CohortActivity AS (
    SELECT
        cohort_month,
        order_month,
        -- Формула: (Год_заказа - Год_когорты)*12 + (Месяц_заказа - Месяц_когорты)
        (EXTRACT(YEAR FROM order_month) - EXTRACT(YEAR FROM cohort_month)) * 12 +
        (EXTRACT(MONTH FROM order_month) - EXTRACT(MONTH FROM cohort_month)) AS lifetime_month,
        COUNT(DISTINCT client_id) AS active_clients,
        SUM(amount) AS cohort_revenue
    FROM OrderCohorts
    GROUP BY cohort_month, order_month, lifetime_month
)

Шаг 4: Финальный расчет Retention

Теперь у нас есть данные о количестве активных клиентов в каждом месяце жизни когорты. Чтобы получить процент удержания, нужно разделить количество активных клиентов в текущем месяце на размер когорты в нулевом месяце.

SELECT
    ca.cohort_month,
    ca.lifetime_month,
    ca.active_clients,
    ca.cohort_revenue,
    -- Подзапрос для получения исходного размера когорты (lifetime_month = 0)
    FIRST_VALUE(ca.active_clients) OVER (PARTITION BY ca.cohort_month ORDER BY ca.lifetime_month) AS initial_cohort_size,
    ROUND(ca.active_clients * 100.0 /
          FIRST_VALUE(ca.active_clients) OVER (PARTITION BY ca.cohort_month ORDER BY ca.lifetime_month), 2) AS retention_rate
FROM CohortActivity ca
ORDER BY ca.cohort_month, ca.lifetime_month;

(Примечание: здесь мы использовали оконную функцию FIRST_VALUE, которую подробно разберем в следующем модуле курса. Она берет значение из первой строки внутри группы).

Результатом этого запроса будет таблица, которую легко визуализировать в BI-системе в виде тепловой карты (Heatmap), где по вертикали идут месяцы регистрации, по горизонтали — месяцы жизни, а в ячейках — процент вернувшихся клиентов.

Анализ пересекающихся периодов (Active Subscriptions)

Еще одна классическая задача аналитика — расчет количества активных подписок (или сотрудников в штате) на каждый день.

Сложность в том, что у нас есть только дата начала (start_date) и дата окончания (end_date) подписки. Если подписка длилась весь март, в базе нет 31 строки для каждого дня марта. Есть только одна строка. Как посчитать активных пользователей на 15 марта?

Для этого используется генерация временных рядов. В PostgreSQL для этого есть мощная функция GENERATE_SERIES().

Синтаксис: GENERATE_SERIES(начало, конец, шаг)

Создадим календарь на текущий месяц:

WITH Calendar AS (
    SELECT GENERATE_SERIES(
        DATE_TRUNC('month', CURRENT_DATE),
        DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month' - INTERVAL '1 day',
        INTERVAL '1 day'
    )::DATE AS report_date
)

Теперь мы можем сделать CROSS JOIN или неэквивалентный JOIN нашей таблицы подписок с этим календарем:

SELECT
    c.report_date,
    COUNT(s.subscription_id) AS active_subscriptions
FROM Calendar c
LEFT JOIN subscriptions s
    -- Подписка активна, если она началась до отчетной даты
    -- и закончилась после нее (или еще не закончилась)
    ON s.start_date <= c.report_date
    AND (s.end_date >= c.report_date OR s.end_date IS NULL)
GROUP BY c.report_date
ORDER BY c.report_date;

Этот шаблон — ключ к решению любых задач, связанных с состояниями, растянутыми во времени (остатки на складе, количество открытых тикетов в поддержке, активные кредиты).

Резюме лучших практик

1. Храните данные в UTC. Это избавит вас от проблем при масштабировании бизнеса на другие страны и при переходе на летнее/зимнее время.
2. Используйте DATE_TRUNC для группировок. Это самый надежный способ агрегировать метрики по неделям, месяцам или кварталам.
3. Форматируйте в самом конце. Превращайте даты в текст с помощью TO_CHAR только в финальном SELECT, чтобы не сломать сортировку.
4. Осторожно с NULL. Если процесс не завершен (заказ не доставлен, подписка не отменена), дата окончания будет NULL. Всегда обрабатывайте такие случаи в логике вычислений (например, через COALESCE или явные проверки IS NULL).

Умение виртуозно жонглировать датами отличает начинающего специалиста от крепкого мидла. Сохраните шаблоны когортного анализа и генерации календаря — они гарантированно понадобятся вам в реальной работе.

Представьте классическую аналитическую задачу: у вас есть таблица со всеми покупками за месяц. Руководитель просит выгрузить список этих транзакций, но добавить к каждой строке дополнительную колонку — долю этой транзакции от общей выручки за месяц.

Если вы используете агрегатную функцию SUM(amount) вместе с оператором группировки, база данных «схлопнет» все строки в одну итоговую цифру. Вы получите общую выручку, но потеряете детализацию по отдельным заказам. Чтобы обойти это, раньше аналитикам приходилось писать сложные запросы с присоединением таблиц самих к себе.

Эту проблему элегантно решают оконные функции — инструмент, который позволяет проводить вычисления над набором строк, не объединяя их.

Анатомия оконной функции

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

Главный маркер оконной функции в SQL — ключевое слово OVER(). Именно оно говорит базе данных: «Не схлопывай строки, а открой окно для вычислений». Окно — это тот самый набор строк, который функция «видит» в момент обработки текущей записи.

Базовый синтаксис выглядит так: ФУНКЦИЯ() OVER (PARTITION BY столбец ORDER BY столбец)

Рассмотрим три главных сценария использования оконных функций на реальных бизнес-кейсах.

Сценарий 1: Агрегация без потери строк (PARTITION BY)

Ключевое слово PARTITION BY работает похоже на оператор группировки: оно разбивает данные на изолированные сегменты (партиции). Оконная функция производит вычисления только внутри той партиции, к которой относится текущая строка.

Бизнес-кейс: Анализ чеков по категориям товаров

Нам нужно вывести список проданных товаров, их цену и среднюю цену товара в этой же категории, чтобы понять, относится ли конкретная продажа к премиум-сегменту.

SELECT
    order_id,
    category,
    product_name,
    price,
    AVG(price) OVER (PARTITION BY category) AS avg_category_price
FROM sales;

Как это работает: СУБД берет первую строку (например, кроссовки за 5000 руб. из категории «Обувь»). Благодаря PARTITION BY category, функция AVG() вычисляет среднее значение только среди всех строк с категорией «Обувь» (допустим, 4000 руб.) и записывает этот результат в новую колонку. Строка с кроссовками остается на месте, но теперь обогащена контекстом.

Сценарий 2: Нарастающий итог и скользящие вычисления (ORDER BY)

Если добавить в конструкцию OVER() ключевое слово сортировки, поведение окна кардинально изменится. Окно перестанет быть статичным и превратится в динамическое.

По умолчанию, если указан ORDER BY, окно включает в себя все строки от начала партиции до текущей строки. Это идеальный механизм для расчета нарастающего итога (cumulative sum) — метрики, показывающей накопление значения с течением времени.

Бизнес-кейс: Выполнение плана продаж

Нам нужно посмотреть, как накапливалась выручка компании по дням в течение января.

SELECT
    sale_date,
    daily_revenue,
    SUM(daily_revenue) OVER (ORDER BY sale_date ASC) AS cumulative_revenue
FROM daily_sales
WHERE EXTRACT(MONTH FROM sale_date) = 1;

Для строки за 1 января окно состоит только из одной строки (выручка 1 января). Для строки за 2 января окно расширяется и включает 1 и 2 января. Для 3 января — суммирует три дня. В результате мы получаем классический график выполнения плана.

Нарастающий итог — это фундамент для проведения ABC-анализа. Чтобы разделить товары на классы A, B и C, аналитики сначала сортируют товары по убыванию выручки, затем считают нарастающий итог выручки, а после делят его на общую сумму, получая накопительную долю в процентах.

Сценарий 3: Ранжирование данных

Оконные функции позволяют присваивать строкам порядковые номера. Для этого используются три специальные функции:

1. ROW_NUMBER() — присваивает уникальный последовательный номер каждой строке (1, 2, 3, 4). Если значения одинаковые, СУБД сама решит, кому дать номер 1, а кому 2.
2. RANK() — присваивает одинаковый ранг одинаковым значениям, но оставляет «дыры» в нумерации (1, 2, 2, 4).
3. DENSE_RANK() — работает как предыдущая, но без «дыр» (1, 2, 2, 3).

Бизнес-кейс: Поиск первой покупки клиента

Чтобы рассчитать метрики удержания, нам нужно найти дату самого первого заказа для каждого клиента.

WITH RankedOrders AS (
    SELECT
        client_id,
        order_id,
        order_date,
        ROW_NUMBER() OVER (PARTITION BY client_id ORDER BY order_date ASC) AS order_rank
    FROM orders
)
SELECT *
FROM RankedOrders
WHERE order_rank = 1;

Здесь мы разбили данные по клиентам (PARTITION BY client_id) и внутри каждого клиента отсортировали заказы по дате (ORDER BY order_date). Самый старый заказ получил номер 1.

Обратите внимание: мы обернули расчет в производную таблицу (CTE). Оконные функции выполняются на самом последнем этапе обработки запроса (после фильтрации сырых строк), поэтому их нельзя напрямую писать в блоке фильтрации. Сначала нужно вычислить ранг, а уже внешним запросом отфильтровать нужные строки.

Сценарий 4: Смещение строк (Анализ временных рядов)

Часто аналитику нужно сравнить текущее значение с предыдущим. Например, выручку этого месяца с выручкой прошлого. В Excel вы бы просто сослались на ячейку выше. В SQL для этого существуют функции смещения:

• LAG() — обращается к данным из предыдущей строки.
• LEAD() — обращается к данным из следующей строки.

Обе функции требуют обязательного указания сортировки внутри окна, иначе понятие «предыдущий» теряет смысл.

Бизнес-кейс: Расчет Month-over-Month (MoM) роста

Посчитаем динамику выручки месяц к месяцу.

WITH MonthlyRevenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS report_month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY DATE_TRUNC('month', order_date)
)
SELECT
    report_month,
    revenue,
    LAG(revenue) OVER (ORDER BY report_month ASC) AS prev_month_revenue,
    -- Формула: (Текущий - Предыдущий) / Предыдущий * 100
    ROUND((revenue - LAG(revenue) OVER (ORDER BY report_month ASC)) /
          LAG(revenue) OVER (ORDER BY report_month ASC) * 100, 2) AS mom_growth_percent
FROM MonthlyRevenue;

В этом запросе мы сначала агрегировали сырые данные до уровня месяцев (используя классическую группировку), а затем применили оконную функцию LAG() к уже сгруппированным данным, чтобы «подтянуть» выручку прошлого месяца в текущую строку и рассчитать процент изменения.

Подводные камни и оптимизация

Оконные функции — это «тяжелая» артиллерия SQL. Они требуют значительных ресурсов оперативной памяти сервера, так как СУБД вынуждена держать в памяти целые партиции данных для вычислений.

1. Не злоупотребляйте окнами. Если задачу можно решить обычной группировкой — используйте её. Оконные функции нужны именно тогда, когда требуется сохранить детализацию строк.
2. Следите за индексами. Если вы часто используете PARTITION BY client_id ORDER BY order_date, убедитесь, что в базе данных существует составной индекс по этим двум столбцам. Это ускорит работу запроса в десятки раз.
3. Избегайте дублирования. Если в запросе несколько раз используется одно и то же окно (как в примере с MoM ростом), в современных диалектах SQL (например, PostgreSQL) можно объявить окно один раз в конце запроса с помощью оператора WINDOW, а в блоке выборки просто ссылаться на его имя.

Освоение оконных функций переводит вас из категории начинающих пользователей SQL в лигу профессиональных дата-аналитиков. С их помощью расчет сложных бизнес-метрик, таких как LTV, когортный анализ и воронки продаж, становится делом нескольких строк элегантного кода.

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

В предыдущих модулях мы изучили технический арсенал SQL: от базовой фильтрации до сложных соединений таблиц и оконных функций. Теперь пришло время применить эти инструменты для решения реальных задач бизнеса.

Бизнес говорит на языке денег. Руководителю не нужен просто «список всех транзакций за месяц» — ему нужны ответы на вопросы: «Растем ли мы?», «Какие товары приносят основную прибыль?», «Где мы теряем клиентов?». В этой статье мы разберем, как с помощью SQL рассчитывать ключевые финансовые метрики, строить воронки продаж и проводить сегментацию ассортимента.

Для всех примеров в этой статье мы будем использовать стандартную структуру данных e-commerce проекта, состоящую из трех таблиц: users (клиенты), orders (заказы) и order_items (состав заказа с указанием себестоимости и цены продажи).

Базовые метрики: Выручка, Прибыль и Средний чек

Анализ всегда начинается с верхнеуровневых показателей (Top-line metrics). Самая частая ошибка начинающих аналитиков — путать выручку и прибыль.

Выручка (Revenue) — это все деньги, которые поступили в кассу компании от продаж. Валовая прибыль (Gross Margin) — это выручка минус себестоимость проданных товаров.

Формула валовой прибыли: $\text{Margin} = \text{Revenue} - \text{COGS}$ Где $\text{COGS}$ (Cost of Goods Sold) — прямые затраты на производство или закупку товара.

Рассчитаем эти показатели за текущий месяц:

SELECT
    DATE_TRUNC('month', o.created_at) AS report_month,
    SUM(oi.price * oi.quantity) AS revenue,
    SUM((oi.price - oi.cost) * oi.quantity) AS gross_margin,
    -- Считаем маржинальность в процентах
    ROUND(SUM((oi.price - oi.cost) * oi.quantity) / SUM(oi.price * oi.quantity) * 100, 2) AS margin_percent
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed'
  AND o.created_at >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY 1;

Обратите внимание: мы фильтруем заказы по статусу completed. В реальном бизнесе учет выручки по неоплаченным или отмененным заказам — грубейшая ошибка, которая приведет к искажению финансовой отчетности.

Средний чек (AOV)

AOV (Average Order Value) показывает, сколько в среднем тратит клиент за один заказ. Эта метрика помогает оценивать эффективность маркетинга и программ лояльности (например, акций «купи на 5000 рублей и получи бесплатную доставку»).

Формула расчета: $\text{AOV} = \frac{\text{Total Revenue}}{\text{Number of Orders}}$

В SQL это выглядит так:

SELECT
    SUM(amount) / COUNT(DISTINCT order_id) AS aov
FROM orders
WHERE status = 'completed';

ARPU и ARPPU

Если AOV оценивает качество заказа, то следующие метрики оценивают качество аудитории:

1. ARPU (Average Revenue Per User) — средняя выручка на одного пользователя (включая тех, кто ничего не купил, а просто зарегистрировался).
2. ARPPU (Average Revenue Per Paying User) — средняя выручка только на платящего пользователя.

Разница критически важна для мобильных приложений и игр (Free-to-Play), где 95% аудитории пользуются продуктом бесплатно, а 5% совершают покупки.

WITH revenue_data AS (
    SELECT SUM(amount) AS total_revenue FROM orders WHERE status = 'completed'
),
user_data AS (
    SELECT
        COUNT(DISTINCT id) AS total_users,
        COUNT(DISTINCT CASE WHEN has_purchases = true THEN id END) AS paying_users
    FROM users
)
SELECT
    total_revenue / total_users AS arpu,
    total_revenue / paying_users AS arppu
FROM revenue_data CROSS JOIN user_data;

Воронка продаж и расчет конверсии

Воронка продаж (Sales Funnel) — это аналитический инструмент, показывающий путь клиента от первого знакомства с продуктом до совершения покупки. На каждом этапе часть людей уходит, поэтому график визуально напоминает сужающуюся воронку.

Главная метрика здесь — Конверсия (Conversion Rate, CR). Это процент пользователей, перешедших с одного этапа на следующий, или от начала воронки до самого конца.

Формула конверсии: $\text{CR} = \frac{\text{Target Actions}}{\text{Total Visitors}} \times 100$ Где $\text{Target Actions}$ — количество целевых действий (например, покупок), а $\text{Total Visitors}$ — общее количество посетителей.

Построим воронку по событиям на сайте (таблица events), используя CTE для пошагового расчета:

WITH funnel_stages AS (
    SELECT
        COUNT(DISTINCT CASE WHEN event_type = 'visit' THEN user_id END) AS visitors,
        COUNT(DISTINCT CASE WHEN event_type = 'add_to_cart' THEN user_id END) AS cart_adds,
        COUNT(DISTINCT CASE WHEN event_type = 'checkout' THEN user_id END) AS checkouts,
        COUNT(DISTINCT CASE WHEN event_type = 'purchase' THEN user_id END) AS purchases
    FROM events
    WHERE event_date >= '2023-09-01'
)
SELECT
    visitors,
    cart_adds,
    checkouts,
    purchases,
    -- Конверсия из визита в покупку (Общая конверсия)
    ROUND(purchases::numeric / visitors * 100, 2) AS overall_cr_percent,
    -- Конверсия из корзины в оплату (Конверсия этапа)
    ROUND(purchases::numeric / cart_adds * 100, 2) AS cart_to_purchase_cr_percent
FROM funnel_stages;

Разбивка на CTE делает код читаемым: сначала мы собираем абсолютные значения (размеры каждого уровня воронки), а затем в основном запросе считаем относительные метрики (проценты конверсии).

ABC-анализ ассортимента

Представьте, что вы аналитик в крупной аптечной сети или супермаркете. У вас десятки тысяч товаров. Как понять, на каких позициях сфокусировать внимание отдела закупок?

Здесь применяется ABC-анализ — метод классификации ресурсов по степени их важности. Он базируется на принципе Парето (правило 80/20): 20% усилий дают 80% результата.

В классическом ABC-анализе товары делятся на три группы по доле в общей выручке:

• Группа A: приносят первые 80% выручки (самые важные товары, их отсутствие на складе — катастрофа).
• Группа B: приносят следующие 15% выручки (стабильный средний класс).
• Группа C: приносят оставшиеся 5% выручки (огромный ассортимент, который почти не дает денег; кандидаты на вывод из матрицы).

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

WITH product_revenue AS (
    -- Шаг 1: Считаем выручку по каждому товару
    SELECT
        product_id,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY product_id
),
cumulative_data AS (
    -- Шаг 2: Считаем нарастающий итог и общую выручку
    SELECT
        product_id,
        revenue,
        SUM(revenue) OVER (ORDER BY revenue DESC) AS running_total,
        SUM(revenue) OVER () AS total_revenue
    FROM product_revenue
)
-- Шаг 3: Вычисляем процент и присваиваем категорию
SELECT
    product_id,
    revenue,
    ROUND((running_total / total_revenue) * 100, 2) AS cumulative_percent,
    CASE
        WHEN (running_total / total_revenue) <= 0.80 THEN 'A'
        WHEN (running_total / total_revenue) <= 0.95 THEN 'B'
        ELSE 'C'
    END AS abc_category
FROM cumulative_data
ORDER BY revenue DESC;

Этот шаблон — золотой стандарт аналитики ассортимента. Сначала мы агрегируем данные (Шаг 1), затем применяем динамическое окно с сортировкой по убыванию выручки для нарастающего итога (Шаг 2), и наконец, используем условный оператор для разметки (Шаг 3).

LTV (Lifetime Value) — Пожизненная ценность клиента

LTV (Lifetime Value) — это предсказание чистого дохода, который компания получит от клиента за все время сотрудничества с ним.

Зачем это нужно? Если вы тратите на привлечение одного клиента (CAC — Customer Acquisition Cost) 1000 рублей, а его первый заказ приносит 800 рублей прибыли, кажется, что вы в минусе. Но если этот клиент возвращается и делает еще пять заказов в течение года, его LTV составит 4800 рублей. Бизнес высокорентабелен!

Существует множество способов расчета LTV. Самый надежный для исторических данных — когортный метод. Мы берем группу людей, совершивших первую покупку в определенном месяце (когорту), и смотрим, сколько денег они принесли за следующие N месяцев.

Упрощенный расчет фактического LTV по когортам в SQL:

WITH first_orders AS (
    -- Находим дату первой покупки каждого клиента
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM orders
    GROUP BY user_id
),
cohort_sizes AS (
    -- Считаем количество людей в каждой когорте
    SELECT
        cohort_month,
        COUNT(DISTINCT user_id) AS users_in_cohort
    FROM first_orders
    GROUP BY cohort_month
),
cohort_revenue AS (
    -- Считаем всю выручку, которую принесли пользователи когорты
    SELECT
        f.cohort_month,
        SUM(o.amount) AS total_cohort_revenue
    FROM first_orders f
    JOIN orders o ON f.user_id = o.user_id
    GROUP BY f.cohort_month
)
-- Объединяем данные для расчета LTV
SELECT
    s.cohort_month,
    s.users_in_cohort,
    r.total_cohort_revenue,
    ROUND(r.total_cohort_revenue / s.users_in_cohort, 2) AS ltv_per_user
FROM cohort_sizes s
JOIN cohort_revenue r ON s.cohort_month = r.cohort_month
ORDER BY s.cohort_month;

Оптимизация запросов и чистый код

Когда вы начнете работать с реальными базами данных, где хранятся миллионы строк, вы заметите, что некоторые запросы выполняются секунды, а другие «висят» часами. Умение писать оптимизированный код отличает junior-специалиста от middle-аналитика.

1. Понимание индексов

Индекс базы данных — это специальная структура данных, которая работает как алфавитный указатель в конце толстой книги. Если вам нужно найти главу про SQL, вы не листаете все 1000 страниц подряд (в базах данных это называется Full Table Scan). Вы открываете указатель, находите букву «S» и сразу переходите на нужную страницу.

Если вы часто фильтруете заказы по дате (WHERE created_at >= '2023-01-01') или соединяете таблицы по ID пользователя (JOIN users ON orders.user_id = users.id), убедитесь, что на столбцах created_at и user_id созданы индексы. Аналитики обычно не создают индексы сами (это задача Data Engineer или DBA), но вы должны уметь попросить об этом, если запрос тормозит.

2. Фильтрация как можно раньше

Золотое правило оптимизации: отсекайте ненужные данные на самом раннем этапе.

Плохо:

WITH all_sales AS (
    SELECT * FROM orders
)
SELECT SUM(amount) FROM all_sales WHERE status = 'completed';

База данных сначала загрузит в память миллионы отмененных заказов, а потом отфильтрует их.

Хорошо:

WITH completed_sales AS (
    SELECT amount FROM orders WHERE status = 'completed'
)
SELECT SUM(amount) FROM completed_sales;

3. Читаемость кода

Ваш код будут читать другие люди (и вы сами через полгода). Соблюдайте стандарты форматирования:

• Пишите ключевые слова SQL заглавными буквами (SELECT, FROM, WHERE).
• Названия таблиц и столбцов — строчными.
• Используйте отступы (Tab или 4 пробела) внутри блоков SELECT и WHERE.
• Всегда давайте осмысленные алиасы таблицам (не t1 и t2, а o для orders и u для users).
• Разбивайте сложную логику на последовательные шаги с помощью CTE (WITH), избегая многоэтажных вложенных подзапросов в блоке FROM.

Коллекция шаблонов аналитика

В завершение, вот несколько паттернов, которые стоит сохранить в свои заметки.

Шаблон 1: Поиск аномалий (Z-оценка) Как найти дни, когда выручка аномально упала или выросла? Сравним день со средним значением за месяц.

WITH daily_stats AS (
    SELECT
        sale_date,
        SUM(amount) AS daily_revenue,
        AVG(SUM(amount)) OVER () AS avg_revenue,
        STDDEV(SUM(amount)) OVER () AS std_dev
    FROM sales
    GROUP BY sale_date
)
SELECT
    sale_date,
    daily_revenue,
    -- Если отклонение больше 2 стандартных отклонений - это аномалия
    ABS(daily_revenue - avg_revenue) / std_dev AS z_score
FROM daily_stats
WHERE ABS(daily_revenue - avg_revenue) / std_dev > 2;

Шаблон 2: Сравнение год к году (YoY - Year over Year) Метрика, которую обожают директора. Сравниваем текущий месяц с тем же месяцем прошлого года, чтобы исключить фактор сезонности.

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', sale_date) AS month,
        SUM(amount) AS revenue
    FROM sales
    GROUP BY 1
)
SELECT
    month,
    revenue AS current_revenue,
    LAG(revenue, 12) OVER (ORDER BY month) AS prev_year_revenue,
    ROUND((revenue - LAG(revenue, 12) OVER (ORDER BY month)) /
          LAG(revenue, 12) OVER (ORDER BY month) * 100, 2) AS yoy_growth_percent
FROM monthly_revenue;

Освоив эти бизнес-метрики и шаблоны, вы перестаете быть просто человеком, который «умеет писать SELECT». Вы становитесь аналитиком, способным находить точки роста компании, оптимизировать ассортимент и оценивать эффективность инвестиций в маркетинг на основе твердых данных.

Представьте ситуацию: вы смотрите на дашборд и видите, что общая ежемесячная аудитория продукта (MAU) стабильно держится на уровне 100 000 человек. Кажется, всё отлично. Но если копнуть глубже, может оказаться, что старые преданные клиенты массово уходят, а цифра в 100 000 поддерживается только за счёт агрессивной и дорогой закупки новой рекламы. Как только рекламный бюджет закончится, бизнес рухнет.

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

Анатомия когортного анализа

Когорта — это группа пользователей, которые совершили определённое стартовое действие в один и тот же промежуток времени. Чаще всего этим действием выступает дата регистрации, дата установки приложения или дата первой покупки.

Главная метрика, которую мы отслеживаем с помощью когорт — это Retention Rate (Коэффициент удержания). Он показывает, какая доля людей из изначальной группы продолжает пользоваться продуктом спустя заданное время.

Формула расчета удержания для конкретного периода: $\text{Retention Rate} = \frac{\text{Active Users in Period N}}{\text{Total Users in Cohort}} \times 100$ Где $\text{Active Users in Period N}$ — количество пользователей из когорты, совершивших действие в период $N$, а $\text{Total Users in Cohort}$ — изначальный размер этой когорты.

В SQL построение любого когортного отчета всегда состоит из трех логических шагов:

1. Определение когорты: найти дату первого действия для каждого уникального пользователя.
2. Отслеживание активности: собрать все последующие действия пользователя.
3. Расчет дистанции (Лайфтайма): вычислить разницу во времени между стартовым действием и последующей активностью (например, сколько месяцев прошло).

Шаг 1: Базовый SQL-шаблон для Monthly Retention

Рассмотрим классическую задачу e-commerce: нам нужно понять, как часто клиенты возвращаются за повторными покупками из месяца в месяц. У нас есть таблица orders с историей всех заказов.

Реализуем трехшаговый алгоритм с помощью CTE:

WITH user_cohorts AS (
    -- Шаг 1: Находим месяц первой покупки для каждого клиента
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
),
user_activities AS (
    -- Шаг 2: Находим все месяцы, когда клиент совершал покупки
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', created_at) AS activity_month
    FROM orders
    WHERE status = 'completed'
),
cohort_distance AS (
    -- Шаг 3: Соединяем когорту с активностью и считаем лайфтайм
    SELECT
        c.user_id,
        c.cohort_month,
        a.activity_month,
        -- Вычисляем разницу в месяцах между активностью и когортой
        EXTRACT(YEAR FROM a.activity_month) * 12 + EXTRACT(MONTH FROM a.activity_month)
        - (EXTRACT(YEAR FROM c.cohort_month) * 12 + EXTRACT(MONTH FROM c.cohort_month)) AS lifetime_month
    FROM user_cohorts c
    JOIN user_activities a ON c.user_id = a.user_id
)
-- Финальная агрегация: считаем размер когорты и количество активных пользователей
SELECT
    cohort_month,
    lifetime_month,
    COUNT(DISTINCT user_id) AS active_users
FROM cohort_distance
GROUP BY cohort_month, lifetime_month
ORDER BY cohort_month, lifetime_month;

Разберем математику в блоке cohort_distance. Базы данных не всегда умеют просто вычитать один месяц из другого так, чтобы получить целое число. Формула (Год2 * 12 + Месяц2) - (Год1 * 12 + Месяц1) — это универсальный и надежный способ получить точную разницу в месяцах (индекс месяца), независимо от того, перешагнули мы через Новый год или нет.

Результат этого запроса даст нам «плоскую» таблицу (cohort_month, lifetime_month, active_users). Нулевой лайфтайм (lifetime_month = 0) всегда будет равен 100% размеру когорты, так как это месяц первой покупки.

Шаг 2: N-Day Retention для мобильных приложений

Если для интернет-магазина нормально, когда клиент возвращается раз в месяц, то для мобильных игр, социальных сетей или SaaS-сервисов счет идет на дни. Здесь используется метрика Day-N Retention.

Логика SQL-запроса остается абсолютно идентичной, меняется только единица измерения времени. Вместо усечения до месяца (DATE_TRUNC('month', ...)) мы приводим метки времени к дате (::DATE или CAST(... AS DATE)), а разницу считаем в днях.

Построим расчет удержания по дням на основе таблицы событий авторизации user_logins:

WITH first_login AS (
    SELECT
        user_id,
        MIN(login_timestamp)::DATE AS cohort_date
    FROM user_logins
    GROUP BY user_id
),
daily_activity AS (
    SELECT DISTINCT
        user_id,
        login_timestamp::DATE AS activity_date
    FROM user_logins
),
retention_data AS (
    SELECT
        f.cohort_date,
        a.activity_date,
        -- В PostgreSQL вычитание дат напрямую дает разницу в днях (integer)
        (a.activity_date - f.cohort_date) AS lifetime_day,
        f.user_id
    FROM first_login f
    JOIN daily_activity a ON f.user_id = a.user_id
)
SELECT
    cohort_date,
    COUNT(DISTINCT CASE WHEN lifetime_day = 0 THEN user_id END) AS day_0_users,
    COUNT(DISTINCT CASE WHEN lifetime_day = 1 THEN user_id END) AS day_1_users,
    COUNT(DISTINCT CASE WHEN lifetime_day = 3 THEN user_id END) AS day_3_users,
    COUNT(DISTINCT CASE WHEN lifetime_day = 7 THEN user_id END) AS day_7_users,
    -- Считаем процент удержания для 7-го дня
    ROUND(
        COUNT(DISTINCT CASE WHEN lifetime_day = 7 THEN user_id END)::NUMERIC /
        COUNT(DISTINCT CASE WHEN lifetime_day = 0 THEN user_id END) * 100, 2
    ) AS day_7_retention_pct
FROM retention_data
GROUP BY cohort_date
ORDER BY cohort_date;

В этом примере мы применили технику условной агрегации (комбинация COUNT и CASE WHEN), чтобы сразу развернуть данные в широкую таблицу. Это избавляет от необходимости делать сводные таблицы (Pivot) в Excel или BI-системе.

Как читать когортную матрицу и находить инсайты

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

1. Чтение по горизонтали (Жизненный цикл когорты). Вы берете одну строку (например, пользователей, пришедших в январе) и смотрите, как они отваливаются с течением времени. Резкое падение между 1-м и 2-м месяцем — это нормально для большинства бизнесов. Но если кривая не выравнивается к 6-му месяцу, а стремится к нулю — продукт не формирует привычку.

2. Чтение по вертикали (Сравнение когорт). Вы смотрите на один и тот же столбец (например, Month 1) сверху вниз.

Если январская когорта на первый месяц показала удержание 20%, февральская — 22%, а мартовская — 15%, нужно задать вопрос: «Что мы сломали в марте?». Возможно, был запущен неудачный редизайн интерфейса или привлечен нецелевой трафик из нового рекламного канала.

3. Чтение по диагонали (Календарные аномалии). Диагональ в когортной матрице представляет собой один и тот же календарный месяц для разных когорт. Например, 3-й месяц жизни январской когорты и 2-й месяц жизни февральской когорты — это апрель. Если на всей диагонали наблюдается просадка метрик, проблема не в продукте и не в качестве пользователей, а во внешнем факторе (сезонность, сбой серверов, агрессивная акция конкурентов).

Продвинутый шаблон: Когортный анализ выручки

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

Мы можем модифицировать наш базовый шаблон, чтобы вместо количества уникальных пользователей (COUNT(DISTINCT user_id)) считать сумму их покупок (SUM(amount)). Это позволит увидеть, когда когорта окупает затраты на свое привлечение.

WITH cohort_definition AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
),
cohort_sizes AS (
    -- Запоминаем изначальное количество людей в когорте для расчета удельных метрик
    SELECT
        cohort_month,
        COUNT(DISTINCT user_id) AS total_users
    FROM cohort_definition
    GROUP BY cohort_month
),
revenue_tracking AS (
    SELECT
        c.cohort_month,
        EXTRACT(YEAR FROM o.created_at) * 12 + EXTRACT(MONTH FROM o.created_at)
        - (EXTRACT(YEAR FROM c.cohort_month) * 12 + EXTRACT(MONTH FROM c.cohort_month)) AS lifetime_month,
        SUM(o.amount) AS monthly_revenue
    FROM cohort_definition c
    JOIN orders o ON c.user_id = o.user_id
    WHERE o.status = 'completed'
    GROUP BY c.cohort_month, lifetime_month
)
SELECT
    r.cohort_month,
    s.total_users,
    r.lifetime_month,
    r.monthly_revenue,
    -- Выручка когорты в конкретный месяц, разделенная на изначальный размер когорты
    ROUND(r.monthly_revenue / s.total_users, 2) AS revenue_per_initial_user
FROM revenue_tracking r
JOIN cohort_sizes s ON r.cohort_month = s.cohort_month
ORDER BY r.cohort_month, r.lifetime_month;

Метрика revenue_per_initial_user показывает, сколько денег в среднем приносит один привлеченный клиент на $N$-ный месяц своей жизни. Если сложить эти значения нарастающим итогом (с помощью оконной функции SUM() OVER(), которую мы разбирали ранее), мы получим фактический график роста LTV по когортам.

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

Представьте, что вы работаете аналитиком в крупной розничной сети. В вашей базе данных числятся десятки тысяч уникальных товаров (SKU) и сотни тысяч покупателей. Ресурсы компании ограничены: маркетологи не могут звонить каждому клиенту лично, а логисты не могут держать на складе бесконечный запас всех товаров.

Бизнесу нужно сфокусироваться. Необходимо точно знать, какие товары приносят основную прибыль и всегда должны быть на полках, а какие лишь занимают место. Точно так же нужно понимать, кто из клиентов является «золотым активом», а кто зашел случайно и больше не вернется. Для решения этих задач в аналитике применяются методы сегментации: ABC-анализ, XYZ-анализ и RFM-моделирование.

Принцип Парето и ABC-анализ

В основе ABC-анализа лежит эмпирическое правило, известное как принцип Парето: 20% усилий дают 80% результата. В контексте продаж это означает, что малая часть ассортимента генерирует львиную долю выручки.

Метод делит все анализируемые объекты (товары, клиентов, поставщиков) на три класса по степени их вклада в общий результат:

• Класс A — самые ценные объекты. Обычно это около 20% ассортимента, которые приносят 80% выручки.
• Класс B — промежуточные объекты. Примерно 30% ассортимента, дающие 15% выручки.
• Класс C — наименее ценные объекты. Оставшиеся 50% ассортимента, которые приносят всего 5% выручки.

Если из ассортимента супермаркета исчезнет хлеб или молоко (товары группы А), магазин понесет колоссальные убытки и отток покупателей. Если закончится экзотический соус (товар группы С), большинство клиентов этого даже не заметят.

SQL-шаблон для ABC-анализа

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

Допустим, у нас есть таблица sales_data с историей продаж.

WITH product_revenue AS (
    -- Шаг 1: Считаем общую выручку по каждому товару
    SELECT
        product_id,
        SUM(amount) AS total_revenue
    FROM sales_data
    WHERE sale_date >= '2023-01-01'
    GROUP BY product_id
),
revenue_share AS (
    -- Шаг 2: Считаем долю товара и нарастающий итог
    SELECT
        product_id,
        total_revenue,
        -- Выручка конкретного товара делится на общую выручку всех товаров
        total_revenue / SUM(total_revenue) OVER() AS share_pct,
        -- Нарастающий итог доли выручки с сортировкой по убыванию
        SUM(total_revenue) OVER(ORDER BY total_revenue DESC) / SUM(total_revenue) OVER() AS cumulative_share
    FROM product_revenue
)
-- Шаг 3: Присваиваем категории на основе нарастающего итога
SELECT
    product_id,
    total_revenue,
    ROUND(cumulative_share * 100, 2) AS cumulative_pct,
    CASE
        WHEN cumulative_share <= 0.80 THEN 'A'
        WHEN cumulative_share <= 0.95 THEN 'B'
        ELSE 'C'
    END AS abc_category
FROM revenue_share
ORDER BY total_revenue DESC;

В блоке revenue_share мы используем конструкцию SUM(total_revenue) OVER(ORDER BY total_revenue DESC). Она берет самый продаваемый товар, затем прибавляет к нему второй по продажам, затем третий и так далее. Как только эта накопительная сумма достигает 80% от всей выручки магазина, конструкция CASE WHEN перестает присваивать категорию 'A' и начинает присваивать 'B'.

XYZ-анализ: измерение стабильности

ABC-анализ отлично показывает, сколько денег приносит товар. Но он ничего не говорит о том, как часто и насколько стабильно его покупают.

Представьте два товара, каждый из которых принес по 1 000 000 рублей за год:

1. Свежий хлеб — продается каждый день примерно на 2 700 рублей. Спрос абсолютно предсказуем.
2. Дорогая кофемашина — продалась один раз в декабре перед праздниками. В остальные 11 месяцев продаж не было.

Оба товара попадут в категорию 'A' по выручке. Но управлять их запасами нужно совершенно по-разному. Для оценки стабильности спроса применяется XYZ-анализ.

Он делит товары на три группы на основе коэффициента вариации (отклонения продаж от среднего значения):

• Класс X (вариация 0–10%) — стабильный спрос. Легко прогнозировать, можно настроить автоматические закупки.
• Класс Y (вариация 10–25%) — спрос с колебаниями. Часто это сезонные товары или товары, зависящие от маркетинговых акций.
• Класс Z (вариация более 25%) — случайный, непредсказуемый спрос. Закупать впрок опасно, лучше работать «под заказ».

Формула коэффициента вариации ($CV$): $CV = \frac{\sigma}{\mu} \times 100$ Где $\sigma$ — стандартное отклонение продаж за период, а $\mu$ — среднее значение продаж за тот же период.

SQL-шаблон для XYZ-анализа

В SQL для расчета стандартного отклонения и среднего значения есть встроенные агрегатные функции STDDEV() и AVG().

WITH monthly_sales AS (
    -- Шаг 1: Агрегируем продажи по месяцам для каждого товара
    SELECT
        product_id,
        DATE_TRUNC('month', sale_date) AS sale_month,
        SUM(quantity) AS monthly_qty
    FROM sales_data
    GROUP BY product_id, DATE_TRUNC('month', sale_date)
),
variation_calc AS (
    -- Шаг 2: Считаем среднее, отклонение и коэффициент вариации
    SELECT
        product_id,
        AVG(monthly_qty) AS avg_qty,
        STDDEV(monthly_qty) AS stddev_qty,
        -- Защита от деления на ноль с помощью NULLIF
        (STDDEV(monthly_qty) / NULLIF(AVG(monthly_qty), 0)) * 100 AS cov_pct
    FROM monthly_sales
    GROUP BY product_id
    -- Исключаем товары, которые продавались менее 3 месяцев (недостаточно данных для статистики)
    HAVING COUNT(sale_month) >= 3
)
-- Шаг 3: Присваиваем категории XYZ
SELECT
    product_id,
    ROUND(cov_pct, 2) AS variation_percentage,
    CASE
        WHEN cov_pct <= 10 THEN 'X'
        WHEN cov_pct <= 25 THEN 'Y'
        ELSE 'Z'
    END AS xyz_category
FROM variation_calc;

Матрица ABC-XYZ: объединение подходов

Настоящая магия аналитики начинается, когда мы объединяем результаты двух анализов. Соединив категории с помощью JOIN, мы получаем матрицу ABC-XYZ, состоящую из 9 сегментов.

Каждый сегмент требует своей бизнес-стратегии:

• AX (Высокая ценность, стабильный спрос) — ядро бизнеса. Эти товары приносят максимум денег и продаются постоянно. Их отсутствие на складе — катастрофа. Стратегия: всегда держать страховой запас, автоматизировать закупки.
• AZ (Высокая ценность, непредсказуемый спрос) — опасная зона. Приносят много денег, но спрос скачет. Стратегия: ручной контроль закупок, работа с поставщиками по предзаказу.
• CX (Низкая ценность, стабильный спрос) — мелочевка, которую покупают постоянно (например, пакеты на кассе). Стратегия: максимальная автоматизация, чтобы не тратить время менеджеров.
• CZ (Низкая ценность, непредсказуемый спрос) — мертвый груз. Приносят копейки, лежат на складе месяцами, замораживая оборотный капитал. Стратегия: распродать со скидкой и вывести из ассортимента.

RFM-анализ: сегментация клиентов

Если ABC и XYZ чаще применяются к товарам, то для анализа базы покупателей золотым стандартом является RFM-анализ. Эта аббревиатура расшифровывается как:

1. Recency (Давность) — сколько времени прошло с момента последней покупки клиента. Чем меньше времени прошло, тем выше вероятность, что клиент купит снова.
2. Frequency (Частота) — сколько всего заказов сделал клиент за свою жизнь. Показывает лояльность.
3. Monetary (Деньги) — какую суммарную выручку принес клиент. Показывает покупательскую способность.

Суть метода в том, чтобы отсортировать всех клиентов по каждому из трех параметров и разделить их на равные группы (квантили). Обычно используют шкалу от 1 до 3 или от 1 до 5.

Клиент с оценкой «555» — это ваш идеальный покупатель: покупал недавно (5), покупает часто (5) и тратит много (5). Клиент «111» — это потерянный клиент, который купил один раз на маленькую сумму несколько лет назад.

SQL-шаблон для RFM-анализа

Для разделения базы на равные группы в SQL существует специальная оконная функция NTILE(N). Она берет отсортированный список строк и делит его на N максимально равных «корзин», присваивая каждой строке номер ее корзины.

WITH customer_stats AS (
    -- Шаг 1: Собираем сырые метрики по каждому клиенту
    SELECT
        customer_id,
        -- Давность: разница в днях между текущей датой и последней покупкой
        CURRENT_DATE - MAX(sale_date) AS recency_days,
        -- Частота: количество уникальных чеков/заказов
        COUNT(DISTINCT order_id) AS frequency_count,
        -- Деньги: общая сумма покупок
        SUM(amount) AS monetary_sum
    FROM sales_data
    GROUP BY customer_id
),
rfm_scoring AS (
    -- Шаг 2: Присваиваем баллы от 1 до 5 с помощью NTILE
    SELECT
        customer_id,
        recency_days,
        frequency_count,
        monetary_sum,
        -- Для Recency: чем МЕНЬШЕ дней, тем ЛУЧШЕ (поэтому сортировка ASC)
        -- NTILE(5) разделит клиентов на 5 групп. 5 - самые недавние, 1 - самые давние.
        -- Внимание: логика сортировки зависит от бизнес-задачи. Здесь мы инвертируем NTILE,
        -- чтобы 5 означало "недавно".
        6 - NTILE(5) OVER(ORDER BY recency_days ASC) AS r_score,

        -- Для Frequency: чем БОЛЬШЕ заказов, тем ЛУЧШЕ (сортировка DESC)
        NTILE(5) OVER(ORDER BY frequency_count DESC) AS f_score,

        -- Для Monetary: чем БОЛЬШЕ денег, тем ЛУЧШЕ (сортировка DESC)
        NTILE(5) OVER(ORDER BY monetary_sum DESC) AS m_score
    FROM customer_stats
)
-- Шаг 3: Формируем итоговый сегмент
SELECT
    customer_id,
    r_score,
    f_score,
    m_score,
    -- Склеиваем цифры в единую строку-код (например, '555')
    CONCAT(r_score, f_score, m_score) AS rfm_segment,
    -- Присваиваем человекочитаемые названия ключевым сегментам
    CASE
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN 'Чемпионы'
        WHEN r_score <= 2 AND f_score >= 4 AND m_score >= 4 THEN 'В зоне риска (Лояльные в прошлом)'
        WHEN r_score >= 4 AND f_score <= 2 THEN 'Новички'
        WHEN r_score <= 2 AND f_score <= 2 AND m_score <= 2 THEN 'Потерянные'
        ELSE 'Обычные клиенты'
    END AS customer_profile
FROM rfm_scoring;

Обратите внимание на логику работы с recency_days. В отличие от выручки, где «больше — значит лучше», с давностью всё наоборот: 5 дней назад — это отлично, а 500 дней назад — плохо. Поэтому при использовании NTILE мы либо сортируем по убыванию и оставляем как есть, либо сортируем по возрастанию и вычитаем результат из 6 (чтобы группа 1 стала группой 5).

Как использовать результаты RFM

Получив таблицу с профилями клиентов, маркетологи могут запускать точечные кампании, экономя бюджет:

• «Чемпионам» не нужно давать скидки — они и так лояльны. Им предлагают ранний доступ к новым коллекциям или премиальный сервис.
• «Новичкам» отправляют приветственную цепочку писем (onboarding) с небольшим бонусом на вторую покупку, чтобы перевести их в статус регулярных покупателей.
• «В зоне риска» — это клиенты, которые раньше приносили много денег, но давно не заходили. Это самая важная группа для реактивации. Им звонят лично или отправляют агрессивную скидку (например, 30%), так как вернуть старого лояльного клиента дешевле, чем привлечь нового.

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

Представьте ситуацию: вы написали блестящий SQL-запрос на 150 строк. Он использует сложные оконные функции, объединяет пять таблиц, рассчитывает когортный Retention и сегментирует клиентов по RFM-модели. Вы отправляете этот код генеральному директору. Что он с ним сделает? Ничего. Бизнесу не нужен код — бизнесу нужны понятные графики, цифры и возможность фильтровать данные в пару кликов.

Чтобы превратить SQL-запросы в интерактивные дашборды, аналитики используют специализированные программы — BI-системы (Business Intelligence). Но BI-система не может работать эффективно, если просто «скормить» ей сырые таблицы. В этой статье мы разберем финальный этап работы аналитика данных: как подготовить данные для визуализации, ускорить работу запросов и построить надежную архитектуру отчетности.

Витрина данных: мост между базой и дашбордом

Когда пользователь открывает дашборд и выбирает в фильтре «Показать продажи за ноябрь», BI-система отправляет SQL-запрос в базу данных. Если ваш дашборд опирается на сырые нормализованные таблицы, базе придется каждый раз заново выполнять тяжелые JOIN, сортировки и агрегации. Дашборд будет грузиться минутами, а сервер базы данных «ляжет» от нагрузки.

Чтобы этого избежать, аналитики создают витрины данных (Datamarts).

Витрина данных — это широкая, предварительно агрегированная и очищенная таблица, специально подготовленная для конкретного бизнес-отчета или дашборда.

Если реляционная база данных строится по принципу нормализации (избежание дублирования), то витрина данных намеренно денормализована. В ней всё собрано в одном месте: и ID клиента, и его имя, и название категории товара, и сумма покупки.

Пример создания витрины данных

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

-- Формируем витрину данных для дашборда продаж
WITH customer_metrics AS (
    SELECT
        customer_id,
        MIN(sale_date) AS first_purchase_date,
        COUNT(DISTINCT order_id) AS total_orders
    FROM sales
    GROUP BY customer_id
)
SELECT
    s.order_id,
    s.sale_date,
    s.amount,
    -- Добавляем информацию о товаре
    p.product_name,
    p.category_name,
    -- Добавляем информацию о клиенте
    c.full_name,
    c.city,
    -- Обогащаем аналитическими признаками
    cm.total_orders,
    CASE
        WHEN s.sale_date = cm.first_purchase_date THEN 'Новый'
        ELSE 'Повторный'
    END AS customer_type
FROM sales s
LEFT JOIN products p ON s.product_id = p.product_id
LEFT JOIN customers c ON s.customer_id = c.customer_id
LEFT JOIN customer_metrics cm ON s.customer_id = cm.customer_id
WHERE s.sale_date >= '2023-01-01';

Теперь BI-системе достаточно сделать простой SELECT * FROM витрина, чтобы мгновенно построить графики по категориям, городам или типам клиентов.

Как сохранить витрину: Views и Materialized Views

Мы написали код витрины. Но как сделать так, чтобы BI-система могла к ней обращаться? В SQL для этого существуют представления (Views).

Представление (View) — это сохраненный в базе данных SQL-запрос, который ведет себя как виртуальная таблица.

Синтаксис предельно прост:

CREATE VIEW sales_dashboard_datamart AS
SELECT ... -- здесь идет наш большой запрос витрины

Когда BI-система обращается к sales_dashboard_datamart, база данных «под капотом» подставляет исходный код и выполняет его. Это удобно для организации кода, но не решает проблему производительности, так как вычисления всё равно происходят в реальном времени.

Для тяжелых дашбордов используется материализованное представление (Materialized View).

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

CREATE MATERIALIZED VIEW sales_dashboard_datamart AS
SELECT ... -- тяжелый запрос

Характеристика	Обычное представление (View)	Материализованное (Materialized View)
Где хранятся данные	Нигде (вычисляются на лету)	На жестком диске (физически)
Скорость чтения	Медленно (зависит от сложности запроса)	Мгновенно (как обычная таблица)
Актуальность данных	Всегда 100% свежие данные	Данные актуальны на момент последнего обновления
Когда использовать	Простые справочники, легкие запросы	Тяжелые дашборды, агрегации за несколько лет

Главный нюанс материализованных представлений — их нужно регулярно обновлять (обычно это делают ночью по расписанию с помощью команды REFRESH MATERIALIZED VIEW).

Оптимизация запросов: как заставить SQL летать

Даже если вы используете материализованные представления, сам процесс их обновления может занимать часы, если код написан неоптимально. Умение писать быстрые запросы — это то, что отличает Junior-аналитика от Middle.

1. Читайте план выполнения (EXPLAIN)

Перед тем как выполнить запрос, СУБД строит план выполнения запроса (Execution Plan) — оптимальный маршрут поиска и соединения данных. Чтобы увидеть этот маршрут, достаточно добавить слово EXPLAIN перед вашим запросом.

EXPLAIN
SELECT customer_id, SUM(amount)
FROM sales
WHERE sale_date = CURRENT_DATE
GROUP BY customer_id;

База данных вернет текст, в котором опишет, как именно она будет искать данные. Если вы видите там Seq Scan (Sequential Scan — последовательное сканирование), это значит, что база будет читать таблицу строка за строкой. Для таблицы в 100 миллионов строк это катастрофа. Здесь на помощь приходят индексы базы данных, которые превращают Seq Scan в молниеносный Index Scan.

2. Фильтруйте данные как можно раньше

Самая частая ошибка новичков — сначала соединить огромные таблицы через JOIN, а потом отфильтровать результат в WHERE.

Плохо:

SELECT *
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.status = 'completed' AND u.country = 'KZ';

База соединит миллионы заказов с миллионами пользователей, создаст гигантскую временную таблицу в памяти, и только потом отбросит 99% строк.

Хорошо:

WITH filtered_orders AS (
    SELECT * FROM orders WHERE status = 'completed'
),
filtered_users AS (
    SELECT * FROM users WHERE country = 'KZ'
)
SELECT *
FROM filtered_orders o
LEFT JOIN filtered_users u ON o.user_id = u.id;

Мы отфильтровали данные до соединения. Теперь JOIN работает не с миллионами, а с тысячами строк. Запрос выполнится в десятки раз быстрее.

3. Избегайте SELECT *

В аналитике правило простое: запрашивайте только те столбцы, которые действительно нужны для отчета. Использование SELECT * в рабочих скриптах — это моветон.

Во-первых, это перегружает сеть (базе нужно передать лишние мегабайты текста). Во-вторых, колоночные базы данных (такие как ClickHouse, часто используемые в аналитике) физически читают с диска только те столбцы, которые указаны в SELECT. Указав конкретные поля, вы можете ускорить запрос в сотни раз.

Подключение к BI-системам

Когда витрины готовы и оптимизированы, наступает этап визуализации. На рынке существует множество BI-систем: Tableau, Power BI, Apache Superset, Metabase, Redash.

Несмотря на разный интерфейс, принцип их работы с SQL одинаков:

1. Вы подключаете BI-систему к вашей базе данных, указывая хост, порт, логин и пароль.
2. В интерфейсе BI-системы вы выбираете вашу витрину (или пишете SQL-запрос напрямую в редакторе BI).
3. Настраиваете визуализацию: ось X — sale_date, ось Y — total_amount, разбивка цветом — category_name.
4. BI-система сама генерирует финальный SQL-запрос с нужными GROUP BY и отправляет его в базу.

Важно понимать: BI-система не хранит данные в себе (за редким исключением). Она лишь транслирует действия пользователя (клики по фильтрам) в SQL-запросы, отправляет их в базу и рисует красивые графики из полученных ответов.

Заключение курса

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

SQL — это не просто язык программирования. Это способ мышления. Он учит разбивать сложные бизнес-вопросы на логические шаги, оперировать множествами и находить закономерности в хаосе сырой информации.

Теперь, когда вы владеете этим инструментом, перед вами открыты двери в мир Data-аналитики. Вы больше не зависите от чужих выгрузок в Excel. Вы можете сами докопаться до истины, найти точки роста для бизнеса и обосновать свои решения твердыми цифрами. Практикуйтесь, изучайте планы выполнения запросов, стройте дашборды — и данные обязательно ответят вам взаимностью.

Анатомия производительности: как заставить SQL летать

Представьте, что вы написали запрос для расчета когортного Retention. На тестовой базе из тысячи строк он отработал за миллисекунду. Вы с гордостью переносите код на рабочий сервер, где хранятся данные за пять лет, нажимаете «Выполнить» — и база данных «зависает» на сорок минут, а затем выдает ошибку нехватки памяти.

Разница между запросом, который просто возвращает правильный результат, и запросом, который делает это эффективно, заключается в понимании внутренних механизмов СУБД. База данных — это не просто хранилище, это сложный математический движок. Чтобы писать быстрый код, аналитику нужно думать так же, как этот движок.

Оптимизатор запросов и статистика

Когда вы отправляете SQL-код на сервер, он не выполняется строка за строкой. SQL — декларативный язык: вы описываете, что хотите получить, а база данных сама решает, как это сделать. За принятие этого решения отвечает оптимизатор на основе стоимости (CBO — Cost-Based Optimizer).

Оптимизатор работает как автомобильный навигатор. Если вам нужно доехать из точки А в точку Б, навигатор оценивает пробки, качество дороги и скоростные ограничения, чтобы выбрать оптимальный маршрут. CBO делает то же самое, опираясь на статистику базы данных: сколько строк в таблице, сколько уникальных значений в столбце, есть ли пустые ячейки.

Каждому возможному плану выполнения оптимизатор присваивает условную «стоимость» (Cost) — математическую оценку того, сколько ресурсов процессора и дискового времени потребуется на операцию. План с наименьшей стоимостью пускается в работу.

От теории к практике: EXPLAIN ANALYZE

Ранее мы упоминали команду EXPLAIN, которая показывает ожидаемый маршрут выполнения. Но для реальной оптимизации этого недостаточно, так как ожидания оптимизатора могут расходиться с реальностью из-за устаревшей статистики.

Для глубокого аудита используется команда EXPLAIN ANALYZE. В отличие от обычного EXPLAIN, она физически выполняет запрос и выводит рядом две цифры: ожидаемую стоимость и реальное время выполнения в миллисекундах.

EXPLAIN ANALYZE
SELECT customer_id, SUM(amount)
FROM sales
WHERE status = 'completed'
GROUP BY customer_id;

В выводе этой команды аналитик ищет три базовых физических метода чтения данных:

1. Seq Scan (Sequential Scan) — последовательное сканирование. База читает таблицу от первой до последней строки. Это оправдано, если вы запрашиваете 80% данных таблицы, но катастрофично, если вам нужна одна строка из миллиона.
2. Index Scan — сканирование по индексу. База обращается к индексу, находит точный адрес нужных строк на диске и точечно считывает только их.
3. Index Only Scan — сканирование только индекса. Идеальный сценарий. База находит все нужные данные прямо внутри структуры индекса и вообще не обращается к основной таблице.

Математика индексов: селективность и SARGability

Наличие индекса на столбце не гарантирует, что база данных будет его использовать. Оптимизатор выберет Index Scan только в том случае, если индекс обладает высокой селективностью.

Селективность индекса — это показатель того, насколько хорошо индекс отсеивает лишние строки. Она рассчитывается по формуле:

$S = \frac{U}{N}$

Где $S$ — селективность (от 0 до 1), $U$ — количество уникальных значений в столбце, $N$ — общее количество строк в таблице.

Если вы создадите индекс на столбец gender (где всего два значения: 'M' и 'F'), его селективность будет стремиться к нулю. При поиске всех мужчин базе данных всё равно придется прочитать половину таблицы. Оптимизатор поймет, что прыгать между индексом и таблицей миллион раз слишком «дорого», проигнорирует индекс и запустит Seq Scan.

Индексы работают лучше всего на столбцах с высокой уникальностью: user_id, email, phone_number.

Правило SARGable-запросов

Даже самый лучший индекс станет бесполезным, если написать условие фильтрации неправильно. В аналитике существует критически важное понятие — SARGable-запрос (Search ARGument ABLE). Это запрос, написанный так, чтобы СУБД могла применить индекс.

Главное правило SARGability: никогда не применяйте функции к индексируемому столбцу в блоке WHERE.

Рассмотрим типичную задачу: найти все продажи за 2023 год. Столбец sale_date проиндексирован.

Неоптимизированный код (Не SARGable):

SELECT order_id, amount
FROM sales
WHERE EXTRACT(YEAR FROM sale_date) = 2023;

Что здесь происходит? База данных не может использовать индекс по датам, потому что вы ищете не дату, а результат функции EXTRACT. СУБД придется прочитать всю таблицу (Seq Scan), применить функцию к каждой строке в памяти, и только потом отфильтровать результат.

Оптимизированный код (SARGable):

SELECT order_id, amount
FROM sales
WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';

Здесь столбец sale_date стоит в чистом виде. Оптимизатор мгновенно найдет в индексе стартовую точку ('2023-01-01') и конечную точку, выдав результат за доли секунды.

Продвинутые индексы: составные и покрывающие

Для сложных аналитических дашбордов одиночных индексов часто не хватает. Когда пользователи фильтруют отчет одновременно по региону, категории и дате, на помощь приходят составные индексы (Composite Indexes) — структуры, построенные сразу по нескольким столбцам.

Ключевая особенность составного индекса — строгая иерархия столбцов (правило крайнего левого префикса). Если вы создали индекс (country, category, status), он будет работать как телефонный справочник, где сначала всё отсортировано по странам, внутри стран — по категориям, а внутри категорий — по статусам.

Составной индекс работает только в том случае, если в запросе используется его первый (крайний левый) столбец.

Условие в WHERE	Будет ли работать индекс (country, category, status)?
WHERE country = 'KZ'	Да, полностью
WHERE country = 'KZ' AND category = 'Electronics'	Да, полностью
WHERE category = 'Electronics'	Нет. Пропущен первый столбец (country)
WHERE country = 'KZ' AND status = 'Active'	Частично (только для поиска страны)

Если ваш запрос запрашивает только те столбцы, которые уже есть в индексе, индекс становится покрывающим (Covering Index). В этом случае СУБД применяет алгоритм Index Only Scan. Ей не нужно обращаться к физической таблице на диске — вся необходимая информация (например, для подсчета количества заказов по странам) извлекается прямо из индексного дерева, что ускоряет запрос в десятки раз.

Физика соединений: как работают JOIN под капотом

Мы привыкли воспринимать объединение таблиц как единую логическую операцию. Но на физическом уровне СУБД может соединять таблицы тремя разными алгоритмами. Понимание этих алгоритмов помогает избежать зависаний при анализе больших данных.

1. Nested Loop Join (Соединение вложенными циклами). База берет первую строку из левой таблицы и пробегает по всей правой таблице в поисках совпадений. Затем берет вторую строку — и снова пробегает правую таблицу. Когда эффективно: Если левая таблица очень маленькая (после фильтрации осталось 10 строк), а правая имеет индекс по ключу соединения. Когда опасно: При соединении двух больших таблиц без индексов. Сложность растет экспоненциально.

2. Hash Join (Хэш-соединение). База берет меньшую из двух таблиц, прогоняет ключи соединения через математическую хэш-функцию и строит в оперативной памяти хэш-таблицу. Затем она один раз сканирует большую таблицу, сверяя ключи с хэш-таблицей в памяти. Когда эффективно: Стандартный выбор для аналитики при соединении больших неиндексированных таблиц. Когда опасно: Если меньшая таблица не помещается в оперативную память (RAM), база начнет сбрасывать временные данные на жесткий диск, что приведет к катастрофическому падению скорости.

3. Merge Join (Соединение слиянием). Обе таблицы сначала сортируются по ключу соединения, а затем база проходит по ним параллельно, как застегивающаяся молния. Когда эффективно: Если обе таблицы уже отсортированы (например, ключи соединения являются кластерными индексами).

Аналитик не может напрямую приказать базе использовать конкретный алгоритм (хотя в некоторых диалектах есть хинты), но может помочь оптимизатору сделать правильный выбор. Главный инструмент здесь — раннее отсечение данных.

Оптимизация оператора IN

Частая задача в аналитике — отфильтровать транзакции по большому списку клиентов. Новички часто используют оператор IN:

SELECT *
FROM transactions
WHERE client_id IN (101, 102, 103 ... /* еще 50 000 ID */);

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

Эффективная альтернатива — использование виртуальных таблиц через JOIN VALUES или временных таблиц.

SELECT t.*
FROM transactions t
JOIN (VALUES (101), (102), (103) /* ... */) AS filter_list(id)
  ON t.client_id = filter_list.id;

В этом случае оптимизатор воспринимает список как полноценную таблицу, строит для нее хэш-таблицу в памяти и применяет быстрый Hash Join.

Архитектура больших данных: Секционирование

Что делать, если таблица продаж выросла до миллиарда строк? Даже индексы перестают спасать: само индексное дерево становится настолько огромным, что не помещается в оперативную память, и базе приходится постоянно читать его с медленного жесткого диска.

Решением становится секционирование (Партицирование) — физическое разделение одной огромной таблицы на множество мелких, управляемых частей (партиций), при сохранении единого логического имени для пользователя.

Чаще всего в аналитике применяется диапазонное партицирование по дате (Range Partitioning). Например, таблица sales физически разбивается на sales_2023_11, sales_2023_12, sales_2024_01 и так далее.

Когда вы пишете запрос:

SELECT SUM(amount)
FROM sales
WHERE sale_date >= '2023-12-01' AND sale_date < '2024-01-01';

Срабатывает механизм Partition Pruning (отсечение партиций). Оптимизатор смотрит на условие и понимает: «Мне не нужно сканировать миллиард строк. Я могу полностью проигнорировать данные за 2022 и 2024 годы и обратиться только к одной маленькой физической таблице sales_2023_12». Это снижает нагрузку на дисковую подсистему в сотни раз.

Чек-лист оптимизации аналитических запросов

Подводя итог глубокому погружению в производительность, зафиксируем главные правила написания Enterprise-кода:

1. Избегайте COUNT(DISTINCT) на больших объемах. Эта функция требует сортировки или хэширования всего набора данных для поиска уникальных значений. Если точная цифра не критична (например, для оценки порядка аудитории), в современных СУБД используют функции аппроксимации (например, APPROX_COUNT_DISTINCT), которые работают в 50 раз быстрее с погрешностью в 1-2%.
2. Заменяйте коррелированные подзапросы на JOIN. Если подзапрос в блоке SELECT или WHERE ссылается на внешнюю таблицу, он будет выполняться заново для каждой строки внешнего запроса. Перепишите логику через LEFT JOIN или CTE.
3. Аккуратно используйте оконные функции. Оператор ORDER BY внутри конструкции OVER() заставляет базу данных физически сортировать каждую партицию. Если вам нужна просто сумма по категории (без нарастающего итога), используйте SUM() OVER(PARTITION BY category) без сортировки.
4. Не дублируйте дедупликацию. Использование UNION вместо UNION ALL заставляет базу данных искать и удалять дубликаты между объединяемыми наборами. Если вы уверены, что наборы не пересекаются, всегда пишите UNION ALL.
5. Читайте планы выполнения. Сделайте команду EXPLAIN ANALYZE своим главным инструментом. Ищите в плане слова Seq Scan на больших таблицах, External Merge Disk (сортировка на диске из-за нехватки RAM) и огромные значения Cost.

Оптимизация SQL — это непрерывный процесс балансировки между потребностями бизнеса в сложных метриках и физическими ограничениями серверов. Умение писать запросы, которые не только выдают верный результат, но и берегут ресурсы инфраструктуры — это ключевой навык, отличающий Senior-аналитика.

Полезные шаблоны SQL для аналитика

Написание SQL-запросов похоже на игру в шахматы. Вы уже знаете, как ходят фигуры (базовые операторы) и изучили тактические приемы (оконные функции, CTE, джойны). Теперь пришло время разобрать классические дебюты — готовые паттерны, которые аналитики данных используют каждый день для решения типовых бизнес-задач.

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

Шаблон 1: Разделение выручки на новых и вернувшихся клиентов

Бизнесу критически важно понимать структуру своих доходов: растем ли мы за счет привлечения новой аудитории или благодаря лояльности старой? Для этого каждую транзакцию нужно разметить: совершена ли она в день первой покупки клиента или позже.

Когорта первого дня (First-day cohort) — аналитический паттерн, при котором мы вычисляем дату первого целевого действия пользователя и сравниваем с ней все его последующие действия.

Вместо тяжелого соединения таблицы самой с собой (Self-Join), мы используем оконную функцию MIN().

WITH user_first_purchase AS (
    SELECT
        order_id,
        customer_id,
        order_date,
        amount,
        -- Находим дату первой покупки для каждого клиента
        MIN(order_date) OVER(PARTITION BY customer_id) AS first_order_date
    FROM orders
    WHERE status = 'completed'
)
SELECT
    DATE_TRUNC('month', order_date) AS report_month,
    SUM(CASE WHEN order_date = first_order_date THEN amount ELSE 0 END) AS new_revenue,
    SUM(CASE WHEN order_date > first_order_date THEN amount ELSE 0 END) AS recurring_revenue,
    SUM(amount) AS total_revenue
FROM user_first_purchase
GROUP BY 1
ORDER BY 1;

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

Шаблон 2: Сессионизация (Проблема пробелов и островов)

Представьте, что у вас есть сырой лог кликов пользователя на сайте. Вам нужно сгруппировать эти клики в осмысленные визиты (сессии). Если пользователь бездействовал более 30 минут, следующий его клик должен считаться началом новой сессии.

Эта задача известна в SQL как Проблема пробелов и островов (Gaps and Islands problem) — класс задач, где нужно найти непрерывные последовательности данных (острова) и разрывы между ними (пробелы). Процесс разбивки потока событий на такие группы называется Сессионизацией.

Решение требует трех шагов в CTE:

1. Найти время предыдущего события.
2. Проверить, превышает ли разница таймаут (если да — ставим флаг 1, иначе 0).
3. Запустить нарастающий итог по этим флагам.

WITH lag_events AS (
    SELECT
        user_id,
        event_time,
        -- Получаем время предыдущего события пользователя
        LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time) AS prev_event_time
    FROM clickstream
),
new_session_flags AS (
    SELECT
        user_id,
        event_time,
        -- Если предыдущего события нет ИЛИ прошло > 30 минут, это новая сессия (1)
        CASE
            WHEN prev_event_time IS NULL THEN 1
            WHEN EXTRACT(EPOCH FROM (event_time - prev_event_time))/60 > 30 THEN 1
            ELSE 0
        END AS is_new_session
    FROM lag_events
)
SELECT
    user_id,
    event_time,
    -- Нарастающий итог флагов создает уникальный ID сессии для пользователя
    SUM(is_new_session) OVER(PARTITION BY user_id ORDER BY event_time) AS session_id
FROM new_session_flags;

Магия происходит на третьем шаге. Нарастающий итог (Cumulative Sum) прибавляет единицу только тогда, когда встречает флаг новой сессии. Все последующие события в рамках этих 30 минут получают нули, поэтому сумма не меняется, и они «приклеиваются» к текущему session_id.

Шаблон 3: Поиск аномалий (Скользящее среднее и Z-оценка)

Дашборды часто перегружены графиками, и руководство может пропустить важный сбой (например, отвалился платежный шлюз). Задача аналитика — написать запрос, который сам подсветит аномальные дни.

Для этого используется Скользящее среднее (Moving Average) — метод сглаживания временных рядов, при котором для каждой точки рассчитывается среднее значение за несколько предыдущих периодов. Чтобы понять, насколько текущий день отклоняется от нормы, применяется Z-оценка (Z-score).

$$Z = \frac{X - \mu}{\sigma}$$

Где:

• $X$ — значение метрики за текущий день (например, количество заказов).
• $\mu$ — скользящее среднее за предыдущие $N$ дней.
• $\sigma$ — стандартное отклонение за те же $N$ дней (показывает типичный разброс значений).

Если $Z > 2$ или $Z < -2$, это означает, что текущее значение выходит за рамки 95% нормальных колебаний. Это статистическая аномалия.

WITH daily_stats AS (
    SELECT
        order_date,
        COUNT(order_id) AS orders_count
    FROM orders
    GROUP BY 1
),
moving_metrics AS (
    SELECT
        order_date,
        orders_count,
        -- Среднее за 7 предыдущих дней (не включая текущий!)
        AVG(orders_count) OVER(
            ORDER BY order_date
            ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
        ) AS moving_avg,
        -- Стандартное отклонение за 7 предыдущих дней
        STDDEV(orders_count) OVER(
            ORDER BY order_date
            ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
        ) AS moving_stddev
    FROM daily_stats
)
SELECT
    order_date,
    orders_count,
    moving_avg,
    (orders_count - moving_avg) / NULLIF(moving_stddev, 0) AS z_score
FROM moving_metrics
WHERE ABS((orders_count - moving_avg) / NULLIF(moving_stddev, 0)) > 2;

Обратите внимание на конструкцию ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING. Мы намеренно исключаем текущий день из расчета среднего, иначе сегодняшняя аномалия исказит «норму», с которой мы ее сравниваем.

Шаблон 4: Динамика роста (YoY и MoM) и безопасное деление

Бизнес всегда мыслит в динамике: как мы выросли по сравнению с прошлым месяцем (MoM — Month-over-Month) или аналогичным месяцем прошлого года (YoY — Year-over-Year).

Для расчета используется Смещение периодов (Period shifting) с помощью оконной функции LAG(). Главная проблема при расчете роста — риск деления на ноль, если в прошлом периоде не было продаж. Для этого применяется функция NULLIF(выражение, значение). Она возвращает NULL, если выражение равно заданному значению. А деление на NULL в SQL безопасно возвращает NULL, не вызывая ошибку (в отличие от деления на ноль).

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
),
shifted_revenue AS (
    SELECT
        month,
        revenue,
        -- Смещение на 1 строку назад (прошлый месяц)
        LAG(revenue, 1) OVER(ORDER BY month) AS prev_month_rev,
        -- Смещение на 12 строк назад (тот же месяц прошлого года)
        LAG(revenue, 12) OVER(ORDER BY month) AS prev_year_rev
    FROM monthly_revenue
)
SELECT
    month,
    revenue,
    -- Формула: (Текущее - Прошлое) / Прошлое
    ROUND((revenue - prev_month_rev) / NULLIF(prev_month_rev, 0) * 100, 2) AS mom_growth_pct,
    ROUND((revenue - prev_year_rev) / NULLIF(prev_year_rev, 0) * 100, 2) AS yoy_growth_pct
FROM shifted_revenue
ORDER BY month DESC;

Этот шаблон гарантирует, что даже если в данных есть пропуски (например, магазин не работал месяц), запрос не упадет с фатальной ошибкой Division by zero.

Шаблон 5: Дедупликация сырых данных

В реальном мире данные часто дублируются. Например, система логистики может присылать статус заказа при каждом его изменении. В таблице order_status_log один заказ будет иметь 10 строк (создан, оплачен, собран, в пути, доставлен). Если вы просто сделаете JOIN с этой таблицей, сумма выручки умножится на 10.

Дедупликация (Deduplication) — процесс очистки набора данных от избыточных копий строк по определенному правилу (например, оставить только самую свежую запись).

Новички пытаются решить это через GROUP BY order_id и MAX(updated_at). Но это позволяет получить только дату, а как вытащить сам текстовый статус, соответствующий этой дате? Правильный паттерн — использование ROW_NUMBER().

WITH ranked_logs AS (
    SELECT
        order_id,
        status,
        updated_at,
        -- Нумеруем статусы каждого заказа от самых новых к старым
        ROW_NUMBER() OVER(
            PARTITION BY order_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM order_status_log
)
SELECT
    order_id,
    status AS current_status,
    updated_at AS last_update_time
FROM ranked_logs
-- Оставляем только строку с номером 1 (самую свежую)
WHERE rn = 1;

Этот шаблон работает как хирургический скальпель. PARTITION BY order_id создает изолированное окно для каждого заказа. ORDER BY updated_at DESC сортирует события внутри окна так, что самое последнее событие всегда получает номер 1. Фильтрация во внешнем запросе отсекает всю историю, оставляя только актуальный срез данных.

Сохраните эти шаблоны. В 80% случаев аналитическая задача на работе сводится к адаптации одного из этих паттернов под конкретные названия таблиц вашей базы данных.

Итоговый проект: полноценный аналитический отчёт

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

Но в реальной работе аналитика данных редко просят просто «написать запрос с оконной функцией». Бизнес приходит с комплексной задачей: «Нам нужен дашборд по итогам года, который покажет здоровье бизнеса, сегментацию клиентов и эффективность ассортимента».

В этой статье мы объединим все изученные ранее концепции и создадим полноценный аналитический пайплайн. Мы пройдем путь от сырых, грязных данных до готовой витрины, которую можно сразу подключать к BI-системе.

Архитектура аналитического решения: ETL и ELT

Прежде чем писать код, необходимо понять свое место в инфраструктуре данных компании. Аналитики редко работают напрямую с «боевой» базой данных (production DB), куда сыплются транзакции с сайта. Это опасно: тяжелый аналитический запрос может «положить» базу, и клиенты не смогут оформить заказ.

Данные копируются в специализированные аналитические хранилища (Data Warehouse). Процесс переноса и трансформации данных называется ETL или ELT.

• ETL (Extract, Transform, Load) — извлечение данных из источника, их трансформация (очистка, агрегация) на промежуточном сервере и загрузка в хранилище.
• ELT (Extract, Load, Transform) — современный подход, при котором сырые данные сначала загружаются в мощное хранилище (например, ClickHouse или PostgreSQL), а уже внутри него трансформируются с помощью SQL-запросов.

В рамках ELT-процесса аналитик выступает главным архитектором трансформаций. Ваша задача — взять десятки нормализованных таблиц и превратить их в одну широкую, понятную бизнесу витрину данных.

Шаг 1. Постановка бизнес-задачи и аудит источников

Представим, что вы работаете в крупной e-commerce компании. Коммерческий директор ставит задачу: подготовить фундамент для годового отчета. Дашборд должен отвечать на три вопроса:

1. Какова динамика чистой выручки по месяцам?
2. Кто наши лучшие клиенты (RFM-сегментация)?
3. Какие товары приносят основную прибыль (ABC-анализ)?

В вашем распоряжении три сырые таблицы:

Таблица	Описание	Ключевые столбцы
orders	Лог всех созданных заказов	order_id, customer_id, created_at, status
order_items	Состав каждого заказа	order_id, product_id, quantity, price, discount
products	Справочник товаров	product_id, category_name, cogs (себестоимость)

Шаг 2. Data Quality Check (DQC)

Новички сразу бросаются писать JOIN и считать выручку. Профессионалы начинают с Data Quality Check (DQC) — проверки качества данных.

Сырые данные всегда содержат аномалии. Если вы не отфильтруете их на старте, ваши финальные метрики будут искажены, и бизнес примет неверные решения на основе вашего отчета.

Что мы ищем на этапе DQC:

• Отрицательные цены или количества (ошибка логирования).
• Заказы без клиентов (системные сбои).
• Тестовые заказы разработчиков (обычно имеют специфические ID или email).
• Дубликаты строк.

Напишем диагностический запрос для оценки масштаба бедствия:

SELECT
    COUNT(*) AS total_rows,
    SUM(CASE WHEN price < 0 THEN 1 ELSE 0 END) AS negative_price_errors,
    SUM(CASE WHEN quantity <= 0 THEN 1 ELSE 0 END) AS zero_qty_errors,
    SUM(CASE WHEN customer_id IS NULL THEN 1 ELSE 0 END) AS missing_customer_errors
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.order_id;

Допустим, мы обнаружили, что 0.5% строк содержат ошибки. В аналитике существует понятие Уровень ошибок данных (Data Error Rate). Он рассчитывается по формуле:

$$Error\ Rate = \frac{Invalid\ Rows}{Total\ Rows} \times 100\%$$

Где $Invalid\ Rows$ — количество строк с аномалиями, а $Total\ Rows$ — общее количество строк. Если показатель меньше 1-2%, мы можем безопасно отфильтровать эти строки в нашем скрипте. Если больше — это повод завести баг-репорт разработчикам.

Шаг 3. Data Storytelling в коде и Защитный SQL

Теперь мы готовы писать итоговый скрипт. Мы будем использовать подход Data Storytelling в коде. Это практика написания SQL-запросов, при которой логика читается сверху вниз, как увлекательная история, благодаря последовательному использованию CTE (Common Table Expressions).

Каждый блок WITH будет выполнять строго одну логическую задачу.

Также мы применим принципы Защитного SQL (Defensive SQL) — стиля написания кода, который предотвращает падение скрипта при неожиданном изменении данных в будущем (например, использование COALESCE для замены NULL на нули и NULLIF для предотвращения деления на ноль).

Этап 3.1: Очистка и обогащение базы (Базовый CTE)

Первый шаг — создать надежный фундамент. Мы объединяем таблицы, фильтруем мусор (результаты нашего DQC) и рассчитываем базовые финансовые метрики на уровне одной строки товара.

WITH clean_sales AS (
    SELECT
        o.order_id,
        o.customer_id,
        o.created_at,
        DATE_TRUNC('month', o.created_at) AS order_month,
        oi.product_id,
        p.category_name,
        oi.quantity,
        oi.price,
        COALESCE(oi.discount, 0) AS discount, -- Защитный SQL: меняем NULL на 0
        p.cogs,
        -- Расчет реальной выручки с учетом скидки
        (oi.price - COALESCE(oi.discount, 0)) * oi.quantity AS net_revenue,
        -- Расчет валовой прибыли (Margin)
        ((oi.price - COALESCE(oi.discount, 0)) - p.cogs) * oi.quantity AS gross_margin
    FROM orders o
    INNER JOIN order_items oi ON o.order_id = oi.order_id
    LEFT JOIN products p ON oi.product_id = p.product_id
    WHERE o.status IN ('completed', 'delivered') -- Берем только успешные заказы
      AND oi.price > 0
      AND oi.quantity > 0
      AND o.customer_id IS NOT NULL
)

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

Этап 3.2: Профилирование клиентов (RFM-метрики)

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

, customer_profiles AS (
    SELECT
        customer_id,
        MIN(created_at) AS first_purchase_date,
        MAX(created_at) AS last_purchase_date,
        COUNT(DISTINCT order_id) AS total_orders, -- Frequency
        SUM(net_revenue) AS lifetime_value,       -- Monetary
        -- Считаем количество дней с последней покупки до текущего момента (Recency)
        EXTRACT(DAY FROM CURRENT_DATE - MAX(created_at)) AS days_since_last_order
    FROM clean_sales
    GROUP BY customer_id
)

Этап 3.3: Профилирование товаров (ABC-классификация)

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

, product_profiles AS (
    SELECT
        product_id,
        category_name,
        SUM(net_revenue) AS product_revenue,
        -- Считаем нарастающий итог выручки в процентах
        SUM(SUM(net_revenue)) OVER(ORDER BY SUM(net_revenue) DESC)
        / NULLIF(SUM(SUM(net_revenue)) OVER(), 0) AS cumulative_share
    FROM clean_sales
    GROUP BY product_id, category_name
),
product_abc AS (
    SELECT
        product_id,
        category_name,
        product_revenue,
        CASE
            WHEN cumulative_share <= 0.80 THEN 'A' -- Топ 80% выручки
            WHEN cumulative_share <= 0.95 THEN 'B' -- Следующие 15%
            ELSE 'C'                               -- Оставшиеся 5%
        END AS abc_class
    FROM product_profiles
)

Здесь мы использовали NULLIF в знаменателе. Если по какой-то причине общая выручка окажется равна нулю (например, сбой в данных за день), скрипт вернет NULL, а не упадет с критической ошибкой базы данных.

Шаг 4. Сборка финальной витрины (Datamart)

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

Денормализация означает, что мы намеренно дублируем некоторые данные (например, класс товара или дату первой покупки клиента) в каждой строке транзакции. Это нарушает правила хранения реляционных баз, но идеально подходит для BI-систем (Tableau, Power BI), так как позволяет им мгновенно строить графики без сложных вычислений «под капотом».

-- Финальный SELECT, собирающий витрину
SELECT
    cs.order_month,
    cs.order_id,
    cs.customer_id,
    cs.product_id,
    cs.category_name,
    cs.net_revenue,
    cs.gross_margin,

    -- Обогащение клиентскими данными
    cp.first_purchase_date,
    CASE
        WHEN DATE_TRUNC('month', cs.created_at) = DATE_TRUNC('month', cp.first_purchase_date)
        THEN 'New'
        ELSE 'Returning'
    END AS customer_type,
    cp.total_orders AS customer_lifetime_orders,

    -- Обогащение товарными данными
    pa.abc_class

FROM clean_sales cs
LEFT JOIN customer_profiles cp ON cs.customer_id = cp.customer_id
LEFT JOIN product_abc pa ON cs.product_id = pa.product_id
ORDER BY cs.order_month DESC, cs.net_revenue DESC;

Разбор результата: почему этот код хорош?

Посмотрите на итоговый скрипт. Он состоит из более чем 60 строк, но читается легко и понятно даже человеком, который не писал этот код.

1. Модульность: Если бизнес попросит изменить логику расчета скидки, вам не нужно переписывать весь запрос. Вы меняете одну строку в CTE clean_sales, и изменения каскадно применяются ко всему отчету.
2. Производительность: Оптимизатор СУБД (CBO) отлично понимает структуру CTE. Он один раз отфильтрует таблицу orders и будет использовать этот компактный набор данных для агрегаций, вместо того чтобы сканировать миллионы строк заново.
3. Бизнес-ценность: Финальная таблица содержит всё необходимое для дашборда. BI-системе остается только сгруппировать данные по order_month и customer_type, чтобы показать график соотношения новых и вернувшихся клиентов, или сделать срез по abc_class, чтобы показать рентабельность топовых товаров.

Напутствие аналитику

SQL — это не просто язык программирования. Это инструмент перевода хаотичной реальности бизнеса в структурированный язык математики и логики.

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

Всегда помните о трех правилах:

1. Не верьте сырым данным. Всегда начинайте с DQC.
2. Пишите код для людей, а не для машин. Используйте алиасы, отступы и CTE. Ваш код будут читать ваши коллеги (или вы сами через полгода).
3. Понимайте бизнес-смысл. Идеально оптимизированный запрос, который считает никому не нужную метрику, бесполезен. Всегда задавайте вопрос: «Какое управленческое решение будет принято на основе этих цифр?».

Вы освоили мощнейший инструмент. Теперь дело за практикой. Успешных вам запросов и чистых данных!