StarRocks-性能优化（实战04）

[6]sql优化-ads_fineReport.new_cust_follow_up_analysis

原始SQL 语句

-- insert overwrite ads_fineReport.new_cust_follow_up_analysis
SELECT 
ct.cust_id,
ct.cust_created_date,
if(ori.one_origin_id is null,'-',ori.one_origin_id) as originid1,
if(ori.one_origin_name is null,'-',ori.one_origin_name) as originname1,
if(ori.two_origin_id is null,'-',ori.two_origin_id) as originid2,
if(ori.two_origin_name is null,'-',ori.two_origin_name) as originname2,
if(t2.comm_owner is null,'-',t2.comm_owner) as user_id_f,
if(t9.user_name is null,'-',t9.user_name) as user_name_f,
t2.comm_time as comm_time_f,
t4.clue_source_time as clue_source_time_t,
if(pro.two_project_id is null,'-',pro.two_project_id) as projectid2,
if(pro.two_project_name is null,'-',pro.two_project_name) as projectname2,
if(pro.one_project_id is null,'-',pro.one_project_id) as projectid1,
if(pro.one_project_name is null,'-',pro.one_project_name) as projectname1,
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,
t3.region_id , 
t3.region_name ,
if(t4.clue_result_owner is null,'-',t4.clue_result_owner) as user_id_t,
if(t10.user_name is null,'-',t10.user_name) as user_name_t,
if(t5.school_id is null,'-',t5.school_id) as school_id_t,
if(t5.schoolName is null,'-',t5.schoolName) as schoolName_t,
if(t5.area_id is null,'-',t5.area_id) as area_id_t,
if(t5.areaName is null,'-',t5.areaName) as areaName_t, 
t5.region_id ,
t5.region_name ,
if(t8.class_sale_status = 'Y','正价课业绩','非正价课业绩') as achieve_type,
t7.receipt_trade_date,
datediff(t7.receipt_trade_date,ct.cust_created_date) as day_diff,
t6.order_id,
t7.receipt_amount,
t7.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,
case when t6.ai_approval_status in ('PAS','PRE','PEN','PRO','PTP') and t6.ait_approval_node = 'SF' and t6.ait_approval_action = 'FOR' and t7.receipt_type = 'I' and t7.receipt_pay_status = 'PAI' then '正业绩' when t7.receipt_type = 'E' then '负业绩' else null end as receiptAmount_type,
if(t11.dpt_id is null, '-',t11.dpt_id) as dpt_id_f,if(t11.dpt_n1 is null,'-',t11.dpt_n1) as dpt_n1_f,if(t11.dpt_n2 is null,'-',t11.dpt_n2) as dpt_n2_f,
if(t12.dpt_id is null, '-',t12.dpt_id) as dpt_id_t,if(t12.dpt_n1 is null,'-',t12.dpt_n1) as dpt_n1_t,if(t12.dpt_n2 is null,'-',t12.dpt_n2) as dpt_n2_t,
t7.ro_owner_id as user_id_r,
if(t13.user_name is null,'-',t13.user_name) as user_name_r,
if(t15.school_id is null,'-',t15.school_id) as school_id_r,
if(t15.schoolName is null,'-',t15.schoolName) as schoolName_r,
if(t15.area_id is null,'-',t15.area_id) as area_id_r,
if(t15.areaName is null,'-',t15.areaName) as areaName_r,
t15.region_id ,t15.region_name ,
if(t14.dpt_id is null, '-',t14.dpt_id) as dpt_id_r,
if(t14.dpt_n1 is null,'-',t14.dpt_n1) as dpt_n1_r,
if(t14.dpt_n2 is null,'-',t14.dpt_n2) as dpt_n2_r,
cast(now() as string) as data_created_time

