chengben/Scripts/16.sql

--16、单站铁塔租赁费（月）
---北10数据（select * from sqmdb_cost.report50016）
select 
smonth as MONTH_ID,
'北10' as ZB_TYPE,
sname as PROV_NAME,
null as CITY_ID,
null as CITY_NAME,
null as GRID_ID,
null as GRID_NAME,
'16' as ZB_CODE,
'单站铁塔租赁费（月）' as ZB_NAME,
column2*10000 as QZ_FZ,
column5 as QZ_FM,
column2*10000/column5/4 as BY_QZ
from sqmdb_cost.report50016
where sname in ('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')
and smonth='202204'

--北10
select 
smonth,
sname,
column2*10000 as tower_cost,
column4 as tower_num_ly,
column3 as tower_num_cm,
(column3+column4)/2 as tower_num_avg,
column2*10000/((column3+column4)/2)/4 as tower_cost_avg
from sqmdb_cost.report50016 
where sname in ('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')
and smonth='202204'

--河北省
select smonth,sname,column1 as tower_cost_avg_prov from sqmdb_cost.report50016 where smonth='202204' and sname='河北'


---地市
--create table sqmdb_cost.rpt_tower_cost_city as 
select 
'202204' as smonth,
(regexp_split_to_array(tc.sname,'_'))[1] as city_name,
tc.tower_cost,
tn.tower_num_ly,
tn.tower_num_cm,
tn.tower_num_avg,
(tc.tower_cost/tn.tower_num_avg)/4 as tower_cost_avg
from
(select sname,sum(column2+column3) as tower_cost from sqmdb_cost.report50 r 
where period_type='M' and smonth between '202201' and '202204' group by sname)tc
join
(with tower_code as (select distinct sdate,city_operator,site_addr_id from sqmdb_cost.tower3 where stype!='室分'
union all
select sdate,city_operator,site_addr_id from sqmdb_cost.tower3 where stype='室分')
select 
dg.city_code,
dg.city_pro,
t1.tower_num_ly,
t2.tower_num_cm,
(t1.tower_num_ly+t2.tower_num_cm)/2 as tower_num_avg
from
(select city_operator,count(*) as tower_num_ly from tower_code where sdate='202112' group by city_operator)t1
join 
(select city_operator,count(*) as tower_num_cm from tower_code where sdate='202204' group by city_operator)t2
on t1.city_operator=t2.city_operator
join
(select distinct city_code,city_name,city_pro from sqmdb_cost.dict_grid)dg
on t1.city_operator=dg.city_name) tn
on tc.sname=tn.city_pro


---网格---网格名称+区县名称
--drop table sqmdb_cost.rpt_tower_cost_grid
--create table sqmdb_cost.rpt_tower_cost_grid as 
select 
tc.smonth,
(regexp_split_to_array(tc.city_name,'_'))[2] as city_name,
tn.area_name,
tn.area_tower,
tc.grid_name,
tc.tower_cost,
tn.tower_num_ly,
tn.tower_num_cm,
tn.tower_num_avg,
(tc.tower_cost/tn.tower_num_avg)/4 as tower_cost_avg
from
(select smonth,city_name,grid_name,(column2+column3) as tower_cost from sqmdb_cost.report49 r 
where period_type='Y' and smonth='202204')tc
join
(with tower_code as (
select distinct sdate,city_operator,area_operator,site_addr_id from sqmdb_cost.tower3 where stype!='室分'
union all
select sdate,city_operator,area_operator,site_addr_id from sqmdb_cost.tower3 where stype='室分')
select 
dg.city_code,
dg.area_name,
dg.area_tower,
dg.grid_city_name,
dg.grid_name,
t1.area_operator,
t1.tower_num_ly,
t2.tower_num_cm,
(t1.tower_num_ly+t2.tower_num_cm)/2 as tower_num_avg
from
(select city_operator,area_operator,count(*) as tower_num_ly from tower_code where sdate='202112' group by city_operator,area_operator)t1
join 
(select city_operator,area_operator,count(*) as tower_num_cm from tower_code where sdate='202204' group by city_operator,area_operator)t2
on t1.city_operator=t2.city_operator and t1.area_operator=t2.area_operator
join
(select distinct city_code,city_name,area_name,grid_city_name,grid_name,area_tower from sqmdb_cost.dict_grid)dg
on t1.city_operator=dg.city_name and t1.area_operator=dg.area_tower) tn
on tc.city_name=tn.grid_city_name and trim(tc.grid_name)=trim(tn.grid_name)



---网格---使用铁塔预提表计算网格站点数
--drop table sqmdb_cost.rpt_tower_cost_grid_1
--create table sqmdb_cost.rpt_tower_cost_grid_1 as 
select 
tc.smonth,
tc.city_name,
dcg.city_code*100 as city_code,
dcg.grid_name,
dcg.grid_code,
--tn.area_name,
--tn.area_tower,
--tc.grid_name,
tc.tower_cost,
tn.tower_num_ly,
tn.tower_num_cm,
tn.tower_num_avg,
(tc.tower_cost/tn.tower_num_avg)/4 as tower_cost_avg
from
(select t1.smonth,t1.city_name,t1.tower_cost,t2.grid_code 
from 
(select smonth,city_name_short(city_name) as city_name,grid_name,(column2+column3) as tower_cost from sqmdb_cost.report49 r where period_type='Y' and smonth='202204')t1
left join sqmdb_cost.dict_grid_code t2 on t1.city_name=t2.city_name and trim(t1.grid_name)=trim(t2.grid_name) 
where t2.grid_code is not null)tc
join
(select yt.city_name,dgc.grid_code,yt.tower_num_ly,yt.tower_num_cm,yt.tower_num_avg from
(select a.city_name,a.cbzx,b.tower_num_ly,a.tower_num_cm,
avg_num(a.tower_num_cm,b.tower_num_ly) as tower_num_avg
from 
(select city_name,cbzx,count(*) as tower_num_cm from sqmdb_cost.tower_yuti where tower_code is not null and smonth='202204' group by city_name,cbzx) a
join
(select city_name,cbzx,count(*) as tower_num_ly from sqmdb_cost.tower_yuti where tower_code is not null and smonth='202112' group by city_name,cbzx) b
on a.city_name=b.city_name and a.cbzx=b.cbzx) yt
left join sqmdb_cost.dict_grid_code dgc on yt.city_name=dgc.city_name and trim(yt.cbzx)=trim(dgc.grid_name)) tn
on tc.grid_code=tn.grid_code
left join sqmdb_cost.dict_code_grid dcg on tc.grid_code=dcg.grid_code