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

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

Представьте, что вы аналитик в крупном маркетплейсе. У вас есть таблица с миллионами строк о продажах за год. Руководство задает вопрос: «Какой регион принес больше всего прибыли в категории электроники в прошлом квартале, если учитывать только заказы дороже 5000 рублей?». Базовый навык SELECT * здесь бессилен — он просто вывалит на вас гору сырых данных. Чтобы ответить на вопрос бизнеса, нужно научиться не просто извлекать данные, а «схлопывать» их, превращая хаос транзакций в стройные показатели.

Логика агрегации: от строк к смыслам

Агрегация — это процесс объединения множества значений в одно результирующее. В SQL за это отвечают функции, которые работают с набором строк: COUNT, SUM, AVG, MIN и MAX. Однако главная сложность для аналитика заключается не в вызове функции, а в понимании контекста, в котором она применяется. Когда мы считаем SUM(amount), SQL должен понимать, по какому признаку суммировать: по всей таблице, по каждому пользователю или по каждой дате.

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

> Важное правило: Все столбцы, которые вы перечисляете в SELECT и которые не обернуты в агрегатную функцию, обязаны присутствовать в блоке GROUP BY. Игнорирование этого правила — самая частая причина ошибок в аналитических запросах.

Рассмотрим пример с интернет-магазином. Допустим, у нас есть таблица orders со столбцами user_id, order_date и revenue. Если мы хотим узнать средний чек каждого клиента, запрос будет выглядеть так: SELECT user_id, AVG(revenue) FROM orders GROUP BY user_id. Здесь база данных сначала найдет все строки одного пользователя, «схлопнет» их и выдаст одно число для каждого ID.

Тонкая настройка фильтров: WHERE против HAVING

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

Оператор WHERE отсекает лишние строки до того, как начнется группировка и расчеты. Если вы хотите посчитать продажи только за декабрь, вы используете WHERE order_date >= '2023-12-01'. Это экономит ресурсы базы данных, так как ей не приходится обрабатывать ненужные данные.

Оператор HAVING применяется после того, как данные были сгруппированы. Он работает с результатами агрегатных функций. Если вам нужно найти не просто всех клиентов, а только «китов» (тех, кто суммарно потратил более 100 000 руб.), вы не сможете использовать WHERE SUM(revenue) > 100000, потому что на этапе WHERE база еще не знает сумму. Правильный путь — использовать HAVING SUM(revenue) > 100000.

| Оператор | Когда применяется | С чем работает | Пример использования | | :--- | :--- | :--- | :--- | | WHERE | До группировки | Со значениями в конкретных строках | Исключить тестовые заказы | | HAVING | После группировки | С результатами функций (SUM, COUNT и т.д.) | Оставить категории с выручкой > 1 млн |

Глубокий разбор: Анализ эффективности маркетинговых каналов

Разберем сложную задачу. Допустим, у нас есть таблица ad_campaigns с данными о затратах на рекламу и таблица conversions с данными о покупках. Нам нужно понять, какие рекламные каналы принесли нам более 50 конверсий в марте, при этом средняя стоимость привлечения (CPA) должна быть ниже 500 рублей.

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

Шаг 2: Группировка. Мы группируем данные по столбцу utm_source (источник трафика). Теперь база данных «видит» отдельно Facebook, Google и TikTok.

Шаг 3: Расчет метрик. Внутри каждой группы мы считаем COUNT(conversion_id) для общего количества покупок и SUM(cost) / COUNT(conversion_id) для вычисления средней стоимости одной покупки.

Шаг 4: Итоговая фильтрация агрегатов. Теперь мы применяем HAVING. Нам нужно оставить только те группы, где количество больше 50, а вычисленный CPA меньше 500.

Шаг 5: Сортировка. В аналитике важно видеть лидеров первыми. Используем ORDER BY по убыванию прибыли.

Пример в цифрах: если канал 'Google_Ads' принес 100 покупок с общими затратами 40 000 руб., то на шаге 3 мы получим CPA = 400. Условие HAVING (100 > 50 И 400 < 500) выполняется, и этот канал попадет в наш отчет. Если же 'FB_Ads' принес 200 покупок, но затраты составили 120 000 руб. (CPA = 600), он будет отсеян на этапе HAVING.

Работа с неочевидными агрегатами: DISTINCT и NULL

В бизнес-аналитике часто возникает задача посчитать количество уникальных сущностей. Например, сколько уникальных пользователей совершили покупки в конкретный день. Обычный COUNT(user_id) может выдать ложный результат, если один и тот же человек купил три раза — он будет посчитан трижды. Для чистоты данных используется COUNT(DISTINCT user_id).

