chengben/Scripts/kpi31/26_grid.sql

--26、H端口实占率
---网格
select 
'202206' 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,
a.area_name as GRID_NAME,
'26' as ZB_CODE,
'H端口实占率' as ZB_NAME,
'是' as IS_RATIO,
a.ftth_port_sts_count as QZ_FZ,
a.ftth_port_count as QZ_FM,
case when a.ftth_port_count=0 then 0 else a.ftth_port_sts_count/a.ftth_port_count end as BY_QZ
from
(select p2.smonth,p2.city_name,p2.area_name,
avg_num(p1.ftth_port_sts_count,p2.ftth_port_sts_count)/10000 as ftth_port_sts_count, 
avg_num(p1.ftth_port_count,p2.ftth_port_count)/10000 as ftth_port_count
from
(select * from sqmdb_cost.kuandai_ftth_month where smonth='202112')p1
join
(select * from sqmdb_cost.kuandai_ftth_month where smonth='202206')p2
on p1.city_name=p2.city_name and p1.area_name=p2.area_name) a
left join sqmdb_cost.dict_grid_code dgc on a.city_name=dgc.city_name and trim(a.area_name)=trim(dgc.grid_name)
join sqmdb_cost.dict_code_grid dcg on dgc.grid_code=dcg.grid_code 
order by dcg.city_code