Ниже — рекомендация и конкретные действия для аналитики месячной выручки по странам на больших объёмах данных.
1) Схема (нормализация/денормализация)
- Рекомендую звёздную схему (star) с денормализованной факт‑таблицей для быстрых агрегатов:
- fact_order_line(order_line_id, order_id, user_id, product_id, created_at, country, quantity, price, revenue)
- хранить заранее вычисленное поле revenue = price * quantity
- dim_user(user_id, country, …), dim_date(date_id, date, year, month, …), dim_product(...)
- Обоснование: фактория (факт‑таблица) содержит всё необходимое для агрегации без JOIN'ов на Users/Orders, что резко снижает I/O и CPU при агрегации по миллиардам строк.
- Дополнительно: поддерживать материализованную таблицу/материализованный вид monthly_country_revenue(month, country, revenue_total) для тягучих отчётов.
Формула выручки:
- $\text{revenue}=\sum (\text{price}\times \text{quantity})$.
2) Партиционирование и кластеризация
- Партиционировать факт‑таблицу по диапазону по created_at (monthly partitions).
- Кластеризовать/сортировать в пределах партиции по country (или по (country, created_at)) для ускорения агрегирования по странам.
- В PostgreSQL: BRIN-index по created_at + B-tree по country; в columnar/OLAP системах — задавать order/primary key как (country, created_at).
3) Индексы (пример для реляционной СУБД типа PostgreSQL)
- На денормализованной факт‑таблице:
- CREATE INDEX idx_fact_created_at_country ON fact_order_line(created_at, country);
- Или (при партицировании) локальные индексы: CREATE INDEX ON fact_order_line_partition_2025_09 (country);
- BRIN для очень больших таблиц: CREATE INDEX brin_fact_created_at ON fact_order_line USING BRIN(created_at);
- Если оставлять нормализованные таблицы:
- orders: CREATE INDEX idx_orders_created_at_user ON orders(created_at, user_id);
- order_items: CREATE INDEX idx_order_items_order_id ON order_items(order_id);
- users: CREATE INDEX idx_users_user_id_country ON users(user_id, country);
- Покрывающие (covering) индексы возможны, но при больших агрегациях чаще выигрывает партиционирование + sequential scan по сжатым столбцам (columnar).
4) Оптимизированный запрос (денормализованная факт‑таблица)
- SQL (Postgres‑style):
SELECT
country,
date_trunc('month', created_at) AS month,
SUM(revenue) AS total_revenue
FROM fact_order_line
WHERE created_at >= DATE '2025-09-01' AND created_at < DATE '2025-10-01'
GROUP BY country, month
ORDER BY country;
- Формула агрегирования для понятности: ${\text{total\_revenue}}_{month,country}={\sum }_{\text{rows}}(\text{price}\times \text{quantity})$.
Если таблица партицирована по месяцу, WHERE должен фильтровать партицию, чтобы избежать сканирования других партиций.
5) Аналогичный запрос на нормализованных таблицах (медленнее, требует join):
SELECT
u.country,
date_trunc('month', o.created_at) AS month,
SUM(oi.price * oi.quantity) AS total_revenue
FROM orders o
JOIN users u ON u.user_id = o.user_id
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.created_at >= DATE '2025-09-01' AND o.created_at < DATE '2025-10-01'
GROUP BY u.country, month;
6) Материализованные агрегаты и инкрементальные обновления
- Для активных месячных отчётов держите materialized view или агрегаты, обновляемые инкрементально (ETL/streaming): ежедневно/почасово обновлять monthly_country_revenue.
- Формат: monthly_country_revenue(month, country, revenue_total, last_updated).
7) Альтернативы с OLAP / колонночными хранилищами
- Columnar DBs / Data warehouses (ClickHouse, ClickHouse/MonetDB, Amazon Redshift, BigQuery, Snowflake, ClickHouse, ClickHouse/ClickHouse):
- Плюсы: быстрая агрегация по большим объёмам, эффективная компрессия, vectorized execution, хранение в колонках → низкий I/O.
- Поддерживают ordering/primary key или sorting key (ClickHouse: ORDER BY (country, created_at)), что ускоряет группировки.
- OLAP/real-time analytics (Druid, Pinot, ClickHouse):
- Подходят для интерактивных KPI, поддерживают быстрые агрегации и rollup'ы, сегментирование и TTL.
- Data warehouse (Snowflake/BigQuery/Redshift):
- Простота ETL/ELT, автоматическое масштабирование, поддержка materialized views и быстрых агрегатов; плата за хранение + запросы.
- Tradeoffs:
- СУБД OLTP (реляционная) + прямой SQL: проще, но медленнее на больших объёмах.
- Columnar/OLAP: требует ETL, может увеличить задержку данных, но выдаёт кратную производительность на агрегатах.
Короткое резюме:
- Для аналитики — денормализовать факт (order_line) с полем revenue, партиционировать по месяцам, кластеризовать по country, поддерживать materialized monthly aggregates.
- Индексы: партиц. + (created_at, country) или BRIN по дате + B-tree по country.
- Рассмотрите перенос аналитики в колонночное хранилище/OLAP (ClickHouse, BigQuery, Snowflake, Druid) для масштабируемой и дешёвой агрегации.