Промежуточные витрины в 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 апреля. Витрины данных и их интеграция в отчетность.
Подробнее
