Untitled
unknown
plain_text
a year ago
15 kB
24
Indexable
WITH dwh_delta AS ( -- Шаг 2 SELECT dc.craftsman_id,-- напишите код здесь AS craftsman_id, dc.craftsman_name,-- напишите код здесь AS craftsman_name, dc.craftsman_address,-- напишите код здесь AS craftsman_address, dc.craftsman_birthday,-- напишите код здесь AS craftsman_birthday, dc.craftsman_email,-- напишите код здесь AS craftsman_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(customer_birthday)) as customer_age,-- напишите код здесь AS customer_age, (fo.order_completion_date - fo.order_created_date) as diff_order_date,-- напишите код здесь AS diff_order_date, fo.order_status,-- напишите код здесь AS order_status, to_char(fo.order_created_date, 'yyyy-mm') as report_period,-- напишите код здесь AS report_period, (case when crd.craftsman_id is null then null else 1 end) as exist_craftsman_id,-- напишите код здесь AS exist_craftsman_id, dc.load_dttm as craftsman_load_dttm,-- напишите код здесь AS craftsman_load_dttm, dcs.load_dttm as customers_load_dttm,-- напишите код здесь AS customers_load_dttm, dp.load_dttm as products_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.craftsman_report_datamart crd on crd.craftsman_id = fo.craftsman_id and fo.load_dttm > (SELECT COALESCE(MAX(load_dttm),'1900-01-01') FROM dwh.load_dates_craftsman_report_datamart) -- напишите код здесь WHERE dc.load_dttm > (SELECT COALESCE(MAX(load_dttm),'1900-01-01') FROM dwh.load_dates_craftsman_report_datamart) or-- напишите код здесь OR dcs.load_dttm > (SELECT COALESCE(MAX(load_dttm),'1900-01-01') FROM dwh.load_dates_craftsman_report_datamart) or-- напишите код здесь OR dp.load_dttm > (SELECT COALESCE(MAX(load_dttm),'1900-01-01') FROM dwh.load_dates_craftsman_report_datamart)-- напишите код здесь; ), dwh_update_delta AS ( -- Шаг 3 SELECT craftsman_id FROM dwh_delta dd where exist_craftsman_id is null ), dwh_delta_insert_result AS ( -- Шаг 4 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 FROM ( SELECT -- в этой выборке делаем расчёт по большинству столбцов, так как все они требуют одной и той же группировки, кроме столбца с самой популярной категорией товаров у мастера. Для этого столбца сделаем отдельную выборку с другой группировкой и выполним join T1.craftsman_id,-- напишите код здесь, T1.craftsman_name,-- напишите код здесь, T1.craftsman_address,-- напишите код здесь, T1.craftsman_birthday,-- напишите код здесь, T1.craftsman_email,-- напишите код здесь, sum(product_price) * 0.9 as craftsman_money,-- напишите код здесь, sum(product_price) * 0.1 as platform_money,-- напишите код здесь, count(order_id) as count_order,-- напишите код здесь, avg(product_price) as avg_price_order,-- напишите код здесь, avg(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 order_status = 'created' THEN 1 ELSE 0 END) as count_order_created,-- напишите код здесь, sum(CASE WHEN order_status = 'in progress' THEN 1 ELSE 0 END) as count_order_in_progress,-- напишите код здесь, sum(CASE WHEN order_status = 'delivery' THEN 1 ELSE 0 END) as count_order_delivery,-- напишите код здесь, sum(CASE WHEN order_status = 'done' THEN 1 ELSE 0 END) as count_order_done,-- напишите код здесь, sum(CASE WHEN order_status != 'done' THEN 1 ELSE 0 END) as count_order_not_done,-- напишите код здесь, T1.report_period-- напишите код здесь FROM dwh_delta AS T1 WHERE T1.exist_craftsman_id is null GROUP BY T1.craftsman_id, T1.craftsman_name, T1.craftsman_address, T1.craftsman_birthday, T1.craftsman_email, T1.report_period ) AS T2 INNER JOIN ( SELECT -- эта выборка поможет определить самый популярный товар у мастера. Это выборка не делается в предыдущем запросе, так как нужна другая группировка. Для данных этой выборки можно применить оконную функцию, которая и покажет самую популярную категорию товаров у мастера count(product_id) as count_product,-- напишите код здесь, dd.craftsman_id as craftsman_id_for_product_type,-- напишите код здесь, dd.product_type-- напишите код здесь FROM dwh_delta AS dd GROUP BY dd.craftsman_id, dd.product_type ORDER BY count_product DESC) AS T3 ON T2.craftsman_id = T3.craftsman_id_for_product_type ) AS T4 WHERE T4.rank_count_product = 1 ORDER BY report_period -- условие помогает оставить в выборке первую по популярности категорию товаров ), dwh_delta_update_result AS ( -- Шаг 5 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 FROM ( SELECT -- в этой выборке делаем расчёт по большинству столбцов, так как все они требуют одной и той же группировки, кроме столбца с самой популярной категорией товаров у мастера. Для этого столбца сделаем отдельную выборку с другой группировкой и выполним join T1.craftsman_id AS craftsman_id, T1.craftsman_name AS craftsman_name, T1.craftsman_address AS craftsman_address, T1.craftsman_birthday AS craftsman_birthday, T1.craftsman_email AS craftsman_email, SUM(T1.product_price) - (SUM(T1.product_price) * 0.1) AS craftsman_money, SUM(T1.product_price) * 0.1 AS platform_money, COUNT(order_id) AS count_order, AVG(T1.product_price) AS avg_price_order, 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 ( SELECT -- в этой выборке достаём из DWH обновлённые или новые данные по мастерам, которые уже присутствуют в витрине dc.craftsman_id AS craftsman_id, dc.craftsman_name AS craftsman_name, dc.craftsman_address AS craftsman_address, dc.craftsman_birthday AS craftsman_birthday, dc.craftsman_email AS craftsman_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(customer_birthday)) as customer_age,-- напишите код здесь AS customer_age, (order_completion_date - order_created_date) AS diff_order_date,-- напишите код здесь AS diff_order_date, fo.order_status AS order_status, to_char(fo.order_created_date, 'yyyy-mm') AS report_period-- напишите код здесь 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 1,2,3,4,5,17-- напишите код здесь ) AS T2 INNER JOIN ( SELECT -- эта выборка поможет определить самый популярный товар у мастера. Выборка не делается в предыдущем запросе, так как нужна другая группировка. Для данных этой выборки можно применить оконную функцию, которая и покажет самую популярную категорию товаров у мастера dd.craftsman_id as craftsman_id_for_product_type,-- напишите код здесь AS craftsman_id_for_product_type, dd.product_type,-- напишите код здесь, count(product_id) as count_product-- напишите код здесь AS count_product FROM dwh_delta AS dd GROUP BY dd.craftsman_id, dd.product_type ORDER BY count_product DESC) AS T3 ON T2.craftsman_id = T3.craftsman_id_for_product_type ) AS T4 WHERE T4.rank_count_product = 1 ORDER BY report_period ), insert_delta AS ( -- Шаг 6 select craftsman_id,craftsman_name,craftsman_address,craftsman_birthday,craftsman_email,craftsman_money,platform_money,count_order,avg_price_order,avg_age_customer,median_time_order_completed,top_product_category,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 ( -- Шаг 7 select craftsman_id, craftsman_name, craftsman_address, craftsman_birthday, craftsman_email, craftsman_money, platform_money, count_order, avg_price_order, avg_age_customer, median_time_order_completed, top_product_category, count_order_created, count_order_in_progress, count_order_delivery, count_order_done, count_order_not_done, report_period from (SELECT craftsman_id, craftsman_name, craftsman_address, craftsman_birthday, craftsman_email, craftsman_money, platform_money, count_order, avg_price_order, avg_age_customer, median_time_order_completed, top_product_category, count_order_created, count_order_in_progress, count_order_delivery, count_order_done, count_order_not_done, report_period FROM dwh_delta_update_result) updates ), insert_load_date AS ( -- Шаг 8 select GREATEST(COALESCE(MAX(craftsman_load_dttm),max(customers_load_dttm),max(products_load_dttm),NOW())) FROM dwh_delta ) SELECT 'increment datamart';
Editor is loading...
Leave a Comment