
set odps.sql.type.system.odps2=true;
set odps.sql.hive.compatible=true;
-- 临时表1:流量明细
with tmp_poi_detail as (
select ds 
       ,poiid    
       ,poi_name  
       ,atag_id
       ,city_name
       ,province_name
       ,district_name
       ,srv_line
       ,l2_srvline
       ,l3_srvline               
       ,is_ad
       ,is_annual_fee
       ,brand_name
       ,brand_code
       ,case when quantile_entrance_show_pv_fs_bycate <= 0.7 then 1 else 0 end as is_top_pv_poi -- 是否高流商户
       ,entrance_show_pv_fs                 -- 泛搜曝光PV 近30天日均
       ,entrance_show_pv_fs_ading           -- 泛搜曝光PV（广告）近30天日均
       ,entrance_show_pv_fs_annual_fee      -- 泛搜曝光PV（年费）近30天日均
    --    ,PERCENTILE(entrance_show_pv_fs, 0.7) OVER(partition by l2_srvline,l3_srvline) as entrance_show_pv_fs_70 -- 高流
  from kbreport.dws_gd_poi_detail_ds
 where ds <= max_pt('gd_info_cdm.dws_gd_info_poi_feature_di')
   and ds >= '20260331' 
   and cleansing_tag rlike '保留'
)

-- poi全量表：

