StarRocks-性能优化【数据治理02】

[1]sql优化-ads_fineReport.ads_Student_service_analysis

原始SQL 语句

-- insert overwrite ads_fineReport.ads_Student_service_analysis                                                                                          
    select distinct  t1.*,dc.className,dc1.className, cast(now() as string) as data_created_time from (select dsc.studentId,du.userYoulunum ,dsc.userId ,cla.className,
dcc.custName,dsc.projectId ,pro.projectName,pro2.projectId, pro2.projectName ,dssp.dptId ,dd.dpt_n1,dd.dpt_n2,dssp.schoolId,
dbsa.school_name ,dbsa.area_id ,dbsa.area_name  as onedpt_name,dbsa.region_id ,dbsa.region_name ,ds.subjectId ,ds.subjectName ,dsc.studentclassYear ,
dsc.studentclassOpendate ,dsc.studentclassExpiredate ,
dssp.studentprojectLmuserid ,ee.userName ,dsc.studentclassOpenstatus ,dsc.studentclassBizsrctype ,
dsc.studentclassOrderno,dsc.studentclassCategory,doc.coType ,doc.coSubtype ,doa.agreementStatus , doa.agreementUserinfoconfirmstatus,
dsb.bill_no ,dsg.goods_name ,dsb.bill_post_status,dsb.bill_send_date,dsb.bill_apply_date ,
if(dsb.bill_class_id like '%,%',split_part(dsb.bill_class_id,',',1),dsb.bill_class_id ) as bill_class_id_one ,
split_part(dsb.bill_class_id,',',2) as bill_class_id_two

from datawarehouse.dwd_bd_BdStudentclass dsc  
left join datawarehouse.dwd_ss_SsStudentproject dssp on dsc.userId = dssp.studentUserid and dsc.projectId = dssp.projectId 
left join datawarehouse.dwd_bd_BdEe de on dssp.studentprojectLmuserid = de.userId and de.eeAvlstatus= 'Y' AND de.userId <> ''
left join datawarehouse.dwd_bd_BdDptee dde on dde.eeId = de.eeId  and dde.dptEerelation = 'P' AND dde.eeId <> ''
left join datawarehouse.dwd_uc_UcUser du on dsc.userId = du.userId AND du.userId <> ''
left join datawarehouse.dwd_bd_BdClass cla on dsc.classId = cla.classId AND cla.classId <> ''
LEFT JOIN (select dbd.dpt_id as dptId , dbd.dpt_n1, dbd.dpt_n2 from dws.dws_bd_Dpt dbd) dd 
ON dd.dptId = dde.dptId AND dd.dptId <> ''
left join dws.dws_bd_SchoolArea dbsa on dssp.schoolId = dbsa.school_id 
left join datawarehouse.dwd_bd_BdProject pro on dsc.projectId = pro.projectId AND pro.projectId <> ''
LEFT JOIN datawarehouse.dwd_bd_BdProject pro2 ON substring(pro.projectLevelcode,1,10) = pro2.projectLevelcode AND pro2.projectLevelcode <> ''
left join datawarehouse.dwd_bd_BdSubject ds on cla.subjectId = ds.subjectId AND ds.subjectId <> ''
left join datawarehouse.dwd_bd_BdEe ee on dssp.studentprojectLmuserid  = ee.userId AND ee.userId <> ''
left join datawarehouse.dwd_oc_OcOrder doo on doo.userId = dsc.userId and doo.orderNo = dsc.studentclassOrderno AND doo.userId <> '' AND doo.orderNo <> ''
left join datawarehouse.dwd_oc_OcAgreement doa on doo.orderId = doa.orderId 
left join datawarehouse.dwd_oc_OcClassorder doc on doc.orderId = doo.orderId AND doc.orderId <> ''
left join dwd.dwd_st_bill dsb on dsb.bill_apply_user = dsc.userId 
left join dwd.dwd_st_bill_dtl dsbd on dsb.bill_id = dsbd.bill_id 
left join dwd.dwd_st_goods dsg on dsbd.goods_id = dsg.goods_id 
left join datawarehouse.dwd_ct_CtCust dcc on dcc.userId = dsc.userId and dcc.custDelstatus = 'N' AND dcc.userId <> ''
 where dsc.studentclassBizsrctype = 'O' and dsc.studentclassOpenstatus in ('O','P') 
group by dsc.studentId ,du.userYoulunum ,dsc.userId ,cla.className,dcc.custName,dsc.projectId ,pro.projectName,
pro2.projectId, pro2.projectName ,dssp.dptId ,dd.dpt_n1,dd.dpt_n2,dssp.schoolId,
dbsa.school_name ,dbsa.area_id ,dbsa.area_name ,dbsa.region_id ,dbsa.region_name  ,ds.subjectId ,ds.subjectName ,dsc.studentclassYear ,
dsc.studentclassOpendate ,dsc.studentclassExpiredate,
dssp.studentprojectLmuserid ,ee.userName ,dsc.studentclassOpenstatus ,dsc.studentclassBizsrctype,
dsc.studentclassOrderno ,dsc.studentclassCategory ,doc.coType ,doc.coSubtype ,doa.agreementStatus , doa.agreementUserinfoconfirmstatus,
dsb.bill_no ,dsg.goods_name ,dsb.bill_post_status,dsb.bill_send_date,dsb.bill_apply_date ,
if(dsb.bill_class_id like '%,%',split_part(dsb.bill_class_id,',',1),dsb.bill_class_id ),
split_part(dsb.bill_class_id,',',2))t1 
left join datawarehouse.dwd_bd_BdClass dc on t1.bill_class_id_one = dc.classId AND dc.classId <> ''
left join datawarehouse.dwd_bd_BdClass dc1 on t1.bill_class_id_two = dc1.classId AND dc1.classId <> '';

