chengben/Scripts/kpi31/22_city.sql

--22、超高电价占比
---地市（可由网格数据汇聚）
select 
a.smonth 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,
'22' as ZB_CODE,
'超高电价占比' as ZB_NAME,
'是' as IS_RATIO,
a.high_price_num as QZ_FZ,
a.total_num as QZ_FM,
a.high_price_rate as BY_QZ
from
(select smonth,city_name,
count(*) as total_num,
sum(case when y.eprice>0.8 then 1 else 0 end) as high_price_num,
sum(case when y.eprice>0.8 then 1 else 0 end)/count(*)::numeric as high_price_rate
from 
(select *,(case when accrued_power=0 then 0 else accrued_cost/accrued_power end) as eprice 
from sqmdb_cost.ytmx_new where build_type_name not in ('办公楼','办公用房','营业用房') and smonth='202207') y 
group by smonth,city_name) a
join 
(select distinct city_code,city_name from sqmdb_cost.dict_grid) dg
on a.city_name=dg.city_name
order by dg.city_code