[5-1]sql优化-ads_fineReport.dws_achievement_base
原始SQL 语句
-- insert overwrite ads_fineReport.new_cust_follow_up_analysis
INSERT overwrite ads_fineReport.dws_achievement_base(
receipt_id,yl_num,cust_id,cust_create_date,cust_mobile,cust_name,cust_name_encode,user_name,school_id,school_name,area_id,area_name,region_id,region_name,
receipt_trade_date,project_name1,project_name2,order_no,receipt_no,co_type,class_type_name,old_class_type_name,class_id,class_name,
class_year,class_teach_method,if_restuday,if_refound,oi_amount,receipt_type,tradeno,system_source,achievement_type,student_type,
origin_name_one,agent_name,receipt_appr_status,receipt_pay_status,receipt_amount,a_owner_id,a_owner_user_id,dpt_id,dpt_n1,dpt_n2,origin_id1,
origin_name1,origin_id2,origin_name2,is_all,achieve_class_type,checked_status,final_checked_status,sf_checked_status,
account_name,ee_no,co_subtype,order_total_amount,order_pay_amount,college,major,major_type,edu_mode_type,receipt_real_status,
class_a_ser_type,project_id1,project_id2,order_achieve_type,order_source,order_memo,data_created_time)
SELECT DISTINCT receipt_id,yl_num,cust_id,cust_create_date,cust_mobile,cust_name,cust_name_encode,user_name,school_id,school_name,area_id,area_name,region_id,region_name,receipt_trade_date,project_name1,project_name2,order_no,receipt_no,co_type,
class_type_name,old_class_type_name,class_id,class_name,class_year,class_teach_method,max(if_restuday) over (PARTITION by receipt_id,class_id),max(if_refound) over (PARTITION by receipt_id,class_id),
max(oi_amount) over (PARTITION by receipt_id,class_id),receipt_type,max(tradeno) over (PARTITION by receipt_id, class_id),system_source,achievement_type,student_type,
origin_name_one,agent_name,receipt_appr_status,receipt_pay_status,receipt_amount,
a_owner_id,a_owner_user_id,dpt_id,dpt_n1,dpt_n2,origin_id1,origin_name1,origin_id2,origin_name2,is_all,
achieve_class_type,checked_status,final_checked_status,if(system_source <> '合作商' and final_checked_status = '审核通过',final_checked_status,sf_checked_status) as sf_checked_status,
account_name,ee_no,co_subtype,order_total_amount,order_pay_amount,college,major,major_type,edu_mode_type,
receipt_real_status,max(class_a_ser_type) over (PARTITION by receipt_id,class_id),project_id1,project_id2,order_achieve_type,order_source,order_memo,data_created_time
from (
select receipt_id,yl_num,cust_id,cust_create_date,cust_mobile,cust_name,cust_name_encode,user_name,school_id,school_name,area_id,area_name,region_id,region_name,receipt_trade_date,project_name1, project_name2,order_no,receipt_no,co_type,class_type_name,old_class_type_name,class_id,class_name,class_year,class_teach_method,if_restuday,
if_refound,oi_amount,receipt_type, tradeno,system_source,achievement_type,student_type,origin_name_one,agent_name,receipt_appr_status,receipt_pay_status,
receipt_amount,a_owner_id,a_owner_user_id,dpt_id,dpt_n1,dpt_n2, origin_id1,origin_name1,origin_id2,origin_name2,is_all,achieve_class_type,checked_status,final_checked_status,sf_checked_status,account_name,ee_no,co_subtype, order_total_amount,order_pay_amount,college,major,major_type,edu_mode_type,receipt_real_status,class_a_ser_type,project_id1,project_id2,order_achieve_type,order_source,order_memo,data_created_time
from ads_fineReport.dwd_achievement_base
union all
select a.receipt_id,a.yl_num,a.cust_id,a.cust_create_date,b.custMobile,a.cust_name,a.cust_name_encode,a.user_name,a.school_id,a.school_name,a.area_id,a.area_name,region_id,region_name,a.receipt_trade_date,a.project_name1,a.project_name2,
a.order_no,a.receipt_no,a.co_type,a.class_type_name,old_class_type_name,a.class_id,a.class_name,a.class_year,a.class_teach_method,a.if_restuday,a.if_refound,a.oi_amount,a.receipt_type,a.tradeno,a.system_source,
a.achievement_type,a.student_type,a.origin_name_one,a.agent_name,a.receipt_appr_status,a.receipt_pay_status,a.receipt_amount,a.a_owner_id,a.a_owner_user_id,a.dpt_id,a.dpt_n1,a.dpt_n2,a.origin_id1,a.origin_name1,
a.origin_id2,a.origin_name2,a.is_all,a.achieve_class_type,a.checked_status,a.final_checked_status,a.sf_checked_status,a.account_name,a.ee_no,a.co_subtype,a.order_total_amount,a.order_pay_amount,
a.college,a.major,a.major_type,edu_mode_type,a.receipt_real_status,a.class_a_ser_type,a.project_id1,a.project_id2,a.order_achieve_type,a.order_source,order_memo,a.data_created_time
from ads_fineReport.dwd_achievement_base_day a
left join (SELECT distinct custMobile from datawarehouse.dwd_ct_CtCust where custMobile <> '') b
on a.cust_mobile = b.custMobile) r
where receipt_amount < 1000000000.0
;
最新 优化后SQL 语句
INSERT overwrite ads_fineReport.dws_achievement_base(
receipt_id,yl_num,cust_id,cust_create_date,cust_mobile,cust_name,cust_name_encode,user_name,school_id,school_name,
area_id,area_name,region_id,region_name,receipt_trade_date,project_name1,project_name2,order_no,receipt_no,co_type,
class_type_name,old_class_type_name,class_id,class_name,class_year,class_teach_method,if_restuday,if_refound,oi_amount,receipt_type,
tradeno,system_source,achievement_type,student_type,origin_name_one,agent_name,receipt_appr_status,receipt_pay_status,receipt_amount,a_owner_id,
a_owner_user_id,dpt_id,dpt_n1,dpt_n2,origin_id1,origin_name1,origin_id2,origin_name2,is_all,achieve_class_type,
checked_status,final_checked_status,sf_checked_status,account_name,ee_no,co_subtype,order_total_amount,order_pay_amount,college,major,major_type,
edu_mode_type,receipt_real_status,class_a_ser_type,project_id1,project_id2,order_achieve_type,order_source,order_memo,data_created_time)
SELECT DISTINCT receipt_id,yl_num,cust_id,cust_create_date,cust_mobile,cust_name,cust_name_encode,user_name,school_id,school_name,area_id,area_name,region_id,region_name,receipt_trade_date,project_name1,project_name2,order_no,receipt_no,co_type,
class_type_name,old_class_type_name,class_id,class_name,class_year,class_teach_method,max(if_restuday) over (PARTITION by receipt_id,class_id),max(if_refound) over (PARTITION by receipt_id,class_id),
max(oi_amount) over (PARTITION by receipt_id,class_id),receipt_type,max(tradeno) over (PARTITION by receipt_id, class_id),system_source,achievement_type,student_type,
origin_name_one,agent_name,receipt_appr_status,receipt_pay_status,receipt_amount,
a_owner_id,a_owner_user_id,dpt_id,dpt_n1,dpt_n2,origin_id1,origin_name1,origin_id2,origin_name2,is_all,
achieve_class_type,checked_status,final_checked_status,if(system_source <> '合作商' and final_checked_status = '审核通过',final_checked_status,sf_checked_status) as sf_checked_status,
account_name,ee_no,co_subtype,order_total_amount,order_pay_amount,college,major,major_type,edu_mode_type,
receipt_real_status,max(class_a_ser_type) over (PARTITION by receipt_id,class_id),project_id1,project_id2,order_achieve_type,order_source,order_memo,data_created_time
from (
select receipt_id,yl_num,cust_id,cust_create_date,cust_mobile,cust_name,cust_name_encode,user_name,school_id,school_name,
area_id,area_name,region_id,region_name,receipt_trade_date,project_name1, project_name2,order_no,receipt_no,co_type,
class_type_name,old_class_type_name,class_id,class_name,class_year,class_teach_method,if_restuday,if_refound,oi_amount,receipt_type,
tradeno,system_source,achievement_type,student_type,origin_name_one,agent_name,receipt_appr_status,receipt_pay_status,receipt_amount,a_owner_id,
a_owner_user_id,dpt_id,dpt_n1,dpt_n2, origin_id1,origin_name1,origin_id2,origin_name2,is_all,achieve_class_type,
checked_status,final_checked_status,sf_checked_status,account_name,ee_no,co_subtype, order_total_amount,order_pay_amount,college,major,major_type,
edu_mode_type,receipt_real_status,class_a_ser_type,project_id1,project_id2,order_achieve_type,order_source,order_memo,data_created_time
from ads_fineReport.dwd_achievement_base
union all
select a.receipt_id,a.yl_num,a.cust_id,a.cust_create_date,a.cust_mobile ,a.cust_name,a.cust_name_encode,a.user_name,a.school_id,a.school_name,
a.area_id,a.area_name,region_id,region_name,a.receipt_trade_date,a.project_name1,a.project_name2,a.order_no,a.receipt_no,a.co_type,
a.class_type_name,old_class_type_name,a.class_id,a.class_name,a.class_year,a.class_teach_method,a.if_restuday,a.if_refound,a.oi_amount,a.receipt_type,
a.tradeno,a.system_source,a.achievement_type,a.student_type,a.origin_name_one,a.agent_name,a.receipt_appr_status,a.receipt_pay_status,a.receipt_amount,a.a_owner_id,
a.a_owner_user_id,a.dpt_id,a.dpt_n1,a.dpt_n2,a.origin_id1,a.origin_name1,a.origin_id2,a.origin_name2,a.is_all,a.achieve_class_type,
a.checked_status,a.final_checked_status,a.sf_checked_status,a.account_name,a.ee_no,a.co_subtype,a.order_total_amount,a.order_pay_amount,a.college,a.major,a.major_type,
edu_mode_type,a.receipt_real_status,a.class_a_ser_type,a.project_id1,a.project_id2,a.order_achieve_type,a.order_source,order_memo,a.data_created_time
from ads_fineReport.dwd_achievement_base_day a
) r where receipt_amount < 1000000000.0 ;
[5-2]sql优化-团培业绩信息
原始SQL 语句
SELECT distinct t1.receipt_id,null as yl_num,t4.cust_id,t4.cust_created_date,t4.custMobile,t4.custName,t4.custName_encode,t8.user_name,t9.school_id,t9.school_name,t9.area_id,t9.area_name,t9.region_id,t9.region_name,
t1.receipt_trade_date,t6_2.project_name as project_name1,t6_1.project_name as project_name2,t1.order_no,t1.receipt_no,
case when t1.co_type = 'GEN' then '正常订单' when t1.co_type = 'REP' then '重读订单' when t1.co_type = 'CHA' then '转班订单' when t1.co_type = 'TRA' then '转让订单' when t1.co_type = 'REF' then '退费订单'
when t1.co_type = 'DEF' then '顺延订单' when t1.co_type = 'GIV' then '赠课订单'when t1.co_type = 'OCHA' then '变更订单' else null end as co_type,t3.class_type_name,t3_1.oldclasstypeName as old_class_type_name,t1.sku_id as class_id,t2.class_name,t2.class_year,
case when t2.class_teach_method = 'N' then '网络' when t2.class_teach_method = 'F' then '面授' when t2.class_teach_method = 'M' then '混合' when t2.class_teach_method = 'L' then '直播' else null end as class_teach_method,
if(t10.contract_service_agreement = '12','是','否') as if_restuday,if(t10.contract_service_agreement = '13','是','否') as if_refound,t1.oi_amount,if(t1.receipt_type = 'I','收入','支出') as receipt_type,
t1.traderecord_channel_tradeno as tradeno,'企业客户' as system_source,'培训业绩' as achievement_type,'团培' as student_type,concat('企业客户-',t5_2.origin_name) as origin_name_one,null as agent_name,
case when t1.receipt_appr_status = 'PRE' then '待提交' when t1.receipt_appr_status = 'PEN' then '待审核' when t1.receipt_appr_status = 'PRO' then '审核中' when t1.receipt_appr_status = 'PAS' then '审核通过'
when t1.receipt_appr_status = 'FAI' then '审核失败' else null end as receipt_appr_status,if(t1.receipt_pay_status = 'PAI','已支付','未支付') as receipt_pay_status,t1.receipt_amount*1.0/100 as receipt_amount,
t8.ee_no as a_owner_id,t8.user_id as a_owner_user_id,t8_2.dpt_id,t8_2.dpt_n1,t8_2.dpt_n2,t5_2.origin_id as origin_id1,t5_2.origin_name as origin_name1,t5_1.origin_id as origin_id2,t5_1.origin_name as origin_name2,
case when t1.order_total_amount <= t1.receipt_amount then '全款收款' when t1.order_total_amount > t1.receipt_amount and t1.order_total_amount > 0 then '部分收款' end as is_all,
if(t2.class_sale_status = 'Y','正价课业绩','非正价课业绩') as achieve_class_type,
if(t1.receipt_appr_status = 'PAS' or (t11.order_check_status = 'Y' and t1.receipt_appr_status <> 'PAS'),'审核通过','审核未通过') as checked_status,
if(t1.receipt_appr_status = 'PAS','审核通过','审核中') as final_checked_status,
if(t12.ait_approval_date > t1.receipt_trade_date,'审核通过','审核中') as sf_checked_status,
t13.account_name,t14.ee_no,case when t1.co_subtype = 'FL' then '平转' when t1.co_subtype = 'UP' then '升转' when t1.co_subtype = 'DO' then '降转' when t1.co_subtype = 'MA' then '营销赠课'
when t1.co_subtype = 'TR' then '引流赠课' else null end as co_subtype,t1.order_total_amount*1.0/100 as order_total_amount,t1.order_pay_amount*1.0/100 as order_pay_amount,
null as college,null as major,null as major_type,null as edu_mode_type,t1.receipt_real_status,null as class_a_ser_type,t6_2.project_id as project_id1,t6_1.project_id as project_id2,null,null,t1.order_memo,
cast(now() as string) as data_created_time
FROM (SELECT d1.receipt_id,d1.receipt_trade_date,d1.receipt_no,d1.receipt_type,d1.traderecord_channel_tradeno,d1.receipt_pay_status,
d1.receipt_amount,d1.order_id,d2.user_id,d2.order_no,d5.project_id,d3.sku_id,d4.co_type,d3.oi_sum_amount as oi_amount,d2.order_total_amount,d2.order_pay_amount,d1.receipt_appr_status,d2.ai_id,
d1.account_code,d4.co_subtype,d1.receipt_real_status,d2.order_memo
FROM (
select receiptId as receipt_id, receiptTradedate as receipt_trade_date, receiptNo as receipt_no, receiptType as receipt_type,
traderecordChanneltradeno as traderecord_channel_tradeno, receiptPaystatus as receipt_pay_status, receiptAmount as receipt_amount,
orderId as order_id, receiptApprstatus as receipt_appr_status, accountCode as account_code, receiptRealstatus as receipt_real_status
from datawarehouse.dwd_tp_OcReceipt where substring(receiptTradedate,1,4) >= '2021' and receiptId != '1055410700752412672'
AND receiptDelstatus = 'N' and receiptAvlstatus = 'Y' and (receiptRealstatus != 'N' or receiptType != 'E')
) d1
INNER JOIN (select * from dwd.dwd_tp_order where order_del_status = 'N' and order_avl_status = 'Y' and order_business_type <> 'I') d2
ON d1.order_id = d2.order_id
LEFT JOIN (
select dtoo.orderId as order_id ,dtoo.oiId as oi_id,dtoo.skuId as sku_id,dtoo.oiSumamount as oi_sum_amount
from datawarehouse.dwd_tp_OcOrderitem dtoo where dtoo.oiAvlstatus = 'Y' and dtoo.oiDelstatus = 'N' and dtoo.oiMainstatus = 'Y' and dtoo.isPhysicsDel = 2
) d3 ON d1.order_id = d3.order_id
LEFT JOIN dwd.dwd_tp_classorder d4
ON d1.order_id = d4.order_id
LEFT JOIN(
select dtoc.orderId as order_id , dtoc.oiId as oi_id, dtoc.projectId as project_id
from datawarehouse.dwd_tp_OcClassorderitem dtoc where dtoc.isPhysicsDel = 2
) d5 ON d1.order_id = d5.order_id and d3.oi_id = d5.oi_id
UNION ALL
SELECT d1.receipt_id,d1.receipt_trade_date,d1.receipt_no,d1.receipt_type,d1.traderecord_channel_tradeno,d1.receipt_pay_status,
d1.receipt_amount,d1.order_id,d2.user_id,d2.order_related_orderno,d5.project_id,d3.sku_id,d4.co_type,d3.oi_sum_amount as oi_amount,d2.order_total_amount,d2.order_pay_amount,d1.receipt_appr_status,d2.ai_id,
d1.account_code,d4.co_subtype,d1.receipt_real_status,d2.order_memo
FROM (
select receiptId as receipt_id, receiptTradedate as receipt_trade_date, receiptNo as receipt_no, receiptType as receipt_type,
traderecordChanneltradeno as traderecord_channel_tradeno, receiptPaystatus as receipt_pay_status, receiptAmount as receipt_amount,
orderId as order_id, receiptApprstatus as receipt_appr_status, accountCode as account_code, receiptRealstatus as receipt_real_status
from datawarehouse.dwd_tp_OcReceipt where substring(receiptTradedate,1,4) >= '2021' AND receiptDelstatus = 'N' and receiptAvlstatus = 'Y'
and receiptRealstatus = 'N' and receiptType = 'E'
) d1
INNER JOIN (select * from dwd.dwd_tp_order where order_del_status = 'N' and order_avl_status = 'Y' and order_business_type <> 'I') d2
ON d1.order_id = d2.order_id
LEFT JOIN (
select dtoo.orderId as order_id ,dtoo.oiId as oi_id,dtoo.skuId as sku_id,dtoo.oiSumamount as oi_sum_amount
from datawarehouse.dwd_tp_OcOrderitem dtoo where dtoo.oiAvlstatus = 'Y' and dtoo.oiDelstatus = 'N' and dtoo.oiMainstatus = 'Y' and dtoo.isPhysicsDel = 2
) d3 ON d1.order_id = d3.order_id
LEFT JOIN dwd.dwd_tp_classorder d4
ON d1.order_id = d4.order_id
LEFT JOIN (
select dtoc.orderId as order_id , dtoc.oiId as oi_id, dtoc.projectId as project_id
from datawarehouse.dwd_tp_OcClassorderitem dtoc where dtoc.isPhysicsDel = 2
) d5 ON d1.order_id = d5.order_id and d3.oi_id = d5.oi_id
) t1
LEFT JOIN dwd.dwd_tp_class t2 ON t1.sku_id = t2.class_id
LEFT JOIN dwd.dwd_tp_class_type t3 ON t2.class_type_id = t3.class_type_id
LEFT JOIN datawarehouse.dwd_bd_BdOldclasstype t3_1 on t3.old_class_type_id = t3_1.oldclasstypeId
LEFT JOIN (
select a.custSourcechannel as cust_source_channel,a.userId as user_id, a.custId as cust_id, a.custCreateddate as cust_created_date, a.custName,a.custName_encode ,b.custMobile
from datawarehouse.dwd_tp_CtCust a
left join (SELECT distinct custMobile from datawarehouse.dwd_ct_CtCust where custMobile <> '') b
on a.custMobile = b.custMobile
) t4 ON t1.user_id = t4.user_id
LEFT JOIN dwd.dwd_tp_origin t5_1 ON t4.cust_source_channel = t5_1.origin_id
LEFT JOIN dwd.dwd_tp_origin t5_2 ON substring(t5_1.origin_level_code,1,10) = t5_2.origin_level_code
LEFT JOIN dwd.dwd_tp_project t6_1 ON t1.project_id = t6_1.project_id
LEFT JOIN dwd.dwd_tp_project t6_2 ON substring(t6_1.project_level_code,1,10) = t6_2.project_level_code
LEFT JOIN (
select dtor.receiptId as receipt_id, max(dtor.roOwnerid) as ro_owner_id,max(dtor.roOwnerschoolid) as ro_owner_school_id
from datawarehouse.dwd_tp_OcReceiptownership dtor where dtor.isPhysicsDel = 2
group by dtor.receiptId
) t7 ON t1.receipt_id = t7.receipt_id
LEFT JOIN dwd.dwd_tp_ee t8
ON t7.ro_owner_id = t8.user_id
LEFT JOIN (select * from dwd.dwd_tp_dpt_ee where dpt_ee_relation = 'P') t8_1 ON t8.ee_id = t8_1.ee_id
LEFT JOIN (select * from dws.dws_bd_Dpt a) t8_2
ON t8_2.dpt_id = t8_1.dpt_id
left join dws.dws_bd_SchoolArea_tp t9 on t7.ro_owner_school_id = t9.school_id
LEFT JOIN dwd.dwd_tp_contract t10
ON t1.order_id = t10.order_id
LEFT JOIN (select distinct ai.order_id,'Y' as order_check_status
from dwd.dwd_tp_order ai
join dwd.dwd_tp_approvalitem ait on ai.ai_id = ait.ai_id
where ait.ait_approval_node = 'SF'
and ait.ait_approval_action = 'FOR') t11 on t1.order_id = t11.order_id
LEFT JOIN (SELECT ai_id,max(ait_approval_date) as ait_approval_date FROM dwd.dwd_tp_approvalitem where ait_approval_node = 'SF'
and ait_approval_action = 'FOR' group by ai_id) t12 ON t1.ai_id = t12.ai_id
LEFT JOIN (SELECT * FROM dwd.dwd_tp_pc_account where account_avl_status = 'Y' and account_del_status = 'N') t13
ON t1.account_code = t13.account_code
LEFT JOIN (SELECT * FROM dwd.dwd_tp_ee where ee_avl_status = 'Y' and ee_del_status = 'N') t14 ON t7.ro_owner_id = t14.user_id
最新 优化后SQL 语句
SELECT distinct d1.receiptId as receipt_id, null as yl_num, t4.custId as cust_id, t4.custCreateddate as cust_created_date, t4.custMobile, t4.custName, t4.custName_encode, t8.user_name,
t9.school_id, t9.school_name, t9.area_id, t9.area_name, t9.region_id, t9.region_name, d1.receiptTradedate as receipt_trade_date, t6_2.project_name as project_name1,
t6_1.project_name as project_name2, d2.order_no, d1.receiptNo as receipt_no,
case d4.co_type when 'GEN' then '正常订单' when 'REP' then '重读订单' when 'CHA' then '转班订单' when 'TRA' then '转让订单' when 'REF' then '退费订单' when 'DEF' then '顺延订单' when 'GIV' then '赠课订单' when 'OCHA' then '变更订单' else null end as co_type,
t3.class_type_name, t3_1.oldclasstypeName as old_class_type_name, d3.skuId as class_id, t2.class_name, t2.class_year,
case t2.class_teach_method when 'N' then '网络' when 'F' then '面授' when 'M' then '混合' when 'L' then '直播' else null end as class_teach_method,
if(t10.contract_service_agreement = '12','是','否') as if_restuday, if(t10.contract_service_agreement = '13','是','否') as if_refound,
d3.oiSumamount as oi_amount, if(d1.receiptType = 'I','收入','支出') as receipt_type, d1.traderecordChanneltradeno as tradeno, '企业客户' as system_source,
'培训业绩' as achievement_type, '团培' as student_type, concat('企业客户-',t5_2.origin_name) as origin_name_one, null as agent_name,
case d1.receiptApprstatus when 'PRE' then '待提交' when 'PEN' then '待审核' when 'PRO' then '审核中' when 'PAS' then '审核通过' when 'FAI' then '审核失败' else null end as receipt_appr_status,
if(d1.receiptPaystatus = 'PAI','已支付','未支付') as receipt_pay_status, d1.receiptAmount*1.0/100 as receipt_amount, t8.ee_no as a_owner_id,
t8.user_id as a_owner_user_id, t8_2.dpt_id, t8_2.dpt_n1, t8_2.dpt_n2, t5_2.origin_id as origin_id1, t5_2.origin_name as origin_name1, t5_1.origin_id as origin_id2,t5_1.origin_name as origin_name2,
case when d2.order_total_amount <= d1.receiptAmount then '全款收款' when d2.order_total_amount > d1.receiptAmount and d2.order_total_amount > 0 then '部分收款' end as is_all,
if(t2.class_sale_status = 'Y','正价课业绩','非正价课业绩') as achieve_class_type, if(d1.receiptApprstatus = 'PAS' or (t11.order_check_status = 'Y' and d1.receiptApprstatus <> 'PAS'),'审核通过','审核未通过') as checked_status,
if(d1.receiptApprstatus = 'PAS','审核通过','审核中') as final_checked_status, if(t12.ait_approval_date > d1.receiptTradedate,'审核通过','审核中') as sf_checked_status, t13.account_name,t8.ee_no,
case d4.co_subtype when 'FL' then '平转' when 'UP' then '升转' when 'DO' then '降转' when 'MA' then '营销赠课' when 'TR' then '引流赠课' else null end as co_subtype,
d2.order_total_amount*1.0/100 as order_total_amount, d2.order_pay_amount*1.0/100 as order_pay_amount, null as college, null as major, null as major_type, null as edu_mode_type,
d1.receiptRealstatus as receipt_real_status, null as class_a_ser_type, t6_2.project_id as project_id1, t6_1.project_id as project_id2, null as order_achieve_type, null as order_source,
d2.order_memo, cast(now() as string) as data_created_time
from datawarehouse.dwd_tp_OcReceipt d1
join dwd.dwd_tp_order d2 on d1.orderId = d2.order_id
left join datawarehouse.dwd_tp_OcOrderitem d3 on d2.order_id = d3.orderId and d3.oiMainstatus = 'Y' and d3.oiAvlstatus = 'Y' and d3.oiDelstatus = 'N' and d3.isPhysicsDel = 2
left join dwd.dwd_tp_classorder d4 on d2.order_id = d4.order_id
left join datawarehouse.dwd_tp_OcClassorderitem d5 on d3.orderId = d5.orderId and d3.oiId = d5.oiId and d5.isPhysicsDel = 2
left join dwd.dwd_tp_class t2 on d3.skuId = t2.class_id
left join dwd.dwd_tp_class_type t3 on t2.class_type_id = t3.class_type_id
left join datawarehouse.dwd_bd_BdOldclasstype t3_1 on t3.old_class_type_id = t3_1.oldclasstypeId
left join datawarehouse.dwd_tp_CtCust t4 on d2.user_id = t4.userId
left join dwd.dwd_tp_origin t5_1 on t4.custSourcechannel = t5_1.origin_id
left join dwd.dwd_tp_origin t5_2 on substring(t5_1.origin_level_code,1,10) = t5_2.origin_level_code
left join dwd.dwd_tp_project t6_1 on d5.projectId = t6_1.project_id
left join dwd.dwd_tp_project t6_2 on substring(t6_1.project_level_code,1,10) = t6_2.project_level_code
left join datawarehouse.dwd_tp_OcReceiptownership t7 on d1.receiptId = t7.receiptId and t7.isPhysicsDel = 2
left join dwd.dwd_tp_ee t8 on t7.roOwnerid = t8.user_id
left join dwd.dwd_tp_dpt_ee t8_1 on t8.ee_id = t8_1.ee_id and t8_1.dpt_ee_relation = 'P'
left join dws.dws_bd_Dpt t8_2 on t8_1.dpt_id = t8_2.dpt_id
left join dws.dws_bd_SchoolArea_tp t9 on t7.roOwnerschoolid = t9.school_id
left join dwd.dwd_tp_contract t10 on d2.order_id = t10.order_id
left join (
select distinct ai.order_id,'Y' as order_check_status
from dwd.dwd_tp_order ai
join dwd.dwd_tp_approvalitem ait on ai.ai_id = ait.ai_id
where ait.ait_approval_node = 'SF' and ait.ait_approval_action = 'FOR'
) t11 on d2.order_id = t11.order_id
left join (
select ai_id,max(ait_approval_date) as ait_approval_date
from dwd.dwd_tp_approvalitem
where ait_approval_node = 'SF' and ait_approval_action = 'FOR'
group by ai_id
) t12 on d2.ai_id = t12.ai_id
left join dwd.dwd_tp_pc_account t13 on d1.accountCode = t13.account_code and receiptAvlstatus = 'Y' and receiptDelstatus = 'N'
where d1.receiptDelstatus = 'N' and d1.receiptAvlstatus = 'Y' and d1.receiptTradedate >= '2021-01-01'
and d2.order_del_status = 'N' and d2.order_avl_status = 'Y' and d2.order_business_type <> 'I'
[5-3]sql优化-订单中心业绩信息
原始SQL 语句
-- 订单中心
SELECT t1.receiptId,t10.userYoulunum,t4.custId,t4.custCreateddate,t4.custMobile,t4.custName,t4.custName_encode,t8.userName,t9.schoolId,t9.schoolName,t9.areaId,t9.areaName,t9.region_id,t9.region_name,
t1.receiptTradedate,if(t1.skuId = 'CLASS20220306210000000042','升学培训',t6.projectName) as project_name1,if(t1.skuId = 'CLASS20220306210000000042','成考培训',t6.projectName2) as project_name2,t1.orderNo,t1.receiptNo,
case t1.coType WHEN 'GEN' THEN '正常订单' WHEN 'REP' THEN '续学订单' WHEN 'CHA' THEN '转班订单' WHEN 'TRA' THEN '转让订单' WHEN 'REF' THEN '退费订单' WHEN 'DEF' THEN '顺延订单' WHEN 'GIV' THEN '赠课订单' WHEN 'DRO' THEN '休学订单' WHEN 'ACT' THEN '活动订单' WHEN 'MAT' THEN '资料订单' WHEN 'OPE' THEN '公开课' WHEN 'TRC' THEN '训练营订单' WHEN 'DRA' THEN '引流商品订单' WHEN 'CAN' THEN '撤销休学订单' WHEN 'WK' THEN '微课商品订单' WHEN 'ATI' THEN '活动订单' WHEN 'CARD' THEN '学习卡订单' WHEN 'ECLASS' THEN '正价体验课订单' WHEN 'TFAREF' THEN '退费订单' WHEN 'EX' THEN '体验课订单' else null end as co_type,
t3.classTypename,t3_1.oldclasstypeName,t1.skuId,t2.className,t2.classYear,case t2.classTeachmethod when 'N' then '网络' when 'F' then '面授' when 'M' then '混合' when 'L' then '直播' else null end as class_teach_method,
if(t2.classAsertype in ('R','K') and t1.coType <> 'GIV','是','否') as if_restuday,if(t2.classAsertype = 'F','是','否') as if_refound,t1.oiAmount,if(t1.receiptType = 'I','收入','支出'),t1.traderecordChanneltradeno,'订单中心' as system_source,
if(t6.projectId2 = 'PROJECT20220517200000000001' or (t6.projectId2 = 'PROJECT20210507210000000002' and substring(t1.receiptTradedate,1,10) >= '2022-03-01'),'合作业绩','培训业绩'),'个人' as student_type,concat('订单中心-',t5.originName) as origin_name_one,t4.agentName,case t1.receiptApprstatus when 'PRE' then '待提交' when 'PEN' then '待审核' when 'PRO' then '审核中' when 'PAS' then '审核通过' when 'FAI' then '审核失败' else null end as receipt_appr_status,
if(t1.receiptPaystatus = 'PAI','已支付','未支付'),t1.receiptAmount*1.0/100,t8.eeNo,t8.userId,t8_2.dptId,t8_2.dpt_n1,t8_2.dpt_n2,t5.originId as origin_id1,t5.originName as origin_name1,t5.originId2 as origin_id2,t5.originName2 as origin_name2,
case when t1.orderTotalamount <= t1.receiptAmount then '全款收款' when t1.orderTotalamount > t1.receiptAmount and t1.orderTotalamount > 0 then '部分收款' end as is_all,if(t2.classSalestatus = 'Y','正价课业绩','非正价课业绩'),
if(t1.receiptApprstatus = 'PAS' or (t11.order_check_status = 'Y' and t1.receiptApprstatus <> 'PAS'),'审核通过','审核未通过') as checked_status,if(t1.receiptApprstatus = 'PAS','审核通过','审核中') as final_checked_status,
if(t12.ait_approval_date > t1.receiptTradedate,'审核通过','审核中') as sf_checked_status,t13.accountName,t14.eeNo,case t1.coSubtype when 'FL' then '平转' when 'UP' then '升转' when 'DO' then '降转' when 'MA' then '营销赠课'
when 'TR' then '引流赠课' else null end as co_subtype,t1.orderTotalamount*1.0/100 as order_total_amount,t1.orderPayamount*1.0/100 as order_pay_amount,null,null,null,null as edu_mode_type,t1.receiptRealstatus,case t2.classAsertype when 'R' then '重读' when 'F' then '退费' when 'N' then '不退费不重读' when 'B' then '无协议' when 'K' then '续学' when 'U' then '不续学不退费' when 'EG' then '启用V1通用协议' when 'DG' then '不启用V1通用协议' when 'O' then '无协议' else null end as class_a_ser_type,
if(t1.skuId = 'CLASS20220306210000000042','PROJECT20221103220000000002',t6.projectId) as project_id1,if(t1.skuId = 'CLASS20220306210000000042','PROJECT20210507210000000002',t6.projectId2) as project_id2,
if(t3.classTypeusesourcetype in ('E','G'),'正价课订单','流量课订单') as order_achieve_type,t1.orderSource,t1.orderMemo,cast(now() as string) as data_created_time
FROM
(SELECT d1.receiptId,d1.receiptTradedate,d1.receiptNo,d1.receiptType,d1.traderecordChanneltradeno,d1.receiptPaystatus,d1.receiptAmount,d1.orderId,d2.userId,d2.orderNo,d5.projectId,d3.skuId,d4.coType,d3.oiAmount,
d2.orderTotalamount,d2.orderPayamount,d1.receiptApprstatus,d2.aiId,d1.accountCode,d4.coSubtype,d1.receiptRealstatus,d2.orderSource,d2.orderMemo
FROM (select * from datawarehouse.dwd_oc_OcReceipt where substring(receiptTradedate,1,4) >= '2021' AND receiptDelstatus = 'N' and receiptAvlstatus = 'Y' AND (receiptRealstatus != 'N' or receiptType != 'E') AND orderId <> '') d1
INNER JOIN (select * from datawarehouse.dwd_oc_OcOrder where orderDelstatus = 'N' and orderAvlstatus = 'Y' and orderBusinesstype = 'I') d2 ON d1.orderId = d2.orderId
LEFT JOIN (select * from datawarehouse.dwd_oc_OcOrderitem where oiAvlstatus = 'Y' and oiDelstatus = 'N' and oiMainstatus = 'Y' and oiId <> '') d3 ON d1.orderId = d3.orderId
LEFT JOIN datawarehouse.dwd_oc_OcClassorder d4 ON d1.orderId = d4.orderId
LEFT JOIN datawarehouse.dwd_oc_OcClassorderitem d5 ON d1.orderId = d5.orderId and d3.oiId = d5.oiId
UNION ALL
SELECT d1.receiptId,d1.receiptTradedate,d1.receiptNo,d1.receiptType,d1.traderecordChanneltradeno,d1.receiptPaystatus,d1.receiptAmount,d1.orderId,d1.userId,d1.orderNo,max(d5.projectId) over (PARTITION by d1.orderId) as projectId,
max(d3.skuId) over (PARTITION by d1.orderId) as skuId,d4.coType,d3_1.oiAmount,d1.orderTotalamount,d1.orderPayamount,d1.receiptApprstatus,d1.aiId,d1.accountCode,d4.coSubtype,d1.receiptRealstatus,d1.orderSource,d1.orderMemo
FROM (select a.receiptId,a.receiptTradedate,a.receiptNo,a.receiptType,a.traderecordChanneltradeno,a.receiptApprstatus,a.receiptPaystatus,a.receiptAmount,
b.ooOperateorderid as orderId,c.orderId as order_id_ori,c.userId,c.orderNo,c.orderTotalamount,c.orderPayamount,c.aiId,a.accountCode,a.receiptRealstatus,c.orderSource,c.orderMemo
from (select * from datawarehouse.dwd_oc_OcReceipt where substring(receiptTradedate,1,4) >= '2021' AND receiptDelstatus = 'N' and receiptAvlstatus = 'Y' AND receiptRealstatus = 'N' AND receiptType = 'E' and orderId <> '') a
INNER JOIN (select * from datawarehouse.dwd_oc_OcOrder where orderDelstatus = 'N' and orderAvlstatus = 'Y' and orderBusinesstype = 'I') c ON a.orderId = c.orderId
LEFT JOIN (select ooOperateorderid,ooOutputorderid
from datawarehouse.dwd_oc_OcOrderoperation where ooOperateorderid != ooOutputorderid group by ooOperateorderid,ooOutputorderid) b ON a.orderId = b.ooOutputorderid) d1
LEFT JOIN (select * from datawarehouse.dwd_oc_OcOrderitem where oiAvlstatus = 'Y' and oiDelstatus = 'N' and oiMainstatus = 'Y' and oiId <> '') d3 ON d1.orderId = d3.orderId
LEFT JOIN (select * from datawarehouse.dwd_oc_OcOrderitem where oiAvlstatus = 'Y' and oiDelstatus = 'N' and oiMainstatus = 'Y') d3_1 ON d1.order_id_ori = d3_1.orderId
LEFT JOIN datawarehouse.dwd_oc_OcClassorder d4 ON d1.order_id_ori = d4.orderId
LEFT JOIN datawarehouse.dwd_oc_OcClassorderitem d5 ON d1.orderId = d5.orderId and d3.oiId = d5.oiId) t1
LEFT JOIN (select * from datawarehouse.dwd_bd_BdClass where classId <> '') t2 ON t1.skuId = t2.classId
LEFT JOIN (select * from datawarehouse.dwd_bd_BdClasstype where classTypeid <> '') t3 ON t2.classTypeid = t3.classTypeid
LEFT JOIN datawarehouse.dwd_bd_BdOldclasstype t3_1 on t3.oldclasstypeId = t3_1.oldclasstypeId and t3_1.oldclasstypeDelstatus = 'N' and t3_1.isPhysicsDel = 2
LEFT JOIN (
select distinct a.userId,a.custId,custCreateddate,a.custMobile,a.custName,a.custName_encode,a.custSourceChannel,c.agentName from (
SELECT * FROM (
select userId,custId,custCreateddate,custMobile,custSourceChannel,createdAgentid,custName, custName_encode, row_number() over (partition by userId order by custCommtime desc) as rnk
from (
select if(a.userId = '' or a.userId is null,b.userId,a.userId) as userId,a.custId,a.custCreateddate,a.custMobile,a.custSourceChannel,a.createdAgentid,
a.custName,a.custName_encode,a.custCommtime
from (select * from datawarehouse.dwd_ct_CtCust where custDelstatus = 'N') a
left join datawarehouse.dwd_uc_UcUser b on a.custMobile = b.userMobile
where a.custMobile != '') cust) r
where rnk = 1) a
left join datawarehouse.dwd_ct_AmAgent c ON a.createdAgentid = c.agentId
) t4 ON t1.userId = t4.userId
LEFT JOIN (select a.originId as originId2,a.originName as originName2,b.originId,b.originName
from datawarehouse.dwd_es_BdOrigin a left join datawarehouse.dwd_es_BdOrigin b on SUBSTR(a.originLevelcode,1,10) = b.originLevelcode where a.originId <> '') t5 on t4.custSourcechannel = t5.originId2
LEFT JOIN (SELECT a.projectId as projectId2,a.projectName as projectName2,b.projectId,b.projectName
from datawarehouse.dwd_bd_BdProject a left join datawarehouse.dwd_bd_BdProject b on SUBSTR(a.projectLevelcode,1,10) = b.projectLevelcode where a.projectId <> '') t6 on t1.projectId = t6.projectId2
LEFT JOIN (select receiptId,max(roOwnerid) as ro_owner_id,max(roOwnerSchoolid) as ro_owner_school_id from datawarehouse.dwd_oc_OcReceiptownership where receiptId <> '' group by receiptId) t7 ON t1.receiptId = t7.receiptId
LEFT JOIN (select * from datawarehouse.dwd_bd_BdEe where userId <> '') t8 ON t7.ro_owner_id = t8.userId
LEFT JOIN (select * from datawarehouse.dwd_bd_BdDptee where dpteeRelation = 'P') t8_1 ON t8.eeId = t8_1.eeId
LEFT JOIN (select dpt_id as dptId,dpt_n1,dpt_n2 from dws.dws_bd_Dpt a) t8_2 ON t8_2.dptId = t8_1.dptId
left join (SELECT dbsa.school_id as schoolId, dbsa.school_name as schoolName,dbsa.area_id as areaId,dbsa.area_name as areaName, region_id,region_name
FROM dws.dws_bd_SchoolArea dbsa) t9 on t7.ro_owner_school_id = t9.schoolId
LEFT JOIN (SELECT * FROM datawarehouse.dwd_uc_UcUser WHERE userId <> '') t10 ON t1.userId = t10.userId
LEFT JOIN (select distinct ai.orderId,'Y' as order_check_status
from (select o.orderId,app.aiId from datawarehouse.dwd_oc_OcOrder o
left join (select * from datawarehouse.dwd_oc_OcApprovalinfo where aiId <> '') app on o.aiId = app.aiId) ai
join datawarehouse.dwd_oc_OcApprovalitem ait on ai.aiId = ait.aiId and ait.aiId <> ''
where ait.aitApprovalnode = 'SF' and ait.aitApprovalaction = 'FOR' and ai.orderId <> '') t11 on t1.orderId = t11.orderId
LEFT JOIN (SELECT aiId,max(aitApprovaldate) as ait_approval_date FROM datawarehouse.dwd_oc_OcApprovalitem where aitApprovalnode = 'SF' and aitApprovalaction = 'FOR' group by aiId) t12 ON t1.aiId = t12.aiId
LEFT JOIN (SELECT * FROM datawarehouse.dwd_oc_PcAccount where accountAvlstatus = 'Y' and accountDelstatus = 'N' and accountCode <> '') t13 ON t1.accountCode = t13.accountCode
LEFT JOIN (select * from datawarehouse.dwd_bd_BdEe where userId <> '') t14 ON t7.ro_owner_id = t14.userId
group by t1.receiptId,t10.userYoulunum,t4.custId,t4.custCreateddate,t4.custMobile,t4.custName,t4.custName_encode,t8.userName,t9.schoolId,t9.schoolName,t9.areaId,t9.areaName,t9.region_id,t9.region_name,t1.skuId,
t1.receiptTradedate,t6.projectName,t6.projectName2,t1.orderNo,t1.receiptNo,t1.coType,t2.classAsertype,t1.oiAmount,t1.receiptType,t1.traderecordChanneltradeno,
t4.agentName,t1.receiptApprstatus,t1.receiptPaystatus,t1.receiptAmount,t8.eeNo,t8.userId,t8_2.dptId,t8_2.dpt_n1,t8_2.dpt_n2,t5.originId,t5.originName,t5.originId2,t5.originName2,
t2.classSalestatus,t11.order_check_status,t12.ait_approval_date,t13.accountName,t14.eeNo,t1.coSubtype,t1.orderTotalamount,t1.orderPayamount,t1.receiptRealstatus,
t6.projectId,t6.projectId2,t3.classTypeusesourcetype,t1.orderSource,t1.orderMemo,t3.classTypename,t3_1.oldclasstypeName,t2.className,t2.classYear,t2.classTeachmethod
最新 优化后SQL 语句
-- 订单中心
SELECT t1.receiptId,t10.userYoulunum,t4.custId,t4.custCreateddate,t4.custMobile,t4.custName,t4.custName_encode,t8.userName,t9.schoolId,t9.schoolName,t9.areaId,t9.areaName,t9.region_id,t9.region_name,
t1.receiptTradedate,if(t1.skuId = 'CLASS20220306210000000042','升学培训',t6.projectName) as project_name1,if(t1.skuId = 'CLASS20220306210000000042','成考培训',t6.projectName2) as project_name2,t1.orderNo,t1.receiptNo,
case t1.coType WHEN 'GEN' THEN '正常订单' WHEN 'REP' THEN '续学订单' WHEN 'CHA' THEN '转班订单' WHEN 'TRA' THEN '转让订单' WHEN 'REF' THEN '退费订单' WHEN 'DEF' THEN '顺延订单' WHEN 'GIV' THEN '赠课订单' WHEN 'DRO' THEN '休学订单' WHEN 'ACT' THEN '活动订单' WHEN 'MAT' THEN '资料订单' WHEN 'OPE' THEN '公开课' WHEN 'TRC' THEN '训练营订单' WHEN 'DRA' THEN '引流商品订单' WHEN 'CAN' THEN '撤销休学订单' WHEN 'WK' THEN '微课商品订单' WHEN 'ATI' THEN '活动订单' WHEN 'CARD' THEN '学习卡订单' WHEN 'ECLASS' THEN '正价体验课订单' WHEN 'TFAREF' THEN '退费订单' WHEN 'EX' THEN '体验课订单' else null end as co_type,
t3.classTypename,t3_1.oldclasstypeName,t1.skuId,t2.className,t2.classYear,case t2.classTeachmethod when 'N' then '网络' when 'F' then '面授' when 'M' then '混合' when 'L' then '直播' else null end as class_teach_method,
if(t2.classAsertype in ('R','K') and t1.coType <> 'GIV','是','否') as if_restuday,if(t2.classAsertype = 'F','是','否') as if_refound,t1.oiAmount,if(t1.receiptType = 'I','收入','支出'),t1.traderecordChanneltradeno,'订单中心' as system_source,
if(t6.projectId2 = 'PROJECT20220517200000000001' or (t6.projectId2 = 'PROJECT20210507210000000002' and substring(t1.receiptTradedate,1,10) >= '2022-03-01'),'合作业绩','培训业绩'),'个人' as student_type,concat('订单中心-',t5.originName) as origin_name_one,t4.agentName,case t1.receiptApprstatus when 'PRE' then '待提交' when 'PEN' then '待审核' when 'PRO' then '审核中' when 'PAS' then '审核通过' when 'FAI' then '审核失败' else null end as receipt_appr_status,
if(t1.receiptPaystatus = 'PAI','已支付','未支付'),t1.receiptAmount*1.0/100,t8.eeNo,t8.userId,t8_2.dptId,t8_2.dpt_n1,t8_2.dpt_n2,t5.originId as origin_id1,t5.originName as origin_name1,t5.originId2 as origin_id2,t5.originName2 as origin_name2,
case when t1.orderTotalamount <= t1.receiptAmount then '全款收款' when t1.orderTotalamount > t1.receiptAmount and t1.orderTotalamount > 0 then '部分收款' end as is_all,if(t2.classSalestatus = 'Y','正价课业绩','非正价课业绩'),
if(t1.receiptApprstatus = 'PAS' or (t11.order_check_status = 'Y' and t1.receiptApprstatus <> 'PAS'),'审核通过','审核未通过') as checked_status,if(t1.receiptApprstatus = 'PAS','审核通过','审核中') as final_checked_status,
if(t12.ait_approval_date > t1.receiptTradedate,'审核通过','审核中') as sf_checked_status,t13.accountName,t14.eeNo,case t1.coSubtype when 'FL' then '平转' when 'UP' then '升转' when 'DO' then '降转' when 'MA' then '营销赠课'
when 'TR' then '引流赠课' else null end as co_subtype,t1.orderTotalamount*1.0/100 as order_total_amount,t1.orderPayamount*1.0/100 as order_pay_amount,null,null,null,null as edu_mode_type,t1.receiptRealstatus,case t2.classAsertype when 'R' then '重读' when 'F' then '退费' when 'N' then '不退费不重读' when 'B' then '无协议' when 'K' then '续学' when 'U' then '不续学不退费' when 'EG' then '启用V1通用协议' when 'DG' then '不启用V1通用协议' when 'O' then '无协议' else null end as class_a_ser_type,
if(t1.skuId = 'CLASS20220306210000000042','PROJECT20221103220000000002',t6.projectId) as project_id1,if(t1.skuId = 'CLASS20220306210000000042','PROJECT20210507210000000002',t6.projectId2) as project_id2,
if(t3.classTypeusesourcetype in ('E','G'),'正价课订单','流量课订单') as order_achieve_type,t1.orderSource,t1.orderMemo,cast(now() as string) as data_created_time
FROM
(
SELECT d1.receiptId, d1.receiptTradedate, d1.receiptNo, d1.receiptType, d1.traderecordChanneltradeno, d1.receiptPaystatus,
d1.receiptAmount, d1.orderId,d2.userId,d2.orderNo,d5.projectId,d3.skuId,d4.coType,d3.oiAmount, d2.orderTotalamount,
d2.orderPayamount,d1.receiptApprstatus,d2.aiId,d1.accountCode,d4.coSubtype,d1.receiptRealstatus,d2.orderSource,d2.orderMemo
from datawarehouse.dwd_oc_OcReceipt d1
join datawarehouse.dwd_oc_OcOrder d2 on d1.orderId = d2.orderId
left join datawarehouse.dwd_oc_OcOrderitem d3 on d2.orderId = d3.orderId and d3.oiAvlstatus = 'Y' and d3.oiDelstatus = 'N' and d3.oiMainstatus = 'Y'
left join datawarehouse.dwd_oc_OcClassorder d4 on d2.orderId = d4.orderId
left join datawarehouse.dwd_oc_OcClassorderitem d5 ON d2.orderId = d5.orderId and d3.oiId = d5.oiId
where d1.receiptTradedate >= '2021-01-01' and d1.receiptDelstatus = 'N'
and d1.receiptAvlstatus = 'Y' and (d1.receiptRealstatus != 'N' or d1.receiptType != 'E')
and d2.orderDelstatus = 'N' and d2.orderAvlstatus = 'Y' and d2.orderBusinesstype = 'I'
UNION ALL
SELECT d1.receiptId,d1.receiptTradedate,d1.receiptNo,d1.receiptType,d1.traderecordChanneltradeno,d1.receiptPaystatus,
d1.receiptAmount,d2_1.ooOutputorderid,d2.userId,d2.orderNo,max(d5.projectId) over (PARTITION by d2_1.ooOutputorderid) as projectId, max(d3.skuId) over (PARTITION by d2_1.ooOutputorderid) as skuId,d4.coType, d3_1.oiAmount,d2.orderTotalamount,
d2.orderPayamount,d1.receiptApprstatus,d2.aiId,d1.accountCode,d4.coSubtype,d1.receiptRealstatus,d2.orderSource,d2.orderMemo
from datawarehouse.dwd_oc_OcReceipt d1
join datawarehouse.dwd_oc_OcOrder d2 on d1.orderId = d2.orderId
left join (select distinct ooOperateorderid,ooOutputorderid from datawarehouse.dwd_oc_OcOrderoperation where ooOperateorderid != ooOutputorderid) d2_1 on d2.orderId = d2_1.ooOutputorderid
left join datawarehouse.dwd_oc_OcOrderitem d3 on d2_1.ooOperateorderid = d3.orderId and d3.oiAvlstatus = 'Y' and d3.oiDelstatus = 'N' and d3.oiMainstatus = 'Y'
left join datawarehouse.dwd_oc_OcOrderitem d3_1 on d2.orderId = d3_1.orderId and d3_1.oiAvlstatus = 'Y' and d3_1.oiDelstatus = 'N' and d3_1.oiMainstatus = 'Y'
left join datawarehouse.dwd_oc_OcClassorder d4 on d2.orderId = d4.orderId
left join datawarehouse.dwd_oc_OcClassorderitem d5 on d2_1.ooOperateorderid = d5.orderId and d3.oiId = d5.oiId
where d1.receiptTradedate > '2021-01-01' and d1.receiptDelstatus = 'N' and d1.receiptAvlstatus = 'Y' and d1.receiptRealstatus = 'N' and d1.receiptType = 'E'
and d2.orderDelstatus = 'N' and d2.orderAvlstatus = 'Y' and d2.orderBusinesstype = 'I'
) t1
LEFT JOIN (select * from datawarehouse.dwd_bd_BdClass where classId <> '') t2 ON t1.skuId = t2.classId
LEFT JOIN (select * from datawarehouse.dwd_bd_BdClasstype where classTypeid <> '') t3 ON t2.classTypeid = t3.classTypeid
LEFT JOIN datawarehouse.dwd_bd_BdOldclasstype t3_1 on t3.oldclasstypeId = t3_1.oldclasstypeId and t3_1.oldclasstypeDelstatus = 'N' and t3_1.isPhysicsDel = 2
LEFT JOIN ads_fineReport.dwd_achievement_base_cust t4 ON t1.userId = t4.userId
LEFT JOIN (select a.originId as originId2,a.originName as originName2,b.originId,b.originName
from datawarehouse.dwd_es_BdOrigin a left join datawarehouse.dwd_es_BdOrigin b on SUBSTR(a.originLevelcode,1,10) = b.originLevelcode where a.originId <> '') t5 on t4.custSourcechannel = t5.originId2
LEFT JOIN (SELECT a.projectId as projectId2,a.projectName as projectName2,b.projectId,b.projectName
from datawarehouse.dwd_bd_BdProject a left join datawarehouse.dwd_bd_BdProject b on SUBSTR(a.projectLevelcode,1,10) = b.projectLevelcode where a.projectId <> '') t6 on t1.projectId = t6.projectId2
LEFT JOIN (select receiptId,max(roOwnerid) as ro_owner_id,max(roOwnerSchoolid) as ro_owner_school_id from datawarehouse.dwd_oc_OcReceiptownership where receiptId <> '' group by receiptId) t7 ON t1.receiptId = t7.receiptId
LEFT JOIN (select * from datawarehouse.dwd_bd_BdEe where userId <> '') t8 ON t7.ro_owner_id = t8.userId
LEFT JOIN (select * from datawarehouse.dwd_bd_BdDptee where dpteeRelation = 'P') t8_1 ON t8.eeId = t8_1.eeId
LEFT JOIN (select dpt_id as dptId,dpt_n1,dpt_n2 from dws.dws_bd_Dpt a) t8_2 ON t8_2.dptId = t8_1.dptId
left join (SELECT dbsa.school_id as schoolId, dbsa.school_name as schoolName,dbsa.area_id as areaId,dbsa.area_name as areaName, region_id,region_name
FROM dws.dws_bd_SchoolArea dbsa) t9 on t7.ro_owner_school_id = t9.schoolId
LEFT JOIN (SELECT * FROM datawarehouse.dwd_uc_UcUser WHERE userId <> '') t10 ON t1.userId = t10.userId
LEFT JOIN (select distinct ai.orderId,'Y' as order_check_status
from (select o.orderId,app.aiId from datawarehouse.dwd_oc_OcOrder o
left join (select * from datawarehouse.dwd_oc_OcApprovalinfo where aiId <> '') app on o.aiId = app.aiId) ai
join datawarehouse.dwd_oc_OcApprovalitem ait on ai.aiId = ait.aiId and ait.aiId <> ''
where ait.aitApprovalnode = 'SF' and ait.aitApprovalaction = 'FOR' and ai.orderId <> '') t11 on t1.orderId = t11.orderId
LEFT JOIN (SELECT aiId,max(aitApprovaldate) as ait_approval_date FROM datawarehouse.dwd_oc_OcApprovalitem where aitApprovalnode = 'SF' and aitApprovalaction = 'FOR' group by aiId) t12 ON t1.aiId = t12.aiId
LEFT JOIN (SELECT * FROM datawarehouse.dwd_oc_PcAccount where accountAvlstatus = 'Y' and accountDelstatus = 'N' and accountCode <> '') t13 ON t1.accountCode = t13.accountCode
LEFT JOIN (select * from datawarehouse.dwd_bd_BdEe where userId <> '') t14 ON t7.ro_owner_id = t14.userId
group by t1.receiptId,t10.userYoulunum,t4.custId,t4.custCreateddate,t4.custMobile,t4.custName,t4.custName_encode,t8.userName,t9.schoolId,t9.schoolName,t9.areaId,t9.areaName,t9.region_id,t9.region_name,t1.skuId,
t1.receiptTradedate,t6.projectName,t6.projectName2,t1.orderNo,t1.receiptNo,t1.coType,t2.classAsertype,t1.oiAmount,t1.receiptType,t1.traderecordChanneltradeno,
t4.agentName,t1.receiptApprstatus,t1.receiptPaystatus,t1.receiptAmount,t8.eeNo,t8.userId,t8_2.dptId,t8_2.dpt_n1,t8_2.dpt_n2,t5.originId,t5.originName,t5.originId2,t5.originName2,
t2.classSalestatus,t11.order_check_status,t12.ait_approval_date,t13.accountName,t14.eeNo,t1.coSubtype,t1.orderTotalamount,t1.orderPayamount,t1.receiptRealstatus,
t6.projectId,t6.projectId2,t3.classTypeusesourcetype,t1.orderSource,t1.orderMemo,t3.classTypename,t3_1.oldclasstypeName,t2.className,t2.classYear,t2.classTeachmethod
-- 业绩处理_订单中心_客户信息_mysql
insert overwrite ads_fineReport.dwd_achievement_base_cust
select distinct a.userId , a.custId, a.custCreateddate, a.custMobile, a.custName, a.custName_encode, a.custSourceChannel,
c.agentName ,cast(now() as string) as data_created_time
from (
select userId , custId, custCreateddate, custMobile, custName, custName_encode, custSourceChannel, createdAgentid
from (
select userId,custId,custCreateddate,custMobile, custName, custName_encode, custSourceChannel, createdAgentid,
row_number() over (partition by userId order by custCommtime desc) as rnk
from (
select a.custId, a.custCreateddate, a.custMobile, a.custSourceChannel, a.custName, a.custName_encode, a.custCommtime, a.createdAgentid,
if(a.userId = '' or a.userId is null,b.userId,a.userId) as userId
from datawarehouse.dwd_ct_CtCust a
left join datawarehouse.dwd_uc_UcUser b on a.custMobile = b.userMobile
where a.custMobile <> '' and a.custDelstatus = 'N'
) cust
) r where r.rnk = 1
) a
left join datawarehouse.dwd_ct_AmAgent c ON a.createdAgentid = c.agentId;
浙公网安备 33010602011771号