Промежуточные витрины в SQL

Привет, Хабр!

Сегодня я хочу поговорить о том, без чего не обходится практически ни один серьёзный проект с большими данными (да и с не слишком большими тоже) — о промежуточных витринах (или более привычно — staging, core, data mart).

Краткий ликбез: stage / core / data mart

Чтобы у нас у всех была единая терминология, пройдёмся:

Stage (или Staging area)

Это прихожая нашего хранилища — туда складываем сырые (или полусырые) данные, которые только‑только пришли из внешних систем. Там делаем минимально необходимую очистку, валидацию, унификацию типов. Но стараемся не заливать туда громоздкие вычислительные истины. Stage — больше про «собрать всё в одном месте, чтобы дальше можно было удобно работать».
Core (или Operational / Enterprise Data Warehouse)

Здесь уже формируем единую модель данных. Это может быть классическая звезда с фактами и измерениями, либо озеро, где под капотом распределённая файловая система, + логика поверх Spark или иных инструментов. Суть в том, что на уровне Core мы из Stage‑данных делаем «правильные», обработанные в рамках корпоративных стандартов структуры, готовые к активному использованию. В Core обычно заботимся о качестве, дедупликациях, связях между объектами.
Data mart (Витрина)

Это уже, как правило, какой‑то user‑friendly уровень. Из Core выгружаем всю необходимую информацию и собираем таблицы или представления, заточенные под конкретную аналитику, отчёты, модель машинного обучения, дашборды. Иногда Data mart — это действительно физические таблицы, иногда это материализованные представления, а где‑то — и вовсе BI‑инструменты, которые подмешивают данные.

В больших проектах структура может быть ещё сложнее, но общую концепцию можно описать по аналогии с цепочкой: RAW (Stage) → CORE → Marts.

Почему важны промежуточные витрины

Зачем нам эти самые промежуточные витрины (да и витрины вообще)? Не проще ли всё грохнуть в один Data Lake, а дальше просто подключить SQL и Python

Разделение ответственности и упрощение процессов

Соблюдая слои Stage, Core и Data Mart, мы явно фиксируем, на каком этапе какие бизнес‑правила должны срабатывать. На уровне Stage — только чистка и унификация. На уровне Core — построение понятных связей, «единой версии правды». На уровне Data Mart — адаптируем данные под конкретные задачи.
Удобство и управляемость

Когда витрина уже не нужна — можно её снести или переделать, при этом базовая информация хранится на Core‑слое в целости и сохранности. Если же всё смешано в одной большой неструктурированной куче, то любые изменения превращаются в мини‑катастрофу: «А мы тут переименовали поле?», «А у нас логика поменялась… ох, полбазы переписывать».
Повышение производительности

Часто мы не хотим гонять сложные join или агрегации в реальном времени. Вместо этого мы собираем какую‑то агрегированную витрину (скажем, user_sessions) один раз в сутки (или час), и потом все запросы к этой витрине летят быстро, поскольку данные уже агрегированы.

Зачем делать витрину «user_sessions» и когда это оправдано

Допустим, есть события от пользователей (клики, просмотры, действия на сайте или в приложении). Часто возникает вопрос: «А может, нам проще агрегировать всё в разрезе сессий?»

Когда стоит делать:

Аналитика поведения. Если нужно считать, насколько длинные у нас сессии, сколько пользователь делает действий за одну сессию, как часто пользователи покидают сайт после 1–2 кликов.
Продуктовые метрики. Например, считать время, проведённое в продукте, или путь пользователя в рамках одной сессии (какие шаги прошёл до конверсии).
Скорость вычислений. Если в дальнейшем часто нужно делать вычисления на уровне сессий, разумно сформировать соответствующую витрину: user_sessions хранит готовые, очищенные данные о каждой сессии.

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

Менее детализированные метрики. Для отчётов топ‑уровня не нужно знать, что пользователь кликнул в 10:05, а потом в 10:07. Достаточно, что за день у него было 12 кликов в разделе «Новости».
Глобальные тренды. Витрина user_daily_metrics может помочь видеть изменения в долгосрочной динамике без излишней детализации сессий.
Оптимизация хранения. Если не нужны данные о конкретных сессиях, то удобнее собирать и хранить конечные агрегаты.

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

