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

[0-1]优化-ads_marketing_cust_all

原始SQL 语句
-- insert into datawarehouse.ads_marketing_cust_all 
SELECT
	if(c.ProjectId='', 'PROJECT00000000000000000000', c.ProjectId) ProjectId,
	a.custCreateddate custCreateddate,
	a.custId custId,
	b.schoolareaId schoolareaId,
	b.schoolareaName schoolareaName,
	b.schoolId schoolId,
	b.schoolName schoolName,
	a.custprojIntention custprojIntention,
	a.custprojGradestatus custprojGradestatus,
	a.custprojSourcechannel custprojSourcechannel,
	a.custLatestcluetime custLatestcluetime,
	a.custCommtime custCommtime,
	a.ciModifieddate ciModifieddate,
	a.custprojCommtime custprojCommtime,
	a.custprojLatestcluetime custprojLatestcluetime 
FROM
	datawarehouse.dwd_ct_CtCustindex a 
JOIN (											
	select b.schoolId, b.schoolName, c.dptId as schoolareaId, if(c.dptName='', '未知大区', c.dptName) schoolareaName  
	from (
		select schoolId, schoolName, a.dptLevelcode from datawarehouse.dwd_bd_BdSchool bs
		left join datawarehouse.dwd_bd_BdDpt a on a.dptRelationid=bs.schoolId
		where bs.schoolDelstatus = 'N'
	) b
	left join datawarehouse.dwd_bd_BdDpt c on c.dptLevelcode = left(b.dptlevelcode, 10) where c.dptType='A' or c.dptName='集团总部'
) b ON a.custprojSchool = b.schoolId
LEFT JOIN ( SELECT ProjectId FROM datawarehouse.dwd_bd_BdProject WHERE projectDelstatus = 'N' ) c ON a.ProjectId = c.ProjectId
WHERE custprojDelstatus='N' AND brandId='YOULU' AND a.custmarketingType = 'T' 
-- and ciModifieddate>'$now_day';
最新 优化后SQL 语句
-- insert into datawarehouse.ads_marketing_cust_all 
SELECT
    dccc.projectId AS ProjectId,
    dccc.custCreateddate AS custCreateddate,
    dccc.custId AS custId,
    bs.area_id AS schoolareaId,
    bs.area_name AS schoolareaName,
    bs.school_id AS schoolId,
    bs.school_name AS schoolName,
    dccc.custprojIntention AS custprojIntention,
    dccc.custprojGradestatus AS custprojGradestatus,
    dccc.custprojSourcechannel AS custprojSourcechannel,
    dccc.custLatestcluetime AS custLatestcluetime,
    dccc.custCommtime AS custCommtime,
    dccc.ciModifieddate AS ciModifieddate,
    dccc.custprojCommtime AS custprojCommtime,
    dccc.custprojLatestcluetime AS custprojLatestcluetime
FROM datawarehouse.dwd_ct_CtCustindex dccc
JOIN dws.dws_bd_School bs on dccc.custprojSchool = bs.school_id 
WHERE bs.school_del_status = 'N' AND (bs.area_type ='A' OR bs.area_name ='集团总部')
AND dccc.custprojDelstatus='N' AND dccc.brandId='YOULU' AND dccc.custmarketingType = 'T'
AND dccc.ciModifieddate>'$now_day';

[0-2]优化-ads_marketing_cust_all_1

原始SQL 语句
-- insert overwrite datawarehouse.ads_marketing_cust_all_1
SELECT
	schoolId,
	schoolareaName,
	schoolName,
	count(custId) as custCount
FROM
	(
	-- 35,539,289
	select custId, schoolareaName, schoolId, schoolName 
	from datawarehouse.ads_marketing_cust_all
	group by custId,schoolareaName,schoolId,schoolName 
	) a
group by
	schoolareaName,
	schoolId,
	schoolName
最新 优化后SQL 语句
-- insert overwrite datawarehouse.ads_marketing_cust_all_1
select schoolId, schoolareaName, schoolName, COUNT(DISTINCT custId) as custCount
from datawarehouse.ads_marketing_cust_all
group by schoolareaName, schoolId, schoolName

