select p1.*
       ,p2.exposure_cnt
  from 
        ( -- 商户收入
         select p2.partner_id
                ,p2.partner_name
                ,p1.srv_line
                ,p2.cate_name   
                ,p2.city_name  
                ,p3.employee_no
                ,p3.employee_name   
                ,p3.l2_dept_name
                ,p3.l3_dept_name
                ,p3.l4_dept_name  
                ,p3.管理名字
                ,p3.管理工号                                
                ,p1.效果广告收入
                ,p1.年费收入
                ,p1.品广收入    
           from
                ( -- 商户收入数据
                 select partner_id
                        ,case when srv_line = '低频' then '教培' else srv_line end as srv_line 
                        ,sum(coalesce(ad_income_amt_1d,0))                                                    as 效果广告收入
                        ,sum(coalesce(new_sign_income_amt_1d,0)) + sum(coalesce(renew_sign_income_amt_1d,0))  as 年费收入
                        ,sum(coalesce(fb_brand_ad_income_amt_1d,0)) + sum(coalesce(brand_ad_income_amt_1d,0)) as 品广收入
                   from kbdw.dws_gd_info_fin_shop_employee_channel_income_1d
                  where ds >= '20250501'
                    and ds <= '20260431'
                    and biz_channel = 1 
                    and srv_line in ('低频','教培','美业','医疗')
                  group by partner_id
                        ,case when srv_line = '低频' then '教培' else srv_line end               
                ) p1 
          inner join 
                ( -- PID维表
                 select p1.partner_id
                        ,p1.partner_name
                        ,p1.city_name
                        ,max(cate_name) as cate_name
                   from
                        ( -- PID维表
                         select p1.partner_id
                                ,p1.partner_name
                                ,p1.atag_id
                                ,p2.city_name
                           from
                                ( -- PID维表
                                 select partner_id
                                        ,max(partner_name)                                           as partner_name
                                        ,max(coalesce(atag_id_level3,atag_id_level2,atag_id_level1)) as atag_id
                                   from gd_info_cdm.dim_gd_info_partner
                                  where ds = MAX_PT('gd_info_cdm.dim_gd_info_partner')
                                  group by partner_id                                    
                                ) p1 
                          inner join 
                                ( -- 门店维表
                                 select partner_id
                                        ,shop_id
                                        ,city_name
                                   from gd_info_cdm.mds_gd_info_shop_basic_ds 
                                  where ds = max_pt('gd_info_cdm.mds_gd_info_shop_basic_ds') 
                                  group by partner_id
                                        ,shop_id
                                        ,city_name 
                                ) p2 
                             on p1.partner_id = p2.partner_id
                          group by p1.partner_id
                                ,p1.partner_name
                                ,p1.atag_id
                                ,p2.city_name     
                        ) p1 
                   left join 
                        ( -- 类目维表
                         select atagid
                                ,srv_line 
                                ,l1_srvline
                                ,l2_srvline
                                ,l3_srvline         
                                ,case when srv_line = '医疗' then 
                                      case when l1_srvline = '妇产及辅助生殖' then '妇产及辅助生殖'
                                      when l1_srvline = '口腔齿科'      then '口腔齿科'
                                      when l1_srvline = '综合医疗'         then '综合医疗'
                                      when l1_srvline = '中医'         then '中医'
                                      WHEN l1_srvline = '眼科'         THEN '眼科'
                                      when l1_srvline = '皮肤科' then '皮肤科'
                                      when l1_srvline = '体检' then '体检'
                                      when l2_srvline = '骨科' then '骨科'
                                      when l2_srvline = '康复养老' then '康复科'
                                      when l2_srvline = '肿瘤科' then '肿瘤科'
                                      WHEN l2_srvline = '耳鼻喉'         THEN '耳鼻喉科'
                                      when l2_srvline = '植发' then '植发'
                                      when l2_srvline = '医学美容' then '医学美容'
                                      when l2_srvline = '生美' then 'SPA/美容/美体'
                                      else l2_srvline end 
                                      when srv_line = '教培' then 
                                      case when l1_srvline = '驾校培训' then '驾校培训'
                                      when l1_srvline = '文印广告' then '文印广告'
                                      when l2_srvline = '结婚' then '婚宴酒店'
                                      when l2_srvline = '摄影' then '拍照摄影'
                                      when l2_srvline = 'K12' then '课后辅导'
                                      when l2_srvline = '学历提升' then '学历提升'
                                      when l2_srvline = '留学'    then '留学'
                                      when l2_srvline = '自习室' then '自习室'
                                      when l2_srvline = '语言培训' then '外语培训'
                                      when l2_srvline = '成人教育' then '成人教育'
                                      else l3_srvline end 
                                      else l3_srvline end as cate_name 
                           from autonavi_analysis.dim_cate_mapping_for_low_frequencya_v2
                          where modified_date = '20260331'                  
                        ) p2 
                     on p1.atag_id = p2.atagid
                  group by p1.partner_id
                        ,p1.partner_name
                        ,p1.city_name
                ) p2 
             on p1.partner_id = p2.partner_id  
           left join   
                ( -- 人户关系
                 select p1.*
                        ,p2.l2_dept_name
                        ,p2.l3_dept_name
                        ,p2.l4_dept_name
                        ,ali_code      as 管理工号
                        ,employee_name as 管理名字
                   from
                        (
                         select partner_id
                                ,employee_no
                                ,employee_name
                           from kbdw.dim_kb_sale_employee_partner_relation
                          where ds = MAX_PT('kbdw.dim_kb_sale_employee_partner_relation') 
                            and new_relation_type = 'ADVERTISING_BELONG'  -- 限定签约
                            and employee_no is not null                            
                        ) p1 
                  inner join
                        ( -- 部门
                         select ali_code
                                ,l2_dept_name
                                ,l3_dept_name
                                ,l4_dept_name
                           from kbreport.dim_gd_c33_employee_info_ds                        
                          where ds = max_pt('kbreport.dim_gd_c33_employee_info_ds') 
                        ) p2 
                     on p1.employee_no = p2.ali_code
                   left join 
                        ( -- 管理
                         select l2_dept_name
                                  ,l3_dept_name
                                  ,l4_dept_name
                                  ,ali_code
                                  ,employee_name
                             from kbreport.dim_gd_c33_employee_info_ds                        
                            where ds = max_pt('kbreport.dim_gd_c33_employee_info_ds')                            
                              and role_name rlike '经理'
                              and l2_dept_name in ('医疗行业线','教培行业线')
                              and is_leave = '0'
                            group by l2_dept_name
                                  ,l3_dept_name
                                  ,l4_dept_name
                                  ,ali_code
                                  ,employee_name  
                        ) p3 
                     on coalesce(p2.l2_dept_name,'1') = coalesce(p3.l2_dept_name,'1')
                    and coalesce(p2.l3_dept_name,'1') = coalesce(p3.l3_dept_name,'1')
                    and coalesce(p2.l4_dept_name,'1') = coalesce(p3.l4_dept_name,'1')  
                ) p3 
             on p1.partner_id = p3.partner_id
        ) p1 
   left join 
        ( -- 商户流量
         select partner_id
                ,sum(exposure_cnt) as exposure_cnt
           from amap_ad.dws_gd_ad_flow_effect_base_di
          where ds <= '20260431'
            and ds >= '20250501' 
          group by partner_id  
        ) p2 
     on p1.partner_id = p2.partner_id
;

