chengben/Scripts/kpi31/20_grid.sql

--20、PUE超标率
---网格
select
'202206' as MONTH_ID,
'网格' as ZB_TYPE,
'河北' as PROV_NAME,
dcg.city_code*100 as CITY_ID,
a.city_name as CITY_NAME,
dcg.grid_code as GRID_ID,
a.area_name as GRID_NAME,
'20' as ZB_CODE,
'PUE超标率' as ZB_NAME,
'是' as IS_RATIO,
a.pue_over as QZ_FZ,
a.pue_total as QZ_FM,
a.pue_over_rate as BY_QZ
from
(select city_name_short(city_name) as city_name,area_name,
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,area_name) a
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