Еще один подводный камень — это значения NULL (пустоты). Важно помнить:

COUNT(*) считает абсолютно все строки, включая те, где все поля пустые.

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

Агрегатные функции вроде SUM или AVG просто игнорируют NULL. Если у вас 10 строк, в 5 из которых revenue равен 100, а в других 5 — NULL, то AVG(revenue) вернет 100, а не 50. Это критично для расчета среднего чека или ARPU (Average Revenue Per User).

Практические сценарии использования CASE внутри агрегатов

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

Представьте, что вам нужно вывести отчет, где в строках — даты, а в столбцах — выручка отдельно по мобильным устройствам и отдельно по десктопам. Вместо того чтобы делать два разных запроса, мы пишем: SUM(CASE WHEN device = 'mobile' THEN revenue ELSE 0 END) AS mobile_rev. Этот прием позволяет «на лету» сегментировать данные внутри одной группы. Например, так можно посчитать долю возвратов в общем объеме продаж: мы суммируем только те строки, где статус заказа 'returned', и делим на общее количество строк.

> Если вы хотите быстро оценить качество данных, используйте связку COUNT(column) и COUNT(*). Если числа сильно различаются, значит, в данных много пропусков, и ваши средние значения (AVG) могут быть искажены.

Если из этой главы запомнить три вещи — это:

WHERE фильтрует сырые строки, а HAVING — уже посчитанные итоги (агрегаты).

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

COUNT(DISTINCT ...) — ваш лучший друг для подсчета реального количества клиентов или сессий, исключающий дубли.

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

В реальном бизнесе данные никогда не лежат в одной «красивой» таблице. Информация о пользователях хранится в CRM, данные о транзакциях — в платежном шлюзе, а логи посещений — в системе аналитики. Работа аналитика на 80% состоит из «сшивания» этих лоскутов в единое полотно. Оператор JOIN — это игла, которая позволяет соединять таблицы по общим признакам, превращая разрозненные ID в осмысленные отчеты с именами, датами и суммами.

Анатомия связей: Ключи и соответствия

Чтобы соединить две таблицы, у них должно быть что-то общее. Обычно это Первичный ключ (Primary Key) в одной таблице и Внешний ключ (Foreign Key) в другой. Представьте таблицу users, где у каждого есть уникальный id, и таблицу orders, где указан user_id. Здесь user_id — это мостик.

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

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

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

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

> В аналитике LEFT JOIN — это стандарт де-факто. Он позволяет сохранить целостность основной выборки (например, списка всех зарегистрированных пользователей) при попытке «подтянуть» к ним дополнительные данные (например, суммы их покупок).

Визуализация типов объединения

Представим две таблицы: A (Сотрудники) и B (Департаменты).

| Тип JOIN | Результат | Пример из жизни | | :--- | :--- | :--- | | INNER | Только сотрудники, привязанные к департаментам. | Список активных работников в штате. | | LEFT | Все сотрудники. У тех, кто без отдела, будет NULL. | Список всех людей для проверки, кто еще не распределен. | | FULL | Все сотрудники и все департаменты (даже пустые). | Полная инвентаризация ресурсов компании. | | CROSS | Каждая строка A с каждой строкой B. | Сетка "Все товары" x "Все магазины" для заполнения цен. |

Развернутый разбор: Поиск «брошенных корзин»

Разберем сценарий: маркетолог просит список имен пользователей, которые добавили товары в корзину, но так и не совершили покупку. У нас есть таблицы users (id, name), carts (user_id, product_id) и orders (user_id, order_id).

Шаг 1: Соединяем пользователей и корзины. Используем INNER JOIN между users и carts. Нам нужны только те, у кого в принципе была корзина. Теперь у нас есть список пар «Имя — Товар».

Шаг 2: Присоединяем таблицу заказов. Здесь мы используем LEFT JOIN по полю user_id. Почему не INNER? Потому что INNER оставил бы только тех, кто совершил покупку, а нам нужны как раз те, у кого покупки нет.

Шаг 3: Фильтрация по отсутствию. После LEFT JOIN у пользователей без заказов в столбце order_id будет стоять NULL. Мы добавляем условие WHERE orders.order_id IS NULL.

Шаг 4: Очистка результата. Используем DISTINCT, чтобы один и тот же пользователь не дублировался, если у него было несколько товаров в корзине.

В итоге: мы взяли всех «корзинщиков», приложили к ним заказы и отфильтровали тех, у кого «приложилось» пустое место. Это классический аналитический прием для поиска отсутствующих действий.

Опасности и нюансы: Дублирование данных

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