[2]sql优化-ads_fineReport.ads_employee_intention

原始SQL 语句

insert overwrite ads_fineReport.ads_employee_intention                                                                                          
    select distinct  dcci.custId ,t1.custId as gencustId,dcci.custCreateddate ,dcci.brandId ,dcci.custprojDelstatus ,dcci.custType ,dcci.custprojCommtime ,
       dcci.custprojIntention ,dcci.projectId as oneprojectId ,pro.projectName as oneprojectName ,pro2.projectId ,pro2.projectName ,
       dcci.custprojSourcechannel ,ori.originName as oneoriginName 
      ,ori2.originId ,ori2.originName ,dcci.custprojOwner ,
      de.userName ,dde.dptId as usedptId ,case when t8_2.dpt_n1 is null  then '其他' else t8_2.dpt_n1 end as dpt_n1,
      case when t8_2.dpt_n2 is null  then '其他' else t8_2.dpt_n2 end as dpt_n2 ,dbsa.school_id ,dbsa.school_name as aadptName ,dbsa.area_id ,dbsa.area_name ,dbsa.region_id ,dbsa.region_name ,
      dcci.custprojCommid , case when tp.status is null and dcci.custType = 'S' then '非正价课' else tp.status end as status ,cast(now() as string) as data_created_time