select p1.ds 
       ,p1.srv_line 
       ,p1.l2_srvline
       ,p1.l3_srvline
       ,p1.province_name
       ,p2.medical_division                                                                                                                                        as 医疗大区
       ,p2.medical_cm                                                                                                                                              as 医疗区域
       ,p2.education_division                                                                                                                                      as 教培大区 
       ,p2.education_cm                                                                                                                                            as 教培区域 
       ,case when p2.city_name is not null then city_level else 'C' end                                                                                            as 城市等级
       ,p1.city_name                                                                                                                                               as 城市
       ,max(case when p3.flag = 2 then p3.flag_nf end)                                                                                                             as 红蓝海
       ,null                                                                                                                                                       as 二级部门
       ,null                                                                                                                                                       as 三级部门
       ,null                                                                                                                                                       as 四级部门             
       -- 现状指标
       ,count(distinct p1.poiid)                                                                                                                                   as poi数量
       ,count(distinct case when p1.is_top_pv_poi = 1 then poiid end)                                                                                              as 高流poi数量
       ,sum(entrance_show_pv_fs)                                                                                                                                   as 全量流量
       ,sum(entrance_show_pv_fs_ading)                                                                                                                             as 广告在投流量
       ,sum(entrance_show_pv_fs_annual_fee)                                                                                                                        as 年费在约流量
       ,count(distinct case when p1.is_annual_fee = 1 then p1.poiid end)                                                                                           as 门店年费供给数
       ,count(distinct case when p1.is_annual_fee = 1  and p1.is_top_pv_poi = 1 then p1.poiid end)                                                                 as 高流门店年费供给数
       ,count(distinct case when p1.is_ad = 1 then p1.poiid end)                                                                                                   as 在投门店数量
       ,count(distinct case when p1.is_ad = 1          and p1.is_top_pv_poi = 1 then p1.poiid end)                                                                 as 高流门店在投门店数
       -- 初始值
       ,max(case when p3.flag = 1 then p3.高流poi数量 end)     as 高流poi数量_起始值
       ,max(case when p3.flag = 1 then p3.全量流量 end)        as 全量流量_起始值
       ,max(case when p3.flag = 1 then p3.广告在投流量 end)     as 广告在投流量_起始值
       ,max(case when p3.flag = 1 then p3.年费在约流量 end)     as 年费在约流量_起始值
       ,max(case when p3.flag = 1 then p3.门店年费供给数 end)    as 门店年费供给数_起始值
       ,max(case when p3.flag = 1 then p3.高流门店年费供给数 end) as 高流门店年费供给数_起始值
       ,max(case when p3.flag = 1 then p3.在投门店数量 end)        as 在投门店数量_起始值
       ,max(case when p3.flag = 1 then p3.高流门店在投门店数 end)      as 高流门店在投门店数_起始值
       -- 目标值
       ,max(case when p3.flag = 2 then p3.flow_rate_ading_target end)     as 广告流量渗透率目标
       ,max(case when p3.flag = 2 then p3.flow_rate_annual_target end)    as 年费流量渗透率目标
       ,max(case when p3.flag = 2 then p3.coverage_rate_store_target end) as 门店覆盖率目标
       ,0 as 新签门店目标
       -- 新签门店
       ,0 as 新签门店数
       -- 拜访
       ,0  as 拜访次数
       ,0  as 上门拜访数
       ,0  as 电话拜访数
       ,0  as 企微拜访数         
  from
       ( -- poi维表
        select * 
          from tmp_poi_detail
       ) p1
 inner join 
       ( -- 城市维表
        select city_name
               ,city_level
               ,province_name
               ,medical_division
               ,medical_cm
               ,education_division
               ,education_cm
          from kbreport.dim_gd_city_cate_da
       ) p2 
    on p1.city_name = p2.city_name
  left join 
       ( -- 目标&初始值
        -- 初始值
        select city_name
               ,l2_srvline
               ,l3_srvline
               ,1                                                                                                                                                          as flag -- 标签
               ,count(distinct case when p1.is_top_pv_poi = 1 then poiid end)                                                                                              as 高流poi数量
               ,sum(entrance_show_pv_fs)                                                                                                                                   as 全量流量
               ,sum(entrance_show_pv_fs_ading)                                                                                                                             as 广告在投流量
               ,sum(entrance_show_pv_fs_annual_fee)                                                                                                                        as 年费在约流量
               ,0                                                                                                                                                          as flow_rate_ading_target
               ,0                                                                                                                                                          as flow_rate_annual_target
               ,0                                                                                                                                                          as coverage_rate_store_target               
               ,count(distinct case when p1.is_annual_fee = 1 then p1.poiid end)                                                                                           as 门店年费供给数
               ,count(distinct case when p1.is_annual_fee = 1  and p1.is_top_pv_poi = 1 then p1.poiid end)                                                                 as 高流门店年费供给数
               ,count(distinct case when p1.is_ad = 1 then p1.poiid end)                                                                                                   as 在投门店数量
               ,count(distinct case when p1.is_ad = 1          and p1.is_top_pv_poi = 1 then p1.poiid end)                                                                 as 高流门店在投门店数
               ,0                                                                                                                                                          as 新签门店目标
               ,null                                                                                                                                                       as flag_nf
               ,null                                                                                                                                                       as flag_gg               
          from tmp_poi_detail p1 
         where ds = '20260331'
         group by city_name
               ,l2_srvline
               ,l3_srvline  
         
         union all -- 目标值
         
        select city_name
               ,l2_srvline
               ,l3_srvline
               ,2                                                                                                                                                          as flag -- 标签
               ,0                                                                                                                                                          as 高流poi数量
               ,0                                                                                                                                                          as 全量流量
               ,0                                                                                                                                                          as 广告在投流量
               ,0                                                                                                                                                          as 年费在约流量
               ,flow_rate_ading_target                                                                                                                                     as flow_rate_ading_target
               ,flow_rate_annual_target                                                                                                                                    as flow_rate_annual_target
               ,coverage_rate_store_target                                                                                                                                 as coverage_rate_store_target
               ,0                                                                                                                                                          as 门店年费供给数
               ,0                                                                                                                                                          as 高流门店年费供给数
               ,0                                                                                                                                                          as 在投门店数量
               ,0                                                                                                                                                          as 高流门店在投门店数
               ,new_sign_shop_target                                                                                                                                       as 新签门店目标
               ,flag_nf                                                                                                                                                    as flag_nf
               ,flag_gg                                                                                                                                                    as flag_gg
          from kbreport.dws_gd_combat_map_value_dim
         where smonth = '202604' 
       ) p3 
    on p1.city_name = p3.city_name
   and p1.l2_srvline = p3.l2_srvline
   and coalesce(p1.l3_srvline,'无')= coalesce(p3.l3_srvline,'无')
 group by p1.ds 
       ,p1.srv_line 
       ,p1.l2_srvline
       ,p1.l3_srvline
       ,p1.province_name
       ,p2.medical_division                                                                                                                                       
       ,p2.medical_cm                                                                                                                                             
       ,p2.education_division                                                                                                                                     
       ,p2.education_cm                                                                                                                                           
       ,case when p2.city_name is not null then city_level else 'C' end                                                                                            
       ,p1.city_name                                                                                                                                              

 union all -- 新签明细

