chengben/Scripts/kpi31/23_city.sql

--23、铁塔共享率  select * from sqmdb_cost.tower3 
---地市
select 
a.sdate as MONTH_ID,
'地市' as ZB_TYPE,
'河北' as PROV_NAME,
dg.city_code*100 as CITY_ID,
a.city_operator as CITY_NAME,
null as GRID_ID,
null as GRID_NAME,
'23' as ZB_CODE,
'铁塔共享率' as ZB_NAME,
'是' as IS_RATIO,
a.share_num as QZ_FZ,
a.total_num as QZ_FM,
a.share_rate as BY_QZ
from
(with t as (select distinct sdate,city_operator,area_operator,site_addr_id,is_duxiang from sqmdb_cost.tower3 where stype in ('新建及共享改造','存量注入'))
select sdate,city_operator,
sum(case when is_duxiang='共享' then 1 else 0 end) as share_num,
count(*) as total_num,
sum(case when is_duxiang='共享' then 1 else 0 end)/count(*)::numeric as share_rate
from t
where sdate='202207'
group by sdate,city_operator) a
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) dg
on a.city_operator=dg.city_name
order by dg.city_code