set odps.sql.type.system.odps2=true;
set odps.sql.hive.compatible=true;


-- 临时表1:占期临时表
with tmp_gd_brnad_ad_occupied_detail_ds as (

select task_id
       ,city_name                 as 城市名称
       ,adcode_chn                as 区域名称
       ,date_time                 as 占期日期   
       ,product_name              as 产品名称
       ,cate_name                 as 类目名称  
       ,industry                  as 行业   
       ,is_key_cate               as 是否重点类目   
       ,user_create               as 创建人工号   
       ,start_time                as 开始日期
       ,end_time                  as 结束日期
       ,sign_date                 as 签约日期
       ,delivery_amount           as 占期金额
       ,partner_id      
       ,partner_name
       ,target_type
       ,row_number() over(partition by target_type,substring(date_time,1,7),city_name,adcode_chn,product_name,cate_name order by date_time) as rk 
       ,date_sub(date_time,row_number() over(partition by substring(date_time,1,7),city_name,adcode_chn,product_name,cate_name order by date_time) - 1) as grp 
  from kbreport.dwd_gd_brnad_ad_occupied_detail_ds
 where ds = max_pt('kbreport.dwd_gd_brnad_ad_occupied_detail_ds')
)


,tmp_gd_brand_space as (

-- 占期巡量表
select p1.smonth
       ,p1.product_name   
       ,p1.province_name
       ,p1.city_level
       ,p1.city_name
       ,p1.district_name
       ,p1.industry
       ,p1.is_key_cate
       ,p1.cate_name
       ,p1.division
       -- 分子
       ,coalesce(p2.占期天数,'可售卖') as 占期天数
       ,p2.占期金额_cross   as 占期金额
       ,p2.partner_cross   as partner_id
       ,p2.partner_name_cross   as partner_name
       ,p2.team_type_cross as team_type
       ,p2.l3_dept_name_cross
       ,p2.l4_dept_name_cross
       ,coalesce(p2.连续占期日期_cross,'可售卖') as 连续占期日期_cross
  from
       ( -- 分母
        select p1.*
               ,p2.smonth
          from
               ( -- 城市✖️类目
                select city_name
                       ,city_level
                       ,industry
                       ,cate_name 
                       ,product_name
                       ,district_name 
                       ,province_name
                       ,division
                       ,is_key_cate
                       ,1 as flag
                  from kbreport.dim_gd_brand_city_cate_da
               ) p1 
         inner join 
               ( -- 月份
                select date_format(cal_date,'yyyy-MM') as smonth
                       ,1 as flag 
                  from gd_cdm.dim_gd_calendar
                 where ds = MAX_PT('gd_cdm.dim_gd_calendar') 
                   and cal_str >= '20251001'
                   and cal_str <= date_format(DATEADD(CURRENT_DATE, 12, 'mm'),'yyyyMM01') -- 未来12月
                 group by date_format(cal_date,'yyyy-MM')
               ) p2 
            on p1.flag = p2.flag  
       ) p1 
  left join 
       ( -- 占期分子
        select p1.target_type
               ,p1.城市名称
               ,p1.区域名称
               ,p1.产品名称
               ,p1.类目名称
               ,p1.行业
               ,p1.smonth
               ,case when max(占期金额) <> min(占期金额)                  then concat(max(占期金额),'|',min(占期金额)) else max(占期金额) end as 占期金额_cross
               ,case when max(partner_id) <> min(partner_id)           then concat(max(partner_id),'|',min(partner_id)) else max(partner_id) end as partner_cross
               ,case when max(partner_name) <> min(partner_name)           then concat(max(partner_name),'|',min(partner_name)) else max(partner_name) end as partner_name_cross
               ,case when max(p2.team_type) <> min(p2.team_type)       then concat(max(p2.team_type),'|',min(p2.team_type)) else max(p2.team_type) end as team_type_cross
               ,case when max(p2.l3_dept_name) <> min(p2.l3_dept_name) then concat(max(p2.l3_dept_name),'|',min(p2.l3_dept_name)) else max(p2.l3_dept_name) end as l3_dept_name_cross
               ,case when max(p2.l4_dept_name) <> min(p2.l4_dept_name) then concat(max(p2.l4_dept_name),'|',min(p2.l4_dept_name)) else  max(p2.l4_dept_name) end as l4_dept_name_cross
               ,case when max(p1.连续占期日期) <> min(p1.连续占期日期)     then coalesce(concat(min(p1.连续占期日期),'|',max(p1.连续占期日期)),'未售卖') else  coalesce(max(p1.连续占期日期),'未售卖') end as 连续占期日期_cross
               ,coalesce(sum(占期天数),'未售卖') as 占期天数
          from
               (
                -- 行政区售卖
                select target_type 
                       ,城市名称
                       ,区域名称
                       ,产品名称
                       ,类目名称
                       ,行业
                       ,是否重点类目
                       ,创建人工号
                       ,开始日期
                       ,结束日期
                       ,签约日期
                       ,占期金额
                       ,partner_id
                       ,partner_name
                       ,grp
                       ,substring(占期日期,1,7) as smonth
                       ,min(占期日期) as 连续开始占期日期
                       ,max(占期日期) as 连续结束占期日期
                       ,concat(min(substring(占期日期,6,5)),'~',max(substring(占期日期,6,5))) as 连续占期日期
                       ,count(distinct 占期日期) as 占期天数
                  from tmp_gd_brnad_ad_occupied_detail_ds
                 where target_type = 'AREA' 
                 group by target_type
                       ,城市名称
                       ,区域名称
                       ,产品名称
                       ,类目名称
                       ,行业
                       ,是否重点类目
                       ,创建人工号
                       ,开始日期
                       ,结束日期
                       ,签约日期
                       ,占期金额
                       ,partner_id
                       ,partner_name
                       ,grp      
                       ,substring(占期日期,1,7)   

                 union all  -- 全城售卖

                select target_type 
                       ,城市名称
                       ,p2.district_name as 区域名称
                       ,产品名称
                       ,类目名称
                       ,行业
                       ,是否重点类目
                       ,创建人工号
                       ,开始日期
                       ,结束日期
                       ,签约日期
                       ,占期金额
                       ,partner_id
                       ,partner_name
                       ,grp
                       ,smonth
                       ,连续开始占期日期
                       ,连续结束占期日期
                       ,连续占期日期
                       ,占期天数
                  from
                       (
                        select target_type
                               ,城市名称
                               ,区域名称
                               ,产品名称
                               ,类目名称
                               ,行业
                               ,是否重点类目
                               ,创建人工号
                               ,开始日期
                               ,结束日期
                               ,签约日期
                               ,占期金额
                               ,partner_id
                               ,partner_name
                               ,grp
                               ,substring(占期日期,1,7) as smonth
                               ,min(占期日期) as 连续开始占期日期
                               ,max(占期日期) as 连续结束占期日期
                               ,concat(min(substring(占期日期,6,5)),'~',max(substring(占期日期,6,5))) as 连续占期日期
                               ,count(distinct 占期日期) as 占期天数
                          from tmp_gd_brnad_ad_occupied_detail_ds
                         where target_type = 'CITY' 
                         group by target_type 
                               ,城市名称
                               ,区域名称
                               ,产品名称
                               ,类目名称
                               ,行业
                               ,是否重点类目
                               ,创建人工号
                               ,开始日期
                               ,结束日期
                               ,签约日期
                               ,占期金额
                               ,partner_id
                               ,partner_name
                               ,grp      
                               ,substring(占期日期,1,7)                        
                       ) p1 
                  left join 
                       ( -- 城市*类目维表
                        select product_name
                               ,city_name
                               ,district_name 
                               ,cate_name
                          from kbreport.dim_gd_brand_city_cate_da
                         group by product_name
                               ,city_name
                               ,district_name 
                               ,cate_name
                       ) p2 
                    on p1.产品名称 = p2.product_name
                   and p1.类目名称 = p2.cate_name
                   and p1.城市名称 = p2.city_name
               ) p1
          left join 
               ( -- 架构表
                select ali_code
                       ,team_type
                       ,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.创建人工号 = p2.ali_code
         group by p1.target_type
               ,p1.城市名称
               ,p1.区域名称
               ,p1.产品名称
               ,p1.类目名称
               ,p1.行业
               ,p1.smonth   
       ) p2 
    on concat(p1.city_name,coalesce(p1.district_name,'无')) = concat(p2.城市名称,coalesce(p2.区域名称,'无'))
   and p1.product_name = p2.产品名称
   and p1.cate_name = p2.类目名称 
   and p1.smonth = p2.smonth
)

select p1.brand_name
       ,p1.province_name
       ,p3.city_level
       ,p1.city_name
       ,p3.industry
       ,p1.cate_name
       ,p2.district_name 
       ,p3.sale_price
       ,p3.is_key_cate
       ,p2.smonth
       ,p2.product_name
       ,p2.连续占期日期_cross
  from
       ( -- SKA品牌
        select brand_name
               ,province_name
               ,city_name
               ,cate_name 
          from kbreport.tmp_hww_bi_ska_brand_city_distribute_da  
         group by brand_name
               ,province_name
               ,city_name
               ,cate_name 
       ) p1 
 inner join 
       ( -- 空余档期
        select smonth
               ,product_name
               ,province_name
               ,city_name
               ,city_level
               ,district_name
               ,cate_name
               ,连续占期日期_cross 
          from tmp_gd_brand_space
       ) p2 
    on p1.city_name = p2.city_name
   and p1.cate_name = p2.cate_name 
  left join
       (
        select product_name
               ,cate_name
               ,industry
               ,city_level
               ,city_name
               ,district_name
               ,province_name
               ,sale_price  
               ,is_key_cate
          from kbreport.dim_gd_brand_city_cate_da
         group by product_name
               ,cate_name
               ,industry
               ,city_level
               ,city_name
               ,district_name
               ,province_name
               ,sale_price 
               ,is_key_cate  
       ) p3 
    on coalesce(p2.district_name,'无') = coalesce(p3.district_name,'无')
   and p2.cate_name = p3.cate_name
   and p2.product_name = p3.product_name 
   and p2.city_name = p3.city_name