from 
datawarehouse.dwd_ct_CtCustindex dcci 
left join datawarehouse.dwd_bd_BdProject pro ON dcci.projectId = pro.projectId  and pro.projectAvlstatus = 'Y' and pro.projectDelstatus = 'N'
left join datawarehouse.dwd_bd_BdProject pro2 on substring(pro.projectLevelcode,1,10)=pro2.projectLevelcode
left join datawarehouse.dwd_es_BdOrigin  ori on ori.originId  = dcci.custprojSourcechannel 
left join datawarehouse.dwd_es_BdOrigin ori2 on substring(ori.originLevelcode,1,10) = ori2.originLevelcode
left join datawarehouse.dwd_bd_BdEe de on de.userId  = dcci.custprojOwner 
left join datawarehouse.dwd_bd_BdDptee  dde on de.eeId = dde.eeId  and dde.dpteeRelation = 'P'
LEFT JOIN (select dbd.dpt_id as dptId , dbd.dpt_n1, dbd.dpt_n2 from dws.dws_bd_Dpt dbd) t8_2 ON t8_2.dptId = dde.dptId 
left join dws.dws_bd_SchoolArea dbsa on dbsa.school_id = dcci.custprojschool
left join (
    select distinct h.custId, aa.status from datawarehouse.dwd_oc_OcOrder  o
    join(
        select distinct oi.orderId ,'正价课' as status from datawarehouse.dwd_oc_OcOrderitem  oi 
        left join datawarehouse.dwd_bd_BdClass cl on oi.skuId = cl.classId
        left join datawarehouse.dwd_bd_BdClasstype cltype on cl.classtypeId = cltype.classtypeId
        where cltype.classtypeUsesourcetype = 'E'
    ) aa  on o.orderId =  aa.orderId
    join datawarehouse.dwd_oc_OcClassorder dooc on o.orderId = dooc.orderId 
    join datawarehouse.dwd_oc_CtCust  a on a.userId =o.userId  
    left join datawarehouse.dwd_ct_CtCust  h on a.custId = h.custId
    where  h.custType = 'S' and h.custDelstatus = 'N' 

    union all
    select distinct h.custId, aa.status from datawarehouse.dwd_oc_OcOrder  o
    join(
        select distinct oi.orderId ,'非正价课' as status from datawarehouse.dwd_oc_OcOrderitem  oi 
        left join datawarehouse.dwd_bd_BdClass cl on oi.skuId = cl.classId
        left join datawarehouse.dwd_bd_BdClasstype cltype on cl.classtypeId = cltype.classtypeId
        where cltype.classtypeUsesourcetype <> 'E'
    ) aa  on o.orderId =  aa.orderId
    join datawarehouse.dwd_oc_OcClassorder doc on o.orderId = doc.orderId 
    join datawarehouse.dwd_oc_CtCust  a on a.userId =o.userId  
    left join datawarehouse.dwd_ct_CtCust  h on a.custId = h.custId
    where  h.custType = 'S' and h.custDelstatus = 'N'  and h.custId not in (
        select distinct h.custId from datawarehouse.dwd_oc_OcOrder  o
        join(
            select distinct oi.orderId ,'正价课' as status from datawarehouse.dwd_oc_OcOrderitem  oi 
            left join datawarehouse.dwd_bd_BdClass cl on oi.skuId = cl.classId
            left join datawarehouse.dwd_bd_BdClasstype cltype on cl.classtypeId = cltype.classtypeId
            where cltype.classtypeUsesourcetype = 'E'
        ) aa  on o.orderId =  aa.orderId
        join datawarehouse.dwd_oc_OcClassorder dooc on o.orderId = dooc.orderId 
        join datawarehouse.dwd_oc_CtCust  a on a.userId =o.userId  
        left join datawarehouse.dwd_ct_CtCust  h on a.custId = h.custId
        where  h.custType = 'S' and h.custDelstatus = 'N' 
    )
) tp on tp.custId = dcci.custId 
left join (
    select distinct custId from datawarehouse.dwd_ct_CtCustindex where custType = 'C' 
    and custId not in (
        select distinct dccc.custId from datawarehouse.dwd_ct_CtComm dccc
    )
) t1 on t1.custId = dcci.custId
where dcci.custprojDelstatus = 'N' and dcci.custStatus = 'N' and dcci.custmarketingtype = 'T' and dcci.brandid = 'YOULU';

[3]sql优化-ads_fineReport.channel_extension_report

原始SQL 语句

