12345678910111213141516171819202122232425262728293031323334353637 |
- --场租(视图: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'
|