Например, вы соединяете таблицу users с таблицей logins (логи входов), чтобы посчитать общую выручку по городам. Если пользователь заходил в систему 10 раз, то после JOIN его строка с выручкой в таблице users размножится 10 раз. Если вы примените SUM(revenue), вы получите сумму в 10 раз больше реальной.

Как этого избежать?

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

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

Используйте COUNT(DISTINCT...) вместо простого COUNT, если не уверены в чистоте связей.

Соединение более двух таблиц

В сложных отчетах цепочка JOIN может состоять из 5-10 звеньев. Важно соблюдать иерархию. Обычно мы начинаем с самой «стабильной» таблицы (например, календарь или справочник пользователей) и последовательно наращиваем на неё факты (сессии, клики, транзакции, возвраты).

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

Если из этой главы запомнить три вещи — это:

LEFT JOIN — основной инструмент аналитика, так как он не теряет данные из главной таблицы.

Условие IS NULL после LEFT JOIN позволяет находить тех, кто НЕ совершил действие.

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

Расчет бизнес-метрик: использование SQL для вычисления LTV, Retention и анализа пользовательского поведения

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

Retention Rate: Как измерить лояльность

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

В SQL это часто решается через Self-JOIN (самообъединение таблицы с самой собой). Представьте таблицу activity. Мы соединяем её с самой собой по полю user_id. В первой копии таблицы мы фиксируем «день 0» (дата регистрации), а во второй — все последующие заходы.

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

Пример: если 1 мая зарегистрировалось 1000 человек (когорта), а 8 мая из них зашло 200 человек, то Retention 7-го дня составит 20%. В SQL мы группируем данные по «дате рождения» пользователя и считаем количество уникальных ID для каждого последующего дня.

LTV (Lifetime Value): Прогнозируем доход

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

Однако аналитику часто нужно смотреть на LTV в динамике: например, LTV 30-го, 60-го или 90-го дня. Это позволяет сравнивать качество трафика из разных рекламных каналов. Если пользователи из Facebook имеют LTV через месяц, а из Google — , то при равной стоимости привлечения Google выгоднее.

Для расчета в SQL мы:

Определяем дату регистрации каждого пользователя.

Считаем сумму платежей, совершенных в течение дней после регистрации.

Делим общую сумму на количество пользователей в когорте.

Развернутый разбор: Построение когортного анализа

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

Шаг 1: Определение профиля пользователя. Создаем временную таблицу (или подзапрос), где для каждого user_id находим минимальную дату покупки. Это будет «месяц рождения» когорты. Используем функцию DATE_TRUNC('month', first_purchase_date).

Шаг 2: Сбор всех активностей. Берем таблицу всех транзакций и также приводим даты к началу месяца.

Шаг 3: Объединение. Делаем LEFT JOIN профилей пользователей с их транзакциями. Теперь у нас для каждой покупки есть информация: «К какой когорте принадлежит этот человек?».

Шаг 4: Расчет дистанции. Вычисляем разницу в месяцах между датой покупки и датой регистрации. В SQL это может быть функция AGE или простое вычитание дат. Если покупка совершена в тот же месяц — это «месяц 0», если в следующем — «месяц 1».

Шаг 5: Финальная агрегация. Группируем результат по «месяцу рождения» и «дистанции». Считаем COUNT(DISTINCT user_id).

В результате мы получим таблицу, из которой легко построить «треугольник» удержания:

Январская когорта: месяц 0 — 100%, месяц 1 — 40%, месяц 2 — 25%.

Февральская когорта: месяц 0 — 100%, месяц 1 — 35%...

Анализ пользовательских путей: Окно в поведение

Кроме глобальных метрик, SQL помогает изучать микро-поведение. Например, Time to First Purchase (время до первой покупки). Мы находим разницу между временем регистрации и временем первого заказа. Если среднее время составляет 48 часов, маркетологи могут настроить пуш-уведомление со скидкой именно на второй день, чтобы «дожать» сомневающихся.

Еще один важный аспект — анализ частоты (Frequency). Используя COUNT(order_id) с группировкой по пользователям, мы можем сегментировать базу:

Одноразовые покупатели (1 заказ).

Постоянные клиенты (2-5 заказов).

Лояльные фанаты (6+ заказов).

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

Ошибки при расчете метрик

Главная ошибка аналитика — игнорирование часовых поясов. Если регистрация записана по UTC, а покупка по местному времени, вы можете получить «покупку раньше регистрации». Всегда приводите даты к единому стандарту перед расчетами.

Вторая ошибка — смешивание валют. Если ваш магазин работает в разных странах, SUM(amount) без учета курса валют даст бессмысленное число. Всегда делайте JOIN со справочником курсов на дату транзакции.