select p1.ds 
       ,p1.srv_line 
       ,p1.l2_srvline
       ,p1.l3_srvline
       ,p2.province_name
       ,p2.medical_division                                                                                                                                        as 医疗大区
       ,p2.medical_cm                                                                                                                                              as 医疗区域
       ,p2.education_division                                                                                                                                      as 教培大区 
       ,p2.education_cm                                                                                                                                            as 教培区域 
       ,case when p2.city_name is not null then city_level else 'C' end                                                                                            as 城市等级
       ,p2.city_name                                                                                                                                               as 城市
       ,p1.flag_nf                                                                                                                                                 as 红蓝海
       ,p1.l2_dept_name as 二级部门
       ,p1.l3_dept_name as 三级部门
       ,p1.l4_dept_name as 四级部门       
       -- 现状指标
       ,0   as poi数量
       ,0   as 高流poi数量
       ,0   as 全量流量
       ,0   as 广告在投流量
       ,0   as 年费在约流量
       ,0   as 门店年费供给数
       ,0   as 高流门店年费供给数
       ,0   as 在投门店数量
       ,0   as 高流门店在投门店数
       -- 初始值
       ,0   as 高流poi数量_起始值
       ,0   as 全量流量_起始值
       ,0   as 广告在投流量_起始值
       ,0   as 年费在约流量_起始值
       ,0   as 门店年费供给数_起始值
       ,0   as 高流门店年费供给数_起始值
       ,0   as 在投门店数量_起始值
       ,0   as 高流门店在投门店数_起始值
       -- 目标值
       ,0                          as 广告流量渗透率目标
       ,0                          as 年费流量渗透率目标
       ,0                          as 门店覆盖率目标
       ,0                          as 新签门店目标
       -- 新签门店
       ,count(distinct p1.shop_id) as 新签门店数
       -- 拜访
       ,0  as 拜访次数
       ,0  as 上门拜访数
       ,0  as 电话拜访数
       ,0  as 企微拜访数         
  from 
       ( -- 新签明细判断
        select p1.ds 
               ,p1.shop_id
               ,p1.srv_line
               ,p1.l2_srvline
               ,p1.l3_srvline
               ,p1.city_name
               ,p2.ali_code
               ,p2.employee_name
               ,p2.l2_dept_name
               ,p2.l3_dept_name
               ,p2.l4_dept_name 
               ,p3.flag_nf
          from 
               ( -- 新签明细
                select ds 
                       ,shop_id
                       ,sale_srvline as srv_line
                       ,l2_srvline
                       ,l3_srvline
                       ,city_name
                       ,ali_code
                  from kbreport.dwd_gd_sign_employee_income_detail_ds    
                 where ds >= '20260401'
                   and new_sign_shop_cnt > 0                   
               ) p1 
         inner join
               ( -- 架构
                select ds 
                       ,ali_code
                       ,employee_name
                       ,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') 
                   and ds >= '20260401'
                   and team_type = '直营'
               ) p2 
            on p1.ali_code = p2.ali_code
           and p1.ds = p2.ds  
          left join 
               ( -- 蓝海判断
                 select city_name
                        ,l2_srvline
                        ,l3_srvline
                        ,new_sign_shop_target                                                                                                                                       as 新签门店目标
                        ,flag_nf                                                                                                                                                    as flag_nf
                        ,flag_gg                                                                                                                                                    as flag_gg
                   from kbreport.dws_gd_combat_map_value_dim
                  where smonth = '202604' 
                -- select p1.city_name
                --        ,p1.l2_srvline
                --        ,p1.l3_srvline 
                --        ,case when p1.年费填充率 > p2.年费填充率 then '红海' else '蓝海' end as is_lh 
                --   from 
                --        ( -- 类目*城市明细
                --         select city_name
                --                ,l2_srvline
                --                ,l3_srvline
                --                ,sum(entrance_show_pv_fs_annual_fee) / sum(entrance_show_pv_fs)   as 年费填充率
                --           from tmp_poi_detail
                --          group by city_name
                --                ,l2_srvline
                --                ,l3_srvline 
                --        ) p1 
                --   left join 
                --        ( -- 判断
                --         select l2_srvline
                --                ,l3_srvline
                --                ,sum(entrance_show_pv_fs_annual_fee) / sum(entrance_show_pv_fs)    as 年费填充率
                --           from tmp_poi_detail    
                --          group by l2_srvline
                --                ,l3_srvline          
                --        ) p2 
                --     on p1.l2_srvline = p2.l2_srvline
                --    and coalesce(p1.l3_srvline,'无') = coalesce(p2.l3_srvline,'无') 
                --  group by p1.city_name
                --        ,p1.l2_srvline
                --        ,p1.l3_srvline 
                --        ,case when p1.年费填充率 > p2.年费填充率 then '红海' else '蓝海' end                  
               ) p3 
            on p1.city_name = p3.city_name
           and p1.l2_srvline = p3.l2_srvline
           and coalesce(p1.l3_srvline,'无')= coalesce(p3.l3_srvline,'无') 
       ) p1 
 inner join 
        ( -- 城市维表
        select city_name
               ,city_level
               ,province_name
               ,medical_division
               ,medical_cm
               ,education_division
               ,education_cm
          from kbreport.dim_gd_city_cate_da
       ) p2 
    on p1.city_name = p2.city_name  
 group by p1.ds 
       ,p1.srv_line     
       ,p1.l2_srvline
       ,p1.l3_srvline
       ,p2.province_name
       ,p2.medical_division                                                                                                                                        
       ,p2.medical_cm                                                                                                                                              
       ,p2.education_division                                                                                                                                      
       ,p2.education_cm                                                                                                                                            
       ,case when p2.city_name is not null then city_level else 'C' end                                                                                            
       ,p2.city_name                                                                                                                                               
       ,p1.l2_dept_name 
       ,p1.l3_dept_name 
       ,p1.l4_dept_name 
       ,p1.flag_nf 

 union all -- 新签门店目标值

