[7]sql优化-datawarehouse.dws_cust_comm_proj_result
原始SQL 语句
insert overwrite datawarehouse.dws_cust_comm_proj_result
select c1.custId,c1.commId,c1.commTime,DATE_FORMAT(c1.commTime,'%Y-%m-%d'),DATE_FORMAT(c1.commTime,'%Y%m'),DATE_FORMAT(c1.commTime,'%Y'),c1.CommprojectId,c5.projectName,c6.projectId,c6.projectName,c1.commOwner,
c7.userName,c1.commprojIntention,c1.commContent,c1.comm_count,c1.rank1,c1.commId_max,c1.CommOwner_first,c8.userName,c1.CommTime_first,DATE_FORMAT(c1.CommTime_first,'%Y-%m-%d'),DATE_FORMAT(c1.CommTime_first,'%Y%m'),
DATE_FORMAT(c1.CommTime_first,'%Y'),c1.CommProjectId_first,c9.projectName,c10.projectId,c10.projectName,c1.CommContent_first,c1.CommprojIntention_first,c1.CommOwner_two,c11.userName,c1.CommTime_two,c1.CommProjectId_two,
c12.projectName,c13.projectId,c13.projectName,c1.CommContent_two,c1.CommprojIntention_two,c1.CommOwner_last,c14.userName,c1.CommTime_last,DATE_FORMAT(c1.CommTime_last,'%Y-%m-%d'),DATE_FORMAT(c1.CommTime_last,'%Y%m'),
DATE_FORMAT(c1.CommTime_last,'%Y'),c1.CommProjectId_last,c15.projectName,c16.projectId,c16.projectName,c1.CommContent_last,c1.CommprojIntention_last,c3.custSourcechannel,c17.originName,c18.originId,c18.originName,
c3.custCreateddate,DATE_FORMAT(c3.custCreateddate,'%Y-%m-%d'),DATE_FORMAT(c3.custCreateddate,'%Y%m'),DATE_FORMAT(c3.custCreateddate,'%Y'),c3.userId,c3.custAreacode,if(c19.dictName = '',null,c19.dictName),c3.custMobile,c3.custInitproject,
c20.projectName,c21.projectId,c21.projectName,c3.custType,c4.custprojSchool,c22.dptName,c23.dptId,c23.dptName,c4.custprojOwner,c24.userName,c3.custBirthday,c3.custCreator,c25.userName,c3.custEduLevel,
case c3.custEduLevel when 'B' THEN '小学' when 'J' THEN '初中' when 'H' THEN '高中' when 'S' THEN '中专' when 'A' THEN '专科' when 'C' THEN '本科' when 'M' THEN '硕士' when 'P' THEN '博士' when 'O' THEN '未知'
else null end as custEdulevelname,c3.custIdCard,c3.custWorkunit,c4.custProjgradestatus,c4.custprojintention,c3.custGender,c4.custprojSourcechannel,c26.originName,c27.originId,c27.originName,
case c4.custprojIntention when 'A' then 'A-强烈意向' when 'A1' then 'A1-首访未接' when 'B' then 'B-较强意向' when 'C' then 'C-一般意向' when 'D' then 'D-暂无意向'
when 'E' then 'E-已成单' when 'F' then 'F-暂未接通' when 'G' then 'G-无效号码' when 'H' then 'H-已报其它' when 'I' then 'I-明年考' when 'J' then 'J-一人多号'
when 'K' then 'K-空' when 'T' then 'T-条件不符' when 'EA' then 'E-A-意向强烈' when 'EB' then 'E-B-沟通顺畅' when 'EC' then 'E-c-沟通困难' when 'ED' then 'E-D-暂无意向'
when 'SA' then '服务已过期' when 'SB' then 'E-已交定金' when 'SC' then '已考过' when 'SD' then '笔试已考过' when 'SE' then '已顺延' when 'SF' then '已退费'
when 'SG' then '企业团培' else null end as custprojIntention_name,
c28.ab_demotion_1_month, c28.ab_demotion_3_month, c28.ab_demotion_6_month, c28.ab_demotion_1_year, c3.userId
from (select * ,
case when rank1=1 then commOwner else '' end as CommOwner_first,
case when rank1=1 then commTime else '' end as CommTime_first,
case when rank1=1 then CommprojectId else '' end as CommProjectId_first,
case when rank1=1 then commContent else '' end as CommContent_first,
case when rank1=1 then commprojIntention else '' end as CommprojIntention_first,
case when rank1=2 then commOwner else '' end as CommOwner_two,
case when rank1=2 then commTime else '' end as CommTime_two,
case when rank1=2 then CommprojectId else '' end as CommProjectId_two,
case when rank1=2 then commContent else '' end as CommContent_two,
case when rank1=2 then commprojIntention else '' end as CommprojIntention_two,
case when rank1 =comm_count then commOwner else '' end CommOwner_last,
case when rank1 =comm_count then commTime else '' end CommTime_last,
case when rank1 =comm_count then CommprojectId else '' end CommProjectId_last,
case when rank1 =comm_count then commContent else '' end CommContent_last,
case when rank1 =comm_count then commprojIntention else '' end CommprojIntention_last,
case when rank1 =comm_count then commId end commId_max
from (
select c1.custId,c1.commId,c1.commTime,c1.projectId as CommprojectId, c1.commOwner,c1.commprojIntention
,c2.commContent
,count(c2.commId) over(PARTITION BY c2.custId) as comm_count,
row_number() OVER(PARTITION BY c2.custId ORDER BY c2.commTime) AS rank1
from datawarehouse.dwd_ct_CtCommproj c1
join datawarehouse.dwd_ct_CtComm c2 on c1.commId =c2.commId
) aa) c1
join (select * from datawarehouse.dwd_ct_CtCust where custAvlstatus = 'Y' and custDelstatus = 'N') c3 on c1.custId =c3.custId
join (select custId,projectId,custprojOwner,custprojSchool,custProjgradestatus,custprojintention,custprojSourcechannel from datawarehouse.dwd_ct_CtCustindex where custprojDelstatus = 'N'
group by custId,projectId,custprojOwner,custprojSchool,custProjgradestatus,custprojintention,custprojSourcechannel) c4 on c1.custId =c4.custId and c1.CommprojectId = c4.projectId
LEFT JOIN datawarehouse.dwd_bd_BdProject c5
ON c1.CommprojectId = c5.projectId
LEFT JOIN datawarehouse.dwd_bd_BdProject c6
ON substring(c5.projectLevelcode,1,10) = c6.projectLevelcode
left join (select * from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G') c7
on c1.commOwner = c7.userId
left join (select * from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G') c8
on c1.CommOwner_first = c8.userId
LEFT JOIN datawarehouse.dwd_bd_BdProject c9
ON c1.CommProjectId_first = c9.projectId
LEFT JOIN datawarehouse.dwd_bd_BdProject c10
ON substring(c9.projectLevelcode,1,10) = c10.projectLevelcode
left join (select * from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G') c11
on c1.CommOwner_two = c11.userId
LEFT JOIN datawarehouse.dwd_bd_BdProject c12
ON c1.CommProjectId_two = c12.projectId
LEFT JOIN datawarehouse.dwd_bd_BdProject c13
ON substring(c12.projectLevelcode,1,10) = c13.projectLevelcode
left join (select * from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G') c14
on c1.CommOwner_last = c14.userId
LEFT JOIN datawarehouse.dwd_bd_BdProject c15
ON c1.CommProjectId_last = c15.projectId
LEFT JOIN datawarehouse.dwd_bd_BdProject c16
ON substring(c15.projectLevelcode,1,10) = c16.projectLevelcode
LEFT JOIN datawarehouse.dwd_es_BdOrigin c17
ON c3.custSourcechannel = c17.originId
LEFT JOIN datawarehouse.dwd_es_BdOrigin c18
ON substring(c17.originLevelcode,1,10) = c18.originLevelcode
left join datawarehouse.dwd_uc_UcDict c19 on c3.custAreacode = c19.dictCode
LEFT JOIN datawarehouse.dwd_bd_BdProject c20
ON c3.custInitproject = c20.projectId
LEFT JOIN datawarehouse.dwd_bd_BdProject c21
ON substring(c20.projectLevelcode,1,10) = c21.projectLevelcode
LEFT JOIN (select distinct dptRelationid,dptName,dptLevelcode from datawarehouse.dwd_bd_BdDpt where dptRelationid != '') c22
ON c4.custprojSchool = c22.dptRelationid
LEFT JOIN datawarehouse.dwd_bd_BdDpt c23 ON substring(c22.dptLevelcode,1,10) = c23.dptLevelcode
left join (select * from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G') c24
on c4.custprojOwner = c24.userId
left join (select * from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G') c25
on c3.custCreator = c25.userId
LEFT JOIN datawarehouse.dwd_es_BdOrigin c26
ON c4.custprojSourcechannel = c26.originId
LEFT JOIN datawarehouse.dwd_es_BdOrigin c27
ON substring(c26.originLevelcode,1,10) = c27.originLevelcode
left join datawarehouse.dws_cust_comm_ab_demotion c28 on c1.custId = c28.cust_id
;
最新 优化后SQL 语句
-- insert overwrite datawarehouse.dws_cust_comm_proj_result
select
c1.custId,
c1.commId,
c1.commTime,
null as commTime_yyyyMMdd, -- DATE_FORMAT(c1.commTime,'%Y-%m-%d'),
null as commTime_yyyyMM, -- DATE_FORMAT(c1.commTime,'%Y%m'),
null as commTime_yyyy, -- DATE_FORMAT(c1.commTime,'%Y'),
c1.CommprojectId,
c5.projectName,
c6.projectId,
c6.projectName,
c1.commOwner,
null as commOwner_name, -- c7.userName,
null as commprojIntention, -- c1.commprojIntention,
c1.commContent,
c1.comm_count,
null as rank1 , -- c1.rank1,
null as commId_max , -- c1.commId_max,
c1.CommOwner_first,
c8.userName,
null as CommTime_first, -- c1.CommTime_first,
null as CommTime_first_yyyyMMdd, -- DATE_FORMAT(c1.CommTime_first,'%Y-%m-%d'),
null as CommTime_first_yyyyMM, -- DATE_FORMAT(c1.CommTime_first,'%Y%m'),
null as CommTime_first_yyyy, -- DATE_FORMAT(c1.CommTime_first,'%Y'),
null as CommProjectId_first_2, -- c1.CommProjectId_first,
null as CommProjectName_first_2, -- c9.projectName,
null as CommProjectId_first_1, -- c10.projectId,
null as CommProjectName_first_1, -- c10.projectName,
null as CommContent_first, -- c1.CommContent_first,
null as CommprojIntention_first, -- c1.CommprojIntention_first,
null as CommOwner_two, -- c1.CommOwner_two,
null as CommOwner_name_two, -- c11.userName,
null as CommTime_two, -- c1.CommTime_two,
null as CommProjectId_two_2, -- c1.CommProjectId_two,
null as CommProjectName_two_2, -- c12.projectName,
null as CommProjectId_two_1, -- c13.projectId,
null as CommProjectName_two_1, -- c13.projectName,
null as CommContent_two, -- c1.CommContent_two,
null as CommprojIntention_two, -- c1.CommprojIntention_two,
null as CommOwner_last, -- c1.CommOwner_last,
c14.userName,
c1.CommTime_last,
null as CommTime_last_yyyyMMdd, -- DATE_FORMAT(c1.CommTime_last,'%Y-%m-%d'),
null as CommTime_last_yyyyMM, -- DATE_FORMAT(c1.CommTime_last,'%Y%m'),
null as CommTime_last_yyyy, -- DATE_FORMAT(c1.CommTime_last,'%Y'),
null as CommProjectId_last_2, -- c1.CommProjectId_last,
null as CommProjectName_last_2, -- c15.projectName,
null as CommProjectId_last_1, -- c16.projectId,
null as CommProjectName_last_1, -- c16.projectName,
null as CommContent_last, -- c1.CommContent_last,
null as CommprojIntention_last, -- c1.CommprojIntention_last,
c3.custSourcechannel,
c17.originName,
null as custSourcechannel_1, -- c18.originId,
null as custSourcechannel_name_1, -- c18.originName,
null as custCreateddate, -- c3.custCreateddate,
null as custCreateddate_yyyyMMdd, -- DATE_FORMAT(c3.custCreateddate,'%Y-%m-%d'),
null as custCreateddate_yyyyMM, -- DATE_FORMAT(c3.custCreateddate,'%Y%m'),
null as custCreateddate_yyyy, -- DATE_FORMAT(c3.custCreateddate,'%Y'),
null as userId, -- c3.userId,
null as custAreacode, -- c3.custAreacode,
null as custArea_name, -- if(c19.dictName = '',null,c19.dictName),
null as custMobile, -- c3.custMobile,
null as custInitproject_id_2, -- c3.custInitproject,
null as custInitproject_name_2, -- c20.projectName,
null as custInitproject_id_1, -- c21.projectId,
null as custInitproject_name_1, -- c21.projectName,
null as custType, -- c3.custType,
c4.custprojSchool,
null as custprojSchool_name, -- c22.dptName,
null as custprojArea_id, -- c23.dptId,
null as custprojArea_name, -- c23.dptName,
null as custprojOwner, -- c4.custprojOwner,
null as custprojOwner_name, -- c24.userName,
null as custBirthday, -- c3.custBirthday,
null as custCreator , -- c3.custCreator,
null as custCreator_name, -- c25.userName,
null as custEduLevel, -- c3.custEduLevel,
null as custEdulevel_name, -- case c3.custEduLevel when 'B' THEN '小学' when 'J' THEN '初中' when 'H' THEN '高中' when 'S' THEN '中专' when 'A' THEN '专科' when 'C' THEN '本科' when 'M' THEN '硕士' when 'P' THEN '博士' when 'O' THEN '未知' else null end as custEdulevelname,
null as custIdCard , -- c3.custIdCard,
null as custWorkunit, -- c3.custWorkunit,
null as custProjgradestatus, -- c4.custProjgradestatus,
null as custprojintention, -- c4.custprojintention,
null as custGender, -- c3.custGender,
c4.custprojSourcechannel,
null as custprojSourcechannel_name_2, -- c26.originName,
null as custprojSourcechannel_1, -- c27.originId,
null as custprojSourcechannel_name_1, -- c27.originName,
case c4.custprojIntention when 'A' then 'A-强烈意向' when 'A1' then 'A1-首访未接' when 'B' then 'B-较强意向' when 'C' then 'C-一般意向'
when 'D' then 'D-暂无意向' when 'E' then 'E-已成单' when 'F' then 'F-暂未接通' when 'G' then 'G-无效号码' when 'H' then 'H-已报其它'
when 'I' then 'I-明年考' when 'J' then 'J-一人多号' when 'K' then 'K-空' when 'T' then 'T-条件不符' when 'EA' then 'E-A-意向强烈'
when 'EB' then 'E-B-沟通顺畅' when 'EC' then 'E-c-沟通困难' when 'ED' then 'E-D-暂无意向' when 'SA' then '服务已过期' when 'SB' then 'E-已交定金'
when 'SC' then '已考过' when 'SD' then '笔试已考过' when 'SE' then '已顺延' when 'SF' then '已退费' when 'SG' then '企业团培' else null end as custprojIntention_name,
c28.ab_demotion_1_month,
c28.ab_demotion_3_month,
c28.ab_demotion_6_month,
c28.ab_demotion_1_year,
c3.userId
from datawarehouse.dws_cust_comm_proj_info c1
join (
select custId, custSourcechannel, userId from datawarehouse.dwd_ct_CtCust where custAvlstatus = 'Y' and custDelstatus = 'N'
) c3 on c1.custId =c3.custId
join (
select custId,projectId,custprojSchool,custprojintention,
-- custprojOwner,custProjgradestatus,
custprojSourcechannel
from datawarehouse.dwd_ct_CtCustindex
where custprojDelstatus = 'N'
group by custId,projectId,custprojSchool,custprojintention,custprojSourcechannel
) c4 on c1.custId =c4.custId and c1.CommprojectId = c4.projectId
LEFT JOIN datawarehouse.dwd_bd_BdProject c5 ON c1.CommprojectId = c5.projectId
LEFT JOIN datawarehouse.dwd_bd_BdProject c6 ON substring(c5.projectLevelcode,1,10) = c6.projectLevelcode
-- left join ( select * from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G') c7 on c1.commOwner = c7.userId
left join (
select userId,userName from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G'
) c8 on c1.CommOwner_first = c8.userId
-- LEFT JOIN datawarehouse.dwd_bd_BdProject c9 ON c1.CommProjectId_first = c9.projectId
-- LEFT JOIN datawarehouse.dwd_bd_BdProject c10 ON substring(c9.projectLevelcode,1,10) = c10.projectLevelcode
-- left join (select * from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G' ) c11 on c1.CommOwner_two = c11.userId
-- LEFT JOIN datawarehouse.dwd_bd_BdProject c12 ON c1.CommProjectId_two = c12.projectId
-- LEFT JOIN datawarehouse.dwd_bd_BdProject c13 ON substring(c12.projectLevelcode,1,10) = c13.projectLevelcode
left join (
select userId, userName from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G'
) c14 on c1.CommOwner_last = c14.userId
-- LEFT JOIN datawarehouse.dwd_bd_BdProject c15 ON c1.CommProjectId_last = c15.projectId
-- LEFT JOIN datawarehouse.dwd_bd_BdProject c16 ON substring(c15.projectLevelcode,1,10) = c16.projectLevelcode
LEFT JOIN datawarehouse.dwd_es_BdOrigin c17 ON c3.custSourcechannel = c17.originId
-- LEFT JOIN datawarehouse.dwd_es_BdOrigin c18 ON substring(c17.originLevelcode,1,10) = c18.originLevelcode
-- left join datawarehouse.dwd_uc_UcDict c19 on c3.custAreacode = c19.dictCode
-- LEFT JOIN datawarehouse.dwd_bd_BdProject c20 ON c3.custInitproject = c20.projectId
-- LEFT JOIN datawarehouse.dwd_bd_BdProject c21 ON substring(c20.projectLevelcode,1,10) = c21.projectLevelcode
-- LEFT JOIN ( select distinct dptRelationid,dptName,dptLevelcode from datawarehouse.dwd_bd_BdDpt where dptRelationid != '' ) c22 ON c4.custprojSchool = c22.dptRelationid
-- LEFT JOIN datawarehouse.dwd_bd_BdDpt c23 ON substring(c22.dptLevelcode,1,10) = c23.dptLevelcode
-- left join ( select * from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G' ) c24 on c4.custprojOwner = c24.userId
-- left join ( select * from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and eeEhrstatus !='G' ) c25 on c3.custCreator = c25.userId
-- LEFT JOIN datawarehouse.dwd_es_BdOrigin c26 ON c4.custprojSourcechannel = c26.originId
-- LEFT JOIN datawarehouse.dwd_es_BdOrigin c27 ON substring(c26.originLevelcode,1,10) = c27.originLevelcode
left join datawarehouse.dws_cust_comm_ab_demotion c28 on c1.custId = c28.cust_id
;
-- --------------------------------
--
insert overwrite datawarehouse.dws_cust_comm_proj_info
select custId, commId , commTime, CommprojectId, commOwner, commContent, comm_count,
case when rank1=1 then commOwner else '' end as CommOwner_first,
-- case when rank1=1 then commTime else '' end as CommTime_first,
-- case when rank1=1 then CommprojectId else '' end as CommProjectId_first,
-- case when rank1=1 then commContent else '' end as CommContent_first,
-- case when rank1=1 then commprojIntention else '' end as CommprojIntention_first,
-- case when rank1=2 then commOwner else '' end as CommOwner_two,
-- case when rank1=2 then commTime else '' end as CommTime_two,
-- case when rank1=2 then CommprojectId else '' end as CommProjectId_two,
-- case when rank1=2 then commContent else '' end as CommContent_two,
-- case when rank1=2 then commprojIntention else '' end as CommprojIntention_two,
case when rank1 =comm_count then commOwner else '' end CommOwner_last,
case when rank1 =comm_count then commTime else '' end CommTime_last,
-- case when rank1 =comm_count then CommprojectId else '' end CommProjectId_last,
-- case when rank1 =comm_count then commContent else '' end CommContent_last,
-- case when rank1 =comm_count then commprojIntention else '' end CommprojIntention_last,
-- case when rank1 =comm_count then commId end commId_max,
cast(now() as string) as data_created_time
from (
select c1.custId,c1.commId,c1.commTime,c1.projectId as CommprojectId, c1.commOwner,c2.commContent ,
-- c1.commprojIntention ,
count(c2.commId) over(PARTITION BY c2.custId) as comm_count,
row_number() OVER(PARTITION BY c2.custId ORDER BY c2.commTime) AS rank1
from datawarehouse.dwd_ct_CtCommproj c1
join datawarehouse.dwd_ct_CtComm c2 on c1.commId =c2.commId
) aa
;
[8]sql优化-ads_fineReport.dws_clue_realtime_custindex
原始SQL 语句
-- 本节点类型适用于MySQL数据源
insert overwrite ads_fineReport.dws_clue_realtime_custindex
SELECT l.clueSourceid AS clueId,
l.clueCreateddate as clueCreateddate,
c.custId as cust_id,
l.userMobile,
ci.total AS recordCount,
ci.ciCreateddate as ciCreateddate,
ci.custprojIntention,
ci.custprojCommcontent,
case when ci.rank1 is null then 1 else ci.rank1 end rank1
FROM datawarehouse.dwd_ct_CtClue l
left JOIN datawarehouse.dwd_ct_CtCust c ON l.userMobile = c.custMobile AND c.custMobile <> ''
AND l.brandId = c.brandId
left JOIN
(SELECT ttt.cust_id,
ttt1.total,
ttt.ciCreateddate,
ttt.custprojIntention,
ttt.custprojCommcontent,
ttt.rank1
FROM
(SELECT t.cust_id,
t.comm_created_date AS ciCreateddate,
t.intention AS custprojIntention,
t.comm_content AS custprojCommcontent,
t.rank1
FROM
(SELECT m.custId AS cust_id, m.commCreateddate AS comm_created_date, m.intention, m.commContent AS comm_content, row_number() OVER(PARTITION BY m.custId ORDER BY m.commCreateddate) AS rank1
FROM (select a.commProjid, a.custId, a.projectId, a.commTime, b.commCreateddate, a.commProjcreateddate, a.commProjintention intention,
a.commProjdelstatus, a.commProjgradestatus, b.commId, b.commContent, b.commStatus, b.commOwner, b.commWay, b.commSchool
from datawarehouse.dwd_ct_CtCommproj a
left join datawarehouse.dwd_ct_CtComm b
on a.commId = b.commId) m
WHERE m.commCreateddate>=date_sub(now(), INTERVAL 90 DAY) AND m.commProjcreateddate>=date_sub(now(), INTERVAL 90 DAY)) t
WHERE t.rank1 in(1,2)
UNION ALL
SELECT tt.cust_id,
tt.comm_created_date AS ciCreateddate,
tt.intention AS custprojIntention,
tt.comm_content AS custprojCommcontent,
3 AS rank1
FROM
( SELECT m.custId AS cust_id, m.commCreateddate AS comm_created_date, m.intention, m.commContent AS comm_content, row_number() OVER(PARTITION BY m.custId ORDER BY m.commCreateddate desc) AS rank1
FROM (select a.commProjid, a.custId, a.projectId, a.commTime, b.commCreateddate, a.commProjcreateddate, a.commProjintention intention,
a.commProjdelstatus, a.commProjgradestatus, b.commId, b.commContent, b.commStatus, b.commOwner, b.commWay, b.commSchool
from datawarehouse.dwd_ct_CtCommproj a
left join datawarehouse.dwd_ct_CtComm b
on a.commId = b.commId) m
WHERE m.commCreateddate>=date_sub(now(), INTERVAL 90 DAY) AND m.commProjcreateddate>=date_sub(now(), INTERVAL 90 DAY)) tt
WHERE tt.rank1=1) ttt
LEFT JOIN
(SELECT count(*) AS total,
m.custId AS cust_id
FROM (select a.commProjid, a.custId, a.projectId, a.commTime, b.commCreateddate, a.commProjcreateddate, a.commProjintention intention,
a.commProjdelstatus, a.commProjgradestatus, b.commId, b.commContent, b.commStatus, b.commOwner, b.commWay, b.commSchool
from datawarehouse.dwd_ct_CtCommproj a
left join datawarehouse.dwd_ct_CtComm b
on a.commId = b.commId) m
WHERE m.commCreateddate>=date_sub(now(), INTERVAL 90 DAY) AND m.commProjcreateddate>=date_sub(now(), INTERVAL 90 DAY)
GROUP BY m.custId) ttt1 ON ttt.cust_id = ttt1.cust_id) ci ON ci.cust_id = c.custId
WHERE length(l.clueSourceid)>0
AND substr(l.clueCreateddate,1,4)>='2018' and l.clueSourcetime>=date_sub(now(), INTERVAL 90 DAY)
and l.clueMarketingtype <>'N'
and length(l.userMobile)>0
GROUP BY l.clueSourceid,
l.clueCreateddate ,
c.custId,
l.userMobile,
ci.total,
ci.ciCreateddate,
ci.custprojIntention,
ci.custprojCommcontent,
case when ci.rank1 is null then 1 else ci.rank1 end;
最新 优化后SQL 语句
-- insert overwrite ads_fineReport.dws_clue_realtime_custindex
SELECT
l.clueSourceid AS clueId,
l.clueCreateddate as clueCreateddate,
c.custId as cust_id,
l.userMobile,
ci.total AS recordCount,
ci.ciCreateddate as ciCreateddate,
ci.custprojIntention,
ci.custprojCommcontent,
case when ci.rank1 is null then 1 else ci.rank1 end rank1
FROM datawarehouse.dwd_ct_CtClue l
left JOIN datawarehouse.dwd_ct_CtCust c ON l.userMobile = c.custMobile AND l.brandId = c.brandId AND c.custMobile <> ''
left JOIN ads_fineReport.dws_cust_comm_interntion ci ON ci.custId = c.custId
WHERE length(l.clueSourceid) > 0 AND substr(l.clueCreateddate,1,4)>='2018' and l.clueSourcetime >= date_sub(now(), INTERVAL 90 DAY) and l.clueMarketingtype <>'N' and length(l.userMobile)>0
GROUP BY l.clueSourceid, l.clueCreateddate, c.custId, l.userMobile, ci.total, ci.ciCreateddate, ci.custprojIntention, ci.custprojCommcontent,
case when ci.rank1 is null then 1 else ci.rank1 end;
--
-- insert overwrite ads_fineReport.dws_cust_comm_interntion
SELECT ttt.custId, ttt1.total, ttt.ciCreateddate, ttt.custprojIntention, ttt.custprojCommcontent, ttt.rank1, cast(now() as string) as data_created_time
FROM (
SELECT t.custId, t.commCreateddate AS ciCreateddate, t.commProjintention AS custprojIntention, t.commContent AS custprojCommcontent, t.rank1
FROM (
select a.custId, b.commCreateddate, a.commProjintention, b.commId, b.commContent ,
row_number() OVER(PARTITION BY a.custId ORDER BY b.commCreateddate) AS rank1
from datawarehouse.dwd_ct_CtCommproj a
left join datawarehouse.dwd_ct_CtComm b on a.commId = b.commId
WHERE b.commCreateddate>=date_sub(now(), INTERVAL 90 DAY) AND a.commProjcreateddate>=date_sub(now(), INTERVAL 90 DAY)
) t
WHERE t.rank1 in(1,2)
UNION ALL
SELECT tt.custId, tt.commCreateddate AS ciCreateddate, tt.commProjintention AS custprojIntention, tt.commContent AS custprojCommcontent, 3 as rank1
FROM (
select a.custId, b.commCreateddate, a.commProjintention, b.commId, b.commContent ,
row_number() OVER(PARTITION BY a.custId ORDER BY b.commCreateddate desc) AS rank1
from datawarehouse.dwd_ct_CtCommproj a
left join datawarehouse.dwd_ct_CtComm b on a.commId = b.commId
WHERE b.commCreateddate>=date_sub(now(), INTERVAL 90 DAY) AND a.commProjcreateddate>=date_sub(now(), INTERVAL 90 DAY)
) tt
WHERE tt.rank1 = 1
) ttt
LEFT JOIN (
select a.custId, count(1) as total
from datawarehouse.dwd_ct_CtCommproj a
left join datawarehouse.dwd_ct_CtComm b on a.commId = b.commId
WHERE b.commCreateddate>=date_sub(now(), INTERVAL 90 DAY) AND a.commProjcreateddate>=date_sub(now(), INTERVAL 90 DAY)
group by a.custId
) ttt1 ON ttt.custId = ttt1.custId
浙公网安备 33010602011771号