SQLize Online / PHPize Online  /  SQLtest Online

-- справочник сотрудников DROP TABLE IF EXISTS tt_employee; CREATE TEMP TABLE tt_employee AS ( SELECT ef.user_ldap -- лдап сотрудника ,ef.pid ,concat(ef."name",' ',ef.name_i,' ',coalesce (ef.name_o,'')*/) AS user_name -- ФИО сотрудника ,CASE WHEN hist.name_appoint = 'специалист по продажам проектов онлайн' THEN 1 ELSE 0 END AS flaf_3_l ,hist.date_trans-- дата назначения ,hist.date_depart-- дата завершения работы в должности ,hist.name_appoint-- наименование должности ,hist.struct_name-- наименование подразделения ,hist.shop_num::integer -- номер магазина сотрудника ,ROW_NUMBER() OVER(PARTITION BY ef.user_ldap,hist.date_trans,hist.date_depart ORDER BY hist.update_dt DESC) AS rn FROM boss_marts.v_emp_find AS ef INNER JOIN boss_marts.v_appoint_hist AS hist ON ef.pid = hist.pid WHERE 1=1 AND ef.user_ldap IS NOT NULL AND work_code NOT IN ('15', '16', '17') -- исключаем временных сотрудников (изм. от 26.08.2024 PCPD-1659) AND hist.name_appoint ='продавец-консультант' ORDER BY user_ldap ); ANALYZE tt_employee; -- формируем-грузим справочник сделок DROP TABLE IF EXISTS tt_deals; CREATE temp TABLE tt_deals AS ( SELECT ro.object_id -- номер лида с которым связана сделка ,COALESCE (b.project_reference_id::text,b2.project_reference_id::text,b3.project_reference_id::text) AS project_reference_id -- !уникальный номер сделки! ,COALESCE (b.status,b2.status,b3.status) AS deal_status ,COALESCE (b.deal_type, b2.deal_type,b3.deal_type) AS deal_type -- тип сделки (уровень: водоснабжение, вх двери ...) ,COALESCE (b.turnover_full, b2.turnover_full,b3.turnover_full ) AS turnover_full -- ТО по сделке (общее) - факт ,b3.exclude_reason AS "Причина исключения" ,string_agg('https://clientprojects.leroymerlin.ru/presales/' || COALESCE (b.project_reference_id::text,b2.project_reference_id::text,b3.project_reference_id::text), ', ') AS deal_relations FROM ( SELECT object_id ,vro.project_reference_id -- string_agg('https://clientprojects.leroymerlin.ru/presales/' || vro.project_reference_id::TEXT, ', ') AS deal_relations FROM presales_project_reference_repository_ods.v_related_object vro -- !!!таблица свзей сделок и сущностей к ней привязанных!!! WHERE vro.object_type = 'LEAD' -- признак лида AND vro.is_actual = '1' GROUP BY object_id, vro.project_reference_id ) ro LEFT JOIN presales_project_all_marts.v_presale_deals b ON b.project_reference_id = ro.project_reference_id -- витрина сделок в2с LEFT JOIN presales_project_all_marts.v_presale_deals_b2b b2 ON b2.project_reference_id = ro.project_reference_id AND b2.combo_flag = 0 -- витрина сделок про LEFT JOIN presales_project_all_marts.v_excluded_deals b3 ON b3.project_reference_id = ro.project_reference_id -- витрина исключенных сделок WHERE COALESCE (b.project_reference_id::text, b2.project_reference_id::text, b3.project_reference_id::text) IS NOT NULL GROUP BY 1, 2, 3, 4, 5, 6 )distributed BY (object_id); analyze tt_deals; DROP TABLE IF EXISTS tt_last_lead; CREATE temp TABLE tt_last_lead AS ( SELECT a.initial_lead_id , a.lead_count, a.updatedlocal, a.status, a.object_id FROM ( SELECT initial_lead_id ,l.lead_count ,l.updatedlocal ,l.status ,l.object_id ,ROW_NUMBER() OVER(PARTITION BY l.initial_lead_id ORDER BY l.lead_count DESC) AS rn FROM presales_project_all_marts.v_leads_actual l -- витрина лидров WHERE l.initial_lead_id IS NOT NULL ) AS a WHERE a.rn = 1 )distributed BY (initial_lead_id); analyze tt_last_lead; DROP TABLE IF EXISTS last_lead; CREATE temp TABLE last_lead AS ( SELECT a.initial_lead_id ,a.lead_count ,a.updatedlocal AS updatedl ,a.status ,a.object_id ,d.project_reference_id ,d.deal_status ,d.deal_type ,d.turnover_full ,d.deal_relations FROM tt_last_lead a LEFT JOIN tt_deals d ON d.object_id = a.object_id )distributed BY (initial_lead_id); analyze last_lead; DROP TABLE IF EXISTS tmp_leads_work; CREATE TEMP TABLE tmp_leads_work AS ( SELECT la.object_id -- уникальный номер лила , "type" AS deal_type -- тип лида (вх двери, отопление) , CASE "type" WHEN 'CEILING' THEN 'Потолки' WHEN 'FOUNDATION' THEN 'Фундамент' WHEN 'ENTRANCE_DOOR' THEN 'Входные двери' WHEN 'PLASTER' THEN 'Штукатурка' WHEN 'FLOOR_SCREED' THEN 'Стяжка' WHEN 'FENCE' THEN 'Заборы' WHEN 'STORAGE' THEN 'Хранение' WHEN 'ROOF' THEN 'Кровля' WHEN 'ENGINEERING' THEN 'Инженерные системы' WHEN 'INTERIOR_DOOR' THEN 'Внутренние двери' WHEN 'STAIRCASE' THEN 'Лестницы' WHEN 'FACADE' THEN 'Фасады' WHEN 'TERRACE' THEN 'Террасы' WHEN 'B2B' THEN 'B2B' WHEN 'BATHROOM' THEN 'Ванные комнаты' WHEN 'DESIGN_CONSULTATION' THEN 'Дизайн консультации' WHEN 'DESIGN_PROJECT' THEN 'Дизайн-проект' WHEN 'KITCHEN' THEN 'Кухни' WHEN 'WINDOW' THEN 'Окна' END AS deal_type_rus, CASE WHEN storeid::integer = 1 THEN COALESCE(tbresp.shop_num , storeid::int) ELSE storeid::int END AS storeid, -- номер магазина лида timezone_name, la.status, -- статус лида channel, -- канал создания лида subchannel, -- подканал лила CASE WHEN la.status IN ('COMPLETED', 'CANCELLED', 'FAILED') THEN 1 ELSE 0 END AS finished, -- финальные статусы лидов --CASE WHEN la.status IN ('COMPLETED', 'CANCELLED', 'FAILED') THEN COALESCE(a.updatedl, la.updatedlocal) ELSE now()::timestamp END AS lead_end_time, --для расчета времени обработки лида --CASE WHEN la.status IN ('COMPLETED', 'CANCELLED', 'FAILED') THEN updatedlocal ELSE now()::timestampEND AS r_lead_end_time, --для расчета времени реакции на лид createdlocal, /* CASE WHEN date_part('hour', createdlocal) < 9 THEN createdlocal::date - '3 hour'::INTERVAL -- 21 ч пред. дня WHEN date_part('hour', createdlocal) >= 21 THEN createdlocal::date + '21 hour'::INTERVAL -- 21 ч тек. дня END AS created_night_start, --для расчета времени обработки лида CASE WHEN date_part('hour', createdlocal) < 9 THEN createdlocal::date + '9 hour'::INTERVAL -- 9 ч тек. дня WHEN date_part('hour', createdlocal) >= 21 THEN createdlocal::date + '1 day 9 hour'::INTERVAL -- 9 ч след. дня END AS created_night_end, --для расчета времени обработки лида updatedlocal, CASE WHEN date_part('hour', COALESCE(a.updatedl, la.updatedlocal)) < 9 THEN COALESCE(a.updatedl, la.updatedlocal)::date - '3 hour'::INTERVAL -- 21 ч пред. дня WHEN date_part('hour', COALESCE(a.updatedl, la.updatedlocal)) >= 21 THEN COALESCE(a.updatedl, la.updatedlocal)::date + '21 hour'::INTERVAL -- 21 ч тек. дня END AS updated_night_start, --для расчета времени обработки лида CASE WHEN date_part('hour', COALESCE(a.updatedl, la.updatedlocal)) < 9 THEN COALESCE(a.updatedl, la.updatedlocal)::date + '9 hour'::INTERVAL -- 9 ч тек. дня WHEN date_part('hour', COALESCE(a.updatedl, la.updatedlocal)) >= 21 THEN COALESCE(a.updatedl, la.updatedlocal)::date + '1 day 9 hour'::INTERVAL -- 9 ч след. дня END AS updated_night_end, --для расчета времени обработки лида CASE WHEN date_part('hour', la.createdlocal) < 9 THEN la.createdlocal::date - '3 hour'::INTERVAL -- 21 ч пред. дня WHEN date_part('hour', la.createdlocal) >= 21 THEN la.createdlocal::date + '21 hour'::INTERVAL -- 21 ч тек. дня END AS r_created_night_start, --для расчета времени реакции на лид CASE WHEN date_part('hour', la.createdlocal) < 9 THEN la.createdlocal::date + '9 hour'::INTERVAL -- 9 ч тек. дня WHEN date_part('hour', la.createdlocal) >= 21 THEN la.createdlocal::date + '1 day 9 hour'::INTERVAL -- 9 ч след. дня END AS r_created_night_end, --для расчета времени реакции на лид CASE WHEN date_part('hour', la.updatedlocal) < 9 THEN la.updatedlocal::date - '3 hour'::INTERVAL -- 21 ч пред. дня WHEN date_part('hour', la.updatedlocal) >= 21 THEN la.updatedlocal::date + '21 hour'::INTERVAL -- 21 ч тек. дня END AS r_updated_night_start, --для расчета времени реакции на лид CASE WHEN date_part('hour', la.updatedlocal) < 9 THEN la.updatedlocal::date + '9 hour'::INTERVAL -- 9 ч тек. дня WHEN date_part('hour', la.updatedlocal) >= 21 THEN la.updatedlocal::date + '1 day 9 hour'::INTERVAL -- 9 ч след. дня END AS r_updated_night_end, --для расчета времени реакции на лид CASE WHEN la.createdlocal>'2022-12-31 20:00:00' AND la.createdlocal <'2023-01-02 9:00:00' THEN '2023-01-02 9:00:00' ELSE la.createdlocal END AS create_newyear_start */ , la.status AS lead_status , la.cancel_reason , CASE WHEN la.createdlocal>'2022-12-31 20:00:00' AND la.createdlocal <'2023-01-02 9:00:00' THEN 1 ELSE 0 END AS NYchack, CASE WHEN la.status IN ('COMPLETED', 'CANCELLED', 'FAILED') AND COALESCE(a.updatedl, la.updatedlocal) <'2023-01-02 9:00:00' AND COALESCE(a.updatedl, la.updatedlocal) >'2022-12-31 20:00:00' THEN 1 ELSE 0 END AS NYupdate, tb.shop_num AS shopCreated -- номер магазина сотрудника, кот создаол лид , la.createdby -- кто создал лид , tb.user_name AS FIOCreated -- фио создателя лида , TB.struct_name AS structCreated -- все по создателю , tb.name_appoint AS jobCreated -- все по создателю , la.responsible AS responsible , tbresp.user_name AS FIOResp , tbresp.struct_name AS structResp -- все по ответственному( кто обработал лид ) , la."comment" , la.initial_lead_id , CASE WHEN la.lead_count IS NULL THEN 0 ELSE la.lead_count END AS lead_count_f --coalesce (a.project_reference_id::text,ro.project_reference_id::text) as tmp_project_reference_id -- номер сделки без учета исключений FROM presales_project_all_marts.v_leads_actual la -- витриан лидов LEFT JOIN tt_employee tb ON tb.user_ldap = la.createdby AND createdlocal >= tb.date_trans AND createdlocal<tb.date_depart AND tb.rn = 1 LEFT JOIN tt_employee tbResp ON tbresp.user_ldap = la.responsible AND createdlocal >= tbresp.date_trans AND createdlocal<tbresp.date_depart AND tbresp .rn = 1 LEFT JOIN last_lead a ON la.object_id = a.initial_lead_id WHERE COALESCE (la.regionid, 0) <> 9999 AND (COALESCE(createdby, '') <> COALESCE(responsible, '') OR (createdby IS NULL AND responsible IS NULL)) -- добавила условие, так как часть лидов приходит createdby = null и responsible = null /*and ( coalesce (tb.shop_num::int4,0) <> ANY(ARRAY[15,29,44,45,54,64,66,116,120,125,131,139,144,158,159,162,171,174,176,235,251,252,253,351,352,390,724,758,760, 780,824,829,850,906,908,912,922,1130,1131,2000,2002,2003,2004,2005,2006,2007,2008,2010,2011,2012,3000,9999]) or coalesce (storeid::int4,0) <> any(ARRAY[15,29,44,45,54,64,66,116,120,125,131,139,144,158,159,162,171,174,176,235,251,252,253,351,352,390,724,758,760, 780,824,829,850,906,908,912,922,1130,1131,2000,2002,2003,2004,2005,2006,2007,2008,2010,2011,2012,3000,9999]) )*/ --WHERE createdlocal >= now()::date - '1 day 3 hour'::INTERVAL --AND updatedlocal >= now()::date - '1 day'::interval ) distributed BY (object_id); analyze tmp_leads_work; --тест /* select * from tmp_leads_work where "Причина исключения" is not null where --tmp_project_reference_id = 'cccbb8be-5b40-4d13-b65e-4e157a9a88ea' initial_lead_id = '652169dbac8a5328d888d192' or object_id = '652169dbac8a5328d888d192' ; */ /* SELECT object_id,vro.project_reference_id --, -- string_agg('https://clientprojects.leroymerlin.ru/presales/' || vro.project_reference_id::TEXT, ', ') AS deal_relations FROM presales_project_reference_repository_ods.v_related_object vro WHERE vro.object_type = 'LEAD' AND vro.is_actual = '1' and object_id = '652169dbac8a5328d888d192' GROUP BY object_id,vro.project_reference_id */ /* УДАЛИТЬ ПОСЛЕ ТЕСТА */ --select * FROM presales_project_all_marts.v_leads_actual la /* insert into tmp_leads_work SELECT object_id, "type" AS deal_type, CASE "type" WHEN 'CEILING' then 'Потолки' WHEN 'FOUNDATION' then 'Фундамент' WHEN 'ENTRANCE_DOOR' then 'Входные двери' WHEN 'PLASTER' then 'Штукатурка' WHEN 'FLOOR_SCREED' then 'Стяжка' WHEN 'FENCE' then 'Заборы' WHEN 'STORAGE' then 'Хранение' WHEN 'ROOF' then 'Кровля' WHEN 'ENGINEERING' then 'Инженерные системы' WHEN 'INTERIOR_DOOR' then 'Внутренние двери' WHEN 'STAIRCASE' then 'Лестницы' WHEN 'FACADE' then 'Фасады' WHEN 'TERRACE' then 'Террасы' WHEN 'B2B' THEN 'B2B' WHEN 'BATHROOM' THEN 'Ванные комнаты' WHEN 'DESIGN_CONSULTATION' THEN 'Дизайн консультации' WHEN 'DESIGN_PROJECT' THEN 'Дизайн-проект' WHEN 'KITCHEN' THEN 'Кухни' WHEN 'WINDOW' THEN 'Окна' END AS deal_type_rus, case when storeid::integer=1 then coalesce(tbresp.shop_num ,storeid::int) else storeid::int end as storeid, timezone_name, status, channel, subchannel, CASE WHEN status IN ('COMPLETED','CANCELLED') THEN 1 ELSE 0 END AS finished, CASE WHEN status IN ('COMPLETED','CANCELLED') THEN updatedlocal ELSE now()::timestamp END AS lead_end_time, createdlocal, CASE WHEN date_part('hour', createdlocal) < 9 THEN createdlocal::date - '3 hour'::INTERVAL -- 21 ч пред. дня WHEN date_part('hour', createdlocal) >= 21 THEN createdlocal::date + '21 hour'::INTERVAL -- 21 ч тек. дня END AS created_night_start, CASE WHEN date_part('hour', createdlocal) < 9 THEN createdlocal::date + '9 hour'::INTERVAL -- 9 ч тек. дня WHEN date_part('hour', createdlocal) >= 21 THEN createdlocal::date + '1 day 9 hour'::INTERVAL -- 9 ч след. дня END AS created_night_end, updatedlocal, CASE WHEN date_part('hour', updatedlocal) < 9 THEN updatedlocal::date - '3 hour'::INTERVAL -- 21 ч пред. дня WHEN date_part('hour', updatedlocal) >= 21 THEN updatedlocal::date + '21 hour'::INTERVAL -- 21 ч тек. дня END AS updated_night_start, CASE WHEN date_part('hour', updatedlocal) < 9 THEN updatedlocal::date + '9 hour'::INTERVAL -- 9 ч тек. дня WHEN date_part('hour', updatedlocal) >= 21 THEN updatedlocal::date + '1 day 9 hour'::INTERVAL -- 9 ч след. дня END AS updated_night_end, case when la.createdlocal>'2022-12-31 20:00:00' and la.createdlocal <'2023-01-02 9:00:00' then '2023-01-02 9:00:00' else la.createdlocal end as create_newyear_start ,status as lead_status ,cancel_reason ,case when la.createdlocal>'2022-12-31 20:00:00' and la.createdlocal <'2023-01-02 9:00:00' then 1 else 0 end as NYchack, case when status IN ('COMPLETED','CANCELLED') and updatedlocal <'2023-01-02 9:00:00' and la.updatedlocal>'2022-12-31 20:00:00' then 1 else 0 end as NYupdate, tb.shop_num as shopCreated ,la.createdby , tb.user_name as FIOCreated,TB.struct_name as structCreated, -- все по создателю la.responsible as responsible,tbresp.user_name as FIOResp,tbresp.struct_name as structResp -- все по ответственному( кто обработал лид ) from tmp_test_ng la left join tt_employee tb on tb.user_ldap = la.createdby and createdlocal>=tb.date_trans and createdlocal<tb.date_depart and tb.rn=1 left join tt_employee tbResp on tbresp.user_ldap = la.responsible and createdlocal>=tbresp.date_trans and createdlocal<tbresp.date_depart and tbresp .rn=1 ; -- SELECT * FROM tmp_leads_work -- select * from tt_employee where user_ldap='60093186' */ DROP TABLE IF EXISTS tt_lead_reaction; CREATE TEMP TABLE tt_lead_reaction AS ( SELECT lw.object_id, lw.deal_type, lw.deal_type_rus, lw.storeid, lw.responsible, lw.fioresp, lw.structresp, lw.timezone_name, lw.status, lw.channel, lw.subchannel, lw.finished, lw.createdlocal, lw.updatedlocal, lw.lead_end_time, date_part('year', lw.createdlocal) AS "year", date_part('month', lw.createdlocal) AS "month", CASE WHEN nychack = 1 AND NYupdate = 1 THEN CASE WHEN lw.updatedlocal-lw.createdlocal<'30 min'::INTERVAL THEN lead_end_time - lw.createdlocal ELSE '30 min'::INTERVAL END WHEN nychack = 1 AND NYupdate = 0 THEN lead_end_time - create_newyear_start ELSE -- все остальное елс ) CASE WHEN created_night_start IS NULL THEN lead_end_time - lw.createdlocal -- обычный расклад (1). WHEN finished = 0 THEN lead_end_time - created_night_end -- если лид создан ночью и не завершен - Тогда берем текущее время - 9 утра 1-го рабочего дня . WHEN finished = 1 AND created_night_end = updated_night_end THEN CASE WHEN lead_end_time - lw.createdlocal < '30 min'::INTERVAL THEN lead_end_time - lw.createdlocal -- создан и закрыт ночью . ELSE '30 min'::INTERVAL END WHEN finished = 1 AND created_night_end <> updated_night_end THEN lead_end_time - created_night_end WHEN finished = 1 AND updated_night_end IS NULL THEN lead_end_time - created_night_end END END AS reaction_time, -- время обработки лида CASE WHEN nychack = 1 AND NYupdate = 1 THEN CASE WHEN lw.updatedlocal-lw.createdlocal<'30 min'::INTERVAL THEN r_lead_end_time - lw.createdlocal ELSE '30 min'::INTERVAL END WHEN nychack = 1 AND NYupdate = 0 THEN r_lead_end_time - create_newyear_start ELSE -- все остальное елс ) CASE WHEN created_night_start IS NULL THEN r_lead_end_time - lw.createdlocal -- обычный расклад (1). WHEN finished = 0 THEN lead_end_time - created_night_end -- если лид создан ночью и не завершен - Тогда берем текущее время - 9 утра 1-го рабочего дня . WHEN finished = 1 AND created_night_end = updated_night_end THEN CASE WHEN r_lead_end_time - lw.createdlocal < '30 min'::INTERVAL THEN r_lead_end_time - lw.createdlocal -- создан и закрыт ночью . ELSE '30 min'::INTERVAL END WHEN finished = 1 AND r_created_night_end <> r_updated_night_end THEN r_lead_end_time - r_created_night_end WHEN finished = 1 AND updated_night_end IS NULL THEN r_lead_end_time - r_created_night_end END END AS r_reaction_time, -- время реакции на лид COALESCE (a.deal_relations, d.deal_relations) AS deal_relations, COALESCE (a.deal_status , d.deal_status ) AS deal_status , COALESCE (a.status, lw.lead_status) AS lead_status , lw.shopcreated , lw.cancel_reason , lw.createdby , FIOcreated , structcreated , jobCreated , COALESCE ( a.project_reference_id::text, d.project_reference_id::text) AS project_reference_id, COALESCE (a.turnover_full , d.turnover_full ) AS turnover_full, lw."comment", d."Причина исключения", lw.initial_lead_id, lw.lead_count_f FROM tmp_leads_work lw LEFT JOIN last_lead a ON lw.object_id = a.initial_lead_id LEFT JOIN tt_deals d ON d.object_id = lw.object_id WHERE NOT (lw.createdlocal >= now()::date - '3 hour'::INTERVAL --созданные после 21:00 вчера AND finished = 0 -- и не завершены ) AND lw.storeid NOT IN (44, 45, 125, 131, 139, 144, 159, 162, 164, 166, 168, 174, 251, 252, 253, 254, 255, 256, 257, 263, 264, 352, 394, 395, 398, 850, 913, 921, 923, 924, 925) GROUP BY lw.object_id, lw.deal_type, lw.deal_type_rus, lw.storeid, lw.responsible, lw.fioresp, lw.structresp, lw.timezone_name, lw.status, lw.channel, lw.subchannel, lw.finished, lw.createdlocal, lw.updatedlocal, lw.lead_end_time, reaction_time, r_reaction_time, date_part('year', lw.createdlocal), date_part('month', lw.createdlocal) , 18 /*case*/ , COALESCE (a.deal_status, d.deal_status), COALESCE (a.status, lw.lead_status) , lw.shopcreated , lw.cancel_reason , lw.createdby , FIOcreated, structcreated, jobCreated, COALESCE(a.project_reference_id, d.project_reference_id), COALESCE (a.deal_relations, d.deal_relations), COALESCE (a.turnover_full , d.turnover_full ) , lw."comment", lw.initial_lead_id, lw.lead_count_f, d."Причина исключения" --having turnover_full >0 ) distributed BY (object_id); analyze tt_lead_reaction; --тест /* select * from tt_lead_reaction where "Причина исключения" is not null --where object_id = '64fdd5f4159ef5731c4e6ec2' ; --where initial_lead_id = '652169dbac8a5328d888d192' or object_id = '652169dbac8a5328d888d192' where initial_lead_id = '654a0b42fc87c81fbde07ab0' or object_id = '654a0b42fc87c81fbde07ab0'; */ --select * from presales_project_all_marts.v_presale_deals_b2b /* SELECT * FROM presales_project_all_marts.v_presale_deals vpd where vpd.project_reference_id in ('b8e6e254-2e1f-4d00-9209-1cd55d29fe6e') */ DROP TABLE IF EXISTS tmp_lead_itog; CREATE TEMP TABLE tmp_lead_itog AS ( SELECT object_id, deal_type_rus, deal_type, storeid, shopcreated, -- shopresp, channel, subchannel, finished, createdlocal, updatedlocal, lead_status , cancel_reason, deal_relations, deal_status, createdby , FIOcreated, structcreated , jobCreated , responsible, FIOresp, structresp, "comment", initial_lead_id, lead_count_f, r_reaction_time, "Причина исключения", sum(turnover_full) AS turnover_full, count(*) AS leads_count, sum(CASE WHEN deal_relations notnull THEN 1 ELSE 0 END ) AS deals, (avg(EXTRACT(epoch FROM reaction_time))/ 60)::int AS avg_reaction_time_min, (avg(EXTRACT(epoch FROM r_reaction_time))/ 60)::int AS r_avg_reaction_time_min FROM tt_lead_reaction GROUP BY object_id, deal_type_rus, deal_type, storeid, shopcreated, channel, subchannel, finished, createdlocal, updatedlocal, lead_status , cancel_reason, deal_relations, createdby , FIOcreated, structcreated , jobCreated, responsible, FIOresp, structresp, "comment", deal_status, initial_lead_id, lead_count_f, r_reaction_time, "Причина исключения" ) distributed BY (object_id); analyze tt_lead_reaction; --Статусная модель сделки, для определения статуса сделки руссофикатор . DROP TABLE IF EXISTS status_model; CREATE temp TABLE status_model(status text, status_rus text) distributed BY (status); INSERT INTO status_model VALUES ('NEW', 'Новая'); INSERT INTO status_model VALUES ('IN_WORK', 'В работе'); INSERT INTO status_model VALUES ('CALCULATION', 'Предрасчет'); INSERT INTO status_model VALUES ('MEASUREMENT', 'Замер'); INSERT INTO status_model VALUES ('PAPERS', 'Договор/ Подготовка документов'); INSERT INTO status_model VALUES ('APPROVAL', 'Согласование КП'); INSERT INTO status_model VALUES ('DEAL_IN_APPROVAL', 'Согласование КП'); INSERT INTO status_model VALUES ('PAYMENT', 'Передана для оплаты'); INSERT INTO status_model VALUES ('PAID', 'Оплачена'); INSERT INTO status_model VALUES ('ASSEMBLING', 'Монтаж/ Сопровождение'); INSERT INTO status_model VALUES ('DEAL_IN_WORK', 'Проект/ Выявление потребности'); INSERT INTO status_model VALUES ('ON_HOLD', 'Ожидание'); INSERT INTO status_model VALUES ('REGISTRATION', 'Оформление'); INSERT INTO status_model VALUES ('AFTER_SALES', 'Послепродажное обслуживание'); INSERT INTO status_model VALUES ('MANUFACTURING', 'Производство/поставка'); INSERT INTO status_model VALUES ('DEAL_COMPLETED', 'Завершена'); INSERT INTO status_model VALUES ('CANCELLED', 'Отменена'); INSERT INTO status_model VALUES ('FAILED', 'Потеряна'); INSERT INTO status_model VALUES ('REFUND', 'Полный возврат'); INSERT INTO status_model VALUES ('PAPERS_IN_WORK', 'Запрос в обратке'); INSERT INTO status_model VALUES ('DEAL_WITH_SOLUTION', 'Заказ'); INSERT INTO status_model VALUES ('PAPERS_COMPLETED', 'Завершена'); -- select * from status_model; DROP TABLE IF EXISTS tt_deals; DROP TABLE IF EXISTS tmp_leads_work; DROP TABLE IF EXISTS last_lead; DROP TABLE IF EXISTS fact; CREATE TEMP TABLE fact AS ( SELECT CASE WHEN lead_count_f = 0 THEN 'leads' ELSE 'lead_task' END AS category, createdlocal, updatedlocal, object_id, lead_status , deal_type_rus, deal_type, vds.hi_region_name, storeid, l.shopcreated , --l.shopresp, vds1.store || ' - ' || initcap(vds1.store_name) AS "Магазин источник лида", channel, subchannel, cancel_reason , deal_relations, s.status_rus AS deal_status_rus , --deal_status, createdby , FIOcreated, structcreated, jobCreated , responsible, FIOresp, structresp, "Причина исключения", "comment", initial_lead_id, lead_count_f, sum(leads_count) AS leads_count, sum(leads_count * finished) AS finished_leads_count, sum(avg_reaction_time_min * finished) AS finished_avg_reaction_time_min, sum(r_avg_reaction_time_min * finished) AS r_finished_avg_reaction_time_min, sum(leads_count *(1-finished)) AS wait_leads_count, sum(avg_reaction_time_min *(1-finished)) AS wait_avg_reaction_time_min, sum(deals) AS deals, sum(l.turnover_full) AS turnover FROM tmp_lead_itog l LEFT JOIN dds.v_dict_stores vds ON vds.store = l.storeid /* для номера магазина где ОБРАБОТАН лид*/ LEFT JOIN dds.v_dict_stores vds1 ON vds1.store = l.shopcreated /* для номера магазина где СОЗДАН лид*/ LEFT JOIN status_model s ON s.status = l.deal_status --where shop_num notnull GROUP BY object_id , createdlocal , updatedlocal , lead_status , deal_type_rus , shopcreated , -- l.shopresp , deal_type , vds.hi_region_name, vds1.store || ' - ' || initcap(vds1.store_name) , storeid , channel , subchannel , cancel_reason , deal_relations , createdby , FIOcreated , structcreated , jobCreated , responsible , FIOresp , structresp , "Причина исключения", "comment" , initial_lead_id, lead_count_f , s.status_rus --having sum(leads_count)>1 or sum(deals)>1 --ORDER BY vds.hi_region_name,storeid, deal_type ); -- select * from fact ; /* insert into fact SELECT 'cheki' as category, l.opened_date , null as updatedlocal, l.object_id, null as lead_status , l.deal_type_rus, l.deal_type, vds.hi_region_name, l.storeid, l.shopcreated::int , vds1.store|| ' - ' ||initcap( vds1.store_name) as "Магазин источник лида", l.channel, null as subchannel, null as cancel_reason , project_reference_id as deal_relations, l.createdby as createdby , l.FIOcreated as FIOcreated, l.structcreated as structcreated, l.responsible, l.FIOresp, l.structresp , null AS leads_count, null AS finished_leads_count, null AS finished_avg_reaction_time_min, null AS wait_leads_count, null AS wait_avg_reaction_time_min, null as deals, sum(coalesce(line_turnover,0)+coalesce(line_turnover_serv,0)) as turnover FROM tt_receipts l --where l.project_reference_id ='b8e6e254-2e1f-4d00-9209-1cd55d29fe6e' LEFT JOIN dds.v_dict_stores vds ON vds.store = l.storeid /* для номера магазина где ОБРАБОТАН лид*/ LEFT JOIN dds.v_dict_stores vds1 ON vds1.store = l.shopcreated::int /* для номера магазина где СОЗДАН лид*/ -- where l.project_reference_id ='b8e6e254-2e1f-4d00-9209-1cd55d29fe6e' GROUP BY l.opened_date , l.object_id, l.deal_type_rus, l.deal_type, vds.hi_region_name, l.storeid, l.shopcreated::int , vds1.store || ' - ' || initcap(vds1.store_name), l.channel, project_reference_id, l.createdby , l.FIOcreated , l.structcreated , l.responsible, l.FIOresp, l.structresp ; */ DROP TABLE IF EXISTS status_model; DROP TABLE IF EXISTS tmp_lead_itog; SELECT category, createdlocal, updatedlocal, object_id, lead_status , --deal_type_rus, deal_type, hi_region_name, storeid, shopcreated , --l.shopresp, "Магазин источник лида", --channel, -- subchannel, -- cancel_reason , deal_relations, deal_status_rus , --deal_status, createdby AS "LDAP создателя" , FIOcreated AS "ФИО создателя", structcreated AS "Отдел создателя", jobCreated AS "Должность создателя" , responsible AS "LDAP ответственного", FIOresp AS "ФИО ответственного", structresp AS "Отдел ответственного", "Причина исключения", "comment", leads_count, initial_lead_id, lead_count_f, --finished_leads_count, finished_avg_reaction_time_min, r_finished_avg_reaction_time_min, wait_leads_count, --wait_avg_reaction_time_min, deals, turnover, CASE WHEN lead_status = 'COMPLETED' THEN 'Создана сделка' WHEN lead_status = 'CANCELLED' THEN 'Отменен' WHEN lead_status = 'NEW' THEN 'Новый' WHEN lead_status = 'IN_PROGRESS' THEN 'В процессе' WHEN lead_status = 'DEAL_CREATING' THEN 'Создана сделка' WHEN lead_status = 'FAILED' THEN 'Недозвон' ELSE 'неизвестно' END AS status_rus, CASE WHEN subchannel = 'LEGAL' THEN 'ЮЛ' WHEN subchannel = 'HEATING' THEN 'Отопление' WHEN subchannel = 'DISCOUNTS' THEN 'Акции' WHEN subchannel = 'PROFI' THEN 'Профи' WHEN subchannel = 'ENGINEERING' THEN 'Инженерные системы' WHEN subchannel = 'DESIGN' THEN 'Дизайн' WHEN subchannel = 'BIZNES' THEN 'Бизнес' WHEN subchannel = 'WATER_FILTER' THEN 'Водяные фильтры' WHEN subchannel = 'INSIDELMWORK' THEN 'Приложение' WHEN subchannel = 'BATHROOM' THEN 'Ванные' WHEN subchannel = 'CONSTRUCTION' THEN 'Стройка' WHEN subchannel = 'STORAGE' THEN 'Хранение' WHEN subchannel = 'ELECTRICITY' THEN 'Электричество' WHEN subchannel = 'BUDGET_ESTIMATOR' THEN 'Бустер' WHEN subchannel = 'VENTILATION' THEN 'Вентиляция' WHEN subchannel = 'CARPENTRY' THEN 'Столярка' WHEN subchannel = 'B2B' THEN 'B2B' WHEN subchannel = 'WATER_SUPPLY' THEN 'Водоснабжение' WHEN subchannel = 'KITCHEN' THEN 'Кухни' WHEN subchannel = 'LANDING' THEN 'Лендинг' WHEN subchannel = 'CONTACT_CENTER' THEN 'Контактный центр' WHEN subchannel = 'MISSEDMEETING' THEN 'Пропущенная запись-ЭО' WHEN subchannel = 'PROJECTS' THEN 'Проекты' ELSE subchannel END AS "Саб-канал_rus", CASE WHEN cancel_reason = 'JOKE' THEN 'Шутка' WHEN cancel_reason = 'OTHER' THEN 'Другое' WHEN cancel_reason = 'NOT_INTERESTED' THEN 'Не интересно' WHEN cancel_reason = 'DOUBLE' THEN 'Дубль' WHEN cancel_reason = 'NO_RESPONSE' THEN 'Клиент не отвечает' WHEN cancel_reason = 'MISTAKE' THEN 'Ошибка' ELSE cancel_reason END AS "Причина отмены_рус", CASE WHEN channel = 'EQ' THEN 'Из мобильного приложения' WHEN channel = 'LANDING' THEN 'Лендинг' WHEN channel = 'PRESALE' THEN 'Из сделки' WHEN channel = 'CONFIGURATION' THEN 'Онлайн-Конфигурация' WHEN channel = 'MEASUREMENT' THEN 'Из замера' WHEN channel = 'SOCIAL' THEN 'Соц. сети' WHEN channel = 'GALLERY' THEN 'Из ГГР' WHEN channel = 'LK' THEN 'Личный кабинет' WHEN channel = 'STAGES' THEN 'Из этапа ремонта' WHEN channel = 'EQ' THEN 'ЭО не явившийся клиент' WHEN channel = 'MOBILE_APP' THEN 'Моб. приложение клиента' ELSE channel END AS "Канал_rus", CASE WHEN finished_avg_reaction_time_min = 0 THEN wait_avg_reaction_time_min ELSE finished_avg_reaction_time_min END AS "Время реакции лида", CASE WHEN r_finished_avg_reaction_time_min = 0 THEN wait_avg_reaction_time_min ELSE r_finished_avg_reaction_time_min END AS "Время реакции на лид (без повторных)", createdlocal::date AS "date", concat(createdby, '-', FIOcreated) AS ldap_fio_created , concat(responsible, '-', FIOresp) AS ldap_fio_responsble FROM fact WHERE storeid NOT IN (113) UNION SELECT category, createdlocal, updatedlocal, object_id, lead_status , --deal_type_rus, deal_type, -- тип лида в какой мир создан hi_region_name, --регион на который создан лид storeid, --магазин на который создан лид shopcreated , --l.shopresp, "Магазин источник лида", deal_relations, deal_status_rus , createdby AS "LDAP создателя" , FIOcreated AS "ФИО создателя", structcreated AS "Отдел создателя", jobCreated AS "Должность создателя" , responsible AS "LDAP ответственного", FIOresp AS "ФИО ответственного", structresp AS "Отдел ответственного", "Причина исключения", "comment", leads_count, initial_lead_id, lead_count_f, --finished_leads_count, finished_avg_reaction_time_min, r_finished_avg_reaction_time_min, wait_leads_count, --wait_avg_reaction_time_min, deals, turnover, CASE WHEN lead_status = 'COMPLETED' THEN 'Создана сделка' WHEN lead_status = 'CANCELLED' THEN 'Отменен' WHEN lead_status = 'NEW' THEN 'Новый' WHEN lead_status = 'IN_PROGRESS' THEN 'В процессе' WHEN lead_status = 'DEAL_CREATING' THEN 'Создана сделка' WHEN lead_status = 'FAILED' THEN 'Недозвон' ELSE 'неизвестно' END AS status_rus, CASE WHEN subchannel = 'LEGAL' THEN 'ЮЛ' WHEN subchannel = 'HEATING' THEN 'Отопление' WHEN subchannel = 'DISCOUNTS' THEN 'Акции' WHEN subchannel = 'PROFI' THEN 'Профи' WHEN subchannel = 'ENGINEERING' THEN 'Инженерные системы' WHEN subchannel = 'DESIGN' THEN 'Дизайн' WHEN subchannel = 'BIZNES' THEN 'Бизнес' WHEN subchannel = 'WATER_FILTER' THEN 'Водяные фильтры' WHEN subchannel = 'INSIDELMWORK' THEN 'Приложение' WHEN subchannel = 'BATHROOM' THEN 'Ванные' WHEN subchannel = 'CONSTRUCTION' THEN 'Стройка' WHEN subchannel = 'STORAGE' THEN 'Хранение' WHEN subchannel = 'ELECTRICITY' THEN 'Электричество' WHEN subchannel = 'BUDGET_ESTIMATOR' THEN 'Бустер' WHEN subchannel = 'VENTILATION' THEN 'Вентиляция' WHEN subchannel = 'CARPENTRY' THEN 'Столярка' WHEN subchannel = 'B2B' THEN 'B2B' WHEN subchannel = 'WATER_SUPPLY' THEN 'Водоснабжение' WHEN subchannel = 'KITCHEN' THEN 'Кухни' WHEN subchannel = 'LANDING' THEN 'Лендинг' WHEN subchannel = 'CONTACT_CENTER' THEN 'Контактный центр' WHEN subchannel = 'MISSEDMEETING' THEN 'Пропущенная запись-ЭО' WHEN subchannel = 'PROJECTS' THEN 'Проекты' ELSE subchannel END AS "Саб-канал_rus", CASE WHEN cancel_reason = 'JOKE' THEN 'Шутка' WHEN cancel_reason = 'OTHER' THEN 'Другое' WHEN cancel_reason = 'NOT_INTERESTED' THEN 'Не интересно' WHEN cancel_reason = 'DOUBLE' THEN 'Дубль' WHEN cancel_reason = 'NO_RESPONSE' THEN 'Клиент не отвечает' WHEN cancel_reason = 'MISTAKE' THEN 'Ошибка' ELSE cancel_reason END AS "Причина отмены_рус", CASE WHEN channel = 'EQ' THEN 'Из мобильного приложения' WHEN channel = 'LANDING' THEN 'Лендинг' WHEN channel = 'PRESALE' THEN 'Из сделки' WHEN channel = 'CONFIGURATION' THEN 'Онлайн-Конфигурация' WHEN channel = 'MEASUREMENT' THEN 'Из замера' WHEN channel = 'SOCIAL' THEN 'Соц. сети' WHEN channel = 'GALLERY' THEN 'Из ГГР' WHEN channel = 'LK' THEN 'Личный кабинет' WHEN channel = 'STAGES' THEN 'Из этапа ремонта' WHEN channel = 'EQ' THEN 'ЭО не явившийся клиент' WHEN channel = 'MOBILE_APP' THEN 'Моб. приложение клиента' ELSE channel END AS "Канал_rus", CASE WHEN finished_avg_reaction_time_min = 0 THEN wait_avg_reaction_time_min ELSE finished_avg_reaction_time_min END AS "Время реакции лида", CASE WHEN r_finished_avg_reaction_time_min = 0 THEN wait_avg_reaction_time_min ELSE r_finished_avg_reaction_time_min END AS "Время реакции на лид (без повторных)", createdlocal::date AS "date", concat(createdby, '-', FIOcreated) AS ldap_fio_created , concat(responsible, '-', FIOresp) AS ldap_fio_responsble FROM fact WHERE storeid = 113 AND deal_type NOT IN ('CEILING' , 'FOUNDATION' , 'ENTRANCE_DOOR' , 'PLASTER' , 'FLOOR_SCREED' , 'FENCE' , 'ROOF' , 'ENGINEERING', 'HEATING' , 'INTERIOR_DOOR' , 'STAIRCASE' , 'FACADE' , 'TERRACE' , 'DESIGN_CONSULTATION' , 'DESIGN_PROJECT' , 'WINDOW') AND createdlocal::date >= '2023-12-01';