------谭总、涛哥----------------------------------------------------------------
--经销商网点数
--(录入期数个数)减去(删除期数个数)
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-------------------------------------------
--经销商网点数
--(录入期数个数)减去(删除期数个数)
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-------------------------------------------