--15、活动ONU单端口能耗（月）
---地市
select 
t1.smonth as MONTH_ID,
'地市' as ZB_TYPE,
'河北' as PROV_NAME,
t1.city_code*100 as CITY_ID,
t1.city_name as CITY_NAME,
null as GRID_ID,
null as GRID_NAME,
'15' as ZB_CODE,
'活动ONU单端口能耗（月）' as ZB_NAME,
'否' as IS_RATIO,
t1.cost_nenghao/10000 as QZ_FZ,
t2.active_onu_num/10000 as QZ_FM,
t1.cost_nenghao/t2.active_onu_num/6 as BY_QZ
from 
(select a.smonth,dg.city_code,dg.city_name,a.cost_nenghao from
(select '202206' as smonth,sname,
sum((column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115)-column5-column19-column108) as cost_nenghao
from sqmdb_cost.report50 r
where period_type='M' and stype ='北方省汇总展现'
and sname in ('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
and smonth between '202201' and '202206' group by sname) a
join (select distinct city_code,city_name,city_pro from sqmdb_cost.dict_grid) dg
on a.sname=dg.city_pro) t1
join
(select n2.smonth,n2.city_name,
avg_num(n1.active_onu_num,n2.active_onu_num) as active_onu_num from
(select * from sqmdb_cost.active_onu where smonth='202112') n1
join 
(select * from sqmdb_cost.active_onu where smonth='202206') n2
on n1.city_name=n2.city_name) t2
on t1.city_name=t2.city_name
order by t1.city_code