Витрины данных на SQL: от сырых таблиц к готовым отчётам

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

Сырые таблицы vs витрина

В OLTP-базе данные нормализованы — разбиты на десятки таблиц. Это удобно для записи, но медленно для аналитики.

Сырой запрос для отчёта по продажам:

SELECT
    u.name,
    p.title AS product,
    c.name  AS category,
    o.created_at,
    oi.quantity,
    oi.price
FROM order_items oi
JOIN orders      o  ON oi.order_id   = o.id
JOIN users       u  ON o.user_id     = u.id
JOIN products    p  ON oi.product_id = p.id
JOIN categories  c  ON p.category_id = c.id
WHERE o.status = 'completed';

Каждый отчёт — это новый многоуровневый JOIN. Медленно, сложно, хрупко.

Витрина — денормализованная таблица, где всё уже собрано:

SELECT * FROM mart_sales WHERE order_date >= '2026-01-01';

Звёздная схема (Star Schema)

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

           dim_users
               |
dim_dates — fact_sales — dim_products
               |
          dim_channels

-- Таблица фактов
CREATE TABLE fact_sales (
    sale_id      bigint PRIMARY KEY,
    date_id      int    REFERENCES dim_dates(date_id),
    user_id      int    REFERENCES dim_users(user_id),
    product_id   int    REFERENCES dim_products(product_id),
    channel_id   int    REFERENCES dim_channels(channel_id),
    quantity     int,
    revenue      numeric(12,2),
    cost         numeric(12,2)
);

-- Таблица измерения: дата
CREATE TABLE dim_dates (
    date_id    int PRIMARY KEY,  -- формат: 20260101
    full_date  date,
    year       int,
    quarter    int,
    month      int,
    week       int,
    day_name   text
);

Преимущество: аналитики пишут простые запросы с одним JOIN, а не цепочки из 6+ таблиц.

SCD Type 1 и Type 2: как хранить историю изменений

SCD (Slowly Changing Dimensions) — подход к хранению изменяющихся данных в измерениях.

Type 1: перезаписать

Просто обновляем запись. История не сохраняется — подходит для исправления ошибок.

UPDATE dim_users
SET email = 'new@example.com', updated_at = NOW()
WHERE user_id = 42;

Type 2: версионирование

Каждое изменение — новая строка. Старая строка помечается как закрытая. Полная история сохранена.

CREATE TABLE dim_users (
    surrogate_key  bigserial PRIMARY KEY,  -- синтетический ключ
    user_id        int,                    -- бизнес-ключ
    name           text,
    tariff_plan    text,
    valid_from     date NOT NULL,
    valid_to       date,                   -- NULL = текущая запись
    is_current     boolean DEFAULT true
);

-- При изменении тарифа пользователя:
BEGIN;

-- 1. Закрываем старую запись
UPDATE dim_users
SET valid_to = CURRENT_DATE - 1,
    is_current = false
WHERE user_id = 42 AND is_current = true;

-- 2. Вставляем новую
INSERT INTO dim_users (user_id, name, tariff_plan, valid_from, valid_to, is_current)
VALUES (42, 'Иван Иванов', 'premium', CURRENT_DATE, NULL, true);

COMMIT;

Теперь можно восстановить состояние на любую дату:

-- Какой тариф был у пользователя 42 на 1 февраля 2026?
SELECT tariff_plan
FROM dim_users
WHERE user_id = 42
  AND valid_from <= '2026-02-01'
  AND (valid_to IS NULL OR valid_to >= '2026-02-01');

Заполнение витрины через INSERT INTO ... SELECT

-- Создаём витрину продаж
CREATE TABLE mart_sales AS
SELECT
    o.id                      AS order_id,
    DATE(o.created_at)        AS order_date,
    u.id                      AS user_id,
    u.name                    AS user_name,
    u.tariff_plan,
    p.id                      AS product_id,
    p.title                   AS product_name,
    c.name                    AS category,
    oi.quantity,
    oi.price                  AS unit_price,
    oi.quantity * oi.price    AS revenue
FROM order_items oi
JOIN orders    o ON oi.order_id   = o.id
JOIN users     u ON o.user_id     = u.id
JOIN products  p ON oi.product_id = p.id
JOIN categories c ON p.category_id = c.id
WHERE o.status = 'completed'
WITH NO DATA;  -- создать структуру без данных

-- Первоначальная загрузка
INSERT INTO mart_sales
SELECT ... -- тот же запрос без WITH NO DATA;

-- Инкрементальное обновление (только новые данные)
INSERT INTO mart_sales
SELECT ...
WHERE o.created_at > (SELECT MAX(order_date) FROM mart_sales);

MATERIALIZED VIEW: витрина без ETL

Для читаемых витрин средней сложности MATERIALIZED VIEW — самое простое решение:

CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT
    DATE(created_at)        AS day,
    SUM(total_amount)       AS revenue,
    COUNT(DISTINCT user_id) AS unique_buyers,
    COUNT(*)                AS orders
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at)
ORDER BY day;

-- Индекс на витрине для быстрых запросов
CREATE INDEX ON mv_daily_revenue (day);

-- Обновление (блокирует чтение)
REFRESH MATERIALIZED VIEW mv_daily_revenue;

-- Обновление без блокировки (требует UNIQUE индекс)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_revenue;

Когда что использовать

Хотите глубже разобраться в проектировании и сложных запросах? Практикуйтесь в тренажёре SQLlab.