-- insert overwrite ads_fineReport.channel_extension_report                                                                                          
SELECT 
t1.cust_id,
t1.cust_created_date,
substring(t1.cust_created_date,1,10) as analysis_date,
concat(substring(cust_created_date,12,2),':00') as analysis_hour,
if(t1.clue_es_ee_id is null,'未知',t1.clue_es_ee_id) as clue_es_ee_id,
if(t1.ee_name is null,'未知',t1.ee_name) as ee_name,
if(t1.clue_dpt_id is null,'未知',t1.clue_dpt_id) as clue_dpt_id,
if(t1.clue_dpt_name is null,'未知',t1.clue_dpt_name) as clue_dpt_name,
if(t8.user_name is null,'未知',t8.user_name) as user_name_f,
if(t3.school_id is null,'未知',t3.school_id) as school_id_f,
if(t3.schoolName is null,'未知',t3.schoolName) as schoolName_f,
if(t3.area_id is null,'未知',t3.area_id) as area_id_f,
if(t3.areaName is null,'未知',t3.areaName) as areaName_f,
if(t4.school_id is null,'未知',t4.school_id) as school_id_c,
if(t4.schoolName is null,'未知',t4.schoolName) as schoolName_c,
if(t4.area_id is null,'未知',t4.area_id) as area_id_c,
if(t4.areaName is null,'未知',t4.areaName) as areaName_c,
if(t1.originid1 is null,'未知',t1.originid1) as originid1,
if(t1.originid2 is null,'未知',t1.originid2) as originid2,
if(t1.projectid1 is null,'未知',t1.projectid1) as projectid1,
if(t1.projectid2 is null,'未知',t1.projectid2) as projectid2,
if(t1.originname1 is null,'未知',t1.originname1) as originname1,
if(t1.originname2 is null,'未知',t1.originname2) as originname2,
if(t1.projectname1 is null,'未知',t1.projectname1) as projectname1,
if(t1.projectname2 is null,'未知',t1.projectname2) as projectname2,
if(t7.class_sale_status = 'Y','正价课业绩','非正价课业绩') as achieve_type,
t6.receipt_created_date,
datediff(t6.receipt_created_date,t1.cust_created_date) as day_diff,
case when t5.ai_approval_status in ('PAS','PRE','PEN','PRO','PTP') and t5.ait_approval_node = 'SF' and t5.ait_approval_action = 'FOR' and t6.receipt_type = 'I' and t6.receipt_pay_status = 'PAI' then '正业绩' when t6.receipt_type = 'E' then '负业绩' else null end as receiptAmount_type,
t5.order_id,
t6.receipt_amount,
t6.receipt_id,
case when t2.comm_status in ('N','Y','A') then '正常沟通' when t2.comm_status in ('E') then '空号' when t2.comm_status in ('B','U','X') then '未接听秒挂' when t2.comm_status in ('S') then '停机' else null end as comm_status,
case when t2.intention = 'A1' then '首访未接' when t2.intention = 'A' then '意向强烈' when t2.intention = 'B' then '较强意向' when t2.intention = 'C' then '一般意向' else null end as  intention,
cast(now() as string) as data_created_time

