Итоговый проект: база данных для аналитики продаж
Превращаем нагруженную транзакционную базу магазина в быстрый, удобный для запросов слой аналитики
Главная идея: Отчётные запросы и запросы приёма заказов хотят прямо противоположного. В этом финальном проекте вы сохраняете исходные OLTP-таблицы для повседневной записи и строите поверх них отдельный слой аналитики в виде звезды: широкую таблицу
fact_sales, окружённую маленькими таблицами измерений. Вы загружаете этот слой одним INSERT ... SELECT, строите дашборды на оконных функциях и ускоряете их правильными индексами — проверяя каждое решение через EXPLAIN.
К концу вы сможете:
- спроектировать схему «звезда» (факт + измерения) на основе существующей OLTP-модели,
- загрузить таблицу фактов из нормализованных источников одним запросом с join,
- строить дашборды рейтингов, нарастающих итогов и когорт с помощью оконных функций,
- подобрать составные индексы для таблицы фактов и подтвердить их через
EXPLAIN, - честно описать компромиссы денормализованной аналитической копии.
1. Источник: наш OLTP-магазин
Всё начинается с нормализованной операционной базы, которую вы уже умеете строить. В ней четыре таблицы: клиенты делают заказы, у каждого заказа есть позиции, а каждая позиция ссылается на товар.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(80),
country VARCHAR(40),
signup_date DATE
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(80),
category VARCHAR(40),
price DECIMAL(10,2)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT REFERENCES customers(customer_id),
order_date DATE,
status VARCHAR(20)
);
CREATE TABLE order_items (
order_id INT REFERENCES orders(order_id),
product_id INT REFERENCES products(product_id),
quantity INT,
unit_price DECIMAL(10,2),
PRIMARY KEY (order_id, product_id)
);Несколько строк, чтобы сделать запросы наглядными:
INSERT INTO customers VALUES
(1,'Aida','KG','2024-01-05'),
(2,'Bekzat','KZ','2024-02-11'),
(3,'Chris','US','2024-02-20');
INSERT INTO products VALUES
(10,'Keyboard','Peripherals',25.00),
(11,'Mouse','Peripherals',15.00),
(12,'Monitor','Displays',180.00);
INSERT INTO orders VALUES
(100,1,'2024-03-01','paid'),
(101,2,'2024-03-02','paid'),
(102,1,'2024-03-15','paid');
INSERT INTO order_items VALUES
(100,10,2,25.00),(100,11,1,15.00),
(101,12,1,180.00),
(102,11,3,15.00),(102,12,1,180.00);Такая схема идеальна для записи заказов: нет дублирования данных, аккуратные внешние ключи. Но отчёт вроде «месячная выручка по категориям с рейтингом» вынужден каждый раз соединять четыре таблицы. Именно здесь слой аналитики оправдывает себя.