chengben/Scripts/14.sql

--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