select concat(smonth,'01') as ds 
       ,p1.srv_line 
       ,p1.l2_srvline
       ,p1.l3_srvline
       ,p2.province_name
       ,p2.medical_division                                                as 医疗大区
       ,p2.medical_cm                                                      as 医疗区域
       ,p2.education_division                                              as 教培大区 
       ,p2.education_cm                                                    as 教培区域 
       ,case when p2.city_name is not null then city_level else 'C' end    as 城市等级
       ,p1.city_name                                                       as 城市
       ,null                                                               as 红蓝海
       ,case when srv_line = '医疗' then '医疗行业线'
             when srv_line = '教培' then '教培行业线'           
             else null end                                                 as 二级部门
       ,case when srv_line = '医疗' then medical_division
             when srv_line = '教培' then education_division
             else null end                                                 as 三级部门
       ,case when srv_line = '医疗' then medical_cm
             when srv_line = '教培' then education_cm
             else null end                                                 as 四级部门             
       -- 现状指标
       ,0 as poi数量
       ,0 as 高流poi数量
       ,0 as 全量流量
       ,0 as 广告在投流量
       ,0 as 年费在约流量 
       ,0 as 门店年费供给数
       ,0 as 高流门店年费供给数
       ,0 as 在投门店数量
       ,0 as 高流门店在投门店数
       -- 初始值
       ,0 as 高流poi数量_起始值
       ,0 as 全量流量_起始值
       ,0 as 广告在投流量_起始值
       ,0 as 年费在约流量_起始值
       ,0 as 门店年费供给数_起始值
       ,0 as 高流门店年费供给数_起始值
       ,0 as 在投门店数量_起始值
       ,0 as 高流门店在投门店数_起始值
       -- 目标值
       ,0                                 as 广告流量渗透率目标
       ,0                                 as 年费流量渗透率目标
       ,0                                 as 门店覆盖率目标
       ,max(new_sign_shop_target)         as 新签门店目标
       -- 新签
       ,0                                 as 新签门店数
       -- 拜访次数
       ,0  as 拜访次数
       ,0  as 上门拜访数
       ,0  as 电话拜访数
       ,0  as 企微拜访数       
  from 
       ( -- 目标
        select smonth
               ,srv_line 
               ,l2_srvline
               ,l3_srvline
               ,city_name
               ,max(flow_rate_ading_target)	      as flow_rate_ading_target
               ,max(flow_rate_annual_target)      as flow_rate_annual_target
               ,max(coverage_rate_store_target)   as coverage_rate_store_target
               ,max(new_sign_shop_target)         as new_sign_shop_target               
          from kbreport.dws_gd_combat_map_value_dim 
         group by srv_line 
               ,l2_srvline
               ,l3_srvline
               ,city_name
               ,smonth
       ) p1 
  left join 
       ( -- 城市维表
        select city_name
               ,city_level
               ,province_name
               ,medical_division
               ,medical_cm
               ,education_division
               ,education_cm
          from kbreport.dim_gd_city_cate_da
       ) p2 
    on p1.city_name = p2.city_name
 where smonth = '202604' 
 group by concat(smonth,'01') 
       ,p1.srv_line 
       ,p1.l2_srvline
       ,p1.l3_srvline
       ,p2.province_name
       ,p2.medical_division                                                
       ,p2.medical_cm                                                      
       ,p2.education_division                                              
       ,p2.education_cm                                                    
       ,case when p2.city_name is not null then city_level else 'C' end    
       ,p1.city_name                                                       
       ,null                                                               
       ,case when srv_line = '医疗' then '医疗行业线'
             when srv_line = '教培' then '教培行业线'           
             else null end                                                 
       ,case when srv_line = '医疗' then medical_division
             when srv_line = '教培' then education_division
             else null end                                                 
       ,case when srv_line = '医疗' then medical_cm
             when srv_line = '教培' then education_cm
             else null end                                                 


 union all -- 拜访明细

