| 12345678910111213141516171819202122232425262728 |
- --08、网络能耗占收比
- ---地市
- 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,
- '8' as ZB_CODE,
- '网络能耗占收比' as ZB_NAME,
- '是' as IS_RATIO,
- a.cost_nenghao as QZ_FZ,
- b.income_year as QZ_FM,
- case when b.income_year=0 then null else a.cost_nenghao/b.income_year end as BY_QZ
- from
- (select '202206' as smonth,sname,stype,
- sum(column5+column19+column27+column41+column49+column57+column70+column78+column86+column94+column100+column108+column115)/10000 as cost_nenghao
- from sqmdb_cost.report50 where period_type='M' and stype ='北方省汇总展现' and smonth between '202201' and '202206' group by sname,stype) a
- left join
- (select report_id,smonth,sname,stype,column4 as income_year from sqmdb_cost.report80040) b
- on a.smonth=b.smonth and a.sname=b.sname
- join
- (select distinct city_code,city_pro from sqmdb_cost.dict_grid) dg
- on a.sname=dg.city_pro
- where a.sname in ('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
- order by dg.city_code
|
