oracel补数经验1

------谭总、涛哥----------------------------------------------------------------
--经销商网点数
--(录入期数个数)减去(删除期数个数)
with real_num as (
  select c.channel_id,c.channel_name,ccr.product_type_val,ct.code_name,d.city_id,ci.city_name
         ,count(*) num
    from t_dealer d
   inner join t_dealer_background b on d.dealer_id = b.dealer_background_id
   inner join t_channel c on d.channel_id = c.channel_id
   inner join t_channel_charge_rel ccr on c.channel_id = ccr.channel_id
   inner join t_code_table ct on ccr.product_type_val = ct.code_val and ct.p_code = 'PARAM_DEALER_PRODUCT_TYPE'
   inner join t_city ci on d.city_id = ci.city_id
   where b.input_period <= '2015年01月上期' and (d.del_period > '2015年01月上期' or d.del_period is null) and ccr.product_type_val = 1
   group by c.channel_id,c.channel_name,ccr.product_type_val,ct.code_name,d.city_id,ci.city_name
),
-- 把"价格/终端"拆分成价格、终端 
--因为渠道负责人表中只分1:价格/终端 2:商务政策,这里需要区别1:价格 2:终端 3:商务政策,跟开发计划经销商个数作比较 -->
--total_num临时表数据是：开发计划经销商数据 union all 实际经销商数据(即网点图数据),会存在重复
total_num as(
  --实际经销商数据(即网点图数据)
  select t1.channel_id,
         t1.channel_name,
         '价格' as product_name,
         '1' as product_val,
         t1.city_id,
         t1.city_name,
         -t1.num as num
    from real_num t1
   where t1.product_type_val = 1
  union all 
  select t1.channel_id,
         t1.channel_name,
         '终端' as product_name,
         '2' as product_val,
         t1.city_id,
         t1.city_name,
         -t1.num
    from real_num t1
   where t1.product_type_val = 1
  union all 
  select t1.channel_id,
         t1.channel_name,
         '商务政策' as product_name,
         '3' as product_val,
         t1.city_id,
         t1.city_name,
         -t1.num
    from real_num t1
   where t1.product_type_val = 2
union all
--开发计划经销商个数数据
select a.channel_id,b.channel_name,ct.code_name as product_name,a.product_val,a.city_id,c.city_name,a.num as num
  from t_year_development_plan a
 inner join t_channel b on a.channel_id = b.channel_id
 inner join t_city c on a.city_id = c.city_id
 inner join t_code_table ct on a.product_val = ct.code_val
 where ct.p_code = 'PARAM_DEALER_PRODUCT' 
),
-- 使用笛卡尔积补城市
--在现有的数据基础上,根据t_year_development_plan_city表补全城市
all_city as (
  select channel_id,
         channel_name,
         product_name,
         product_val,
         b.city_id,   --注意：这里只能拿b.city_id
         c.city_name, 
         0 as num     --注意：这里只能用0
    from (select distinct channel_id,
                 channel_name,
                 product_name,
                 product_val 
            from total_num 
         ) a,t_year_development_plan_city b,t_city c 
   where b.year = substr('2015年01月上期',0,4)
     and b.city_id = c.city_id
),
--开发计划经销商个数 减去 实际经销商数据(即网点图数据)
count_num as (
  select channel_id,
         channel_name,
         product_name,
         product_val,
         city_id,
         city_name,
         sum(num) as num
   from (
            select * 
              from total_num 
            union all 
            select * from all_city
        )
  group by channel_id,
           channel_name,
           product_name,
           product_val,
           city_id,
           city_name
)
--根据城市序号排序
select distinct a.*,b.sn
  from count_num a
  left join t_year_development_plan_city b on a.city_id = b.city_id
 order by a.channel_id,b.sn 




----------------------------------------------------------------------
----------------------------------------------------------------------




--陈立华-------start----补的城市num为空字符串-------------------------


--(录入期数个数)减去(删除期数个数)
with real_num as (
  select c.channel_id,c.channel_name,ccr.product_type_val,ct.code_name,d.city_id,ci.city_name
         ,count(*) num
    from t_dealer d
   inner join t_dealer_background b on d.dealer_id = b.dealer_background_id
   inner join t_channel c on d.channel_id = c.channel_id
   inner join t_channel_charge_rel ccr on c.channel_id = ccr.channel_id
   inner join t_code_table ct on ccr.product_type_val = ct.code_val and ct.p_code = 'PARAM_DEALER_PRODUCT_TYPE'
   inner join t_city ci on d.city_id = ci.city_id
   where d.coop_state_val in (4,5)
     and b.input_period <= '2015年01月上期' and (d.del_period > '2015年01月上期' or d.del_period is null) and ccr.product_type_val = 1
   group by c.channel_id,c.channel_name,ccr.product_type_val,ct.code_name,d.city_id,ci.city_name
),
--把"价格/终端"拆分成价格、终端 
--因为渠道负责人表中只分1:价格/终端 2:商务政策,这里需要区别1:价格 2:终端 3:商务政策 --
total_num as (
  select * from (
    select t1.channel_id,
           t1.channel_name,
           '价格' as product_name,
           '1' as product_val,
           t1.city_id,
           t1.city_name,
           to_char(t1.num) as num
      from real_num t1
     where t1.product_type_val = 1
    union all 
    select t1.channel_id,
           t1.channel_name,
           '终端' as product_name,
           '2' as product_val,
           t1.city_id,
           t1.city_name,
            to_char(t1.num) as num
      from real_num t1
     where t1.product_type_val = 1
    union all 
    select t1.channel_id,
           t1.channel_name,
           '商务政策' as product_name,
           '3' as product_val,
           t1.city_id,
           t1.city_name,
            to_char(t1.num) as num
      from real_num t1
     where t1.product_type_val = 2
  ) 
  where product_val = 1
),
--全连得到接所有城市
all_city as (
  select a.channel_id,
         a.channel_name,
         a.product_name,
         a.product_val,
         b.city_id,
         c.city_name,
         '' as num
    from total_num a
    join t_year_development_plan_city b on 1=1
   inner join t_city c on b.city_id = c.city_id 
   where b.year = substr('2015年01月上期',0,4) 
),
--所有记录中去除存在经销商的城市记录
all_city_remove_total_num as (
  select a.* 
    from all_city a 
   where not exists ( select 1
                        from total_num t
                       where t.channel_id = a.channel_id
                         and t.product_val = a.product_val
                         and t.city_id = a.city_id
                    )
),
-- 存在经销商的城市记录 与 不存在经销商的城市记录(补数记录) 合并
merge_city as (
  select * from total_num
  union all
  select * from all_city_remove_total_num
) 
--根据城市序号排序
select distinct a.*,'2015年01月上期' as input_period,b.sn
  from merge_city a
 left join t_year_development_plan_city b on a.city_id = b.city_id
 order by a.channel_id,a.product_val,b.sn 


--------------end-------------------------------------------