Если из этой главы запомнить три вещи — это:

Retention считается через сопоставление даты первого действия и дат всех последующих активностей.

LTV требует жесткой привязки к «возрасту» пользователя (день 30, день 60), чтобы сравнение было честным.

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

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

Данные в сыром виде редко бывают чистыми. В базе данных интернет-магазина цена может храниться как текст, даты — в разных форматах, а в именах пользователей могут встречаться лишние пробелы или спецсимволы. Прежде чем строить графики или обучать модели, аналитик должен провести «гигиеническую» обработку данных. SQL предоставляет мощный арсенал функций для трансформации, которые делают данные пригодными для анализа в Excel, Python или BI-системах.

Приведение типов: CAST и преобразования

Одна из самых частых проблем — неверный тип данных. Если число хранится как строка (VARCHAR), вы не сможете его сложить или найти среднее. Для исправления используется оператор CAST или его сокращенная форма ::.

Например, CAST(price AS FLOAT) превратит строку '199.99' в число, с которым можно работать. Это особенно важно при импорте данных из CSV-файлов, где база часто ошибается с автоматическим определением типов.

Будьте осторожны с датами. Строка '2023-15-01' вызовет ошибку при попытке конвертации в DATE. Аналитик должен уметь использовать функции вроде TO_DATE или STR_TO_DATE, указывая явный шаблон (например, 'YYYY-DD-MM'), чтобы база поняла логику записи.

Борьба с пустотой: Обработка NULL и пустых строк

Пропуски в данных (NULL) могут исказить отчет. Как мы уже знаем, агрегатные функции их игнорируют, но в расчетах они могут быть опасны. Если вы считаете Выручка - Себестоимость, и себестоимость равна NULL, результат всей операции будет NULL.

Для решения этой проблемы используются функции:

COALESCE(column, 0): Возвращает первое не-пустое значение. Если в столбце NULL, функция подставит 0 (или любое другое значение по умолчанию). Это спасает математические формулы.

NULLIF(column, 0): Обратная операция. Она превращает значение (например, ноль) в NULL. Это критически важно для предотвращения ошибки «деление на ноль». Вместо revenue / clicks пишите revenue / NULLIF(clicks, 0). Если кликов ноль, результат будет NULL, а не системная ошибка, которая «уронит» весь запрос.

> В бизнес-отчетах часто лучше выводить «Не указано» вместо пустого места. COALESCE(city, 'Unknown') сделает ваш отчет в Excel гораздо более читаемым для коллег.

Текстовая магия: Очистка строк

Данные, введенные пользователями вручную — это хаос. Лишние пробелы в начале или конце строки (« Москва ») приведут к тому, что GROUP BY создаст две разные группы для одного города.

Основные инструменты очистки текста:

TRIM(): Удаляет пробелы по краям.

LOWER() / UPPER(): Приводит всё к одному регистру. Всегда делайте LOWER(email) перед поиском дубликатов или объединением таблиц по текстовым полям.

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

SUBSTR() / LEFT() / RIGHT(): Позволяют вырезать куски текста. Например, извлечь домен из email-адреса, чтобы проанализировать, почтой каких сервисов пользуются ваши клиенты.

Развернутый разбор: Подготовка данных для экспорта в Python/Excel

Представьте, что вам нужно выгрузить данные о продажах для построения прогноза в Python. Дата-сайентист просит данные в формате: «Дата, ID товара, Очищенная цена, Категория».

Шаг 1: Фильтрация аномалий. Мы исключаем заказы с отрицательной ценой или тестовые аккаунты (например, email которых содержит 'test@').

Шаг 2: Стандартизация цен. Используем COALESCE(price, 0) и приводим к типу NUMERIC(10,2). Если цена была в строке с лишними символами, предварительно применяем REPLACE.

Шаг 3: Обработка дат. Python любит формат ISO (YYYY-MM-DD). Мы используем TO_CHAR(order_date, 'YYYY-MM-DD'), чтобы гарантировать, что при экспорте формат не «поплывет».

Шаг 4: Сегментация на лету. Если в Excel коллеге будет удобнее видеть категории не кодами (1, 2, 3), а названиями, мы добавляем CASE WHEN category_id = 1 THEN 'Electronics' ... END.

Шаг 5: Экспорт. Большинство SQL-клиентов позволяют сохранить результат в CSV. Важно проверить кодировку (UTF-8) и разделитель (запятая или точка с запятой), чтобы Excel не превратил ваши данные в нечитаемую кашу.

Работа с дубликатами

