-- 分片区 select case when a.region_id='未知' then '未知' else a.salesregion_name end salesregion_name, sum(a.total_follow_csm_qty) total_follow_csm_qty, -- 跟进消费者 sum(a.total_unfollow_csm_qty) total_unfollow_csm_qty, -- 未跟进消费者 sum(aa.inc_csm_qty) inc_csm_qty, sum(a.total_core_csm_qty) total_core_csm_qty, sum(aa.inc_core_csm_qty) inc_core_csm_qty, sum(a.total_core_csm_mem_qty) total_core_csm_mem_qty, sum(aa.inc_core_csm_mem_qty) inc_core_csm_mem_qty, sum(a.total_active_core_csm_qty) total_active_core_csm_qty, sum(d.active_consumer_qty) as active_core_csm_target, sum(a.total_active_core_csm_qty)/sum(d.active_consumer_qty) as active_core_csm_target_ratio, sum(fa.sunkol_qty) sunkol_qty, sum(case when '${p_cmpy_code}' = '5137' then fa.tq_kol_qty else fa.kol_qty end) kol_qty, sum(fa.moonkol_qty) moonkol_qty, sum(fa.kolonsult_qty) kolonsult_qty, sum(fa.starkol_qty) starkol_qty, sum(fa.koc_qty) koc_qty, sum(fa.poc_qty) poc_qty, sum(b.mc_visit_times) as inc_visit_qty, sum(b.mc_present_times) as inc_present_qty, sum(c.act_times) as inc_mc_qty, sum(c.act_fee_act) as inc_mc_amt, sum(d.core_consumer_qty) as csm_124_target_qty, round(sum(a.total_core_csm_qty)/sum(d.core_consumer_qty),4) csm_124_target_ratiofrom (-- 计算总数-已跟进消费者总数&未跟进消费者总数&核心消费者总数&核心消费者中会员覆盖数&活跃核心消费者数量（筛选结束时间之前的数据） select IFNULL(cd.cms_region_id,'未知') as region_id, IFNULL(cd.cms_region_name,'未知') as region_name, IFNULL(cd.cms_area_id,'未知') as salesregion_id, IFNULL(cd.cms_area_name,'未知') as salesregion_name, count(distinct case when cd.is_follow=1 then cd.one_id else null end) total_follow_csm_qty, -- 已跟进消费者总数 count(distinct case when cd.is_follow<>1 then cd.one_id else null end) total_unfollow_csm_qty, -- 未跟进消费者总数 count(distinct case when cd.is_follow=1 and cd.is_core_consumer=1 then cd.one_id else null end) total_core_csm_qty, -- 核心消费者总数 count(distinct case when cd.is_follow=1 and is_core_consumer=1 and cd.member_id is not null then cd.one_id else null end) total_core_csm_mem_qty, -- 核心消费者中会员覆盖数 count(distinct case when cd.is_follow=1 and cd.is_core_consumer=1 then td.one_id else null end) total_active_core_csm_qty -- 活跃核心消费者数量 from dm_r_cm_csm_cmpy_consumer_detail as cd -- 筛选有权限的数据 inner join ( -- 取只有公司权限数据 select distinct fa.company_code, '1' as salesregion_id from (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fa left join (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fb on fa.company_code=fb.company_code union all -- 取只有片区的权限数据 select distinct fa.company_code, dco.l5_id as salesregion_id from (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fa left join (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fb on fa.company_code=fb.company_code left join (select l5_id,l5_code from dim.dim_company_org group by l5_id,l5_code) as dco -- 获取片区的id on fa.per_code=dco.l5_code where fb.company_code is null ) as ta on cd.company_code=ta.company_code and case when ta.salesregion_id<>'1' then cd.cms_area_id else 1 end = case when ta.salesregion_id<>'1' then ta.salesregion_id else 2 end -- 如果权限表里片区字段为1，表示拥有该公司下的所有权限； -- 如果权限表里片区字段不为1，表示拥有该公司下某个片区的所有权限；-- 原来的逻辑-- left join (select * from dm_r_cm_csm_mc_ic_times_detail where business_year_id=(select max(business_year_id) from dm_r_cm_csm_mc_ic_times_detail)) as td -- 各财年触达次数-- on cd.one_id=td.one_id -- 本财年活跃核心消费者-- and cd.cms_company_id=td.cms_company_id-- 新的逻辑 left join (select one_id,cms_company_id from dm_r_cm_csm_mc_ic_times_detail where business_year_id=(select max(business_year_id) from dm_r_cm_csm_mc_ic_times_detail) group by one_id,cms_company_id having sum(mc_ic_times)>1 -- 触达次数大于1的数据为活跃消费者 ) as td -- 各财年触达次数 on cd.one_id=td.one_id -- 本财年活跃核心消费者 and cd.cms_company_id=td.cms_company_id where 1=1 and cd.is_emp= 0 and cd.is_acct = 0 and cd.is_authentication=1 and cd.company_cate_code = '${p_cmpy_code}' and cd.csm_create_date <= '${edate}' ${if(len(region_code)==0,"","AND cd.cms_region_name = '"+ region_code +"'")} ${if(len(area_code)==0,"","AND cd.cms_area_name = '"+ area_code +"'")} group by IFNULL(cd.cms_region_id,'未知') , IFNULL(cd.cms_region_name,'未知'), IFNULL(cd.cms_area_id,'未知'), IFNULL(cd.cms_area_name,'未知')) as a left join (-- 新增消费者数量&新增核心消费者&核心消费者中新增会员覆盖数 select IFNULL(cd.cms_region_id,'未知') as region_id, IFNULL(cd.cms_region_name,'未知') as region_name, IFNULL(cd.cms_area_id,'未知') as salesregion_id, IFNULL(cd.cms_area_name,'未知') as salesregion_name, count(distinct case when cd.csm_create_date >= '${p_begin_day}' then cd.one_id else null end) inc_csm_qty, -- 新增消费者 count(distinct case when cd.is_core_consumer=1 and cd.csm_create_date >= '${p_begin_day}' then cd.one_id else null end) inc_core_csm_qty, -- 新增核心消费者 count(distinct case when is_core_consumer=1 and cd.csm_create_date >= '${p_begin_day}' and cd.member_id is not null then cd.one_id else null end) inc_core_csm_mem_qty -- 核心消费者中新增会员覆盖数 from dm_r_cm_csm_cmpy_consumer_detail as cd -- 筛选有权限的数据 inner join ( -- 取只有公司权限数据 select distinct fa.company_code, '1' as salesregion_id from (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fa left join (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fb on fa.company_code=fb.company_code union all -- 取只有片区的权限数据 select distinct fa.company_code, dco.l5_id as salesregion_id from (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fa left join (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fb on fa.company_code=fb.company_code left join (select l5_id,l5_code from dim.dim_company_org group by l5_id,l5_code) as dco -- 获取片区的id on fa.per_code=dco.l5_code where fb.company_code is null ) as ta on cd.company_code=ta.company_code and case when ta.salesregion_id<>'1' then cd.cms_area_id else 1 end = case when ta.salesregion_id<>'1' then ta.salesregion_id else 2 end -- 如果权限表里片区字段为1，表示拥有该公司下的所有权限； -- 如果权限表里片区字段不为1，表示拥有该公司下某个片区的所有权限； where 1=1 and cd.is_emp= 0 and cd.is_acct = 0 and cd.is_authentication=1 and cd.is_follow=1 and cd.company_cate_code = '${p_cmpy_code}' and cd.csm_create_date <= '${edate}' ${if(len(region_code)==0,"","AND cd.cms_region_name = '"+ region_code +"'")} ${if(len(area_code)==0,"","AND cd.cms_area_name = '"+ area_code +"'")} group by IFNULL(cd.cms_region_id,'未知') , IFNULL(cd.cms_region_name,'未知'), IFNULL(cd.cms_area_id,'未知'), IFNULL(cd.cms_area_name,'未知')) as aa on a.region_id=aa.region_id and a.salesregion_id=aa.salesregion_id left join (-- 核心消费者分城市 select IFNULL(cd.cms_region_id,'未知') as region_id, IFNULL(cd.cms_region_name,'未知') as region_name, IFNULL(cd.cms_area_id,'未知') as salesregion_id, IFNULL(cd.cms_area_name,'未知') as salesregion_name, count(distinct case when sdk.k_type_query = 'SunKOL' then cd.one_id else null end) sunkol_qty, --太阳级KOL总数量 count(distinct case when sdk.k_type_query like '%KOL%' then cd.one_id else null end) kol_qty, -- KOL总数 count(distinct case when sdk.k_type_query = 'KOLGeneral' then cd.one_id else null end) tq_kol_qty, -- 特曲普通会员 count(distinct case when sdk.k_type_query = 'KOLConsult' then cd.one_id else null end) kolonsult_qty, -- KOL 品牌顾问 count(distinct case when sdk.k_type_query = 'MoonKOL' then cd.one_id else null end) moonkol_qty, -- 月亮级KOL总数量 count(distinct case when sdk.k_type_query = 'StarKOL' then cd.one_id else null end) starkol_qty, -- 星星级KOL总数量 count(distinct case when sdk.k_type_query = 'KOC' then cd.one_id else null end) koc_qty, -- KOC总数量 count(distinct case when sdk.k_type_query = 'POC' then cd.one_id else null end) poc_qty -- POC总数量 from dm_r_cm_csm_cmpy_consumer_detail as cd -- 筛选有权限的数据 inner join ( -- 取只有公司权限数据 select distinct fa.company_code, '1' as salesregion_id from (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fa left join (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fb on fa.company_code=fb.company_code union all -- 取只有片区的权限数据 select distinct fa.company_code, dco.l5_id as salesregion_id from (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fa left join (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fb on fa.company_code=fb.company_code left join (select l5_id,l5_code from dim.dim_company_org group by l5_id,l5_code) as dco -- 获取片区的id on fa.per_code=dco.l5_code where fb.company_code is null ) as ta on cd.company_code=ta.company_code and case when ta.salesregion_id<>'1' then cd.cms_area_id else 1 end = case when ta.salesregion_id<>'1' then ta.salesregion_id else 2 end -- 如果权限表里片区字段为1，表示拥有该公司下的所有权限； -- 如果权限表里片区字段不为1，表示拥有该公司下某个片区的所有权限； left join ( -- 获取筛选结束日期时的k_type类型,且取最新的一条 select sd.consumer_id, sd.field_value as k_type_query, ROW_NUMBER() over(partition by consumer_id,create_date order by create_time desc) as rn_num from dim.dim_csm_track_scd as sd where sd.field_type='k_type' -- 限制类型为k_type and sd.start_date <= DATE_FORMAT('${edate}', '%Y%m%d') and ifnull(sd.end_date,"99991231") >= DATE_FORMAT('${edate}', '%Y%m%d') -- business_date为前端传过来的筛选时间的结束日期 ) as sdk on sdk.rn_num=1 and cd.ma_consumer_id=sdk.consumer_id where 1=1 and cd.is_emp= 0 and cd.is_acct = 0 and cd.is_follow=1 and cd.is_authentication=1 and cd.company_cate_code = '${p_cmpy_code}' and cd.csm_create_date <= '${edate}' ${if(len(region_code)==0,"","AND cd.cms_region_name = '"+ region_code +"'")} ${if(len(area_code)==0,"","AND cd.cms_area_name = '"+ area_code +"'")} group by IFNULL(cd.cms_region_id,'未知'), IFNULL(cd.cms_region_name,'未知'), IFNULL(cd.cms_area_id,'未知'), IFNULL(cd.cms_area_name,'未知')) as faon a.region_id=fa.region_id and a.salesregion_id=fa.salesregion_idleft join ( -- 消费者礼赠拜访指标(省份城市+销售公司) select IFNULL(cd.region_id,'未知') region_id, IFNULL(cd.salesregion_id,'未知') salesregion_id, sum(mc_visit_times) as mc_visit_times, -- 拜访人数 sum(mc_present_times) as mc_present_times -- 礼赠人数 from dm_t_cm_csm_date_consumer as cd -- 筛选有权限的数据 inner join ( -- 取只有公司权限数据 select distinct fa.company_code, '1' as salesregion_id from (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fa left join (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fb on fa.company_code=fb.company_code union all -- 取只有片区的权限数据 select distinct fa.company_code, dco.l5_id as salesregion_id from (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fa left join (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fb on fa.company_code=fb.company_code left join (select l5_id,l5_code from dim.dim_company_org group by l5_id,l5_code) as dco -- 获取片区的id on fa.per_code=dco.l5_code where fb.company_code is null ) as ta on cd.company_code=ta.company_code and case when ta.salesregion_id<>'1' then cd.salesregion_id else 1 end = case when ta.salesregion_id<>'1' then ta.salesregion_id else 2 end -- 如果权限表里片区字段为1，表示拥有该公司下的所有权限； -- 如果权限表里片区字段不为1，表示拥有该公司下某个片区的所有权限； where 1=1 and company_cate_code = '${p_cmpy_code}' and business_date>='${p_begin_day}' ${if(len(region_code)==0,"","AND region_name = '"+ region_code +"'")} ${if(len(area_code)==0,"","AND salesregion_name = '"+ area_code +"'")} group by IFNULL(cd.region_id,'未知'), IFNULL(cd.salesregion_id,'未知') having sum(mc_visit_times)>0 or sum(mc_present_times)>0) as b on a.region_id=b.region_id and a.salesregion_id=b.salesregion_idleft join ( -- 活动场次和活动费用 --分城市 select IFNULL(ct.region_id,'未知') region_id, IFNULL(ct.salesregion_id,'未知') salesregion_id, sum(inc_act_qty) as act_times, -- 活动场次 sum(inc_act_fee_amt) as act_fee_act -- 活动费用from dm_r_cm_csm_core_csm_target as ct -- 核心目标达成明细表 -- 筛选有权限的数据 inner join ( -- 取只有公司权限数据 select distinct fa.company_code, '1' as salesregion_id from (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fa left join (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fb on fa.company_code=fb.company_code union all -- 取只有片区的权限数据 select distinct fa.company_code, dco.l5_id as salesregion_id from (select company_code,per_code from dim.test_user_authority where user_code='${fine username}' and granularity='片区') as fa left join (select company_code from dim.test_user_authority where user_code='${fine username}' and granularity='公司') as fb on fa.company_code=fb.company_code left join (select l5_id,l5_code from dim.dim_company_org group by l5_id,l5_code) as dco -- 获取片区的id on fa.per_code=dco.l5_code where fb.company_code is null ) as ta on ct.company_code=ta.company_code and case when ta.salesregion_id<>'1' then ct.salesregion_id else 1 end = case when ta.salesregion_id<>'1' then ta.salesregion_id else 2 end -- 如果权限表里片区字段为1，表示拥有该公司下的所有权限； -- 如果权限表里片区字段不为1，表示拥有该公司下某个片区的所有权限；inner join (select * from dim.man_dim_cmpy_cate where theme='consumer') dcc on ct.cms_company_id=dcc.company_idwhere 1=1 and dcc.company_cate_code = '${p_cmpy_code}' and create_date>='${p_begin_day}' ${if(len(region_code)==0,"","AND region_name = '"+ region_code +"'")} ${if(len(area_code)==0,"","AND salesregion_name = '"+ area_code +"'")}group by IFNULL(ct.region_id,'未知'), IFNULL(ct.salesregion_id,'未知')) as c on a.region_id=c.region_id and a.salesregion_id=c.salesregion_idleft join ( -- 124目标人数指标(省份城市) select IFNULL(region_id,'未知') region_id, IFNULL(area_id,'未知') as salesregion_id, sum(core_consumer_qty) as core_consumer_qty, -- 目标核心消费者人数 sum(ifnull(active_consumer_qty,0)) as active_consumer_qty -- 目标活跃消费者人数 from man_consumer_target where year_id = (select max(year_id) from man_consumer_target) -- 手工124目标取最新导入的那一年 and cmpy_id = '${p_cmpy_code}' ${if(len(province_code)== 0,"","AND region_name = '" + region_code + "'")} ${if(len(city_code)== 0,"","AND area_name = '" + area_code + "'")} group by IFNULL(region_id,'未知'), IFNULL(area_id,'未知')) as d on a.region_id=d.region_id and a.salesregion_id=d.salesregion_idgroup by case when a.region_id='未知' then '未知' else a.salesregion_name endhaving sum(a.total_core_csm_qty)>0order by sum(a.total_core_csm_qty) desc;
