chengben/Scripts/kpi31/25_grid.sql

--25、基站单站运行成本效益
---网格
select
t1.smonth as MONTH_ID,
'网格' as ZB_TYPE,
'河北' as PROV_NAME,
t1.city_code*100 as CITY_ID,
t1.city_name as CITY_NAME,
t1.grid_code as GRID_ID,
t1.grid_name as GRID_NAME,
'25' as ZB_CODE,
'基站单站运行成本效益' as ZB_NAME,
'否' as IS_RATIO,
t1.cost_wuxian_hexin as QZ_FZ,
t2.site_count as QZ_FM,
t1.cost_wuxian_hexin/t2.site_count/6 as BY_QZ
from
(select fz.smonth,dcg.city_code,dcg.area_code,dcg.grid_code,dcg.city_name,fz.grid_name,fz.cost_wuxian,fz.cost_hexin,fz.cost_wuxian_hexin from 
(select smonth,city_name,grid_name,
(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18) as cost_wuxian,
(column19+column20+column21+column22+column23+column24+column25+column26) as cost_hexin,
(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18+column19+column20+column21+column22+column23+column24+column25+column26) as cost_wuxian_hexin
from sqmdb_cost.report49
where period_type='Y' and grid_name!='合计' and smonth='202206') fz
join sqmdb_cost.dict_grid_code dgc on city_name_short(fz.city_name)=dgc.city_name and trim(fz.grid_name)=trim(dgc.grid_name)
join sqmdb_cost.dict_code_grid dcg on dgc.grid_code=dcg.grid_code) t1
join
(select city_code,area_code,count(*) as site_count from 
(select distinct eqp_sid,city_code,area_code from sqmdb_cost.equip where profession ='移动无线') et
group by city_code,area_code) t2
on t1.city_code=t2.city_code and t1.area_code=t2.area_code
order by t1.city_code