FROM (SELECT custId as cust_id,custCreateddate as cust_created_date,custSourceChannel as cust_source_channel,custInitProject as cust_init_project,userId as user_id
	FROM datawarehouse.dwd_ct_CtCust
	WHERE custAvlstatus = 'Y' AND custDelstatus = 'N' AND custCreateddate >= DATE_SUB(now(),INTERVAL 24 MONTH)
) ct
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.cust_source_channel = 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.cust_init_project = pro.two_project_id
LEFT JOIN (
    SELECT cust_id,comm_owner,comm_time,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 24 MONTH)
        ) r -- 沟通表
		WHERE commprojdelStatus = 'N' and commTime <> '2000-01-01') t
	WHERE t.rnk = 1
) t2 ON ct.cust_id = t2.cust_id
LEFT JOIN (
    select dbsa.school_id ,dbsa.school_name as schoolName,dbsa.area_id , dbsa.area_name as areaName , dbsa.region_id ,dbsa.region_name 
    from dws.dws_bd_SchoolArea dbsa
) t3 ON t2.comm_school = t3.school_id
LEFT JOIN (
    SELECT custId as cust_id,clueResultOwner as clue_result_owner,clueSourceTime as clue_source_time,clueResultSchool as clue_result_school
	FROM (
        SELECT custId,clueResultOwner,clueResultSchool,clueSourceTime,row_number() over(partition by custId order by clueSourceTime) as rnk
		FROM datawarehouse.dwd_ct_CtClue  -- 营销线索表
		WHERE clueDelStatus = 'N' and clueDistStatus = 'Y' and cluesourceTime <> '2000-01-01'
    ) t
	WHERE t.rnk = 1
) t4 ON ct.cust_id = t4.cust_id
LEFT JOIN (
    select dbsa.school_id ,dbsa.school_name as schoolName, dbsa.area_id , dbsa.area_name as areaName, dbsa.region_id ,dbsa.region_name from dws.dws_bd_SchoolArea dbsa
) t5 ON t4.clue_result_school = t5.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'
) t6 ON ct.user_id = t6.user_id
-- 增加收据所属信息：
LEFT JOIN (
    SELECT a.orderId as order_id,a.receiptTradeDate as receipt_trade_date,a.receiptAmount as receipt_amount,a.receiptType as receipt_type,a.receiptId as receipt_id,
		a.receiptPayStatus as receipt_pay_status,b.roOwnerId as ro_owner_id,b.roOwnerSchoolId as ro_owner_school_id
	FROM datawarehouse.dwd_oc_OcReceipt a
	LEFT JOIN datawarehouse.dwd_oc_OcReceiptownership b ON a.receiptId = b.receiptId
	WHERE receiptDelStatus = 'N' AND receiptRealStatus = 'Y' AND receiptType in ('I','E')
) t7 ON t6.order_id = t7.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'
) t8 ON t6.order_id = t8.order_id
LEFT JOIN (
    SELECT distinct user_id,user_name,dpt_id
	FROM (
        select a.userId as user_id, a.userName as user_name, b.dptId as dpt_id, b.dpteeRelation
		from datawarehouse.dwd_bd_BdEe a 
		left join datawarehouse.dwd_bd_BdDptee b on a.eeId = b.eeId
    ) r
WHERE dpteeRelation = 'P'
) t9 ON t2.comm_owner = t9.user_id
LEFT JOIN (
    SELECT distinct user_id,user_name,dpt_id
	FROM (
        select a.userId as user_id, a.userName as user_name, b.dptId as dpt_id, b.dpteeRelation
		from datawarehouse.dwd_bd_BdEe a 
		left join datawarehouse.dwd_bd_BdDptee b on a.eeId = b.eeId
    ) r
	WHERE dpteeRelation = 'P'
) t10 ON t4.clue_result_owner = t10.user_id
LEFT JOIN (
    select dbd.dpt_id , dbd.dpt_n1, dbd.dpt_n2 from dws.dws_bd_Dpt dbd
) t11 ON t9.dpt_id = t11.dpt_id
LEFT JOIN (
    select dbd.dpt_id , dbd.dpt_n1, dbd.dpt_n2 from dws.dws_bd_Dpt dbd
) t12 ON t10.dpt_id = t12.dpt_id
LEFT JOIN (
    SELECT distinct user_id,user_name,dpt_id
	FROM (
        select a.userId as user_id, a.userName as user_name, b.dptId as dpt_id, b.dpteeRelation
		from datawarehouse.dwd_bd_BdEe a 
		left join datawarehouse.dwd_bd_BdDptee b on a.eeId = b.eeId) r
		WHERE dpteeRelation = 'P'
) t13 ON t7.ro_owner_id = t13.user_id
LEFT JOIN (
    select dbd.dpt_id , dbd.dpt_n1, dbd.dpt_n2 from dws.dws_bd_Dpt dbd
) t14 ON t13.dpt_id = t14.dpt_id
LEFT JOIN (
    select dbsa.school_id , dbsa.school_name as schoolName ,dbsa.area_id ,dbsa.area_name as areaName ,dbsa.region_id ,dbsa.region_name from dws.dws_bd_SchoolArea dbsa
) t15 ON t7.ro_owner_school_id = t15.school_id
group by 
ct.cust_id,ct.cust_created_date,if(ori.one_origin_id is null,'-',ori.one_origin_id),if(ori.one_origin_name is null,'-',ori.one_origin_name),
if(ori.two_origin_id is null,'-',ori.two_origin_id),if(ori.two_origin_name is null,'-',ori.two_origin_name),if(t2.comm_owner is null,'-',t2.comm_owner),
if(t9.user_name is null,'-',t9.user_name),t2.comm_time,t4.clue_source_time,
if(pro.two_project_id is null,'-',pro.two_project_id),if(pro.two_project_name is null,'-',pro.two_project_name),
if(pro.one_project_id is null,'-',pro.one_project_id),if(pro.one_project_name is null,'-',pro.one_project_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),t3.region_id , t3.region_name ,
if(t4.clue_result_owner is null,'-',t4.clue_result_owner),if(t10.user_name is null,'-',t10.user_name),
if(t5.school_id is null,'-',t5.school_id),if(t5.schoolName is null,'-',t5.schoolName),if(t5.area_id is null,'-',t5.area_id),if(t5.areaName is null,'-',t5.areaName),t5.region_id ,t5.region_name, 
if(t8.class_sale_status = 'Y','正价课业绩','非正价课业绩'),t7.receipt_trade_date,
datediff(t7.receipt_trade_date,ct.cust_created_date),t6.order_id,t7.receipt_amount,t7.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,
case when t6.ai_approval_status in ('PAS','PRE','PEN','PRO','PTP') and t6.ait_approval_node = 'SF' and t6.ait_approval_action = 'FOR' and t7.receipt_type = 'I' and t7.receipt_pay_status = 'PAI' then '正业绩' when t7.receipt_type = 'E' then '负业绩' else null end,
if(t11.dpt_id is null, '-',t11.dpt_id),if(t11.dpt_n1 is null,'-',t11.dpt_n1),if(t11.dpt_n2 is null,'-',t11.dpt_n2),
if(t12.dpt_id is null, '-',t12.dpt_id),if(t12.dpt_n1 is null,'-',t12.dpt_n1),if(t12.dpt_n2 is null,'-',t12.dpt_n2),
t7.ro_owner_id,if(t13.user_name is null,'-',t13.user_name),
if(t15.school_id is null,'-',t15.school_id),if(t15.schoolName is null,'-',t15.schoolName),if(t15.area_id is null,'-',t15.area_id),if(t15.areaName is null,'-',t15.areaName),t15.region_id ,t15.region_name ,
if(t14.dpt_id is null, '-',t14.dpt_id),if(t14.dpt_n1 is null,'-',t14.dpt_n1),if(t14.dpt_n2 is null,'-',t14.dpt_n2),
cast(now() as string);

