🛠️ Tools: PostgreSQL (DBMS), Python (Data Engineering)
💽 Dataset: dataset-open
💬 Data Description: datadescription-open
1) Что такое USS и в чем заключается его основное преимущество по сравнению с традиционными звездной и снежинкой схемами?
Unified Star Schema (USS) - это методология проектирования схемы данных для аналитических систем. Она объединяет данные из различных источников в одну звездную схему, устраняя несогласованности и дублирование.
Авторы: Инмон Б., Пуппини Ф. Книга: The Unified Star Schema
| Критерий | USS | Снежинка | Звезда |
|---|---|---|---|
| Структура | Одна звездная схема, объединяющая данные из разных источников | Множество нормализованных таблиц | Одна фактовая таблица, окруженная измерениями |
| Производительность запросов | Высокая | Низкая (из-за необходимости объединения таблиц) | Высокая |
| Дублирование данных | Минимальное | Может быть высоким из-за нормализации | Минимальное |
| Количество таблиц | Меньше | Больше | Среднее |
| Изменение структуры данных | Сложное | Простое | Среднее |
| Поддержка исторических данных | Да | Да | Ограниченная (часто требует дополнительных таблиц) |
| Использование в аналитике | Оптимальное | Неэффективное из-за сложности запросов | Оптимальное |
Постройте USS структуру для анализа продаж и определения самых популярных продуктов в разных магазинах. Включите таблицы для фактов продаж и измерений продуктов, магазинов и времени.
Решение задачи: Task-1-Solution
Screenshot:
Решение для настройки базы данных: store_db[DB Creation].sql + data_engineering - preprocessing + data_fill_csv_sql
Схема:
ERD-Схема:
2) Какой подход к моделированию связей сущностей в USS структуре может использоваться для обеспечения гибкости и простоты масштабирования?
В USS для гибкости и масштабирования рекомендуется использовать "унифицированные ключи". Это подход, при котором каждой сущности присваивается уникальный и постоянный идентификатор системы. Это способствует гибкости и облегчает масштабирование системы при увеличении объема данных и источников.
Создайте USS структуру для анализа активности клиентов и определения их предпочтений в разных магазинах. Включите таблицы для фактов заказов и измерений клиентов, магазинов и времени.
Решение: Task-2-Solution
Screenshots:
3) Как USS таблицы могут быть использованы для оптимизации выполнения сложных аналитических запросов?
⭐ Звездная схема: Использлование звездной схемы в USS облегчает агрегирование данных и ускоряет выполнение групповых функций, как AVG, SUM, COUNT для сложных аналитических запросов.
🔑 Унифицированные ключи: Уникальные идентификаторы для сущностей облегчают объединение данных из разных источников, уменьшая необходимость дополнительных операций соединения таблиц.
📋 Минимизация дублирования: USS устраняет дублирование данных, что сокращает объем обработки и оптимизирует производительность запросов.
🆔 Индексы: Возможность создания оптимальных индексов на унифицированных ключах улучшает скорость выполнения запросов, особенно при больших объемах данных.
🔗 Предварительное объединение данных: USS позволяет предварительно объединять данные из различных источников, что уменьшает время выполнения запросов, так как предобработка уже выполнена.
➕ Удобство добавления данных: USS упрощает процесс добавления новых данных, что облегчает масштабирование системы при увеличении объема данных и источников.
Постройте USS структуру для анализа эффективности маркетинговых акций в различных магазинах. Включите таблицы для фактов заказов и измерений магазинов, продуктов, клиентов и времени.
Решение: Task-3-Solution
Screenshot:
4) Какие инструменты или программное обеспечение можно использовать для работы с USS структурами и выполнения аналитических запросов?
🐘 PostgreSQL: Мощная система управления базами данных (СУБД) с поддержкой звездных схем и унифицированных ключей (Использовалось на практике для решение данного кейса "Моделирование данных").
📊 Microsoft Power BI: Еще один мощный инструмент для визуализации данных и работы с USS структурами.
📈 Tableau: Визуализационный инструмент, поддерживающий USS структуры и облегчающий создание интерактивных отчетов.
✨ Apache Spark: Распределенный фреймворк для обработки больших объемов данных и выполнения сложных аналитических запросов.
🐝 Apache Hive: Система управления данными на основе Hadoop, позволяющая использовать USS для аналитики.
Создайте USS структуру для анализа покупательского поведения и предсказания спроса на определенные продукты в разных магазинах. Включите таблицы для фактов заказов и измерений продуктов, клиентов, магазинов и времени.
Решение: Task-4-Solution
Screenshots:
В USS структуре факты представляют числовые данные, которые измеряются и агрегируются, а измерения описывают контекст или характеристики фактов. Их сочетание позволяет анализировать данные в различных срезах и уровнях детализации, обеспечивая гибкость и простоту в аналитике.
Напишите SQL запрос для получения общего количества продаж за каждый месяц в указанном периоде.
Решение: Task-5-Solution
Screenshot:
6) Как можно масштабировать USS структуру для обработки больших объемов данных и поддержания производительности?
Для масштабирования USS структуры при обработке больших объемов данных и поддержания производительности можно использовать горизонтальное масштабирование с помощью репликации или шардинга. Также можно оптимизировать индексы и настройки базы данных для улучшения производительности запросов.
Напишите SQL запрос для получения самых популярных продуктов по количеству заказов.
Решение: Task-6-Solution
Screenshot:
Напишите SQL запрос для получения среднего чека заказов в каждом магазине.
Решение: Task-7-Solution
Screenshot:
Выясните топ-5 продуктов с наибольшим объемом продаж за последние три месяца и определите, какие клиенты совершили эти покупки.
Решение: Task-8-Solution
Screenshot:
🔎 Анализ таблицы заказов [ML/DS]: Визуализация общих данных и построение модели машинного обучения для предсказания продаж
Решение [source code]: Orders-Analysis-DS-ML
ML Model(Для предсказания примерного количества продаж): forecast-model.pkl
Общее количество продаж по месяцам:
Сегментация клиентов и количества покупок:
Анализ продуктов:
Анализ магазинов:
Сезоннный анализ продаж:
🧐 Author: Arman Zhalgasbayev - @grembim
📬 Email: [email protected]
📦 Project Link (Github Repository): https://github.com/silvermete0r/SDU_Hackathon_USS_DB_Analysis