[12]sql优化-ads_fineReport.ads_school_Conversion_rate_analysis_order
原始SQL 语句
insert overwrite ads_fineReport.ads_school_Conversion_rate_analysis_order
select * from (
select *,row_number() over(partition by t1.userId,t1.receiptNo,t1.orderNo,t1.orderSource,t1.order_source_name,t1.orderPlatformappcode,t1.order_platform_appcode_name,
t1.receiptTradedate,t1.receipt_amount,t1.receiptId,t1.coType,t1.class_name,t1.class_type_use_source_type,t1.class_type_use_source_type_name,
t1.roOwnerid,t1.userName,t1.dptRelationid,t1.ro_dpt_name,t1.ro_dpt_id,t1.ro_dpt_name2,t1.region_id ,t1.region_name,t1.receipt_pay_status,t1.receipt_type order by t1.cust_id)
as rnk from (
select t1.userId,group_concat(t1.custId) as cust_id,t1.receiptNo,t1.orderNo,t1.orderSource,t1.order_source_name,t1.orderPlatformappcode,t1.order_platform_appcode_name,
t1.receiptTradedate,t1.receipt_amount,t1.receiptId,t1.coType,GROUP_CONCAT(t1.className) as class_name,max(if(t1.classTypeusesourcetype = 'E','E',t1.classTypeusesourcetype)) as class_type_use_source_type
,max(IF(t1.class_type_use_source_type_name = '系统课订单','系统课订单',t1.class_type_use_source_type_name )) as class_type_use_source_type_name
,t1.roOwnerid,t1.userName,t1.dpt11_id,t1.dptPath,t1.dptRelationid,t1.ro_dpt_name,t1.ro_dpt_id,t1.ro_dpt_name2,t1.region_id ,t1.region_name,t1.receipt_pay_status,t1.receipt_type,max(t1.project_ida) ,
max(t1.project_namea),max(t1.projectId),max(t1.projectName),t1.orderCreateddate, max(if(t1.achieve_type = '正价课业绩','正价课业绩',achieve_type)) as achieve_type,t1.custCreateddate,t1.one_origin_id,t1.one_origin_name,
t1.two_origin_id,t1.two_origin_name,array_join(array_sort(array_distinct(array_agg(t1.dptId))),',') as dpt_id ,array_join(array_sort(array_distinct(array_agg(t1.dpt_name))),',') as dpt_name ,
array_join(array_sort(array_distinct(array_agg(t1.clueEseeid))),',') as clue_es_ee_id,array_join(array_sort(array_distinct(array_agg(t1.user_namea))),',') as user_namea ,
t1.cust_init_projectname,t1.cust_creator_name,t1. data_created_time
from (
SELECT distinct doo.userId ,ct.custId , dor.receiptNo, doo.orderNo,doo.orderSource,
case when doo.orderSource = 'OP' then '运营平台'
when doo.orderSource='OA' THEN '运营平台移动端'
when doo.orderSource='YP' THEN '优路教育官网'
when doo.orderSource='YA' THEN '优路学员移动端'
when doo.orderSource='TM' THEN '天猫'
when doo.orderSource='JD' THEN '京东'
when doo.orderSource='PDD' THEN '拼多多'
when doo.orderSource='DY' THEN '抖音'
when doo.orderSource='WXSHOP' THEN '微信小商店'
when doo.orderSource='DA' THEN '合作代理平台'
when doo.orderSource='EC' THEN '企业客户'
when doo.orderSource='WMP' THEN '微信小程序'
when doo.orderSource='XET' THEN '小鹅通'
when doo.orderSource='YOUZAN' THEN '有赞'
when doo.orderSource='MC' THEN '营销中心'
when doo.orderSource='SH' THEN '学员自助'
when doo.orderSource='KS' THEN '快手小店'
when doo.orderSource='HD' THEN '活动中心'
when doo.orderSource='WXVD' THEN '视频号小店'
END order_source_name,
doo.orderPlatformappcode,
case doo.orderPlatformappcode
when 'OP.WEB' then '运营平台网站'
when 'OP.APP' then '运营平台App'
when 'PORTAL.WEB' then '优路教育官网'
when 'YP.TRIALCLASS.WEB' then '优路官网体验课'
when 'OP.WKWEB' then '优路微课官网'
when 'YA.TRIALCLASS.H5' then '优路教育H5网站'
when 'PORTAL.APP' then '优路教育App'
when 'YOULU.TEACHER.APP' then '优路教师APP'
when 'YA.TRIALCLASS.APP' then '优路教育APP体验课'
when 'TMALL.MAIN' then '原天猫优路教育旗舰店'
when 'YOULU.TMALL.MAIN' then '天猫优路教育旗舰店'
when 'GEEDU.TMALL.MAIN' then '天猫环球卓越旗舰店'
when 'YOULU.TMALL.SHANGHAI' then '天猫优路教育企业店'
when 'JD.MAIN' then '原京东优路教育旗舰店'
when 'YOULU.JD.MAIN' then '京东优路教育旗舰店'
when 'YOULU.JD.OFFICIAL' then '京东优路官方旗舰店'
when 'YOULU.JD.BOOK' then '京东优路图书专营店'
when 'YOULU.JD.SHANGHAI' then '上海优路教育企业店'
when 'YOULU.PDD.OFFICIAL' then '优路教育官方旗舰店'
when 'YOULU.PDD.BOOK' then '优路图书专营店'
when 'YOULU.PDD.GEEDU' then '优路环优专卖店'
when 'YOULU.DY.BOOKOFFICIAL' then '抖音-优路教育图书旗舰店'
when 'YOULU.DY.BOOKZMD' then '抖音-优路教育官方专卖店'
when 'YOULU.WXVD.TEACHER' then '视频号小店-优路教师考试'
when 'YOULU.WXVD.YLTEC' then '视频号小店-北京优路教育'
when 'YOULU.WXVD.KJKST' then '视频号小店-会计师考试通'
when 'YOULU.WXVD.YLEJXD' then '视频号小店-优路二建小店'
when 'YOULU.WXVD.YLYJXD' then '视频号小店-优路一建智学小店'
when 'YOULU.WXVD.YLYJDP' then '视频号小店-优路一建店铺'
when 'YOULU.WXVD.YLXFDP' then '视频号小店-优路消防店铺'
when 'YOULU.WXVD.YLEJQYD' then '视频号小店-优路二建企业店'
when 'OULU.WXVD.YLAGQYD' then '视频号小店-优路安工企业店'
when 'YOULU.WXSHOP.MATERIAL' then '微信小商店-优路备考资料小店'
when 'YOULU.WXSHOP.MATERIALTEST' then '微信小商店-UAT测试店铺'
when 'YOULU.WXSHOP.INTELLIGENCE' then '微信小商店-优路智学小店'
when 'YOULU.WXSHOP.MATERIALREP' then '微信小商店-优路备考资料库'
when 'CAP.DA.ECOMMERCE' then '分销联盟-电商'
when 'CAP.DA.OFFLINE' then '分销联盟-线下'
when 'CAP.DA.OTHER' then '分销联盟-其它'
when 'CAP.DA.LINGYI' then '零一裂变'
when 'EC.WEB' then '企业客户Web'
when 'YOULU.WX.ZONGHEBEIKAO' then '优路备考'
when 'YOULU.WX.YIWEI' then '优路医考'
when 'YOULU.WX.FAKAO' then '法考小程序'
when 'YOULU.WX.JIAOSHI' then '教师小程序'
when 'YOULU.WX.GONGKAO' then '公考小程序'
when 'YOULU.WX.CESHI' then '优路测试小程序'
when 'YOULU.WX.ZSB' then '专升本小程序'
when 'YOULU.WX.WEIKE' then '优路微课小程序'
when 'XET.HQYL' then '北京环球优路'
when 'XET.YLTEAC' then '优路考教师'
when 'XET.YLEDU' then '优路教育'
when 'XET.YLFK' then '优路法考'
when 'XET.YIWEICOLLEGE' then '优路医卫学院'
when 'XET.YLKAOYAN' then '优路考研'
when 'XET.YLZSB' then '优路专升本'
when 'YOUZAN.YWSYB' then '优路医卫学院'
when 'MC.GROUPBUYING' then '拼团'
when 'MC.SECKILL' then '秒杀'
when 'MC.TRAINCAMP' then '训练营'
when 'MC.LINGYI' then '零一裂变'
when 'SH.CONTINUE' then '自助续学'
when 'YOULU.KS.YJBKJ' then '快手小店-优路教育专卖店'
when 'YOULU.TMALL.TAOBAOSHANGHAI' then '淘宝上海优路教育'
when 'YOULU.DY.YLKP' then '抖音-优路教育康培专卖店'
when 'HD.PT' then '拼团'
when 'HD.MS' then '秒杀'
when 'HD.CJ' then '抽奖'
when 'HD.KJ' then '砍价'
when 'HD.PL' then '评论'
end as order_platform_appcode_name,
dor.receiptTradedate,dor.receiptAmount /100.0 as receipt_amount,dor.receiptId
,doc.coType,b.className ,b.classTypeusesourcetype,
case when b.classTypeusesourcetype = 'E' then '系统课订单'
when b.classTypeusesourcetype != 'E' then '流量课订单'end class_type_use_source_type_name
,doros.roOwnerid,de.userName ,dpt11.dptId as dpt11_id ,dpt11.dptPath ,dbsa.school_id as dptRelationid,
dbsa.school_name as ro_dpt_name, dbsa.area_id as ro_dpt_id, dbsa.area_name as ro_dpt_name2 , dbsa.region_id ,dbsa.region_name ,
case when dor.receiptPaystatus = 'UNP' then '未支付'
when dor.receiptPaystatus = 'PAI' then '已支付' ELSE dor.receiptPaystatus end receipt_pay_status,
case when dor.receiptType = 'I' then '收入'
when dor.receiptType = 'E' then '支出' else dor.receiptType end receipt_type,item.projectId as project_ida
,dp.projectName as project_namea,dp2.projectId,dp2.projectName
,doo.orderCreateddate, case when b.classTypeusesourcetype = 'E' then '正价课业绩'
when b.classTypeusesourcetype <> 'E' and b.classTypeusesourcetype != '' then '非正价课业绩'
else b.classTypeusesourcetype end as achieve_type
,ct.custCreateddate,ori.one_origin_id,ori.one_origin_name,ori.two_origin_id,ori.two_origin_name,
dpt.dptId ,concat(dpt2.dptName,'_',dpt.dptName) as dpt_name ,ec.clueEseeid ,de3.userName as user_namea ,
pro.projectName as cust_init_projectname,case when de2.userName != 'system' then '其他' else de2.userName end as cust_creator_name,
cast(now() as string) as data_created_time
FROM datawarehouse.dwd_oc_OcOrder doo
inner join datawarehouse.dwd_oc_OcReceipt dor
on dor.orderId = doo.orderId and dor.orderId <> ''
left join datawarehouse.dwd_oc_OcReceiptownership doros on doros.receiptId = dor.receiptId and doros.roOwnerid <> '' and doros.roOwnerschoolid <> '' AND doros.receiptId <> ''
inner join datawarehouse.dwd_oc_OcClassorderitem item on doo.orderId = item.orderId AND item.orderId <> ''
left join datawarehouse.dwd_bd_BdProject dp on dp.projectId = item.projectId AND dp.projectId <> ''
left join datawarehouse.dwd_bd_BdProject dp2 on SUBSTRING(dp.projectLevelcode ,1,10) = dp2.projectLevelcode AND dp2.projectLevelcode <> ''
left join datawarehouse.dwd_bd_BdEe de on doros.roOwnerid = de.userId AND de.userId<>''
left join datawarehouse.dwd_bd_BdDptee dde22 on de.eeId = dde22.eeId and dde22.dptEerelation = 'P' AND dde22.eeId <>''
left join datawarehouse.dwd_bd_BdDpt dpt11 on dpt11.dptId = dde22.dptId AND dpt11.dptId <> ''
left join dws.dws_bd_SchoolArea dbsa on doros.roOwnerschoolid = dbsa.school_id
inner join (select distinct
oc.orderId,
oc.coType,
ocit.skuId AS class_id,
ocit.oiMainstatus
from datawarehouse.dwd_oc_OcClassorder oc
left join datawarehouse.dwd_oc_OcOrderitem ocit on oc.orderId = ocit.orderId AND ocit.orderId <> '' and ocit.oiDelstatus = 'N') doc -- 订单项表
on doo.orderId = doc.orderId
LEFT JOIN (select
a.classId,
a.className,
a.classAvlstatus,
a.classDelstatus,
b.classTypeusesourcetype
from datawarehouse.dwd_bd_BdClass a
left join datawarehouse.dwd_bd_BdClasstype b on a.classTypeid = b.classTypeid AND b.classTypeid <> '') b -- 班级表
ON doc.class_id = b.classId AND b.classId <> ''
left join datawarehouse.dwd_oc_CtCust docc on docc.userId = doo.userId AND docc.userId <> ''
left join datawarehouse.dwd_ct_CtCust ct on ct.custId = docc.custId and ct.custAvlstatus = 'Y' AND ct.custDelstatus = 'N' and ct.brandId = 'YOULU' AND ct.custId <> ''
LEFT JOIN datawarehouse.dwd_bd_BdProject pro ON ct.custInitproject = pro.projectId AND pro.projectId <> ''
left join datawarehouse.dwd_bd_BdEe de2 on ct.custCreator = de2.userId AND de2.userId <> ''
LEFT JOIN (select a.originId two_origin_id,a.originName two_origin_name,a.originCode two_origin_code,a.originLevelcode as origin_level_code,
b.originId one_origin_id,b.originName one_origin_name,b.originCode one_origin_code,
a.originAvlstatus as origin_avl_status,a.originDelstatus as origin_del_status
from datawarehouse.dwd_es_BdOrigin a
left join datawarehouse.dwd_es_BdOrigin b
on substring(a.originLevelcode,1,10)=b.originLevelcode and b.originLevelcode <> '') ori ON ct.custSourcechannel = ori.two_origin_id AND ori.two_origin_id <> ''
left join datawarehouse.dwd_ct_CtClue dcc on dcc.custId = ct.custId and dcc.clueNewstatus = 'Y' AND dcc.custId <> ''
left join datawarehouse.dwd_es_clueeslog ec on ec.clueId = dcc.clueSourceid and ec.clueStatus = 'N' AND ec.clueId <> ''
left join datawarehouse.dwd_bd_BdEe de3 on de3.eeId = ec.clueEseeid AND de3.eeId <> ''
left join datawarehouse.dwd_bd_BdDptee dde on de3.eeId = dde.eeId and dde.dptEerelation = 'P' AND dde.eeId <> ''
left join datawarehouse.dwd_bd_BdDpt dpt on dpt.dptId = dde.dptId
left join datawarehouse.dwd_bd_BdDpt dpt2 on SUBSTRING(dpt.dptLevelcode,1,10) = dpt2.dptLevelcode
WHERE classAvlstatus = 'Y' AND doo.orderAvlstatus = 'Y' AND doo.orderDelstatus = 'N'
AND classDelstatus = 'N' and dor.receiptDelstatus = 'N' AND dor.receiptRealstatus = 'Y'
and doc.coType = 'GEN' and dor.receiptPaystatus = 'PAI'and doc.oiMainstatus = 'Y' ) t1
group by t1.userId,t1.receiptNo,t1.orderNo,t1.orderSource,t1.order_source_name,t1.orderPlatformappcode,t1.order_platform_appcode_name,
t1.receiptTradedate,t1.receipt_amount,t1.receiptId,t1.coType
,t1.roOwnerid,t1.userName,t1.dpt11_id,t1.dptPath,t1.dptRelationid,t1.ro_dpt_name,t1.ro_dpt_id,t1.ro_dpt_name2,t1.region_id ,t1.region_name,t1.receipt_Pay_status,t1.receipt_type,
t1.orderCreateddate, t1.custCreateddate,t1.one_origin_id,t1.one_origin_name,
t1.two_origin_id,t1.two_origin_name ,t1.cust_init_projectname,t1.cust_creator_name,t1.data_created_time) t1
) t2
where t2.rnk = 1;
最新 优化后SQL 语句
select distinct
t1.userId ,
t6.custId ,
t5.receiptNo,
t1.orderNo,
t1.orderSource,
t1_1.dict_name as order_source_name,
t1.orderPlatformappcode ,
t1_2.dict_name as order_platform_appcode_name,
t5.receiptTradedate,
t5.receiptAmount /100.0 as receiptAmount,
t5.receiptId,
t3.coType,
t2_1.className,
t2_2.classtypeUsesourcetype ,
case t2_2.classtypeUsesourcetype when 'E' then '系统课订单' else '流量课订单' end as class_type_use_source_type_name,
t5_1.roOwnerid,
t5_2.user_name as ro_owner_name,
t5_2.dpt_id_two as dptid,
t5_2.dpt_path ,
t5_1.roOwnerschoolid as school_id_ro,
t5_3.school_name as school_name_ro,
t5_3.area_id as area_id_ro,
t5_3.area_name as area_name_ro,
t5_3.region_id ,
t5_3.region_name ,
case when t5.receiptPaystatus = 'UNP' then '未支付' when t5.receiptPaystatus = 'PAI' then '已支付' ELSE t5.receiptPaystatus end receipt_pay_status,
case when t5.receiptType = 'I' then '收入' when t5.receiptType = 'E' then '支出' else t5.receiptType end receipt_type,
t4.projectId as project_id_two_ro,
t4_1.project_name_two as project_name_two_ro,
t4_1.project_id_one as project_id_one_ro,
t4_1.project_name_one as project_name_one_ro,
t1.orderCreateddate,
case when t2_2.classTypeusesourcetype = 'E' then '正价课业绩'
when t2_2.classTypeusesourcetype <> 'E' and t2_2.classTypeusesourcetype != '' then '非正价课业绩'
else t2_2.classTypeusesourcetype end as achieve_type,
t6.custCreateddate,
t6_1.origin_id_one as originid1,
t6_1.origin_name_one as originname1,
t6_1.origin_id_two as originid2,
t6_1.origin_name_two as originname2,
t7_1.dpt_id_two as clue_es_dpt_id,
t7_1.dpt_name_two as clue_es_dpt_name,
t7_1.ee_id as clue_es_ee_id,
t7_1.user_name as clue_es_ee_name,
t6_2.project_name_two as projectname2,
case when t6_3.user_name != 'system' then '其他' else t6_3.user_name end as cust_creator_name,
cast(now() as string) as data_created_time,
1 as rnk
from datawarehouse.dwd_oc_OcOrder t1
left join datawarehouse.dwd_oc_OcOrderitem t2 on t1.orderId = t2.orderId
left join datawarehouse.dwd_oc_OcClassorder t3 on t1.orderId = t3.orderId
left join datawarehouse.dwd_oc_OcClassorderitem t4 on t2.orderId = t4.orderId and t2.oiId = t4.oiId
join datawarehouse.dwd_oc_OcReceipt t5 on t1.orderId = t5.orderId
left join datawarehouse.dwd_oc_OcReceiptownership t5_1 on t5.receiptId = t5_1.receiptId
join datawarehouse.dwd_ct_CtCust t6 on t1.userId = t6.userId
left join dws.dws_fr_Dict t1_1 on t1.orderSource = t1_1.dict_code and t1_1.dict_type = 'order_source'
left join dws.dws_fr_Dict t1_2 on t1.orderPlatformappcode = t1_2.dict_code and t1_2.dict_type = 'platform_appcode'
left join datawarehouse.dwd_bd_BdClass t2_1 on t2.skuId = t2_1.classId and t2_1.classDelstatus = 'N' and t2_1.isPhysicsDel = 2
left join datawarehouse.dwd_bd_BdClasstype t2_2 on t2_1.classtypeId = t2_2.classtypeId and t2_2.classtypeDelstatus = 'N' and t2_2.isPhysicsDel = 2
left join dws.dws_bd_EeDpt t5_2 on t5_1.roOwnerid = t5_2.user_id
left join dws.dws_bd_SchoolArea t5_3 on t5_1.roOwnerschoolid = t5_3.school_id
left join dws.dws_bd_ProjectInfo t4_1 on t4.projectId = t4_1.project_id_two
left join dws.dws_es_ClueOrigin t6_1 on t6.custSourcechannel = t6_1.origin_id_two
left join dws.dws_bd_ProjectInfo t6_2 on t6.custInitproject = t6_2.project_id_two
left join dws.dws_bd_EeDpt t6_3 on t6.custCreator = t6_3.user_id
left join ads_fineReport.dws_clue_achievement_clue t7 on t6.custId = t7.custId and t7.clueStatus = 'N'
left join dws.dws_bd_EeDpt t7_1 on t7.clueEseeid = t7_1.ee_id
where t1.orderAvlstatus = 'Y' and t1.orderDelstatus = 'N'
and t2.oiMainstatus = 'Y'
and t3.coType = 'GEN'
and t5.receiptDelstatus = 'N' and t5.receiptRealstatus = 'Y' and t5.receiptPaystatus = 'PAI'
and t6.custAvlstatus = 'Y' and t6.custDelstatus = 'N' and t6.isPhysicsDel = 2;
浙公网安备 33010602011771号