StarRocks-性能优化（实战07）

[11]sql优化-datawarehouse.cust_info_view

原始SQL 语句
insert overwrite datawarehouse.cust_info_view
select 
t1.custId,
t1.custCreateddate,
t1.custMobile,
t1.custEdulevel,
t1.custWorkunit,
t1.custGender,
t1.custBirthday,
IF(t1.custBirthday <> '',YEAR(NOW())-SUBSTRING(t1.custBirthday, 1, 4),0) as custAge, 
t1.custType,
t1.custActivedate,
t1.custAreacode,
t5.dictname,
t1.custCreator,
t8.userName,
t2.custprojGradestatus,
t6.school_id,
t6.school_name ,
t6.area_id as areaId,
t6.area_name as areaName,
t10.originName2,
t10.originName1,
t2.projectId,
t4.projectName2,
t4.projectId,
t4.projectName,
t7.userName,
t9.originName2,
t9.originName1,
t3.projectName2,
t3.projectName,
t1.userId,
t11.userYoulunum,
t11.userCreateddate,
t13.dictName,
case t1.custGender when 'M' then '男' when 'F' then '女' else '' end as custGender_name,
case t1.custType when 'C' then '客户' when 'S' then '学员' else '' end as custType_name,
case t2.custprojGradestatus when 'Y' then '已报班' when 'N' then '未报班' else '' end as custprojGradestatus_name,
t12.studentCreateddate,
case t1.CustSubType when 'CP' then '普通客户' when 'CW' then '微课客户' when 'SP' then '学员' end as custSubType,
t14.custprojLatestSourcechannel_name as custprojLatestSourcechannel_name,
t14.custprojCreateddate as custprojCreateddate,
t14.custprojLatestcluetime,
CONCAT(t10.originName1,'>>',t10.originName2) as custprojSourcechannel_name
from (select * from datawarehouse.dwd_ct_CtCust where custAvlstatus = 'Y' and custDelstatus = 'N' and isPhysicsDel = 2) t1
inner join datawarehouse.dwd_ct_CtCustindex t2 on t1.custId = t2.custId and t2.isPhysicsDel = 2 and t2.custprojDelstatus = 'N'
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) t3
on t1.custInitproject = t3.projectId2
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) t4
on t2.projectId = t4.projectId2
left join datawarehouse.dwd_uc_UcDict t5 on t1.custAreacode = t5.dictCode  -- dictname
left join dws.dws_bd_SchoolArea t6 on t2.custprojSchool = t6.school_id 
LEFT JOIN datawarehouse.dwd_bd_BdEe t7 on t2.custprojOwner = t7.userId and t7.isPhysicsDel = 2 and t7.eeDelstatus = 'N' -- userName
LEFT JOIN datawarehouse.dwd_bd_BdEe t8 on t1.custCreator = t8.userId and t8.isPhysicsDel =2 and t8.eeDelstatus = 'N' -- userName
left JOIN (select a.originId as originId2,a.originName as originName2,b.originId as originId1,b.originName as originName1
                        from datawarehouse.dwd_es_BdOrigin a
                        left join datawarehouse.dwd_es_BdOrigin b on substring(a.originLevelcode,1,10) = b.originLevelcode) t9
on t1.custSourcechannel = t9.originId2
left JOIN (select a.originId as originId2,a.originName as originName2,b.originId as originId1,b.originName as originName1
                        from datawarehouse.dwd_es_BdOrigin a
                        left join datawarehouse.dwd_es_BdOrigin b on substring(a.originLevelcode,1,10) = b.originLevelcode) t10
on t2.custprojSourcechannel = t10.originId2
LEFT join (select * from datawarehouse.dwd_uc_UcUser where userId <> '' and isPhysicsDel = 2 and userDelstatus = 'N' and userAvlstatus = 'Y') t11
on t1.userId = t11.userId
left join (select * from datawarehouse.dwd_bd_BdStudent where isPhysicsDel = 2 and studentAvlstatus = 'Y' and studentDelstatus = 'N' and userId<>'') t12
on t1.userId = t12.userId
left join (select * from datawarehouse.dwd_ct_UcDict dcud where dictType = 'edulevel') t13
on t1.custEdulevel = t13.dictCode
left join (select custprojId,custprojLatestcluetime,custprojCreateddate,CONCAT(t10.originName1,'>>',t10.originName2) as custprojLatestSourcechannel_name
		from datawarehouse.dwd_ct_CtCustproj dccc 
		left JOIN (select a.originId as originId2,a.originName as originName2,b.originId as originId1,b.originName as originName1
		        from datawarehouse.dwd_es_BdOrigin a
		        left join datawarehouse.dwd_es_BdOrigin b on substring(a.originLevelcode,1,10) = b.originLevelcode) t10 on dccc.custprojLatestSourcechannel = t10.originId2
		where dccc.custprojDelstatus = 'N') t14 on t2.custprojId = t14.custprojId 

