Ниже — краткое описание возможных схем нормализации и два SQL‑запроса.
Схемы нормализации (до 3NF, варианты):
- Базовая нормализованная схема (OLTP, 3NF)
- users(user_id PK, name, email, created_at, ...)
- products(product_id PK, name, description, price, category_id, ...)
- orders(order_id PK, user_id FK → users.user_id, order_date, status, total, ...)
- order_items(order_item_id PK, order_id FK → orders.order_id, product_id FK → products.product_id, quantity, unit_price) — разрешает many‑to‑many между orders и products
- reviews(review_id PK, user_id FK → users.user_id, product_id FK → products.product_id, rating, comment, created_at)
- inventory(product_id FK → products.product_id, location_id, quantity, restock_date, PRIMARY KEY(product_id, location_id))
Комментарий: все неповторяющиеся атрибуты вынесены в отдельные таблицы; order_items разбивает состав заказа; индексы на FK и на order_date / product_id / user_id.
- Расширения / детализация
- product_categories(category_id PK, name) и products.category_id → product_categories
- locations(location_id PK, name, address) и inventory.location_id → locations
- Денормализованный / аналитический вариант (star schema) для отчётов
- fact_orders(order_key PK, user_key FK, product_key FK, date_key, quantity, revenue, ...)
- dim_users(...), dim_products(...), dim_date(...)
Комментарий: удобен для агрегатов и BI; обновляется через ETL из OLTP.
Примеры SQL‑запросов (в синтаксисе PostgreSQL / стандарт SQL):
а) Топ-$5$ наиболее обзорных товаров (по числу записей в reviews):
SELECT p.product_id, p.name, COUNT(r.review_id) AS reviews_count
FROM products p
LEFT JOIN reviews r ON r.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY reviews_count DESC
LIMIT $5$;
(Если нужно учитывать только отзывы с рейтингом или за период — добавить WHERE или HAVING.)
б) Пользователи с частотой заказов $>$ $10$ за год — два варианта.
1) По календарному году (каждый год отдельно; вернёт пары user + year, где заказов больше $10$):
SELECT u.user_id, u.name, DATE_PART('year', o.order_date) AS year, COUNT(o.order_id) AS orders_per_year
FROM users u
JOIN orders o ON o.user_id = u.user_id
GROUP BY u.user_id, u.name, DATE_PART('year', o.order_date)
HAVING COUNT(o.order_id) > $10$;
2) За последние $1$ год (rolling window, последние $365$ дней) — пользователи, у которых > $10$ заказов за последний год:
SELECT u.user_id, u.name, COUNT(o.order_id) AS orders_last_year
FROM users u
JOIN orders o ON o.user_id = u.user_id
WHERE o.order_date >= NOW() - INTERVAL '$1$ year'
GROUP BY u.user_id, u.name
HAVING COUNT(o.order_id) > $10$;
Примечания:
- Для производительности: добавьте индексы на orders(order_date), orders(user_id), reviews(product_id).
- При больших объёмах для аналитики предпочтителен денормализованный витрина‑подход (fact/dim) и предварительная агрегация.