$ sudo teach IT

customers.csv

customer_id,customer_name,customer_segment,city
1,Иван Петров,Премиум,Москва
2,Анна Смирнова,Стандарт,Санкт-Петербург
3,Олег Иванов,Премиум,Казань
4,Мария Кузнецова,VIP,Екатеринбург
5,Дмитрий Соколов,Стандарт,Новосибирск
6,Елена Морозова,Премиум,Самара
7,Сергей Волков,VIP,Краснодар
8,Наталья Орлова,Стандарт,Томск

products.csv

product_id,product_name,category,cost_amount
101,iPhone 16,Смартфоны,650
102,Samsung Galaxy S25,Смартфоны,590
103,MacBook Air M4,Ноутбуки,980
104,Lenovo ThinkPad,Ноутбуки,820
105,LG OLED C4,Телевизоры,930
106,Sony WH-1000XM6,Наушники,210
107,Xiaomi Pad 7,Планшеты,290
108,Apple Watch Series 11,Носимые устройства,310

orders.csv

order_id,order_date,customer_id,product_id,quantity,sales_amount
1001,2025-01-05,1,101,1,899
1002,2025-01-07,2,103,1,1399
1003,2025-01-12,3,106,2,698
1004,2025-01-18,4,105,1,1499
1005,2025-02-03,5,104,1,1199
1006,2025-02-08,2,107,1,449
1007,2025-02-16,6,108,2,898
1008,2025-02-20,1,106,1,349
1009,2025-03-02,3,101,2,1798
1010,2025-03-10,7,105,1,1499
1011,2025-03-18,5,102,1,799
1012,2025-03-25,8,107,2,898
1013,2025-04-04,1,103,1,1399
1014,2025-04-11,2,108,1,449
1015,2025-04-19,3,104,1,1199
1016,2025-04-28,9,101,1,899
1017,2025-05-06,4,105,2,2998
1018,2025-05-12,6,106,1,349
1019,2025-05-21,7,103,1,1399
1020,2025-05-30,10,102,1,799
1021,2025-06-08,5,107,1,449
1022,2025-06-15,8,108,2,898
1023,2025-06-24,2,101,1,899
1024,2025-06-29,3,105,1,1499

На предыдущих уроках мы научились работать с Dataset, строить визуализации, создавать Dashboard и использовать SQL Lab для подготовки данных. Однако в реальной работе аналитика данные редко находятся в одной удобной таблице.

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

В результате возникает важная задача: Как собрать нужные данные в единый аналитический набор? Именно здесь начинается настоящая работа аналитика. Большая часть времени в BI-проектах уходит не на построение графиков, а на подготовку данных. Даже самый красивый Dashboard бесполезен, если в его основе лежат неправильно подготовленные данные.

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

Ключевые термины

  • JOIN — операция объединения данных из нескольких таблиц.
  • INNER JOIN — объединение только совпадающих записей.
  • LEFT JOIN — объединение всех записей из основной таблицы и совпадающих записей из второй таблицы.
  • Aggregation — объединение данных в группы с помощью функций SUM, COUNT, AVG и других.
  • GROUP BY — оператор группировки данных.
  • Dimension — аналитический разрез данных, например категория товара или город.
  • Metric — рассчитываемый показатель, например выручка или прибыль.
  • Fact Table — таблица событий или операций, например заказов.
  • Lookup Table — справочная таблица, содержащая дополнительную информацию.
  • Analytical Dataset — набор данных, подготовленный для аналитики и визуализации.

Почему данные редко бывают готовы к анализу

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

На практике ситуация выглядит иначе. Представим интернет-магазин электроники. В базе данных могут существовать следующие таблицы:

orders
customers
products
employees
cities

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

При этом руководитель может попросить отчет Покажите выручку по категориям товаров в разрезе клиентских сегментов. Такого набора данных в базе обычно не существует. Его необходимо подготовить самостоятельно. Именно для этого используются JOIN и агрегации.

Самый популярный JOIN в аналитике

В BI-проектах чаще всего используются два типа объединения: INNER JOIN, LEFT JOIN. Для большинства аналитических задач этого достаточно.

Начнем с INNER JOIN.

Представим запрос:

SELECT
    o.order_id,
    o.sales_amount,
    c.customer_segment
FROM orders o
INNER JOIN customers c
    ON o.customer_id = c.customer_id;

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

Почему аналитики часто используют LEFT JOIN

На практике гораздо чаще встречается LEFT JOIN. Причина связана с тем, что аналитики обычно не хотят терять данные.

Представим ситуацию. В таблице заказов есть запись о продаже. Но по какой-то причине информация о клиенте отсутствует. Если использовать INNER JOIN, заказ исчезнет из результата. Для бизнеса это может оказаться серьезной проблемой.

LEFT JOIN работает иначе. Он сохраняет все строки из основной таблицы. Если совпадение найдено — данные подставляются. Если совпадения нет — поля второй таблицы будут содержать NULL.

