chengben/Scripts/kpi31/06_city.sql

--06、百元资产网运成本（省内口径）
---地市
select 
a.smonth as MONTH_ID,
'地市' as ZB_TYPE,
'河北' as PROV_NAME,
dg.city_code*100 as CITY_ID,
a.sname as CITY_NAME,
null as GRID_ID,
null as GRID_NAME,
'6' as ZB_CODE,
'百元资产网运成本（省内口径）' as ZB_NAME,
'否' as IS_RATIO,
a.cost_total as QZ_FZ,
b.zichan as QZ_FM,
a.cost_total/b.zichan*100 as BY_QZ
from
(select smonth,stype,sname,cost_total from sqmdb_cost.chengben_shengnei 
where stype='地市' and smonth='202206') a
join
(select sname,stype,sum(column7+column8)/2 as zichan from sqmdb_cost.report80054
where sname in('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
and period_type='M' and smonth between '202201' and '202206' group by sname,stype) b
on a.sname=b.sname
join
(select distinct city_code,city_pro from sqmdb_cost.dict_grid) dg
on a.sname=dg.city_pro
order by dg.city_code