select p1.ds 
       ,p1.srv_line
       ,p1.l2_srvline
       ,p1.l3_srvline
       ,p1.province_name
       ,p2.medical_division
       ,p2.medical_cm
       ,p2.education_division
       ,p2.education_cm
       ,case when p2.city_name is not null then city_level else 'C' end   as 城市等级
       ,p2.city_name                                                      as 城市
       ,p3.flag_nf                                                        as 红蓝海
       ,p1.l2_dept_name                                                   as 二级部门         
       ,p1.l3_dept_name                                                   as 三级部门         
       ,p1.l4_dept_name                                                   as 四级部门              
       -- 现状指标
       ,0   as poi数量
       ,0   as 高流poi数量
       ,0   as 全量流量
       ,0   as 广告在投流量
       ,0   as 年费在约流量
       ,0   as 门店年费供给数
       ,0   as 高流门店年费供给数
       ,0   as 在投门店数量
       ,0   as 高流门店在投门店数
       -- 初始值
       ,0   as 高流poi数量_起始值
       ,0   as 全量流量_起始值
       ,0   as 广告在投流量_起始值
       ,0   as 年费在约流量_起始值
       ,0   as 门店年费供给数_起始值
       ,0   as 高流门店年费供给数_起始值
       ,0   as 在投门店数量_起始值
       ,0   as 高流门店在投门店数_起始值
       -- 目标值
       ,0   as 广告流量渗透率目标
       ,0   as 年费流量渗透率目标
       ,0   as 门店覆盖率目标
       ,0   as 新签门店目标
       -- 新签门店
       ,0   as 新签门店数      
       -- 拜访数
       ,count(distinct p1.visit_type,p1.leads_id)                          as 拜访次数
       ,count(distinct case when visit_type rlike '上门' then leads_id end) as 上门拜访数
       ,count(distinct case when visit_type rlike '电话' then leads_id end) as 电话拜访数
       ,count(distinct case when visit_type rlike '企微' then leads_id end) as 企微拜访数
  from 
       ( -- 拜访明细判断
        select p1.ds 
               ,p1.leads_id
               ,p1.poi_id
               ,p1.atag_id
               ,p1.visit_type
               ,p1.province_name
               ,p1.city_name
               ,p2.srv_line
               ,p2.l2_srvline
               ,p2.l3_srvline
               ,p2.l4_srvline     
               ,p3.l2_dept_name
               ,p3.l3_dept_name
               ,p3.l4_dept_name                         
          from 
               ( -- 拜访明细
                select ds 
                       ,leads_id
                       ,poi_id
                       ,atag_id
                       ,visit_style
                       ,city_name
                       ,province_name
                       ,ali_code
                       ,REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(visit_style,'1','上门'),'2','外呼'),'3','电话'),'4','其他'),'5','企微') as visit_type
                  from kbdw.dwd_gd_info_sale_leads_claim_visit_sign_di
                 where ds >= '20260401'
                   and visit_style is not null
               ) p1 
         inner join 
               (
                select atagid
                       ,srv_line   
                       ,l1_srvline      as l2_srvline
                       ,l2_srvline      as l3_srvline
                       ,l3_srvline      as l4_srvline
                  from autonavi_analysis.dim_cate_mapping_for_low_frequencya_v2
                 where modified_date = '20260331'                  
               ) p2 
            on p1.atag_id = p2.atagid
         inner join 
               ( -- 架构
                select ds 
                       ,ali_code
                       ,employee_name
                       ,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') 
                   and ds >= '20260401'
                   and team_type = '直营'               
               ) p3 
            on p1.ali_code = p3.ali_code
           and p1.ds       = p3.ds  
       ) p1 
 inner join 
       ( -- 城市维表       
        select city_name
               ,city_level
               ,province_name
               ,medical_division
               ,medical_cm
               ,education_division
               ,education_cm
          from kbreport.dim_gd_city_cate_da 
       ) p2 
    on p1.city_name = p2.city_name
 inner join 
       ( -- 红蓝海标签
        select city_name
               ,l2_srvline
               ,l3_srvline
               ,flag_nf   
          from kbreport.dws_gd_combat_map_value_dim
         where smonth = '202604'        
       ) p3 
    on p1.city_name  = p3.city_name 
   and p1.l2_srvline = p3.l2_srvline
   and p1.l3_srvline = p3.l3_srvline 
 group by p1.ds 
       ,p1.srv_line
       ,p1.l2_srvline
       ,p1.l3_srvline
       ,p1.province_name
       ,p2.medical_division
       ,p2.medical_cm
       ,p2.education_division
       ,p2.education_cm
       ,case when p2.city_name is not null then city_level else 'C' end   
       ,p2.city_name                                                      
       ,p3.flag_nf                                                        
       ,p1.l2_dept_name                                                   
       ,p1.l3_dept_name                                                   
       ,p1.l4_dept_name                                                   
