chengben/Scripts/kpi31/25_city.sql

--25、基站单站运行成本效益
---地市
select 
t1.smonth as MONTH_ID,
'地市' as ZB_TYPE,
'河北' as PROV_NAME,
t2.city_code*100 as CITY_ID,
t1.sname as CITY_NAME,
null as GRID_ID,
null 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 '202206' as smonth,sname,stype,
sum(column2+column3+column4+column5+column6+column7+column8+column9+column10+column11+column12+column13+column14+column15+column16+column17+column18) as cost_wuxian,
sum(column19+column20+column21+column22+column23+column24+column25+column26) as cost_hexin,
sum(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.report50
where sname in('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
and period_type='M' and smonth between '202201' and '202206' group by sname,stype) t1
left join
(select city_code,
case when city_code=1301 then '石家庄_河北'
when city_code=1302 then '唐山_河北'
when city_code=1303 then '秦皇岛_河北'
when city_code=1304 then '邯郸_河北'
when city_code=1305 then '邢台_河北'
when city_code=1306 then '保定_河北'
when city_code=1307 then '张家口_河北'
when city_code=1308 then '承德_河北'
when city_code=1309 then '沧州_河北'
when city_code=1310 then '廊坊_河北'
when city_code=1311 then '衡水_河北'
when city_code=1312 then '雄安_河北'
else '' end as city_name,
count(*) as site_count
from (select distinct eqp_sid,city_code from sqmdb_cost.equip where profession ='移动无线') a
group by city_code) t2
on t1.sname=t2.city_name
order by t2.city_code