group by t1.custId,t1.custCreateddate,t1.custMobile,t1.custIdcard,t1.custEdulevel,t1.custWorkunit,t1.custGender,t1.custBirthday,t1.custType,t1.custActivedate,
t1.custAreacode,t5.dictname,t1.custCreator,t8.userName,t2.custprojGradestatus,t6.school_id ,t6.school_name ,t6.area_id ,t6.area_name ,
t10.originName2,t10.originName1,t2.projectId,t4.projectName2,t4.projectId,t4.projectName,t7.userName,
t9.originName2,t9.originName1,t3.projectName2,t3.projectName,t1.userId,t11.userYoulunum,t12.studentCreateddate,t13.dictName,t11.userCreateddate,custSubType,
t14.custprojLatestSourcechannel_name,t14.custprojCreateddate,t14.custprojLatestcluetime;
最新 优化后SQL 语句
-- 【客户信息_前置客户信息表】
CREATE TABLE `cust_info_view_cust` (
  `custId` varchar(200) NULL COMMENT "客户ID",
  `custCreateddate` varchar(300) NULL COMMENT "客户创建时间",
  `custMobile` varchar(300) NULL COMMENT "客户手机号",
  `custEdulevel` varchar(300) NULL COMMENT "客户学历编号",
  `custWorkunit` varchar(2000) NULL COMMENT "客户工作单位名称",
  `custGender` varchar(300) NULL COMMENT "客户性别(M:男性;F:女性)",
  `custBirthday` varchar(300) NULL COMMENT "客户出生年月",
  `custAge` varchar(65333) NULL COMMENT "年龄",
  `custType` varchar(300) NULL COMMENT "客户类型(C:客户;S:学员)",
  `custActivedate` varchar(300) NULL COMMENT "激活時間",
  `custAreacode` varchar(300) NULL COMMENT "所在地区",
  `custArea_name` varchar(1000) NULL COMMENT "所在地区名称",
  `custCreator` varchar(300) NULL COMMENT "客户创建人标识",
  `custCreator_name` varchar(300) NULL COMMENT "客户创建人名称",
  `custSourcechannel_name_2` varchar(300) NULL COMMENT "客户初始来源二级名称",
  `custSourcechannel_name_1` varchar(300) NULL COMMENT "客户初始来源一级名称",
  `custInitproject_name_2` varchar(300) NULL COMMENT "客户初始项目二级名称",
  `custInitproject_name_1` varchar(300) NULL COMMENT "客户初始项目一级名称",
  `userId` varchar(300) NULL COMMENT "用户标识",
  `userYoulunum` varchar(300) NULL COMMENT "优路号",
  `userCreateddate` varchar(65333) NULL COMMENT "用户创建时间",
  `custEdulevel_name` varchar(65333) NULL COMMENT "学历名称",
  `custGender_name` varchar(65333) NULL COMMENT "性别名称",
  `custType_name` varchar(65333) NULL COMMENT "客户类型名称",
  `custSubType` varchar(65333) NULL COMMENT "客户子类型名称",
  `studentCreateddate` varchar(65333) NULL COMMENT "学生创建时间",
  `data_created_time` varchar(300) NULL COMMENT "数据更新时间(yyyy-MM-dd HH:mm:ss)"
) ENGINE=OLAP 
DUPLICATE KEY(`custId`)
COMMENT "客户信息_前置客户信息表"
DISTRIBUTED BY HASH(`custId`) BUCKETS 12 
PROPERTIES (
"replication_num" = "1",
"in_memory" = "false",
"storage_format" = "DEFAULT",
"enable_persistent_index" = "false",
"compression" = "LZ4"
);


