123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114 |
- --14、单载频载扇能耗(月)
- ---北10
- --create table sqmdb_cost.rpt_dzpzs_cost_b10 as
- select fz.smonth,fz.sname,fz.cost_yw,fz.cost_hexin,
- (fz.cost_yw+fz.cost_hexin) as cost_total,
- fm.zps_num_no5g,
- case when fm.zps_num_no5g=0 then 0 else (fz.cost_yw+fz.cost_hexin)/fm.zps_num_no5g/3 end as cost_zps_no5g,
- case
- when fz.sname='北京' then 110000
- when fz.sname='天津' then 120000
- when fz.sname='河北' then 130000
- when fz.sname='山西' then 140000
- when fz.sname='内蒙古' then 150000
- when fz.sname='辽宁' then 210000
- when fz.sname='吉林' then 220000
- when fz.sname='黑龙江' then 230000
- when fz.sname='山东' then 370000
- when fz.sname='河南' then 410000
- end as prov_code
- from
- (select '202203' as smonth,sname,
- sum(column4+column5)/10000 as cost_yw,
- sum(column19)/10000 as cost_hexin
- from sqmdb_cost.report50
- where period_type='M'
- and sname in('北京','天津','河北','山西','内蒙古','辽宁','吉林','黑龙江','山东','河南')
- and smonth between '202201' and '202203' group by sname) fz
- join
- (select smonth,sname,zps_total_no5g as zps_num_no5g
- from sqmdb_cost.jt_number_b10 where smonth='202203') fm
- on fz.sname=fm.sname
- --全省
- select fz.smonth,fz.sname,fz.cost_yw,fz.cost_hexin,
- (fz.cost_yw+fz.cost_hexin) as cost_total,
- fm.zps_num_with5g,
- case when fm.zps_num_with5g=0 then 0 else (fz.cost_yw+fz.cost_hexin)/fm.zps_num_with5g/4 end as cost_zps_with5g
- from
- (select '202204' as smonth,sname,
- sum(column4+column5)/10000 as cost_yw,
- sum(column19)/10000 as cost_hexin
- from sqmdb_cost.report50
- where period_type='M' and sname ='河北' and smonth between '202201' and '202204' group by sname) fz
- join
- (select a.city_name,(a.zps_total+b.zps_total)/10000/2 as zps_num_with5g from
- (select '河北' as city_name,zps_total from sqmdb_cost.zpzs_city where smonth='202112' and city_name='全省')a
- join
- (select '河北' as city_name,zps_total from sqmdb_cost.zpzs_city where smonth='202204' and city_name='全省')b
- on a.city_name=b.city_name) fm
- on fz.sname=fm.city_name
- ---地市
- select fz.smonth,fz.city_sname,fz.cost_yw,fz.cost_hexin,
- (fz.cost_yw+fz.cost_hexin) as cost_total,
- fm.zps_num_with5g,
- fm.zps_num_no5g,
- case when fm.zps_num_with5g=0 then 0 else (fz.cost_yw+fz.cost_hexin)/fm.zps_num_with5g/4 end as cost_zps_with5g,
- case when fm.zps_num_no5g=0 then 0 else (fz.cost_yw+fz.cost_hexin)/fm.zps_num_no5g/4 end as cost_zps_no5g,
- fz.city_code*100 as city_code
- from
- (select a.smonth,dg.city_sname,dg.city_code,a.cost_yw,a.cost_hexin from
- (select '202204' as smonth,sname,
- sum(column4+column5)/10000 as cost_yw,
- sum(column19)/10000 as cost_hexin
- from sqmdb_cost.report50
- where period_type='M'
- and sname in('石家庄_河北','唐山_河北','秦皇岛_河北','邯郸_河北','邢台_河北','保定_河北','张家口_河北','承德_河北','廊坊_河北','沧州_河北','衡水_河北','雄安_河北')
- and smonth between '202201' and '202204' group by sname) a
- join (select distinct city_code,city_sname,city_pro from sqmdb_cost.dict_grid) dg
- on a.sname=dg.city_pro) fz
- join
- (select
- city_name,
- zps_total_with5g as zps_num_with5g,
- zps_total_no5g as zps_num_no5g
- from sqmdb_cost.jt_number_city where month_id='202204') fm
- on fz.city_sname=fm.city_name
- order by fz.city_code
- --单载频载扇能耗--网格
- --create table sqmdb_cost.rpt_dzpzs_cost_grid_1 as
- select fm.smonth,dcg.city_name,dcg.grid_name,fz.cost_yw,fz.cost_hexin,
- (fz.cost_yw+fz.cost_hexin) as cost_total,
- fm.zps_num_with5g,
- fm.zps_num_no5g,
- case when fm.zps_num_with5g=0 then 0 else (fz.cost_yw+fz.cost_hexin)/fm.zps_num_with5g/4 end as cost_zps_with5g,
- null cost_zps_no5g,
- dcg.city_code,dcg.area_code
- from
- (select jt.smonth,jt.city_name,jt.grid_name,jt.cost_yw,jt.cost_hexin,dgc.grid_code from
- (select smonth,city_name_short(city_name) as city_name,grid_name,(column4+column5) as cost_yw,column19 as cost_hexin
- from sqmdb_cost.report49 where period_type='Y' and smonth='202204')jt
- left join sqmdb_cost.dict_grid_code dgc on jt.city_name=dgc.city_name and trim(jt.grid_name)=trim(dgc.grid_name)
- where dgc.grid_code is not null) fz
- join
- (select z1.smonth,z1.city_name as city_name,z1.grid_name,
- z2.zps_num_total as zps_num_total_ly,
- z1.zps_num_total as zps_num_total_cm,
- avg_num(z1.zps_num_total,z2.zps_num_total) as zps_num_with5g,
- null as zps_num_no5g,
- dgc1.grid_code
- from
- (select * from sqmdb_cost.zpzs where smonth='202204') z1
- join
- (select * from sqmdb_cost.zpzs where smonth='202112') z2
- on trim(z1.city_name)=trim(z2.city_name) and trim(z1.grid_name)=trim(z2.grid_name)
- left join sqmdb_cost.dict_grid_code dgc1 on trim(z1.city_name)=trim(dgc1.city_name) and trim(z1.grid_name)=trim(dgc1.grid_name)
- where dgc1.grid_code is not null) fm
- on fz.grid_code=fm.grid_code
- left join sqmdb_cost.dict_code_grid dcg on fz.grid_code=dcg.grid_code
|