chengben/Scripts/铁塔场租视图.sql

--场租（视图：v_tower_cost_changd）新建及共享改造、存量注入、室分 有场租，微站没有
select * from sqmdb_cost.v_tower_cost_changd where sdate='202204'

--关联逻辑：
--1：铁塔订单地市、区县、网格场租、地市场租关联网格编码字典，得到网格编码（要网格铁塔单站租赁费尽可能全，字典是关键）
--2：网格编码字典关联网格铁塔单站租赁费，得到网格铁塔铁塔单站租赁费
--3：地市关联地市铁塔单站租赁费，得到地市铁塔单站租赁费
--4：铁塔订单中的地市和区县关联dict_grid得到资源系统的地市和区县，以便和资源系统关联
with tower_cz as (select * from sqmdb_cost.tower3 where changd_cost>0)
select 
cz.*,
tg.tower_cost_avg as tower_cost_grid,
tc.tower_cost_avg as tower_cost_city,
dg.area_name 
from
(select 
cz_grid.sdate,
cz_grid.city_operator,
cz_grid.area_operator,
cz_grid.changd_cost_grid,
cz_city.changd_cost_city,
dgc.grid_code 
from
(select sdate,city_operator,area_operator,avg(changd_cost) as changd_cost_grid 
from tower_cz group by sdate,city_operator,area_operator) cz_grid
left join
(select sdate,city_operator,avg(changd_cost) as changd_cost_city
from tower_cz group by sdate,city_operator) cz_city
on cz_grid.sdate=cz_city.sdate and cz_grid.city_operator=cz_city.city_operator
left join sqmdb_cost.dict_grid_code dgc 
on city_name_short(cz_grid.city_operator)=dgc.city_name and cz_grid.area_operator=dgc.grid_name) cz
left join sqmdb_cost.rpt_tower_cost_grid tg on cz.sdate=tg.smonth and cz.grid_code=tg.grid_code
left join sqmdb_cost.rpt_tower_cost_city tc on cz.sdate=tc.smonth and city_name_short(cz.city_operator)=tc.city_name
left join sqmdb_cost.dict_grid dg on cz.city_operator=dg.city_name and cz.area_operator=dg.area_tower
where cz.sdate='202204'