FROM (
    SELECT ct.custId as cust_id,ct.userId as user_id,clue_info.clueEseeid as clue_es_ee_id,ee.userName as ee_name,clue_info.clueEsdptid as clue_dpt_id,dic.origindictName as clue_dpt_name,ct.custCreateddate as cust_created_date,
	ori.one_origin_id as originid1,ori.one_origin_name as originname1,ori.two_origin_id as originid2,ori.two_origin_name as originname2,pro.two_project_id as projectid2,pro.two_project_name as projectname2,
	pro.one_project_id as projectid1,pro.one_project_name as projectname1,c.custProjSchool as cust_proj_school
	FROM (
        SELECT * 
		FROM datawarehouse.dwd_ct_CtCust
		WHERE custAvlstatus = 'Y' AND custDelstatus = 'N'
		AND custCreateddate >= DATE_SUB(now(),INTERVAL 12 MONTH)
    ) ct  
	INNER JOIN datawarehouse.dwd_ct_CtCustproj c ON ct.custId = c.custId
	LEFT JOIN (
        select a.originId as two_origin_id,a.originName as two_origin_name,b.originId as one_origin_id,b.originName as one_origin_name
        from datawarehouse.dwd_es_BdOrigin a 
        left join datawarehouse.dwd_es_BdOrigin b on substring(a.originLevelcode,1,10) = b.originLevelcode
        group by a.originId,a.originName,b.originId,b.originName
    ) ori ON ct.custSourceChannel = ori.two_origin_id
    LEFT JOIN (
        select a.projectId as two_project_id,a.projectName as two_project_name,b.projectId as one_project_id,b.projectName as one_project_name
        from datawarehouse.dwd_bd_BdProject a 
        left join datawarehouse.dwd_bd_BdProject b on substring(a.projectLevelcode,1,10) = b.projectLevelcode
        group by a.projectId,a.projectName,b.projectId,b.projectName
    ) pro ON ct.custInitProject = pro.two_project_id
    LEFT JOIN (
        select custId as cust_id,clueSourceid as clue_source_id
        FROM (
            SELECT *,row_number() over(partition by custId order by clueSourcetime) as rnk
            FROM datawarehouse.dwd_ct_CtClue
            WHERE clueDelStatus = 'N' AND clueCategory = 'A' AND clueSourcetime >= DATE_SUB(now(),INTERVAL 12 MONTH)
        ) r
        WHERE rnk = 1
    ) clue ON ct.custId = clue.cust_id
    LEFT JOIN datawarehouse.dwd_es_clueeslog clue_info  ON clue.clue_source_id = clue_info.clueId
    LEFT JOIN (
        SELECT * 
        FROM datawarehouse.dwd_es_BdOrigindict
        WHERE origindictAvlstatus = 'Y' AND origindictDelstatus = 'N'
    ) dic ON clue_info.clueEsdptid = dic.origindictId
	LEFT JOIN (
        select a.eeId as ee_id, a.eeNo, a.userId, a.userName, a.schoolId, a.eePostype, b.dptId, b.posId, b.dpteeRelation, b.dpteeSourceid
		from datawarehouse.dwd_bd_BdEe a 
		left join datawarehouse.dwd_bd_BdDptee b on a.eeId = b.eeId
    ) ee ON clue_info.clueEseeid = ee.ee_id
) t1
LEFT JOIN (
    SELECT cust_id,comm_owner,comm_school,intention,comm_status
	FROM (
        SELECT custId as cust_id,commOwner as comm_owner,commSchool as comm_school,commStatus as comm_status,intention,commTime as comm_time,row_number() over(partition by custId order by commTime) as rnk
		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
			where a.commTime >= DATE_SUB(now(),INTERVAL 12 MONTH)
        ) r -- 沟通表
		WHERE commprojdelStatus = 'N' and commTime <> '2000-01-01'
    ) t WHERE t.rnk = 1
) t2 ON t1.cust_id = t2.cust_id
left join (
    select dbsa.school_id,dbsa.school_name as schoolName,dbsa.area_id ,dbsa.area_name as areaName from dws.dws_bd_SchoolArea dbsa
) t3 ON t2.comm_school = t3.school_id
left join (
    select dbsa.school_id,dbsa.school_name as schoolName,dbsa.area_id ,dbsa.area_name as areaName from dws.dws_bd_SchoolArea dbsa
) t4 ON t1.cust_proj_school = t4.school_id
LEFT JOIN (
    SELECT a.user_id,a.order_id,a.order_total_amount,a.ai_approval_status,b.aitApprovalnode as ait_approval_node,b.aitApprovalAction as ait_approval_action
	FROM (
        select o.orderId as order_id, o.userId as user_id, o.orderTotalamount*0.01 as order_total_amount, o.orderAvlStatus as order_avl_status,
			o.orderDelStatus as order_del_status, app.aiApprovalStatus as ai_approval_status, app.aiId as ai_id
		from datawarehouse.dwd_oc_OcOrder o
		left join datawarehouse.dwd_oc_OcApprovalinfo app on o.aiId = app.aiId
    ) a			
	LEFT JOIN datawarehouse.dwd_oc_OcApprovalitem b	ON a.ai_id = b.aiId
	WHERE a.user_id <> '' AND a.order_avl_status = 'Y' AND a.order_del_status = 'N' AND a.ai_approval_status IN ('PAS','PRE','PEN','PRO','PTP')
		AND b.aitApprovalNode = 'SF' AND b.aitApprovalAction = 'FOR'
) t5 ON t1.user_id = t5.user_id
LEFT JOIN (
    SELECT orderId as order_id,receiptTradedate as receipt_created_date,receiptAmount as receipt_amount,receiptType as receipt_type,receiptId as receipt_id,receiptPayStatus as receipt_pay_status
	FROM datawarehouse.dwd_oc_OcReceipt  -- 收据表
	WHERE receiptDelStatus = 'N' AND receiptRealStatus = 'Y' AND receiptType in ('I','E')
) t6 ON t5.order_id = t6.order_id
LEFT JOIN (
    SELECT  orderId as order_id,'Y' as class_sale_status
	FROM datawarehouse.dwd_oc_OcOrderitem a  -- 订单项表
	LEFT JOIN datawarehouse.dwd_bd_BdClass b  ON a.skuId = b.classId -- 班级表
	WHERE classAvlStatus = 'Y' AND classDelStatus = 'N'	AND classSaleStatus = 'Y'
) t7 ON t5.order_id = t7.order_id
LEFT JOIN (
    SELECT distinct user_id,user_name
	FROM (
        select a.userId as user_id, a.userName as user_name, b.dpteeRelation
		from datawarehouse.dwd_bd_BdEe a 
		left join datawarehouse.dwd_bd_BdDptee b on a.eeId = b.eeId
    ) r 
	WHERE dpteeRelation = 'P'
) t8 ON t2.comm_owner = t8.user_id
group by 
t1.cust_id,t1.cust_created_date,substring(t1.cust_created_date,1,10),concat(substring(cust_created_date,12,2),':00'),
if(t1.clue_es_ee_id is null,'未知',t1.clue_es_ee_id),if(t1.ee_name is null,'未知',t1.ee_name),if(t1.clue_dpt_id is null,'未知',t1.clue_dpt_id),
if(t1.clue_dpt_name is null,'未知',t1.clue_dpt_name),if(t8.user_name is null,'未知',t8.user_name),if(t3.school_id is null,'未知',t3.school_id),
if(t3.schoolName is null,'未知',t3.schoolName),if(t3.area_id is null,'未知',t3.area_id),if(t3.areaName is null,'未知',t3.areaName),
if(t4.school_id is null,'未知',t4.school_id),if(t4.schoolName is null,'未知',t4.schoolName),if(t4.area_id is null,'未知',t4.area_id),
if(t4.areaName is null,'未知',t4.areaName),if(t1.originid1 is null,'未知',t1.originid1),if(t1.originid2 is null,'未知',t1.originid2),
if(t1.projectid1 is null,'未知',t1.projectid1),if(t1.projectid2 is null,'未知',t1.projectid2),
if(t1.originname1 is null,'未知',t1.originname1),if(t1.originname2 is null,'未知',t1.originname2),if(t1.projectname1 is null,'未知',t1.projectname1),
if(t1.projectname2 is null,'未知',t1.projectname2),if(t7.class_sale_status = 'Y','正价课业绩','非正价课业绩'),
t6.receipt_created_date,datediff(t6.receipt_created_date,t1.cust_created_date),case when t5.ai_approval_status in ('PAS','PRE','PEN','PRO','PTP') and t5.ait_approval_node = 'SF' and t5.ait_approval_action = 'FOR' 
and t6.receipt_type = 'I' and t6.receipt_pay_status = 'PAI' then '正业绩' when t6.receipt_type = 'E' then '负业绩' else null end,
t5.order_id,t6.receipt_amount,t6.receipt_id,
case when t2.comm_status in ('N','Y','A') then '正常沟通' when t2.comm_status in ('E') then '空号' when t2.comm_status in ('B','U','X') then '未接听秒挂' when t2.comm_status in ('S') then '停机' else null end,
case when t2.intention = 'A1' then '首访未接' when t2.intention = 'A' then '意向强烈' when t2.intention = 'B' then '较强意向' when t2.intention = 'C' then '一般意向' else null end,cast(now() as string);