-- 调度
insert overwrite datawarehouse.cust_info_view_cust
select DISTINCT 
t1.custId,
t1.custCreateddate,
t1.custMobile,
t1.custEdulevel,
t1.custWorkunit,
t1.custGender,
t1.custBirthday,
IF(t1.custBirthday <> '',YEAR(NOW())-SUBSTRING(t1.custBirthday, 1, 4),0) as custAge, 
t1.custType,
t1.custActivedate,
t1.custAreacode,
t1_2.dictName as custArea_name, 
t1.custCreator,
t1_3.userName as custCreator_name,
t1_4.origin_name_two as custSourcechannel_name_2, 
t1_4.origin_name_one as custSourcechannel_name_1,
t1_1.project_name_two as custInitproject_name_2, 
t1_1.project_name_one as custInitproject_name_1,
t1.userId,
t1_5.userYoulunum , 
t1_5.userCreateddate , 
t1_7.dictName as custEdulevel_name,
case t1.custGender when 'M' then '男' when 'F' then '女' else '' end as custGender_name,
case t1.custType when 'C' then '客户' when 'S' then '学员' else '' end as custType_name,
case t1.CustSubType when 'CP' then '普通客户' when 'CW' then '微课客户' when 'SP' then '学员' end as custSubType,
t1_6.studentCreateddate , 
cast(now() as string) as data_created_time
-- 客户表
from datawarehouse.dwd_ct_CtCust t1 
-- 客户初始项目
left join dws.dws_bd_ProjectInfo t1_1 on t1.custInitproject = t1_1.project_id_two 
-- 所在地区
left join datawarehouse.dwd_uc_UcDict t1_2 on t1.custAreacode = t1_2.dictCode
-- 客户创建人信息
left join datawarehouse.dwd_bd_BdEe t1_3 on t1.custCreator = t1_3.userId and t1_3.eeDelstatus = 'N' and t1_3.isPhysicsDel =2
-- 客户初始来源
left join dws.dws_es_ClueOrigin t1_4 on t1.custSourcechannel = t1_4.origin_id_two 
-- 用户信息
left join datawarehouse.dwd_uc_UcUser t1_5 on t1.userId = t1_5.userId and t1_5.userAvlstatus = 'Y' and t1_5.userDelstatus = 'N' and t1_5.isPhysicsDel = 2
-- 学员信息
left join datawarehouse.dwd_bd_BdStudent t1_6 on t1.userId = t1_6.userId and t1_6.studentAvlstatus = 'Y' and t1_6.studentDelstatus = 'N' and t1_6.isPhysicsDel = 2
-- 客户学历
left join datawarehouse.dwd_ct_UcDict t1_7 on t1.custEdulevel = t1_7.dictCode and t1_7.dictType = 'edulevel'

-- 本节点类型适用于MySQL数据源
insert overwrite datawarehouse.cust_info_view
select 
t1.custId,
t1.custCreateddate,
t1.custMobile,
t1.custEdulevel,
t1.custWorkunit,
t1.custGender,
t1.custBirthday,
t1.custAge, 
t1.custType,
t1.custActivedate,
t1.custAreacode,
t1.custArea_name,
t1.custCreator,
t1.custCreator_name,
t2.custprojGradestatus ,
t2_2.school_id as custprojSchool_id,
t2_2.school_name as custprojSchool_name,
t2_2.area_id as custprojArea_id,
t2_2.area_name as custprojArea_name,
t2_3.origin_name_two as custprojSourcechannel_name_2,
t2_3.origin_name_one as custprojSourcechannel_name_1,
t2.projectId as commProjectId_2,
t2_1.project_name_two as commProjectName_2,
t2_1.project_id_one as commProjectId_1,
t2_1.project_name_one as commProjectName_1,
t2_4.userName as custprojOwner_name,
t1.custSourcechannel_name_2,
t1.custSourcechannel_name_1,
t1.custInitproject_name_2,
t1.custInitproject_name_1,
t1.userId,
t1.userYoulunum,
t1.userCreateddate,
t1.custEdulevel_name,
t1.custGender_name,
t1.custType_name,
case t2.custprojGradestatus when 'Y' then '已报班' when 'N' then '未报班' else '' end as custprojGradestatus_name,
t1.studentCreateddate,
t1.CustSubType,
t3.custprojId, 
t3.custprojLatestcluetime, 
t3.custprojCreateddate, 
CONCAT(t3_1.origin_name_one ,'>>',t3_1.origin_name_two) as custprojLatestSourcechannel_name 

-- 【客户信息_前置客户信息表】
from datawarehouse.cust_info_view_cust t1
-- 客户索引表
inner join datawarehouse.dwd_ct_CtCustindex t2 on t1.custId = t2.custId and t2.isPhysicsDel = 2 and t2.custprojDelstatus = 'N'
-- 客户所属项目
left join dws.dws_bd_ProjectInfo t2_1 on t2.projectId = t2_1.project_id_two 
-- 客户项目所属分校
left join dws.dws_bd_SchoolArea t2_2 on t2.custprojSchool = t2_2.school_id 
-- 客户项目初始来源
left join dws.dws_es_ClueOrigin t2_3 on t2.custprojSourcechannel = t2_3.origin_id_two 
-- 客户项目所属人
left join datawarehouse.dwd_bd_BdEe t2_4 on t2.custprojOwner = t2_4.userId and t2_4.eeDelstatus = 'N' and t2_4.isPhysicsDel = 2 
-- 客户行为来源、客户项目创建时间、最后行为时间
left join datawarehouse.dwd_ct_CtCustproj t3 on t2.custprojId = t3.custprojId and t3.custprojDelstatus = 'N'
-- 客户项目来源
left join dws.dws_es_ClueOrigin t3_1 on t3.custprojLatestSourcechannel = t3_1.origin_id_two

posted on   cloud_wh  阅读(6)  评论(0)  编辑  收藏  举报

相关博文：
·  StarRocks-性能优化（实战02）
·  StarRocks-性能优化（实战04）
·  MySQL 数据库优化的层次和思路以及相关工具
·  MySQL高级3【性能分析工具-索引优化-查询优化】尚硅谷
·  第09章_性能分析工具的使用
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示