Пример: логика витрины для RFM-анализа

Теперь перейдём к более конкретной штуке — RFM‑анализ. Это классический способ сегментации пользователей по трём параметрам:

Recency (давность последней покупки),
Frequency (частота),
Monetary (сколько денег оставил).

Предположим, есть интернет‑магазин. Мы хотим раз в сутки пересчитывать RFM‑классификацию по всем пользователям.

Для этого:

Из Stage берем сырые данные: таблица с заказами (дата заказа, пользователь, сумма, товары, статусы оплаты и т. п.).
В Core у нас уже есть чистая факт‑таблица core_orders плюс измерение dim_users (если вы используете классическую модель «звезда»).
В Data Mart мы хотим создать dm_rfm_user_metrics.

Пример структуры витрины dm_rfm_user_metrics:

CREATE TABLE IF NOT EXISTS dm_rfm_user_metrics (     user_id            BIGINT,     last_order_date    DATE,     recency_days       INT,     order_count        BIGINT,     total_amount       DECIMAL(15, 2),     r_score            TINYINT,     f_score            TINYINT,     m_score            TINYINT,     rfm_segment        VARCHAR(50),     calculation_date   DATE DEFAULT CURRENT_DATE,     PRIMARY KEY (user_id, calculation_date) );

Допустим, мы ежедневно запускаем скрипт, который:

Берёт всех пользователей, у которых за последние N дней были заказы (или за всё время, если не слишком много данных).
Считает дату последнего заказа (max(order_date)), разницу между сегодняшним днём и этой датой (получаем recency_days).
Считает общее количество заказов на пользователя (получаем order_count).
Считает суммарный доход (total_amount).
По каждому признаку (Recency, Frequency, Monetary) выставляет score (например, от 1 до 5). Здесь можно использовать квантильный подход или заранее заданные интервалы:
```
CASE     WHEN recency_days <= 7 THEN 5     WHEN recency_days <= 14 THEN 4     WHEN recency_days <= 30 THEN 3     WHEN recency_days <= 90 THEN 2     ELSE 1 END AS r_score
```
И аналогично для F и M.
Формируем rfm_segment — например, склеиваем три цифры вида ‘R5F5M5’ или используем другие правила сегментирования.

Итог: у нас появляется витрина dm_rfm_user_metrics, которая каждый день обновляется и хранит ключевые RFM‑метрики. Сюда уже может подключаться команда маркетинга, BI‑аналитики, дата‑сайентисты, чтобы строить гипотезы, прогнозировать повторные заказы и т. п.

Ниже небольшой псевдо‑SQL скрипт. Представим, что у нас уже есть очищенная таблица core_orders:

INSERT INTO dm_rfm_user_metrics SELECT      c.user_id,     MAX(c.order_date) AS last_order_date,     DATEDIFF(day, MAX(c.order_date), CURRENT_DATE) AS recency_days,     COUNT(*) AS order_count,     SUM(c.order_amount) AS total_amount,     -- R score     CASE         WHEN DATEDIFF(day, MAX(c.order_date), CURRENT_DATE) <= 7  THEN 5         WHEN DATEDIFF(day, MAX(c.order_date), CURRENT_DATE) <= 14 THEN 4         WHEN DATEDIFF(day, MAX(c.order_date), CURRENT_DATE) <= 30 THEN 3         WHEN DATEDIFF(day, MAX(c.order_date), CURRENT_DATE) <= 90 THEN 2         ELSE 1     END AS r_score,     -- F score     CASE         WHEN COUNT(*) >= 10 THEN 5         WHEN COUNT(*) >= 5  THEN 4         WHEN COUNT(*) >= 3  THEN 3         WHEN COUNT(*) >= 2  THEN 2         ELSE 1     END AS f_score,     -- M score (условно)     CASE         WHEN SUM(c.order_amount) >= 50000 THEN 5         WHEN SUM(c.order_amount) >= 20000 THEN 4         WHEN SUM(c.order_amount) >= 5000  THEN 3         WHEN SUM(c.order_amount) >= 1000  THEN 2         ELSE 1     END AS m_score,     CAST(NULL AS VARCHAR(50)) AS rfm_segment, -- Заполним позже апдейтом     CURRENT_DATE AS calculation_date FROM core_orders c WHERE c.order_status = 'PAID' GROUP BY c.user_id;