[0-3]优化-ads_fineReport.xyyd_ck_achievement

原始SQL 语句
-- insert overwrite ads_fineReport.xyyd_ck_achievement
SELECT DISTINCT t1.id as receipt_id,t3.id as order_id,dbsa.school_id, dbsa.school_name, dbsa.area_id,dbsa.area_name,dbsa.region_id ,dbsa.region_name ,t1.receipt_time as receipt_trade_date,
case when t1.receipt_type =1 or t3.order_type ='TF' then -t1.receipt_amount else t1.receipt_amount end receipt_amount,cast(now() as string) as data_created_time
FROM 
(select * from dwd.dwd_yyd_oc_receipt_used 
	where receipt_source_code != '05' and collection_type != '' and is_deleted = 0 and is_void = 0 AND substring(receipt_time,1,10) >= '2022-04-01' and receipt_category!='XN'
	AND receipt_type = 0 AND is_paid = 1) t1
INNER JOIN (select * from dwd.dwd_yyd_oc_order_item where product_model = '01' and is_deleted = 0) t2 ON t1.order_id=t2.order_id
INNER JOIN (select * from dwd.dwd_yyd_oc_order where is_deleted = 0 and order_type <> 'ZF') t3 on t1.order_id=t3.id
LEFT JOIN (select distinct id,if(display_name = '总部','DPT20200409010000004548',external_id) as external_id from dwd.dwd_yyd_abp_organization_units) t_1
ON t1.school_id = t_1.id
left join dws.dws_bd_SchoolArea dbsa on t_1.external_id = dbsa.school_dpt_id 
INNER JOIN (select * from dwd.dwd_yyd_sys_ddic where dd_type_code = 'PaymentsType' AND dd_name like '%线下%') t4 ON t1.collection_type = t4.dd_value;
最新 优化后SQL 语句
-- insert overwrite ads_fineReport.xyyd_ck_achievement
SELECT 
dyoru.id as receipt_id, 
dyoo.id as order_id, 
dbsa.school_id, 
dbsa.school_name, 
dbsa.area_id, 
dbsa.area_name,
dbsa.region_id, 
dbsa.region_name, 
dyoru.receipt_time as receipt_trade_date,
case when dyoru.receipt_type = 1 or dyoo.order_type ='TF' then -dyoru.receipt_amount else dyoru.receipt_amount end receipt_amount,
cast(now() as string) as data_created_time
from dwd.dwd_yyd_oc_receipt_used dyoru 
join dwd.dwd_yyd_oc_order dyoo on dyoru.order_id = dyoo.id and dyoo.is_deleted = 0 and dyoo.order_type <> 'ZF'
join dwd.dwd_yyd_oc_order_item dyooi on dyoo.id = dyooi.order_id 
join dwd.dwd_yyd_sys_ddic dysd on dyoru.collection_type = dysd.dd_value and dysd.dd_type_code = 'PaymentsType' and dysd.dd_name like '%线下%'
left join (
	select DISTINCT id, case display_name when '总部' then 'DPT20200409010000004548' else external_id end as external_id
    from dwd.dwd_yyd_abp_organization_units dyaou
) temp_1 on dyoru.school_id = temp_1.id
left join dws.dws_bd_SchoolArea dbsa on temp_1.external_id = dbsa.school_dpt_id 
where dyoru.is_void = 0 and dyoru.receipt_category != 'XN' and dyoru.receipt_type = 0 and dyoru.is_paid = 1
and dyoru.receipt_source_code != '05' and dyoru.collection_type != '' and dyoru.is_deleted = 0
and dyoru.receipt_time >= '2022-04-01' and dyooi.product_model = '01' and dyooi.is_deleted = 0
group by dyoru.id, dyoo.id, dbsa.school_id, dbsa.school_name, dbsa.area_id, dbsa.area_name, dbsa.region_id, dbsa.region_name, dyoru.receipt_time,
case when dyoru.receipt_type = 1 or dyoo.order_type ='TF' then -dyoru.receipt_amount else dyoru.receipt_amount end

