chengben/Scripts/局站网管能耗.sql

with 
rru as (select * from sqmdb_cost.nenghao_rru),
bbu as (select * from sqmdb_cost.nenghao_bbu)
select smonth,station_sid,sum(nh_kwh_total) as nh_total 
from(
--aau
select smonth,station_sid,'AAU_5G' as stype,sum(nh_kwh) as nh_kwh_total from
(select station_sid,cell_oid,siteid,kid --AAUµÄÊÇ¿òºÅ
from sqmdb_cost.station_aau) sa
join
(select * from rru where net_type ='5G') wg
on sa.siteid=wg.siteid and sa.kid=wg.kid
group by smonth,station_sid
union all
--rru
select smonth,station_sid,'RRU_4G' as stype,sum(nh_kwh) as nh_kwh_total from
(select distinct station_sid,cid,
(regexp_split_to_array(cid,'\.'))[2] as siteid,
(regexp_split_to_array(cid,'\.'))[3] as ci
from
(select station_sid,unnest(string_to_array(cell_oid,'|')) as cid from sqmdb_cost.station_rru) r ) sr
join 
(select * from rru where net_type ='4G') wg
on sr.siteid=wg.siteid and sr.ci=wg.ci
group by smonth,station_sid
union all
--bbu_4g
select smonth,station_sid,'BBU_4G' as stype,sum(nh_kwh) as nh_kwh_total
from
(select b4.smonth,r2.station_sid,se.enodeb_id,b4.nh_kwh
from sqmdb_cost.sc_enodeb se  
join sqmdb_cost.roomid ri 
on se.room_id=ri.room_id
join sqmdb_cost.room r2
on ri.room_sid=r2.room_sid
join
(select * from bbu where net_type ='4G') b4
on se.enodeb_id=b4.siteid) wg4b
group by smonth,station_sid
union all
--bbu_5g
select smonth,station_sid,'BBU_5G' as stype,sum(nh_kwh) as nh_kwh_total
from
(select b5.smonth,r2.station_sid,sg.gnodeb_id,b5.nh_kwh
from sqmdb_cost.sc_gnodeb sg  
join sqmdb_cost.roomid ri 
on sg.room_id=ri.room_id
join sqmdb_cost.room r2
on ri.room_sid=r2.room_sid
join 
(select * from bbu where net_type ='5G') b5
on sg.gnodeb_id=b5.siteid) wg5b
group by smonth,station_sid) uni
group by smonth,station_sid


select * from v_station_omc_nh where smonth='202203'


--create table sqmdb_cost.rpt_station_nh_month_uni as 
select smonth,station_sid,'AAU_5G' as stype,sum(nh_kwh) as nh_kwh_total from
(select station_sid,cell_oid,siteid,kid --AAUµÄÊÇ¿òºÅ
from sqmdb_cost.station_aau) sa
join
(select * from sqmdb_cost.nenghao_rru nr where net_type ='5G' and smonth='202204') wg
on sa.siteid=wg.siteid and sa.kid=wg.kid
group by smonth,station_sid