Построение аналитической БД интернет-магазина (модель Звезда)

1.

Итоговая аттестация
Цель проекта: Построение аналитической БД интернет-магазина
(модель Звезда)

2.

Инструменты, используемые для
разработки БД:
• СУБД PostgreSQL
• pgAdmin - платформа для администрирования и
настройки СУБД PostgreSQL
2

3.

Задачи
1.Разработать схему «Звезда» с таблицами измерений и фактов
2.Заполнить таблицы тестовыми данными
3.Написать аналитические SQL-запросы с агрегатами, ROLLUP, CUBE и
оконными функциями
4.Создать виртуальные и материализованные представления
5.Добавить индексы для ускорения выполнения запросов
6.Реализовать триггерную функцию для логирования изменений в
измерениях
3

4.

Результаты
Физическая модель данных
Создание таблиц
Создана аналитическая модель "Звезда",
включающая:
fact_sales — факты продаж (кол-во, сумма,
скидка, дата, метод оплаты)
dim_products — товары (категория, бренд,
цена)
dim_customers — клиенты (город, email,
уровень лояльности)
dim_dates — календарь (день, неделя,
квартал, праздник)
dim_employees — сотрудники (отдел,
должность)
Все таблицы связаны через внешние ключи.
Реализовано наполнение 1000 случайных продаж.
4

5.

Результаты
Оптимизация запросов
Для повышения производительности были
созданы индексы:
Результат оптимизации
Индексы ускоряют JOIN, фильтрацию,
группировку и сортировку в аналитических
запросах.
• fact_sales — индексы по внешним ключам
(product_id, customer_id, date_id,
employee_id), payment_method
• dim_customers — индексы по loyalty_level
и city
• dim_products — индексы по category и
brand
5

6.

Результаты
VIEW и MATERIALIZED VIEW
Созданы 3 представления:
view_avg_sales_by_loyalty
• 1. view_avg_sales_by_loyalty — средний
чек по городу и уровню лояльности клиента
• 2. view_sales_by_category_brand —
агрегированные продажи по категориям и
брендам
• 3. mv_running_sales_total —
материализованное представление с
накопленными продажами по датам
Возвращает средний чек по городам и уровням
лояльности. Показывает, где и у кого выше "чистый"
средний чек
view_sales_by_category_brand
Сравнение категорий и брендов по продажам.
Полезно для ассортимента и стратегий закупки
mv_running_sales_total
• Накопленные продажи по дням. Используется
для анализа динамики и отчетов.
6

7.

Результаты
Журналирование изменений
Запросы изменения данных
Создана триггерная функция для таблицы dim_customers:
INSERT INTO dim_customers (first_name, last_name, email, city, registration_date,
loyalty_level)
• Автоматически сохраняет INSERT, UPDATE и DELETE в таблицу
audit_log_customers
• Старые и новые данные хранятся в формате JSONB
• Лог содержит тип операции, дату и идентификатор клиента
Это обеспечивает аудит, безопасность и контроль изменений
справочника.
VALUES ('Тест', 'Клиент', 'testuser@example.com', 'Томск', '2024-12-01',
'Regular');
UPDATE dim_customers
SET city = 'Казань', loyalty_level = 'Silver'
WHERE email = 'testuser@example.com';
DELETE FROM dim_customers
WHERE email = 'testuser@example.com';
Просмотр таблицы
7

8.

Заключение
Цель проекта достигнута:
• Реализована структура аналитической БД
интернет-магазина
• Написаны гибкие SQL-запросы и агрегаты
• Созданы представления и индексы
• Настроено журналирование через триггеры
Проект готов для использования в BI-аналитике.
8

9. Спасибо за внимание