最新 优化后SQL 语句

insert overwrite ads_fineReport.new_cust_follow_up_analysis
SELECT 
ct.cust_id,
ct.cust_created_date,
if(ori.one_origin_id is null,'-',ori.one_origin_id) as originid1,
if(ori.one_origin_name is null,'-',ori.one_origin_name) as originname1,
if(ori.two_origin_id is null,'-',ori.two_origin_id) as originid2,
if(ori.two_origin_name is null,'-',ori.two_origin_name) as originname2,
t2.user_id_f,
t2.user_name_f,
t2.comm_time_f,
t4.clue_source_time as clue_source_time_t,
if(pro.two_project_id is null,'-',pro.two_project_id) as projectid2,
if(pro.two_project_name is null,'-',pro.two_project_name) as projectname2,
if(pro.one_project_id is null,'-',pro.one_project_id) as projectid1,
if(pro.one_project_name is null,'-',pro.one_project_name) as projectname1,
t2.school_id_f,
t2.schoolName_f,
t2.area_id_f,
t2.areaName_f,
t2.region_id_f , 
t2.region_name_f ,
if(t4.clue_result_owner is null,'-',t4.clue_result_owner) as user_id_t,
if(t10.user_name is null,'-',t10.user_name) as user_name_t,
if(t5.school_id is null,'-',t5.school_id) as school_id_t,
if(t5.schoolName is null,'-',t5.schoolName) as schoolName_t,
if(t5.area_id is null,'-',t5.area_id) as area_id_t,
if(t5.areaName is null,'-',t5.areaName) as areaName_t, 
t5.region_id ,
t5.region_name ,
if(t8.class_sale_status = 'Y','正价课业绩','非正价课业绩') as achieve_type,
t7.receipt_trade_date,
datediff(t7.receipt_trade_date,ct.cust_created_date) as day_diff,
t6.order_id,
t7.receipt_amount,
t7.receipt_id,
t2.comm_status,
t2.intention,
case when t6.ai_approval_status in ('PAS','PRE','PEN','PRO','PTP') and t6.ait_approval_node = 'SF' and t6.ait_approval_action = 'FOR' and t7.receipt_type = 'I' and t7.receipt_pay_status = 'PAI' then '正业绩' when t7.receipt_type = 'E' then '负业绩' else null end as receiptAmount_type,
t2.dpt_id_f,
t2.dpt_name1_f,
t2.dpt_name2_f,
if(t12.dpt_id is null, '-',t12.dpt_id) as dpt_id_t,if(t12.dpt_n1 is null,'-',t12.dpt_n1) as dpt_n1_t,if(t12.dpt_n2 is null,'-',t12.dpt_n2) as dpt_n2_t,
t7.ro_owner_id as user_id_r,
if(t13.user_name is null,'-',t13.user_name) as user_name_r,
if(t15.school_id is null,'-',t15.school_id) as school_id_r,
if(t15.schoolName is null,'-',t15.schoolName) as schoolName_r,
if(t15.area_id is null,'-',t15.area_id) as area_id_r,
if(t15.areaName is null,'-',t15.areaName) as areaName_r,
t15.region_id ,t15.region_name ,
if(t14.dpt_id is null, '-',t14.dpt_id) as dpt_id_r,
if(t14.dpt_n1 is null,'-',t14.dpt_n1) as dpt_n1_r,
if(t14.dpt_n2 is null,'-',t14.dpt_n2) as dpt_n2_r,
cast(now() as string) as data_created_time
FROM (SELECT custId as cust_id,custCreateddate as cust_created_date,custSourceChannel as cust_source_channel,custInitProject as cust_init_project,userId as user_id
	FROM datawarehouse.dwd_ct_CtCust
	WHERE custAvlstatus = 'Y' AND custDelstatus = 'N' AND custCreateddate >= DATE_SUB(now(),INTERVAL 24 MONTH)
) ct
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.cust_source_channel = 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.cust_init_project = pro.two_project_id
LEFT JOIN ads_fineReport.dws_cust_comm_first t2 on ct.cust_id = t2.cust_id
LEFT JOIN (
    SELECT custId as cust_id,clueResultOwner as clue_result_owner,clueSourceTime as clue_source_time,clueResultSchool as clue_result_school
	FROM (
        SELECT custId,clueResultOwner,clueResultSchool,clueSourceTime,row_number() over(partition by custId order by clueSourceTime) as rnk
		FROM datawarehouse.dwd_ct_CtClue  -- 营销线索表
		WHERE clueDelStatus = 'N' and clueDistStatus = 'Y' and cluesourceTime <> '2000-01-01'
    ) t
	WHERE t.rnk = 1
) t4 ON ct.cust_id = t4.cust_id
LEFT JOIN (
    select dbsa.school_id ,dbsa.school_name as schoolName, dbsa.area_id , dbsa.area_name as areaName, dbsa.region_id ,dbsa.region_name from dws.dws_bd_SchoolArea dbsa
) t5 ON t4.clue_result_school = t5.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'
) t6 ON ct.user_id = t6.user_id
-- 增加收据所属信息：
LEFT JOIN (
    SELECT a.orderId as order_id,a.receiptTradeDate as receipt_trade_date,a.receiptAmount as receipt_amount,a.receiptType as receipt_type,a.receiptId as receipt_id,
		a.receiptPayStatus as receipt_pay_status,b.roOwnerId as ro_owner_id,b.roOwnerSchoolId as ro_owner_school_id
	FROM datawarehouse.dwd_oc_OcReceipt a
	LEFT JOIN datawarehouse.dwd_oc_OcReceiptownership b ON a.receiptId = b.receiptId
	WHERE receiptDelStatus = 'N' AND receiptRealStatus = 'Y' AND receiptType in ('I','E')
) t7 ON t6.order_id = t7.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'
) t8 ON t6.order_id = t8.order_id
LEFT JOIN (
    SELECT distinct user_id,user_name,dpt_id
	FROM (
        select a.userId as user_id, a.userName as user_name, b.dptId as dpt_id, b.dpteeRelation
		from datawarehouse.dwd_bd_BdEe a 
		left join datawarehouse.dwd_bd_BdDptee b on a.eeId = b.eeId
    ) r
	WHERE dpteeRelation = 'P'
) t10 ON t4.clue_result_owner = t10.user_id
LEFT JOIN (
    select dbd.dpt_id , dbd.dpt_n1, dbd.dpt_n2 from dws.dws_bd_Dpt dbd
) t12 ON t10.dpt_id = t12.dpt_id
LEFT JOIN (
    SELECT distinct user_id,user_name,dpt_id
	FROM (
        select a.userId as user_id, a.userName as user_name, b.dptId as dpt_id, b.dpteeRelation
		from datawarehouse.dwd_bd_BdEe a 
		left join datawarehouse.dwd_bd_BdDptee b on a.eeId = b.eeId) r
		WHERE dpteeRelation = 'P'
) t13 ON t7.ro_owner_id = t13.user_id
LEFT JOIN (
    select dbd.dpt_id , dbd.dpt_n1, dbd.dpt_n2 from dws.dws_bd_Dpt dbd
) t14 ON t13.dpt_id = t14.dpt_id
LEFT JOIN (
    select dbsa.school_id , dbsa.school_name as schoolName ,dbsa.area_id ,dbsa.area_name as areaName ,dbsa.region_id ,dbsa.region_name from dws.dws_bd_SchoolArea dbsa
) t15 ON t7.ro_owner_school_id = t15.school_id
group by 
ct.cust_id,ct.cust_created_date,if(ori.one_origin_id is null,'-',ori.one_origin_id),if(ori.one_origin_name is null,'-',ori.one_origin_name),
if(ori.two_origin_id is null,'-',ori.two_origin_id),if(ori.two_origin_name is null,'-',ori.two_origin_name),
t2.user_id_f, t2.user_name_f, t2.comm_time_f, t4.clue_source_time,
if(pro.two_project_id is null,'-',pro.two_project_id),if(pro.two_project_name is null,'-',pro.two_project_name),
if(pro.one_project_id is null,'-',pro.one_project_id),if(pro.one_project_name is null,'-',pro.one_project_name),
t2.school_id_f, t2.schoolName_f, t2.area_id_f, t2.areaName_f, t2.region_id_f , t2.region_name_f ,
if(t4.clue_result_owner is null,'-',t4.clue_result_owner),if(t10.user_name is null,'-',t10.user_name),
if(t5.school_id is null,'-',t5.school_id),if(t5.schoolName is null,'-',t5.schoolName),if(t5.area_id is null,'-',t5.area_id),if(t5.areaName is null,'-',t5.areaName),t5.region_id ,t5.region_name, 
if(t8.class_sale_status = 'Y','正价课业绩','非正价课业绩'),t7.receipt_trade_date,
datediff(t7.receipt_trade_date,ct.cust_created_date),t6.order_id,t7.receipt_amount,t7.receipt_id,
t2.comm_status, t2.intention,
case when t6.ai_approval_status in ('PAS','PRE','PEN','PRO','PTP') and t6.ait_approval_node = 'SF' and t6.ait_approval_action = 'FOR' and t7.receipt_type = 'I' and t7.receipt_pay_status = 'PAI' then '正业绩' when t7.receipt_type = 'E' then '负业绩' else null end,
t2.dpt_id_f, t2.dpt_name1_f, t2.dpt_name2_f,
if(t12.dpt_id is null, '-',t12.dpt_id),if(t12.dpt_n1 is null,'-',t12.dpt_n1),if(t12.dpt_n2 is null,'-',t12.dpt_n2),
t7.ro_owner_id,if(t13.user_name is null,'-',t13.user_name),
if(t15.school_id is null,'-',t15.school_id),if(t15.schoolName is null,'-',t15.schoolName),if(t15.area_id is null,'-',t15.area_id),if(t15.areaName is null,'-',t15.areaName),t15.region_id ,t15.region_name ,
if(t14.dpt_id is null, '-',t14.dpt_id),if(t14.dpt_n1 is null,'-',t14.dpt_n1),if(t14.dpt_n2 is null,'-',t14.dpt_n2),
cast(now() as string);

