chengben/Scripts/kpi31/23_grid.sql

--23、铁塔共享率  select * from sqmdb_cost.tower3 
---网格
select 
t1.sdate as MONTH_ID,
'网格' as ZB_TYPE,
'河北' as PROV_NAME,
dcg.city_code*100 as CITY_ID,
dcg.city_name as CITY_NAME,
dcg.grid_code as GRID_ID,
dcg.grid_name as GRID_NAME,
'23' as ZB_CODE,
'铁塔共享率' as ZB_NAME,
'是' as IS_RATIO,
t1.share_num as QZ_FZ,
t1.total_num as QZ_FM,
t1.share_rate as BY_QZ
from
(select sdate,city_name,grid_code,
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
(select t.*,dgc.grid_code from 
(select distinct sdate,city_name_short(city_operator) as city_name,area_operator,site_addr_id,is_duxiang 
from sqmdb_cost.tower3 
where stype in ('新建及共享改造','存量注入') and sdate='202206') t
join sqmdb_cost.dict_grid_code dgc on t.city_name=dgc.city_name and trim(t.area_operator)=dgc.grid_name
where dgc.grid_code is not null) a
group by sdate,city_name,grid_code) t1
join sqmdb_cost.dict_code_grid dcg on t1.grid_code=dcg.grid_code
order by dcg.city_code