;






with city_detail AS 
(
 SELECT      --维度：shopid*ds
            a.ds AS 日期
            ,case when a.ds = max_pt('kbreport.dws_gd_poi_detail_ds') then '当前值' 
            when a.ds = '20260331' then '初始值' else '历史值' end  as 日期维度
            ,a.city_name
            ,a.province_name
            ,a.district_name
            ,bloc_name as 大区
            ,COALESCE(city_tier,'C档') AS city_tier
            ,CASE   WHEN city_tier IN ('S档','A档','B档') THEN 'SAB档'
                    ELSE 'C档'
            END AS 城市分档
            ,a.srv_line
            ,l2_srvline
            ,l3_srvline
            ,COUNT(DISTINCT a.poiid) AS leads数
            ,COUNT(DISTINCT CASE    WHEN m_is_ad > 0
                        OR m_is_new_shop > 0
                        OR m_has_product > 0 THEN a.poiid END) AS M在线poi数
            ,COUNT(DISTINCT CASE    WHEN is_cooperation > 0 THEN a.poiid END) AS 合作poi数
            ,COUNT(DISTINCT CASE    WHEN is_ad > 0 THEN a.poiid END) AS 在投poi数
             ,COUNT(DISTINCT CASE    WHEN (IF(quantile_detail_pv_bycate <= 0.5,1,0) + IF(quantile_entrance_show_pv_fs_bycate <= 0.5,1,0) + IF(quantile_ltv_bycate <= 0.2,1,0)) > 1 AND a.srv_line = '教培'   THEN a.poiid 
             WHEN (IF(quantile_detail_pv_bycate <= 0.7,1,0) + IF(quantile_entrance_show_pv_fs_bycate <= 0.7,1,0) + IF(quantile_ltv_bycate <= 0.2,1,0)) > 1 AND a.srv_line = '医疗'   THEN a.poiid   END) AS 高优poi个数
            ,COUNT(DISTINCT CASE    WHEN is_cooperation > 0 AND (IF(quantile_detail_pv_bycate <= 0.5,1,0) + IF(quantile_entrance_show_pv_fs_bycate <= 0.5,1,0) + IF(quantile_ltv_bycate <= 0.2,1,0)) > 1 AND a.srv_line = '教培'   THEN a.poiid 
             WHEN is_cooperation > 0 AND (IF(quantile_detail_pv_bycate <= 0.7,1,0) + IF(quantile_entrance_show_pv_fs_bycate <= 0.7,1,0) + IF(quantile_ltv_bycate <= 0.2,1,0)) > 1 AND a.srv_line = '医疗'   THEN a.poiid   END)   AS 高优合作poi个数
             ,COUNT(DISTINCT CASE    WHEN is_ad > 0 AND (IF(quantile_detail_pv_bycate <= 0.5,1,0) + IF(quantile_entrance_show_pv_fs_bycate <= 0.5,1,0) + IF(quantile_ltv_bycate <= 0.2,1,0)) > 1 AND a.srv_line = '教培'   THEN a.poiid 
             WHEN is_ad > 0 AND (IF(quantile_detail_pv_bycate <= 0.7,1,0) + IF(quantile_entrance_show_pv_fs_bycate <= 0.7,1,0) + IF(quantile_ltv_bycate <= 0.2,1,0)) > 1 AND a.srv_line = '医疗'   THEN a.poiid   END)   AS 高优在投poi个数
            ,COUNT(DISTINCT CASE    WHEN quantile_entrance_show_pv_fs_bycate <= 0.7 THEN a.poiid END) AS 高流poi个数_70
            ,COUNT(DISTINCT CASE    WHEN is_cooperation > 0 AND quantile_entrance_show_pv_fs_bycate <= 0.7 THEN a.poiid END) AS 高流合作poi个数_70
            ,COUNT(DISTINCT CASE    WHEN quantile_entrance_show_pv_fs_bycate <= 0.6 THEN a.poiid END) AS 高流poi个数_60
            ,COUNT(DISTINCT CASE    WHEN is_cooperation > 0 AND quantile_entrance_show_pv_fs_bycate <= 0.6 THEN a.poiid END) AS 高流合作poi个数_60
            ,COUNT(DISTINCT CASE    WHEN quantile_entrance_show_pv_fs_bycate <= 0.5 THEN a.poiid END) AS 高流poi个数_50
            ,COUNT(DISTINCT CASE    WHEN is_cooperation > 0 AND quantile_entrance_show_pv_fs_bycate <= 0.5 THEN a.poiid END) AS 高流合作poi个数_50
            ,COUNT(DISTINCT CASE    WHEN quantile_detail_pv_bycate <= 0.7 THEN a.poiid END) AS 高流poi个数_70_dpv
            ,COUNT(DISTINCT CASE    WHEN is_cooperation > 0 AND quantile_detail_pv_bycate <= 0.7 THEN a.poiid END) AS 高流合作poi个数_70_dpv
            ,SUM(entrance_show_pv_fs) AS 日均泛搜含品牌搜PV
            ,SUM(entrance_show_pv_fs_ading) AS 日均泛搜含品牌搜PV_在投
            ,SUM(entrance_show_pv_fs_annual_fee) AS 日均泛搜含品牌搜PV_已合作
            ,SUM(detail_pv) AS 详情页PV
            ,SUM(detail_pv_ading) AS 详情页PV_在投
            ,SUM(detail_pv_annual_fee) AS 详情页PV_已合作
    FROM    kbreport.dws_gd_poi_detail_ds a
    LEFT JOIN   (
                    SELECT  DISTINCT city_name
                            ,city_tier
                    FROM    kbreport.city_nf_tier
                    WHERE   district_name <> '其他行政区'
                ) t3
    ON      t3.city_name = a.city_name
    left join (
             SELECT  DISTINCT srv_line
                            ,province
                            ,bloc_name
                    FROM    kbreport.city_bloc
    )t4 ON      t4.province = a.province_name
    and t4.srv_line = a.srv_line
    where cleansing_tag like '%保留%'
    GROUP BY a.ds
             ,a.city_name
             ,a.province_name
             ,a.district_name
             ,city_tier
             ,key_cate_name
             ,l2_srvline
             ,l3_srvline
             ,a.srv_line
             ,bloc_name
)
SELECT  a.*
        ,flow_rate_ading_start
        ,flow_rate_annual_start
        ,coverage_rate_store_start
        ,flow_rate_ading_target
        ,flow_rate_annual_target
        ,coverage_rate_store_target
        ,case when flow_rate_ading_target - flow_rate_ading_start>=0 then flow_rate_ading_target - flow_rate_ading_start end  as flow_rate_ading_gap
        ,case when flow_rate_annual_target - flow_rate_annual_start >=0 then flow_rate_annual_target - flow_rate_annual_start end  as flow_rate_annual_gap
        ,flag_nf
        ,flag_gg
        ,ROW_NUMBER() OVER (PARTITION BY a.srv_line,重点类目 ORDER BY 日均泛搜含品牌搜PV DESC ) AS 同类目城市流量排名
        ,ROW_NUMBER() OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ORDER BY 日均泛搜含品牌搜PV DESC ) AS 同类目同档位城市流量排名
        ,SUM(日均泛搜含品牌搜PV_已合作) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) / SUM(日均泛搜含品牌搜PV) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) AS 同类目同档位城市平均年费填充率_泛搜pv
        ,SUM(日均泛搜含品牌搜PV_在投) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) / SUM(日均泛搜含品牌搜PV) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) AS 同类目同档位城市平均广告填充率_泛搜pv
        ,SUM(日均泛搜含品牌搜PV_在投) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) / SUM(日均泛搜含品牌搜PV_已合作) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) AS 同类目同档位城市平均在投率_泛搜pv
        ,SUM(日均泛搜含品牌搜PV_已合作) OVER (PARTITION BY a.srv_line,重点类目 ) / SUM(日均泛搜含品牌搜PV) OVER (PARTITION BY a.srv_line,重点类目 ) AS 同类目平均年费填充率_泛搜pv
        ,SUM(日均泛搜含品牌搜PV_在投) OVER (PARTITION BY a.srv_line,重点类目 ) / SUM(日均泛搜含品牌搜PV) OVER (PARTITION BY a.srv_line,重点类目 ) AS 同类目平均广告填充率_泛搜pv
        ,SUM(日均泛搜含品牌搜PV_在投) OVER (PARTITION BY a.srv_line,重点类目 ) / SUM(日均泛搜含品牌搜PV_已合作) OVER (PARTITION BY a.srv_line,重点类目 ) AS 同类目平均在投率_泛搜pv
        ,SUM(详情页PV_已合作) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) / SUM(详情页PV) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) AS 同类目同档位城市平均年费填充率_dpv
        ,SUM(详情页PV_在投) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) / SUM(详情页PV) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) AS 同类目同档位城市平均广告填充率_dpv
        ,SUM(详情页PV_在投) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) / SUM(详情页PV_已合作) OVER (PARTITION BY 城市分档,a.srv_line,重点类目 ) AS 同类目同档位城市平均在投率_dpv
FROM    city_detail a
left join (
        SELECT srv_line,
                l2_srvline,
                l3_srvline,
                city_name,
                 flow_rate_ading_start,
                flow_rate_annual_start,
                coverage_rate_store_start,
                flow_rate_ading_target,
                flow_rate_annual_target,
                coverage_rate_store_target,
                flag_nf,
                flag_gg
        from kbreport.dws_gd_combat_map_value_dim
)b on a.srv_line = b.srv_line
and a.l2_srvline = b.l2_srvline
and a.重点类目 = b.l3_srvline
and a.city_name = b.city_name