Далее можно обновить поле rfm_segment, склеив значения из R/F/M или используя дополнительную таблицу сегментации:

UPDATE dm_rfm_user_metrics SET rfm_segment = CONCAT('R', r_score, 'F', f_score, 'M', m_score) WHERE calculation_date = CURRENT_DATE; -- Обновляем за сегодня

Пример пайплайна на Pytho

Небольшой пример, как можно в PySpark собирать данные для витрины:

from pyspark.sql import SparkSession from pyspark.sql.functions import (     col, max as spark_max, sum as spark_sum,     count as spark_count, datediff, current_date, when, lit, concat )  def calculate_rfm_metrics(spark: SparkSession):     # Читаем таблицу core_orders (может быть в Parquet, Hive и т.д.)     df_orders = spark.table("core_orders") \         .filter(col("order_status") == "PAID")      # Считаем агрегации     df_agg = df_orders.groupBy("user_id") \         .agg(             spark_max("order_date").alias("last_order_date"),             spark_count("*").alias("order_count"),             spark_sum("order_amount").alias("total_amount")         )      # Считаем recency     df_result = df_agg.withColumn(         "recency_days",         datediff(current_date(), col("last_order_date"))     )      # Добавляем R, F, M scores     df_result = df_result \         .withColumn("r_score", when(col("recency_days") <= 7, lit(5))                                  .when(col("recency_days") <= 14, lit(4))                                  .when(col("recency_days") <= 30, lit(3))                                  .when(col("recency_days") <= 90, lit(2))                                  .otherwise(lit(1))) \         .withColumn("f_score", when(col("order_count") >= 10, lit(5))                                  .when(col("order_count") >= 5, lit(4))                                  .when(col("order_count") >= 3, lit(3))                                  .when(col("order_count") >= 2, lit(2))                                  .otherwise(lit(1))) \         .withColumn("m_score", when(col("total_amount") >= 50000, lit(5))                                  .when(col("total_amount") >= 20000, lit(4))                                  .when(col("total_amount") >= 5000, lit(3))                                  .when(col("total_amount") >= 1000, lit(2))                                  .otherwise(lit(1))) \         .withColumn("calculation_date", current_date()) \         .withColumn("rfm_segment", concat(             lit("R"), col("r_score"),             lit("F"), col("f_score"),             lit("M"), col("m_score")         ))      # Записываем результат в таблицу dm_rfm_user_metrics     # в режиме append (инкрементное добавление)     df_result.write \         .mode("append") \         .format("parquet") \         .saveAsTable("dm_rfm_user_metrics")  if __name__ == "__main__":     spark = SparkSession.builder.appName("RFMCalculation").getOrCreate()     calculate_rfm_metrics(spark)     spark.stop()

Код можно завернуть в Airflow DAG или любой другой оркестратор (Prefect, Luigi), настроить расписание (например, каждый день в 5 утра).

Ваша задача — подходить к разработке витрин осознанно, не плодить дублей, документировать всё, что создаёте, и поддерживать в актуальном состоянии. Если витрина нужна на две недели (для эксперимента) — заранее продумайте её снос. Если нужна надолго — сделайте её robust (с мониторингом, SLA, логикой обновлений).

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

1 апреля. Tableau: работа с визуализациями и построение дашборда.
Подробнее
16 апреля. Построение эффективных дашбордов с помощью Power BI.
Подробнее
21 апреля. Витрины данных и их интеграция в отчетность.
Подробнее

ссылка на оригинал статьи https://habr.com/ru/articles/894850/