[0-4]优化-ads_fineReport.receipt_detail_business_info

原始SQL 语句
-- insert overwrite ads_fineReport.receipt_detail_business_info
select distinct t0.receiptId,t1.orderNo,if(t4.oiMainstatus = 'Y',t7.className,null) as pay_class_name,t8.classTypeusesourcetype as order_catagory,t2.custCreateddate,t5.projectName as c_init_project,
case t1.orderSource when 'OP' then '运营平台Web' when 'OA' then '运营平台App' when 'YP' then '优路教育官网' when 'YA' then '优路教育App' 
when 'TM' then '天猫' when 'JD' then '京东' when 'DA' then '合作代理平台' when 'EC' then '企业客户' when 'WMP' then '微信小程序' when 'XET' then '小鹅通' 
when 'MC' then '营销中心' when 'PDD' then '拼多多' when 'DY' then '抖音' when 'YOUZAN' then '有赞' when 'WXSHOP' then '微信小商店' when 'WX' then '网销平台' when 'SH' then '学员自助' when 'KS' then '快手小店' when 'HD' THEN '活动中心' when 'WXVD' THEN '视频号小店' else null end as order_source,
case t1.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,
t1.orderCreateddate,t6.userName
from (select distinct receiptId,orderId from datawarehouse.dwd_oc_OcReceipt where substring(receiptTradedate,1,4) >= '2021') t0
left join (select * from datawarehouse.dwd_oc_OcOrder where orderDelstatus = 'N' and orderAvlstatus = 'Y' and orderId <> '') t1
on t0.orderId = t1.orderId
LEFT join (select * from datawarehouse.dwd_ct_CtCust where custAvlstatus = 'Y' and custDelstatus = 'N' and userId <> '') t2
ON t1.userId = t2.userId
LEFT join (select * from datawarehouse.dwd_oc_OcClassorder where orderId <> '') t3
ON t1.orderId = t3.orderId
LEFT join (select * from datawarehouse.dwd_oc_OcOrderitem where oiAvlstatus = 'Y' and oiDelstatus = 'N' and orderId <> '') t4
ON t1.orderId = t4.orderId
LEFT join (select * from datawarehouse.dwd_bd_BdProject where projectId <> '') t5
on t2.custInitproject = t5.projectId
LEFT join (select distinct userId,userName from datawarehouse.dwd_bd_BdEe where eeAvlstatus = 'Y' and eeDelstatus = 'N' and userId <> '') t6
ON t2.custCreator = t6.userId
left join (select * from datawarehouse.dwd_bd_BdClass where classAvlstatus = 'Y' and classDelstatus = 'N' and classId <> '' and classTypeid <> '') t7
on t4.skuId = t7.classId
left join (select distinct classTypeid,classTypeusesourcetype from datawarehouse.dwd_bd_BdClasstype where classTypeid <> '' and classTypeusesourcetype = 'E') t8
on t7.classTypeid = t8.classTypeid;
最新 优化后SQL 语句
-- insert overwrite ads_fineReport.receipt_detail_business_info
select
door.receiptId,
dooo.orderNo,
case dooo2.oiMainstatus when 'Y' then dbbc.className else null end as pay_class_name,
dbbc2.classTypeusesourcetype as order_catagory,
dccc.custCreateddate,
dbbp.projectName as c_init_project,
case dooo.orderSource when 'OP' then '运营平台Web' when 'OA' then '运营平台App' when 'YP' then '优路教育官网' when 'YA' then '优路教育App' when 'TM' then '天猫' when 'JD' then '京东' when 'DA' then '合作代理平台' when 'EC' then '企业客户' when 'WMP' then '微信小程序' when 'XET' then '小鹅通' when 'MC' then '营销中心' when 'PDD' then '拼多多' when 'DY' then '抖音' when 'YOUZAN' then '有赞' when 'WXSHOP' then '微信小商店' when 'WX' then '网销平台' when 'SH' then '学员自助' when 'KS' then '快手小店' when 'HD' THEN '活动中心' when 'WXVD' THEN '视频号小店' else null end as order_source,
case dooo.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,
dooo.orderCreateddate,
dbbe.userName
from datawarehouse.dwd_oc_OcReceipt door 
left join datawarehouse.dwd_oc_OcOrder dooo on door.orderId = dooo.orderId and dooo.orderDelstatus = 'N' and dooo.orderAvlstatus = 'Y'
left join datawarehouse.dwd_oc_OcClassorder dooc on dooo.orderId = dooc.orderId 
left join datawarehouse.dwd_oc_OcOrderitem dooo2 on dooo.orderId = dooo2.orderId and dooo2.oiAvlstatus = 'Y' and dooo2.oiDelstatus = 'N'
left join datawarehouse.dwd_bd_BdClass dbbc on dooo2.skuId = dbbc.classId and dbbc.classAvlstatus = 'Y' and dbbc.classDelstatus = 'N'
left join datawarehouse.dwd_bd_BdClasstype dbbc2 on dbbc.classtypeId = dbbc2.classtypeId and dbbc2.classtypeUsesourcetype = 'E'
left join datawarehouse.dwd_ct_CtCust dccc on dooo.userId = dccc.userId and dccc.custAvlstatus = 'Y' and dccc.custDelstatus = 'N'
left join datawarehouse.dwd_bd_BdProject dbbp on dccc.custInitproject = dbbp.projectId 
left join datawarehouse.dwd_bd_BdEe dbbe on dccc.custCreator = dbbe.userId and dbbe.eeAvlstatus = 'Y' and dbbe.eeDelstatus = 'N'
where  door.receiptTradedate >= '2021-01-01'
group by door.receiptId, dooo.orderNo, dbbc2.classTypeusesourcetype, dccc.custCreateddate, 
dbbp.projectName, dooo.orderSource, dooo.orderPlatformappcode, dooo.orderCreateddate, dbbe.userName, 
case dooo2.oiMainstatus when 'Y' then dbbc.className else null end

