В рамках данного проекта из DWH создается и наполняется витрина данных о покупателях маркетплейса товаров ручной работы. Подключение упаковано в транзакцию и осуществляется инкрементально.
-- Скрипт создания и наполнения витрины данных
BEGIN TRANSACTION;
-- DDL витрины данных
DROP TABLE IF EXISTS dwh.customer_report_datamart;
CREATE TABLE IF NOT EXISTS dwh.customer_report_datamart (
-- Бизнесу требуются следующие данные:
id BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL, -- идентификатор записи;
customer_id BIGINT NOT NULL, -- идентификатор заказчика;
customer_name VARCHAR NOT NULL, -- Ф. И. О. заказчика;
customer_address VARCHAR NOT NULL, -- адрес заказчика;
customer_birthday DATE NOT NULL, -- дата рождения заказчика;
customer_email VARCHAR NOT NULL, -- электронная почта заказчика;
customer_money NUMERIC(15,2) NOT NULL, -- сумма, которую потратил заказчик;
platform_money BIGINT NOT NULL, -- сумма, которую заработала платформа от покупок заказчика за месяц (10% от суммы, которую потратил заказчик);
count_order BIGINT NOT NULL, -- количество заказов у заказчика за месяц;
avg_price_order NUMERIC(10,2) NOT NULL, -- средняя стоимость одного заказа у заказчика за месяц;
order_median_tume DATE NOT NULL, -- !!! медианное время в днях от момента создания заказа до его завершения за месяц;
top_product_category VARCHAR NOT NULL, -- самая популярная категория товаров у этого заказчика за месяц;
top_crafrtsman VARCHAR NOT NULL, -- !!! идентификатор самого популярного мастера ручной работы у заказчика. Если заказчик сделал одинаковое количество заказов у нескольких мастеров, возьмите любого;
count_order_created BIGINT NOT NULL, -- количество созданных заказов за месяц;
count_order_in_progress BIGINT NOT NULL, -- количество заказов в процессе изготовки за месяц
count_order_delivery BIGINT NOT NULL, -- количество заказов в доставке за месяц
count_order_done BIGINT NOT NULL, -- количество завершённых заказов за месяц
count_order_not_done BIGINT NOT NULL, -- количество незавершённых заказов за месяц
report_period VARCHAR NOT NULL, -- отчётный период год и месяц
CONSTRAINT customer_report_datamart_pk PRIMARY KEY (id)
);
-- DDL таблицы инкрементальных загрузок
DROP TABLE IF EXISTS dwh.load_dates_customer_report_datamart;
CREATE TABLE IF NOT EXISTS dwh.load_dates_customer_report_datamart (
id BIGINT GENERATED ALWAYS AS IDENTITY NOT NULL,
load_dttm DATE NOT NULL,
CONSTRAINT load_dates_customer_report_datamart_pk PRIMARY KEY (id)
);
-- определяем, какие данные были изменены в витрине или добавлены в DWH. Формируем дельту изменений
WITH
dwh_delta AS (
SELECT
dcs.customer_id AS customer_id,
dcs.customer_name AS customer_name,
dcs.customer_address AS customer_address,
dcs.customer_birthday AS customer_birthday,
dcs.customer_email AS customer_email,
fo.order_id AS order_id,
dp.product_id AS product_id,
dp.product_price AS product_price,
dp.product_type AS product_type,
DATE_PART('year', AGE(dcs.customer_birthday)) AS customer_age,
fo.order_completion_date - fo.order_created_date AS diff_order_date,
fo.order_status AS order_status,
TO_CHAR(fo.order_created_date, 'yyyy-mm') AS report_period,
dcs.customer_id AS exist_customer_id,
dc.load_dttm AS craftsman_load_dttm,
dcs.load_dttm AS customers_load_dttm,
dp.load_dttm AS products_load_dttm
FROM dwh.f_order fo
INNER JOIN dwh.d_craftsman dc ON dc.craftsman_id = fo.craftsman_id
INNER JOIN dwh.d_customer dcs ON dcs.customer_id = fo.customer_id
INNER JOIN dwh.d_product dp ON dp.product_id = fo.product_id
LEFT JOIN dwh.customer_report_datamart crd ON crd.customer_id = dc.customer_id
WHERE (fo.load_dttm > (SELECT COALESCE(MAX(load_dttm),'1900-01-01') FROM dwh.load_dates_customer_report_datamart)) OR
(dc.load_dttm > (SELECT COALESCE(MAX(load_dttm),'1900-01-01') FROM dwh.load_dates_customer_report_datamart)) OR
(dcs.load_dttm > (SELECT COALESCE(MAX(load_dttm),'1900-01-01') FROM dwh.load_dates_customer_report_datamart)) OR
(dp.load_dttm > (SELECT COALESCE(MAX(load_dttm),'1900-01-01') FROM dwh.load_dates_customer_report_datamart))
),
-- делаем выборку покупателей, по которым были изменения в DWH.
-- По этим мастерам данные в витрине нужно будет обновить
dwh_update_delta AS (
SELECT
dd.exist_customer_id AS customer_id
FROM dwh_delta dd
WHERE dd.exist_customer_id IS NOT NULL
),
-- делаем расчёт витрины по новым данным.
-- Этой информации по покупателям в рамках расчётного периода раньше не было,
-- это новые данные. Их можно просто вставить (insert) в витрину без обновления
dwh_delta_insert_result AS (
SELECT
T4.craftsman_id AS craftsman_id,
T4.craftsman_name AS craftsman_name,
T4.craftsman_address AS craftsman_address,
T4.craftsman_birthday AS craftsman_birthday,
T4.craftsman_email AS craftsman_email,
T4.craftsman_money AS craftsman_money,
T4.platform_money AS platform_money,
T4.count_order AS count_order,
T4.rank_count_product as rank_count_product,
T4.rank_count_craftsman as rank_count_craftsman,
T4.product_type AS top_product_category,
T4.median_time_order_completed AS median_time_order_completed,
T4.count_order_created AS count_order_created,
T4.count_order_in_progress AS count_order_in_progress,
T4.count_order_delivery AS count_order_delivery,
T4.count_order_done AS count_order_done,
T4.count_order_not_done AS count_order_not_done,
T4.report_period AS report_period
FROM (
-- в этой выборке объединяем две внутренние выборки
-- по расчёту столбцов витрины и применяем оконную функцию
-- для определения самой популярной категории товаров
SELECT
*,
RANK() OVER (PARTITION BY T2.craftsman_id ORDER BY count_product DESC) AS rank_count_product, -- самая популярная категория товаров у этого заказчика за месяц;
RANK() OVER (PARTITION BY T5.craftsman_id ORDER BY T5.count_customer DESC) AS rank_count_craftsman -- идентификатор самого популярного мастера ручной работы у заказчика. Если заказчик сделал одинаковое количество заказов у нескольких мастеров, возьмите любого;
FROM (
-- в этой выборке делаем расчёт по большинству столбцов,
-- так как все они требуют одной и той же группировки,
-- кроме столбца с самой популярной категорией товаров у покупателя.
-- Для этого столбца сделаем отдельную выборку с другой группировкой и выполним JOIN
SELECT
T1.customer_id AS customer_id, -- идентификатор заказчика;
T1.customer_name AS customer_name, -- Ф. И. О. заказчика;
T1.customer_address AS customer_address, -- адрес заказчика;
T1.customer_birthday AS customer_birthday, -- дата рождения заказчика;
T1.customer_email AS customer_email,-- электронная почта заказчика;
SUM(T1.product_price) AS customer_money, -- сумма, которую потратил заказчик;
SUM(T1.product_price) * 0.1 AS platform_money, -- сумма, которую заработала платформа от покупок заказчика за месяц (10% от суммы, которую потратил заказчик);
COUNT(order_id) AS count_order, -- количество заказов у заказчика за месяц;
AVG(T1.product_price) AS avg_price_order, -- средняя стоимость одного заказа у заказчика за месяц;
PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY diff_order_date) AS order_median_tume, -- медианное время в днях от момента создания заказа до его завершения за месяц;
--AVG(T1.customer_age) AS avg_age_customer,
--PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY diff_order_date) AS median_time_order_completed,
SUM(CASE WHEN T1.order_status = 'created' THEN 1 ELSE 0 END) AS count_order_created, -- количество созданных заказов за месяц;
SUM(CASE WHEN T1.order_status = 'in progress' THEN 1 ELSE 0 END) AS count_order_in_progress, -- количество заказов в процессе изготовки за месяц
SUM(CASE WHEN T1.order_status = 'delivery' THEN 1 ELSE 0 END) AS count_order_delivery, -- количество заказов в доставке за месяц
SUM(CASE WHEN T1.order_status = 'done' THEN 1 ELSE 0 END) AS count_order_done, -- количество завершённых заказов за месяц
SUM(CASE WHEN T1.order_status != 'done' THEN 1 ELSE 0 END) AS count_order_not_done, -- количество незавершённых заказов за месяц
T1.report_period AS report_period -- отчётный период год и месяц
FROM dwh_delta AS T1
WHERE T1.exist_craftsman_id IS NULL
GROUP BY T1.customer_id, T1.customer_name, T1.customer_address, T1.customer_birthday, T1.customer_email, T1.report_period
) AS T2
INNER JOIN (
-- Эта выборка поможет определить самый популярный товар у покупателя.
-- Эта выборка не делается в предыдущем запросе, так как нужна другая группировка.
-- Для данных этой выборки можно применить оконную функцию,
-- которая и покажет самую популярную категорию товаров у мастера
SELECT
dd.customer_id AS customer_id_for_product_type,
dd.product_type,
COUNT(dd.product_id) AS count_product
FROM dwh_delta AS dd
GROUP BY dd.customer_id, dd.product_type
ORDER BY count_product DESC
) AS T3 ON T3.customer_id_for_product_type = T2.customer_id
INNER JOIN (
-- Эта выборка поможет определить самого популярного мастера у покупателя.
-- Эта выборка не делается в предыдущем запросе, так как нужна другая группировка.
-- Для данных этой выборки можно применить оконную функцию,
-- которая и покажет самую популярную категорию товаров у мастера
SELECT
dd.customer_id AS customer_id_for_count_craftsman,
COUNT(dd.craftsman_id) AS count_craftsman
FROM dwh_delta AS dd
GROUP BY dd.customer_id
ORDER BY count_craftsman DESC
) AS T5 ON T5.customer_id_for_count_craftsman = T2.customer_id
) AS T4
WHERE T4.rank_count_product = 1 AND T4.rank_count_craftsman = 1
ORDER BY report_period -- условие помогает оставить в выборке первую по популярности категорию товаров и мастеров
),
-- делаем перерасчёт для существующих записей витринs,
-- так как данные обновились за отчётные периоды.
-- Логика похожа на insert, но нужно достать конкретные данные из DWH
dwh_delta_update_result AS (
SELECT
T4.craftsman_id AS craftsman_id,
T4.craftsman_name AS craftsman_name,
T4.craftsman_address AS craftsman_address,
T4.craftsman_birthday AS craftsman_birthday,
T4.craftsman_email AS craftsman_email,
T4.craftsman_money AS craftsman_money,
T4.platform_money AS platform_money,
T4.count_order AS count_order,
T4.avg_price_order AS avg_price_order,
T4.avg_age_customer AS avg_age_customer,
T4.product_type AS top_product_category,
T4.median_time_order_completed AS median_time_order_completed,
T4.count_order_created AS count_order_created,
T4.count_order_in_progress AS count_order_in_progress,
T4.count_order_delivery AS count_order_delivery,
T4.count_order_done AS count_order_done,
T4.count_order_not_done AS count_order_not_done,
T4.report_period AS report_period
FROM (
SELECT -- в этой выборке объединяем две внутренние выборки по расчёту столбцов витрины и применяем оконную функцию для определения самой популярной категории товаров
*,
RANK() OVER (PARTITION BY T2.craftsman_id ORDER BY count_product DESC) AS rank_count_product, -- самая популярная категория товаров у этого заказчика за месяц;
RANK() OVER (PARTITION BY T5.craftsman_id ORDER BY T5.count_customer DESC) AS rank_count_craftsman -- идентификатор самого популярного мастера ручной работы у заказчика. Если заказчик сделал одинаковое количество заказов у нескольких мастеров, возьмите любого;
FROM (
SELECT -- в этой выборке делаем расчёт по большинству столбцов, так как все они требуют одной и той же группировки, кроме столбца с самой популярной категорией товаров у мастера. Для этого столбца сделаем отдельную выборку с другой группировкой и выполним JOIN
T1.customer_id AS customer_id, -- идентификатор заказчика;
T1.customer_name AS customer_name, -- Ф. И. О. заказчика;
T1.customer_address AS customer_address, -- адрес заказчика;
T1.customer_birthday AS customer_birthday, -- дата рождения заказчика;
T1.customer_email AS customer_email,-- электронная почта заказчика;
SUM(T1.product_price) AS customer_money, -- сумма, которую потратил заказчик;
SUM(T1.product_price) * 0.1 AS platform_money, -- сумма, которую заработала платформа от покупок заказчика за месяц (10% от суммы, которую потратил заказчик);
COUNT(order_id) AS count_order, -- количество заказов у заказчика за месяц;
AVG(T1.product_price) AS avg_price_order, -- средняя стоимость одного заказа у заказчика за месяц;
PERCENTILE_CONT(.5) WITHIN GROUP (ORDER BY diff_order_date) AS order_median_tume, -- медианное время в днях от момента создания заказа до его завершения за месяц;
--AVG(T1.customer_age) AS avg_age_customer,
--PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY diff_order_date) AS median_time_order_completed,
SUM(CASE WHEN T1.order_status = 'created' THEN 1 ELSE 0 END) AS count_order_created, -- количество созданных заказов за месяц;
SUM(CASE WHEN T1.order_status = 'in progress' THEN 1 ELSE 0 END) AS count_order_in_progress, -- количество заказов в процессе изготовки за месяц
SUM(CASE WHEN T1.order_status = 'delivery' THEN 1 ELSE 0 END) AS count_order_delivery, -- количество заказов в доставке за месяц
SUM(CASE WHEN T1.order_status = 'done' THEN 1 ELSE 0 END) AS count_order_done, -- количество завершённых заказов за месяц
SUM(CASE WHEN T1.order_status != 'done' THEN 1 ELSE 0 END) AS count_order_not_done, -- количество незавершённых заказов за месяц
T1.report_period AS report_period -- отчётный период год и месяц
FROM (
-- в этой выборке достаём из DWH обновлённые
-- или новые данные по мастерам, которые уже есть в витрине
SELECT
dcs.customer_id AS customer_id,
dcs.customer_name AS customer_name,
dcs.customer_address AS customer_address,
dcs.customer_birthday AS customer_birthday,
dcs.customer_email AS customer_email,
fo.order_id AS order_id,
dp.product_id AS product_id,
dp.product_price AS product_price,
dp.product_type AS product_type,
DATE_PART('year', AGE(dcs.customer_birthday)) AS customer_age,
fo.order_completion_date - fo.order_created_date AS diff_order_date,
fo.order_status AS order_status,
TO_CHAR(fo.order_created_date, 'yyyy-mm') AS report_period
FROM dwh.f_order fo
INNER JOIN dwh.d_craftsman dc ON fo.craftsman_id = dc.craftsman_id
INNER JOIN dwh.d_customer dcs ON fo.customer_id = dcs.customer_id
INNER JOIN dwh.d_product dp ON fo.product_id = dp.product_id
INNER JOIN dwh_update_delta ud ON fo.craftsman_id = ud.craftsman_id
) AS T1
GROUP BY T1.customer_id, T1.customer_name, T1.customer_address, T1.customer_birthday, T1.customer_email, T1.report_period
) AS T2
INNER JOIN (
-- Эта выборка поможет определить самый популярный товар у покупателя.
-- Эта выборка не делается в предыдущем запросе, так как нужна другая группировка.
-- Для данных этой выборки можно применить оконную функцию,
-- которая и покажет самую популярную категорию товаров у мастера
SELECT
dd.customer_id AS customer_id_for_product_type,
dd.product_type,
COUNT(dd.product_id) AS count_product
FROM dwh_delta AS dd
GROUP BY dd.customer_id, dd.product_type
ORDER BY count_product DESC
) AS T3 ON T3.customer_id_for_product_type = T2.customer_id
INNER JOIN (
-- Эта выборка поможет определить самого популярного мастера у покупателя.
-- Эта выборка не делается в предыдущем запросе, так как нужна другая группировка.
-- Для данных этой выборки можно применить оконную функцию,
-- которая и покажет самую популярную категорию товаров у мастера
SELECT
dd.customer_id AS customer_id_for_count_craftsman,
COUNT(dd.craftsman_id) AS count_craftsman
FROM dwh_delta AS dd
GROUP BY dd.customer_id
ORDER BY count_craftsman DESC
) AS T5 ON T5.customer_id_for_count_craftsman = T2.customer_id
) AS T4
WHERE T4.rank_count_product = 1 AND T4.rank_count_craftsman = 1
ORDER BY report_period
),
-- выполняем insert новых расчитанных данных для витрины
insert_delta AS (
INSERT INTO dwh.customer_report_datamart (
craftsman_id,
craftsman_name,
craftsman_address,
craftsman_birthday,
craftsman_email,
craftsman_money,
platform_money,
count_order,
rank_count_product,
rank_count_craftsman,
top_product_category,
median_time_order_completed,
count_order_created,
count_order_in_progress,
count_order_delivery,
count_order_done,
count_order_not_done,
report_period
)
SELECT
craftsman_id,
craftsman_name,
craftsman_address,
craftsman_birthday,
craftsman_email,
craftsman_money,
platform_money,
count_order,
rank_count_product,
rank_count_craftsman,
top_product_category,
median_time_order_completed,
count_order_created,
count_order_in_progress,
count_order_delivery,
count_order_done,
count_order_not_done,
report_period
FROM dwh_delta_insert_result
),
-- выполняем обновление показателей в отчёте по уже существующим мастерам
update_delta AS (
UPDATE dwh.customer_report_datamart SET
craftsman_id = updates.craftsman_id,
craftsman_name = updates.craftsman_name,
craftsman_address = updates.craftsman_address,
craftsman_birthday = updates.craftsman_birthday,
craftsman_email = updates.craftsman_email,
craftsman_money = updates.craftsman_money,
platform_money = updates.platform_money,
count_order = updates.count_order,
rank_count_product = updates.rank_count_product,
rank_count_craftsman = updates.rank_count_craftsman,
top_product_category = updates.top_product_category,
median_time_order_completed = updates.median_time_order_completed,
count_order_created = updates.count_order_created ,
count_order_in_progress = updates.count_order_in_progress,
count_order_delivery = updates.count_order_delivery,
count_order_done = updates.count_order_done,
count_order_not_done = updates.count_order_not_done,
report_period = updates.report_period
FROM (
SELECT
craftsman_id,
craftsman_name,
craftsman_address,
craftsman_birthday,
craftsman_email,
craftsman_money,
platform_money,
count_order,
rank_count_product,
rank_count_craftsman,
top_product_category,
median_time_order_completed,
count_order_created,
count_order_in_progress,
count_order_delivery,
count_order_done,
count_order_not_done,
report_period
FROM dwh_delta_update_result
) AS updates
WHERE dwh.customer_report_datamart.customer_id = updates.customer_id
)
-- делаем запись в таблицу загрузок о том,
-- когда была совершена загрузка, чтобы в следующий раз взять данные,
-- которые будут добавлены или изменены после этой даты
insert_load_date AS (
INSERT INTO dwh.load_dates_customer_report_datamart (
load_dttm
)
SELECT GREATEST(COALESCE(MAX(craftsman_load_dttm), NOW()),
COALESCE(MAX(customers_load_dttm), NOW()),
COALESCE(MAX(products_load_dttm), NOW()))
FROM dwh_delta
)
-- инициализируем запрос CTE
SELECT 'increment datamart';
COMMIT TRANSACTION;