chengben/Scripts/kpi31/29_city.sql

--29、单公里承载用户数
---地市
select 
month_id 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,
'29' as ZB_CODE,
'单公里承载用户数' as ZB_NAME,
'否' as IS_RATIO,
total_user_yw+total_user_kd as QZ_FZ,
total_gl as QZ_FM,
per_km_user as BY_QZ
from 
(select month_id,city_name,
total_user_yw,total_user_kd,total_gl,
round((total_user_yw+total_user_kd)/total_gl) as per_km_user
from sqmdb_cost.jt_number_city jnc 
where month_id='202206') a
join 
(select distinct city_code,city_sname from sqmdb_cost.dict_grid) dg
on a.city_name=dg.city_sname
order by dg.city_code