[0-5]优化-ads_fineReport.qc_school_total_mobile_uat

原始SQL 语句
-- insert overwrite ads_fineReport.qc_school_total_mobile_uat
SELECT *,cast(now() as string) as lastUpdateTime
FROM
(SELECT everyDay,systemType,dataType,school_id,school_name,
sum(if(receipt_Type = '收入' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as advancePay, -- 预收金额
sum(if(receipt_Type = '收入' and (checked_status = '审核通过') and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as actualPay, -- 实收金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as returnPay, -- 实退金额
sum(if(receipt_Type = '收入' and (checked_status = '审核通过') and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) + 
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as netPay, -- 净收金额
sum(if(receipt_Type = '支出',cast(class_amount as float), 0.0)) as advanceReturn, -- 预退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as alreadyReturn, -- 已退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '未支付', cast(class_amount as float), 0.0)) as awaitReturn, -- 待退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '未支付', cast(class_amount as float), 0.0)) as validAwaitReturn, -- 有效待退金额
count(distinct(case when receipt_Type = '收入' and receipt_pay_status = '已支付' then order_no else null end)) as orderCount -- 订单数量
FROM (SELECT *,
	DATE_FORMAT(receipt_trade_date,'yyyyMMdd') as everyDay,
	case system_source when '订单中心' then 0 when 'CRM1' then 1 when 'CRM2' then 2 when 'CRM3' then 3 when 'CRM4' then 4 when 'CRM5' then 5 when 'CRM6' then 6 when 'WX' then 7 
	when 'HQ' then 8 when 'BJ' then 9 when '安协' then 10 when '中过' then 11 when '优一点' then 12 when '老学历' then 13 when '合作商' then 14 when '企业客户' then 15 when '新优一点' then 16
	when '升学业务' then 17 else null end as systemType,case achievement_type when '培训业绩' then 1 when '合作业绩' then 2 when '其他业绩' then 3 else null end as dataType 
	FROM ads_fineReport.ads_achievement_base_uat) r
WHERE receipt_id not in
(select distinct receipt_id from ads_fineReport.ads_achievement_base_uat where system_source = '订单中心' and substring(receipt_trade_date,1,10) >= '2022-03-01' and project_id2 = 'PROJECT20210507210000000002')
GROUP BY everyDay,systemType,dataType,school_id,school_name) r
where substring(everyDay,1,4) <> '2020'
;
最新 优化后SQL 语句
-- insert overwrite ads_fineReport.qc_school_total_mobile_uat
SELECT everyDay,systemType,dataType,school_id,school_name,
sum(if(receipt_Type = '收入' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as advancePay, -- 预收金额
sum(if(receipt_Type = '收入' and (checked_status = '审核通过') and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as actualPay, -- 实收金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as returnPay, -- 实退金额
sum(if(receipt_Type = '收入' and (checked_status = '审核通过') and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) + 
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as netPay, -- 净收金额
sum(if(receipt_Type = '支出',cast(class_amount as float), 0.0)) as advanceReturn, -- 预退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as alreadyReturn, -- 已退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '未支付', cast(class_amount as float), 0.0)) as awaitReturn, -- 待退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '未支付', cast(class_amount as float), 0.0)) as validAwaitReturn, -- 有效待退金额
count(distinct(case when receipt_Type = '收入' and receipt_pay_status = '已支付' then order_no else null end)) as orderCount, -- 订单数量
cast(now() as string) as lastUpdateTime
FROM (
	SELECT school_id ,school_name ,receipt_type ,receipt_pay_status ,class_amount ,checked_status ,order_no ,receipt_id ,
	DATE_FORMAT(receipt_trade_date,'yyyyMMdd') as everyDay,
	case system_source when '订单中心' then 0 when 'CRM1' then 1 when 'CRM2' then 2 when 'CRM3' then 3 when 'CRM4' then 4 when 'CRM5' then 5 when 'CRM6' then 6 when 'WX' then 7 
	when 'HQ' then 8 when 'BJ' then 9 when '安协' then 10 when '中过' then 11 when '优一点' then 12 when '老学历' then 13 when '合作商' then 14 when '企业客户' then 15 when '新优一点' then 16
	when '升学业务' then 17 else null end as systemType,
	case achievement_type when '培训业绩' then 1 when '合作业绩' then 2 when '其他业绩' then 3 else null end as dataType 
	FROM ads_fineReport.ads_achievement_base_uat
) r
WHERE receipt_id not in (select distinct receipt_id from ads_fineReport.ads_achievement_base_uat where system_source = '订单中心' and receipt_trade_date >= '2022-03-01' and project_id2 = 'PROJECT20210507210000000002')
GROUP BY everyDay,systemType,dataType,school_id,school_name

[0-6]优化-ads_fineReport.qc_project_total_mobile_uat

原始SQL 语句
-- insert overwrite ads_fineReport.qc_project_total_mobile_uat
SELECT *
FROM
(SELECT everyDay,systemType,dataType,project_id,project_name2,
sum(if(receipt_Type = '收入' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as advancePay, -- 预收金额
sum(if(receipt_Type = '收入' and (checked_status = '审核通过') and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as actualPay, -- 实收金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as returnPay, -- 实退金额
sum(if(receipt_Type = '收入' and (checked_status = '审核通过') and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) + 
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as netPay, -- 净收金额
sum(if(receipt_Type = '支出',cast(class_amount as float), 0.0)) as advanceReturn, -- 预退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as alreadyReturn, -- 已退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '未支付', cast(class_amount as float), 0.0)) as awaitReturn, -- 待退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '未支付', cast(class_amount as float), 0.0)) as validAwaitReturn, -- 有效待退金额
count(distinct(case when receipt_Type = '收入' and receipt_pay_status = '已支付' then order_no else null end)) as orderCount, -- 订单数量
count(distinct(case when receipt_Type = '支出' then order_no else null end)) as returnOrderCount, -- 退费订单数量
null as orderBusinessType
FROM (SELECT *,null as project_id,
	DATE_FORMAT(receipt_trade_date,'yyyyMMdd') as everyDay,
	case system_source when '订单中心' then 0 when 'CRM1' then 1 when 'CRM2' then 2 when 'CRM3' then 3 when 'CRM4' then 4 when 'CRM5' then 5 when 'CRM6' then 6 when 'WX' then 7 
	when 'HQ' then 8 when 'BJ' then 9 when '安协' then 10 when '中过' then 11 when '优一点' then 12 when '老学历' then 13 when '合作商' then 14 when '企业客户' then 15 when '新优一点' then 16
	when '升学业务' then 17 else null end as systemType,case achievement_type when '培训业绩' then 1 when '合作业绩' then 2 when '其他业绩' then 3 else null end as dataType 
	FROM ads_fineReport.ads_achievement_base_uat) r
WHERE receipt_id not in
(select distinct receipt_id from ads_fineReport.ads_achievement_base_uat where system_source = '订单中心' and substring(receipt_trade_date,1,10) >= '2022-03-01' and project_id2 = 'PROJECT20210507210000000002')
GROUP BY everyDay,systemType,dataType,project_id,project_name2) r
;
最新 优化后SQL 语句
-- insert overwrite ads_fineReport.qc_project_total_mobile_uat
SELECT everyDay,systemType,dataType,project_id,project_name2,
sum(if(receipt_Type = '收入' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as advancePay, -- 预收金额
sum(if(receipt_Type = '收入' and (checked_status = '审核通过') and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as actualPay, -- 实收金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as returnPay, -- 实退金额
sum(if(receipt_Type = '收入' and (checked_status = '审核通过') and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) + 
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as netPay, -- 净收金额
sum(if(receipt_Type = '支出',cast(class_amount as float), 0.0)) as advanceReturn, -- 预退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '已支付', cast(class_amount as float), 0.0)) as alreadyReturn, -- 已退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '未支付', cast(class_amount as float), 0.0)) as awaitReturn, -- 待退金额
sum(if(receipt_Type = '支出' and receipt_pay_status = '未支付', cast(class_amount as float), 0.0)) as validAwaitReturn, -- 有效待退金额
count(distinct(case when receipt_Type = '收入' and receipt_pay_status = '已支付' then order_no else null end)) as orderCount, -- 订单数量
count(distinct(case when receipt_Type = '支出' then order_no else null end)) as returnOrderCount, -- 退费订单数量
null as orderBusinessType
FROM (SELECT null as project_id, project_name2 ,receipt_type ,receipt_pay_status ,class_amount ,checked_status ,order_no ,receipt_id ,
	DATE_FORMAT(receipt_trade_date,'yyyyMMdd') as everyDay,
	case system_source when '订单中心' then 0 when 'CRM1' then 1 when 'CRM2' then 2 when 'CRM3' then 3 when 'CRM4' then 4 when 'CRM5' then 5 when 'CRM6' then 6 when 'WX' then 7 
	when 'HQ' then 8 when 'BJ' then 9 when '安协' then 10 when '中过' then 11 when '优一点' then 12 when '老学历' then 13 when '合作商' then 14 when '企业客户' then 15 when '新优一点' then 16
	when '升学业务' then 17 else null end as systemType,
	case achievement_type when '培训业绩' then 1 when '合作业绩' then 2 when '其他业绩' then 3 else null end as dataType 
	FROM ads_fineReport.ads_achievement_base_uat
) r
WHERE receipt_id not in (select distinct receipt_id from ads_fineReport.ads_achievement_base_uat where system_source = '订单中心' and receipt_trade_date >= '2022-03-01' and project_id2 = 'PROJECT20210507210000000002')
GROUP BY everyDay,systemType,dataType,project_id,project_name2

posted on 2025-02-24 14:07  cloud_wh  阅读(63)  评论(0)    收藏  举报

导航