Перед экспортом важно убедиться, что данные не дублируются. Если в системе произошел сбой и одна транзакция записалась дважды, ваш LTV будет завышен. Используйте конструкцию ROW_NUMBER() OVER (PARTITION BY transaction_id ORDER BY created_at) для нумерации одинаковых записей. Затем в WHERE оставьте только те, где номер равен 1. Это самый надежный способ «дедупликации» данных в SQL.

Если из этой главы запомнить три вещи — это:

Никогда не доверяйте типам данных «по умолчанию» — используйте CAST.

Защищайте расчеты от NULL через COALESCE и от деления на ноль через NULLIF.

Всегда делайте TRIM и LOWER для текстовых полей перед группировкой или поиском.

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

За каждым быстрым и понятным SQL-запросом стоит качественная архитектура базы данных. Если данные спроектированы плохо, даже простой расчет выручки превращается в кошмар с десятками JOIN и сложной очисткой. Бизнес-аналитику важно понимать принципы проектирования, чтобы уметь не только читать данные, но и ставить задачи разработчикам на создание новых таблиц или витрин данных (Data Marts).

Нормализация: Избавляемся от избыточности

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

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

Объем: База растет в разы быстрее, чем нужно.

Аномалии обновления: Если клиент сменит адрес, нам придется менять его в 10 строках. Забудем одну — данные станут противоречивыми.

Аномалии удаления: Если мы удалим информацию о единственной покупке клиента, мы навсегда потеряем информацию о самом клиенте.

Чтобы этого избежать, данные разбивают на связанные таблицы: Users, Products и Orders. Это и есть суть нормализации.

Три нормальные формы (упрощенно для аналитика)

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

Первая нормальная форма (1NF): В каждой ячейке — только одно значение. Нельзя хранить список товаров через запятую в одной строке orders. Каждому товару — своя строка.

Вторая нормальная форма (2NF): Таблица должна иметь первичный ключ, и все остальные поля должны зависеть от него целиком. Информация о категории товара должна лежать в таблице товаров, а не в таблице заказов.

Третья нормальная форма (3NF): Поля не должны зависеть друг от друга, только от ключа. Если у нас есть city_id и city_name, то city_name нужно вынести в отдельный справочник, так как название города зависит от его ID, а не от ID пользователя.

| Состояние | Плюсы | Минусы | | :--- | :--- | :--- | | Нормализовано (3NF) | Данные чистые, нет дублей, легко обновлять. | Много JOIN-ов, запросы писать сложнее. | | Денормализовано | Запросы летают, всё в одной таблице. | Риск ошибок, избыточность, сложность поддержки. |

Денормализация и аналитические витрины

Хотя разработчики стремятся к нормализации, аналитикам часто удобнее работать с денормализованными данными. Когда вам нужно быстро построить отчет в Tableau или Excel, делать 10 JOIN каждый раз — неудобно и медленно.

Для этого создаются Витрины данных (Data Marts). Это таблицы, которые специально «собраны» из нормализованных кусочков. Например, витрина dm_sales_performance может уже содержать в себе и имя клиента, и категорию товара, и примененную скидку. Она обновляется раз в сутки и служит единым источником правды для всех аналитиков компании.

Развернутый разбор: Проектирование системы лояльности

Допустим, нам нужно спроектировать хранение данных для системы кэшбэка.

Шаг 1: Сущности. Выделяем главные объекты: Пользователь, Транзакция, Правило начисления кэшбэка.

Шаг 2: Таблица users. Храним user_id, имя, дату регистрации и текущий баланс баллов. Баланс — это спорный момент. Его можно вычислять каждый раз из транзакций (надежно, но медленно) или хранить как «снимок» (быстро, но нужно следить за актуальностью).

Шаг 3: Таблица transactions. Здесь храним transaction_id, user_id, amount, timestamp. Это наши «сырые» факты.

Шаг 4: Таблица cashback_logs. Связываем её с transactions через transaction_id. Храним сколько баллов начислено или списано. Почему не в основной таблице транзакций? Потому что одна транзакция может вызвать несколько начислений (например, базовый кэшбэк + бонус по акции).

Шаг 5: Справочник promotions. Храним условия акций: promo_id, название, процент кэшбэка, даты действия.

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

Индексы: Секрет скорости

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

Аналитику важно знать:

Индексы ускоряют SELECT и JOIN.

Индексы замедляют INSERT и UPDATE (так как указатель нужно перестраивать при каждом изменении).

Всегда должен быть индекс по полям, которые вы чаще всего используете в WHERE и JOIN (обычно это ID и даты).

Если из этой главы запомнить три вещи — это:

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

Аналитические витрины — это осознанная денормализация для удобства и скорости отчетов.

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