-- -----------------------------------
-- 
insert overwrite ads_fineReport.dws_cust_comm_first
select t1.cust_id , 
if(t1.comm_owner is null ,'-',t1.comm_owner) as user_id_f,
t1.comm_time as comm_time_f,
case when t1.comm_status in ('N','Y','A') then '正常沟通' when t1.comm_status in ('E') then '空号' when t1.comm_status in ('B','U','X') then '未接听秒挂'
	 when t1.comm_status in ('S') then '停机' else null end as comm_status,
case t1.intention when 'A1' then '首访未接' when 'A' then '意向强烈' when 'B' then '较强意向' when 'C' then '一般意向' else null end as intention,
if(t1_1.school_id is null,'-',t1_1.school_id) as school_id_f,
if(t1_1.school_name is null,'-',t1_1.school_name) as school_name_f,
if(t1_1.area_id is null,'-',t1_1.area_id) as area_id_f,
if(t1_1.area_name is null,'-',t1_1.area_name) as area_name_f,
if(t1_1.region_id is null,'-',t1_1.region_id) as region_id_f,
if(t1_1.region_name is null,'-',t1_1.region_name) as region_name_f,
if(t1_2.user_name is null,'-',t1_2.user_name) as user_name_f,
if(t1_2.dpt_id_two is null, '-',t1_2.dpt_id_two) as dpt_id_f,
if(t1_2.dpt_name_two is null,'-',t1_2.dpt_name_two) as dpt_n1_f,
if(t1_2.dpt_n2 is null,'-',t1_2.dpt_n2) as dpt_n2_f,
cast(now() as string) as data_created_time
from (
	select cust_id, comm_owner, comm_time, comm_school, intention, comm_status from (
		select a.custId as cust_id, a.commTime as comm_time, a.commprojIntention as intention, b.commStatus as comm_status, b.commOwner as comm_owner, b.commSchool as comm_school ,
		row_number() over(partition by a.custId order by a.commTime) as rnk
		from datawarehouse.dwd_ct_CtCommproj a 
		join datawarehouse.dwd_ct_CtComm b on a.commId = b.commId
		where b.commDelstatus = 'N' and a.commprojDelstatus = 'N' 
		and a.commTime >= DATE_SUB(now(),INTERVAL 24 MONTH)
	) r where r.rnk = 1
) t1 
left join dws.dws_bd_SchoolArea t1_1 on t1.comm_school = t1_1.school_id 
left join dws.dws_bd_EeDpt t1_2 on t1.comm_owner = t1_2.user_id