Например:

SELECT
    o.order_id,
    o.sales_amount,
    c.customer_segment
FROM orders o
LEFT JOIN customers c
    ON o.customer_id = c.customer_id;

Именно поэтому большинство аналитических запросов строится на LEFT JOIN. Он безопаснее и позволяет сохранить максимум информации.

Что такое агрегация

После объединения данных обычно возникает следующая задача. Нужно не просто получить список заказов, а рассчитать бизнес-показатели.

Например:

  • выручку;
  • прибыль;
  • количество заказов;
  • средний чек.

Для этого используются агрегирующие функции.

Например:

SUM()
COUNT()
AVG()
MIN()
MAX()

Они позволяют превращать миллионы отдельных записей в понятные показатели бизнеса.

GROUP BY в аналитике

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

Например:

SELECT
    category,
    SUM(sales_amount) AS revenue
FROM orders
GROUP BY category;

Вместо тысяч заказов мы получим несколько строк:

Ноутбуки
Смартфоны
Телевизоры
Наушники

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

Подготовка данных под визуализацию

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

Первый Dataset содержит:

order_id
customer_id
product_id
...
ещё 50 технических колонок

Второй Dataset содержит:

month
category
revenue
profit
orders_count

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

Расчет бизнес-показателей

Очень часто аналитический Dataset содержит дополнительные вычисления. Например, прибыль может рассчитываться как sales_amount - cost_amount.

  • Маржинальность: (sales_amount - cost_amount) / sales_amount
  • Средний чек: SUM(sales_amount) / COUNT(order_id)

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

Подготовка данных для временной аналитики

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

Например:

SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(sales_amount) AS revenue
FROM orders
GROUP BY month;

Полученный Dataset идеально подходит для построения Time Series Chart. Если же использовать сырые данные по каждому заказу, визуализация будет работать медленнее и окажется менее удобной. Поэтому агрегация часто выполняется заранее.

Когда создавать отдельный Dataset

Начинающие аналитики часто пытаются использовать один универсальный Dataset для всех задач. На практике это редко бывает удобно.

Представим два дашборда:

  • Первый анализирует продажи по товарам.
  • Второй анализирует поведение клиентов.

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

Как выглядит хороший аналитический Dataset

Хороший Dataset обладает несколькими признаками.

Он содержит:

  • понятные названия колонок;
  • готовые бизнес-показатели;
  • минимально необходимое количество полей;
  • корректные типы данных;
  • удобную структуру для визуализации.

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

Наиболее частые ошибки

  • Использовать INNER JOIN вместо LEFT JOIN без понимания последствий.
    Это может привести к потере части данных.

  • Объединять таблицы по неправильному ключу.
    В результате показатели будут рассчитаны неверно.

  • Не использовать GROUP BY при агрегации.
    Запрос либо завершится ошибкой, либо даст неожиданный результат.

  • Создавать слишком большие Dataset.
    Лишние поля усложняют анализ и снижают производительность.

  • Не проверять результат JOIN.
    После объединения таблиц полезно убедиться, что количество строк соответствует ожиданиям.

  • Использовать непонятные названия колонок.
    Dataset должен быть понятен не только автору запроса.

  • Дублировать расчеты в нескольких местах.
    Лучше централизовать бизнес-логику внутри Dataset.

  • Пытаться сделать один универсальный Dataset для всех задач.
    Обычно это приводит к усложнению проекта.

Наиболее частые вопросы

  • Какой JOIN используется чаще всего в аналитике?
    Обычно LEFT JOIN, поскольку он позволяет сохранить все записи основной таблицы.

  • Когда использовать INNER JOIN?
    Когда нужны только строки, имеющие соответствия в обеих таблицах.

  • Можно ли объединять больше двух таблиц?
    Да. В аналитических запросах часто используются три, четыре и более таблиц.

  • Зачем нужна агрегация?
    Она позволяет превращать детальные данные в бизнес-показатели.

  • Что лучше: считать показатели в SQL или через Metrics в Superset?
    Зависит от задачи. Сложные расчеты часто удобнее выполнять в SQL.

  • Нужно ли создавать отдельный Dataset под каждый Dashboard?
    Не всегда. Но под разные аналитические задачи часто создают разные Dataset.

  • Как понять, что Dataset подготовлен хорошо?
    Если на его основе легко строить графики и не требуется дополнительная обработка данных.

Итоги

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

Мы разобрались, как использовать JOIN для объединения таблиц, научились применять агрегации и группировки, а также рассмотрели подходы к созданию аналитических Dataset для Dashboard и отчетов.

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

В следующем уроке мы познакомимся с продвинутыми визуализациями Apache Superset и научимся использовать Heatmap, Treemap, Funnel Chart, Scatter Plot и Box Plot для решения более сложных аналитических задач.

На следующий урок