chengben/Scripts/kpi31/20_city.sql

--20、PUE超标率
---地市
select
'202206' as MONTH_ID,
'地市' as ZB_TYPE,
'河北' as PROV_NAME,
dg.city_code*100 as CITY_ID,
a.city_name as CITY_NAME,
null as GRID_ID,
null as GRID_NAME,
'20' as ZB_CODE,
'PUE超标率' as ZB_NAME,
'是' as IS_RATIO,
pue_total as QZ_FZ,
pue_over as QZ_FM,
pue_over_rate as BY_QZ
from
(select 
case when city_name='雄安新区' then '雄安' else city_name end,
count(*) as pue_total,
sum(case when pue>1.5 then 1 else 0 end) as pue_over,
sum(case when pue>1.5 then 1 else 0 end)/count(*)::numeric as pue_over_rate
from sqmdb_cost.pue_top 
group by city_name) 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