[4]sql优化-ads-fineReport.ads_operation_follow_analysis

原始SQL 语句

-- insert overwrite ads_fineReport.ads_operation_follow_analysis   
    SELECT a.custCreateddate,a.custId,a.projectid,pro2.projectName as custinitprojectname,pro2.projectLevelcode as custinitprojectlevelcode,pro3.projectId as Projectcollegeid,
pro3.projectName as Projectcollegename,pro3.projectLevelcode as Projectcollegelevelcode,comm.comm_time,a.custProjOwner as Custprojowner,ee.UserName,dd.dptId,dd.dpt_n1,dd.dpt_n2,
proj.custProjschool AS Custprojschool,dbsa.school_name as Custprojschoolname,dbsa.area_id as Custprojarea, dbsa.area_name as Custprojareaname,comm.comm_time ,a.custprojSourcechannel as custsourcechannel2,
ori.originName as custsourcechannelname2,ori2.originId as custsourcechannel,ori2.originName as custsourcechannelname,comm.commProjIntention,comm.commContent,comm.commStatus,
comm.commOwner,comm.userName,comm.schoolId,comm.school_name,t2.clueResultowner,t2.userName,t2.schoolId,t2.schoolname, cast(now() as string) as data_created_time
from(
    select commTime,projectid,custId,custCreateddate,custProjOwner,custDelstatus ,custprojSourcechannel
    from(
            select * ,row_number() over(partition by custId,projectid order by commTime desc) num
            from (
                    select b.commTime,c.projectid,c.custId,c.custCreateddate,c.custProjOwner,c.custDelstatus,c.custprojSourcechannel
                    from datawarehouse.dwd_ct_CtCommproj  b 
                    right join (
                        select h.custInitproject projectid,a.custId,a.custCreateddate,
                        a.custProjOwner,a.custProjDelstatus as custDelstatus,
                        h.custSourcechannel custprojSourcechannel 
                        from datawarehouse.dwd_ct_CtCustindex a
                        left join datawarehouse.dwd_ct_CtCust h on a.custId = h.custId AND h.custId <> ''
                        where a.custCreateddate >= date_add(now(), interval -2 month)
                    ) c 
                    on b.custId=c.custId and b.projectId=c.projectid
            )A 
    )as b
    where b.num = 1  
    group by commTime,projectid,custId,custCreateddate,custProjOwner,custDelstatus ,custprojSourcechannel
)a
left join datawarehouse.dwd_bd_BdProject pro2 on a.projectid=pro2.projectId AND pro2.projectId <> ''
left join datawarehouse.dwd_bd_BdProject pro3 on substring(pro2.projectLevelcode,1,10)=pro3.projectLevelcode AND pro3.projectLevelcode <> ''
left join datawarehouse.dwd_bd_BdEe ee on a.custProjOwner=ee.userId AND ee.userId <> ''
left join datawarehouse.dwd_bd_BdDptee dde on ee.eeId = dde.eeId and dde.dptEerelation = 'P'
LEFT JOIN (select dbd.dpt_id as dptId , dbd.dpt_n1, dbd.dpt_n2 from dws.dws_bd_Dpt dbd) dd 
ON dd.dptId = dde.dptId AND dd.dptId <> ''
left join datawarehouse.dwd_ct_CtCustproj proj on a.custId = proj.custId and a.projectid = proj.projectId AND proj.custId <> '' AND proj.projectId <> ''
left join dws.dws_bd_SchoolArea dbsa on proj.custProjschool = dbsa.school_id 
left join (
    select * from (
        select c.custId,b.projectId,c.custProjCommtime,b.commProjIntention,
            a.commContent,a.commStatus,b.commOwner,
            case when b.commTime is null or b.commTime = '' then c.custProjCommtime  
            else b.commTime end as comm_time,
            ee.userName, ee.schoolId,dpt.dptName school_name,
        row_number()over(partition by c.custId,b.projectId order by b.commTime) as num
        from (select * from datawarehouse.dwd_ct_CtCommproj where commTime is not null) b 
        join (select * from datawarehouse.dwd_ct_CtCustindex where custCreateddate >= date_add(now(), interval -2 month)) c on b.custId=c.custId and b.projectId=c.projectId
        left join datawarehouse.dwd_ct_CtComm a on a.commId = b.commId AND a.commId <> ''
        left join datawarehouse.dwd_bd_BdEe ee on b.commOwner=ee.userId AND ee.userId <> ''
        left join datawarehouse.dwd_bd_BdDpt dpt  on ee.schoolId = dpt.dptRelationid AND dpt.dptRelationid <> ''
    ) co
    where co.num=1
)comm on a.custId=comm.custId and a.projectid=comm.projectId AND comm.custId <> '' AND comm.projectId <> ''
left join datawarehouse.dwd_es_BdOrigin ori on a.custprojSourcechannel=ori.originId AND ori.originId <> ''
left join datawarehouse.dwd_es_BdOrigin ori2 on substring(ori.originLevelcode,1,10)=ori2.originLevelcode AND ori2.originLevelcode <> ''
left join (
    select f.custId,f.clueResultowner,ee.userName, ee.schoolId,dpt.dptName schoolname
    from(
        select custId,clueResultowner,row_number() over(partition by custId order by clueCreateddate asc) num
        from datawarehouse.dwd_ct_CtClue
    ) as f 
    left join datawarehouse.dwd_bd_BdEe ee on f.clueResultowner=ee.userId AND ee.userId <> ''
    left join datawarehouse.dwd_bd_BdDpt dpt  on ee.schoolId = dpt.dptRelationid AND dpt.dptRelationid <> ''
    where f.num =1
) t2 on a.custId = t2.custId AND t2.custId <> ''
where proj.custProjdelstatus='N'
group by a.custId,a.custCreateddate,a.projectid,pro2.projectName,pro2.projectLevelcode,pro3.projectId,pro3.projectName,pro3.projectLevelcode,comm.comm_time,a.custProjOwner,ee.UserName,dd.dptId,
        dd.dpt_n1,dd.dpt_n2,proj.custProjschool,dbsa.school_name ,dbsa.area_id ,dbsa.area_name ,comm.comm_time,a.custprojSourcechannel,ori.originName,ori2.originId,ori2.originName,comm.commProjIntention,
		comm.commContent,comm.commStatus,comm.commOwner,comm.userName, comm.schoolId,comm.school_name,t2.clueResultowner,t2.userName,t2.schoolId,t2.schoolname;

我是小昊

StarRocks-性能优化【数据治理02】

[1]sql优化-ads_fineReport.ads_Student_service_analysis

原始SQL 语句

最新优化后SQL 语句

[2]sql优化-ads_fineReport.ads_employee_intention

原始SQL 语句

最新优化后SQL 语句

[3]sql优化-ads_fineReport.channel_extension_report

原始SQL 语句

最新优化后SQL 语句

[4]sql优化-ads-fineReport.ads_operation_follow_analysis

原始SQL 语句

最新优化后SQL 语句

导航

我是小昊

StarRocks-性能优化【数据治理02】

[1]sql优化-ads_fineReport.ads_Student_service_analysis

原始SQL 语句

最新 优化后SQL 语句

[2]sql优化-ads_fineReport.ads_employee_intention

原始SQL 语句

最新 优化后SQL 语句

[3]sql优化-ads_fineReport.channel_extension_report

原始SQL 语句

最新 优化后SQL 语句

[4]sql优化-ads-fineReport.ads_operation_follow_analysis

原始SQL 语句

最新 优化后SQL 语句

导航

最新优化后SQL 语句

最新优化后SQL 语句

最新优化后SQL 语句

最新优化后SQL 语句