chengben/Scripts/kpi31/27_city.sql

--27、单OLT承载用户数
---地市
with grid as (select ou.smonth,dg.city_code,ou.city_name,ou.area_name,kd_num,olt_num from sqmdb_cost.olt_user ou 
join 
(select distinct city_code,city_name from sqmdb_cost.dict_grid) dg
on ou.city_name=dg.city_name
where ou.smonth='202206')
select 
smonth as MONTH_ID,
'地市' as ZB_TYPE,
'河北' as PROV_NAME,
city_code*100 as CITY_ID,
city_name as CITY_NAME,
null as GRID_ID,
null as GRID_NAME,
'27' as ZB_CODE,
'单OLT承载用户数' as ZB_NAME,
'否' as IS_RATIO,
sum(kd_num) as QZ_FZ,
sum(olt_num) as QZ_FM,
round(sum(kd_num)/sum(olt_num)) as BY_QZ
from grid group by smonth,city_code,city_name
order by city_code