valera.beretar
70 lines
4.0 KiB
SQL
70 lines
4.0 KiB
SQL
-- Задание 1
|
|
CREATE MATERIALIZED VIEW my_category_revenue AS
|
|
SELECT
|
|
p.category,
|
|
SUM(oi.quantity * oi.price_at_purchase) AS total_revenue
|
|
FROM orders o
|
|
JOIN order_items oi ON oi.order_id = o.id
|
|
JOIN products p ON p.id = oi.product_id
|
|
WHERE o.status = 'paid'
|
|
GROUP BY p.category;
|
|
|
|
SELECT * FROM my_category_revenue
|
|
ORDER BY total_revenue DESC;
|
|
-- Ответ на вопросы
|
|
-- 1. Не нужно писать длинные запросы и результат будет уже подсчитан.
|
|
|
|
|
|
-- Задание 2
|
|
CREATE MATERIALIZED VIEW mv_user_activity AS
|
|
SELECT
|
|
u.id AS user_id,
|
|
u.name AS user_name,
|
|
COUNT(o.user_id) AS orders_count
|
|
FROM users u
|
|
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid'
|
|
GROUP BY u.id, u.name;
|
|
|
|
SELECT * FROM mv_user_activity
|
|
WHERE orders_count > 0
|
|
ORDER BY orders_count DESC;
|
|
-- Ответ на вопросы
|
|
-- 1. Я так понял, что если критерий "самых лояльных" клиентов это колличество оплаченных заказов,
|
|
-- то "самые лояльные" это те у кого этих заказов больше всего.
|
|
-- 2. Покажет "самых лояльных" клиентов за последный месяц, кто-то из клиетов может сделать много заказов за 1-2 месяца, а кто то за год по чуть чуть заказывает.
|
|
-- Возможно еще какие то из клиентов ориентируются на "сезонные" товары и у них будет сплеск продажи за последний месяц, или кто то из клиентов покупал много за год, а в этом месяце перестал делать заказы.
|
|
|
|
|
|
-- Задание 3
|
|
CREATE MATERIALIZED VIEW mv_daily_revenue AS
|
|
SELECT
|
|
o.order_date::date AS sales_date,
|
|
SUM(oi.quantity * price_at_purchase) AS daily_revenue
|
|
FROM orders o
|
|
JOIN order_items oi ON oi.order_id = o.id
|
|
WHERE o.status = 'paid'
|
|
GROUP BY o.order_date::date;
|
|
|
|
SELECT * FROM mv_daily_revenue
|
|
ORDER BY sales_date DESC;
|
|
-- Ответ на вопросы
|
|
-- 1. «Просадки» и «пики» будут видны в колонке daily_revenue
|
|
-- 2. Быстрее считывать с готовой витрины mv_daily_revenuе, на лету из сырых таблиц дольше будет обрабатываться запрос.
|
|
|
|
|
|
-- Задание 4
|
|
REFRESH MATERIALIZED VIEW mv_daily_revenue;
|
|
-- Расписание интернет‑магазина с несколькими заказами в день:
|
|
-- Для такого сценария подойдет обновление витрины раз в неделю по ночам.
|
|
-- Так как заказов не так уж и много, можно обновлять статискиту чаще.
|
|
-- Ночью, потому что заказов будет меньше в то время суток и бд быстрее обработает ззапрос.
|
|
|
|
-- Расписание крупного магазина с тысячами заказов в час:
|
|
-- Для такого сценария подойдет обновление витрин раз в месяц - раз в квартал по ночам,
|
|
-- Так как данных много и частые расчеты будут сильно нагружать базу данных.
|
|
-- Возможно, нужно будет получить данные аналитикам раньше указанных сроков.
|
|
|
|
-- Ответ на вопросы
|
|
-- 1. Данные будут устаревшими, из за того результат работы аналитиков будут не достоверными.
|
|
-- 2. Слишком частые обновления витрит будут сильно нагружать БД.
|