123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168 |
- --铁塔成本
- with chengben as (
- select sdate,city_operator,city_service,city_site,stype,is_duxiang,avg(total_cost) as avgcost from sqmdb_cost.tower2
- where total_cost>0
- group by sdate,city_operator,city_service,city_site,stype,is_duxiang)
- select a.*,d.cbzx,chengben.avgcost,
- a.total_cost/chengben.avgcost as cost_rate
- from sqmdb_cost.tower2 a
- left join chengben on chengben.sdate=a.sdate and chengben.city_operator=a.city_operator and chengben.stype=a.stype
- and chengben.is_duxiang=a.is_duxiang
- left join sqmdb_cost.dict_tower_cbzx d on a.site_addr_id=d.tower_code;
- --局站租金
- with station_cost as (
- select a.city_code,b.dicttext as city_name,a.area_code,c.dicttext as area_name,a.station_sid,e.cbzx,d.total_cost as yuezujin,'铁塔' as stype from sqmdb_cost.station2 a
- join sqmdb_cost.tower2 d on a.tower_code=d.site_addr_id
- left join sqmdb_cost.dict b on b.dictid=a.city_code::varchar
- left join sqmdb_cost.dict c on c.dictid=a.area_code::varchar
- left join sqmdb_cost.dict_tower_cbzx e on a.tower_code=e.tower_code
- union
- select a.city_code,b.dicttext as city_name,a.area_code,c.dicttext as area_name,d.station_sid,e.cbzx,d.yuezujin,'合同' as stype
- from sqmdb_cost.station_hetong d
- left join sqmdb_cost.station2 a on a.station_sid=d.station_sid
- left join sqmdb_cost.dict b on b.dictid=a.city_code::varchar
- left join sqmdb_cost.dict c on c.dictid=a.area_code::varchar
- left join sqmdb_cost.hetong2 e on e.sid=d.hetong_sid||E'\t'
- )
- select city_code,city_name,area_code,area_name,station_sid,sum(yuezujin)
- from station_cost
- group by city_code,city_name,area_code,area_name,station_sid
- --租赁费(chengben_exception_1_grid_202203.csv)
- ----横向匹配 局站表a <- 合同索引b <- 合同系统c <- 铁塔全量表d
- select tb1.*,tb2.city_code*100 as city_code,tb3.area_code from
- (select
- distinct
- t1.sdate,
- t1.city_name,
- t1.area_name,
- t1.station_sid,
- t1.station_sname,
- t1.property_type,
- t1.sid as hetong_sid,
- t1.hetong_yuezujin as cost_hetong,
- t1.tower_code,
- t1.tower_cost as cost_tower,
- t1.cost_total,
- t2.cbzx,
- t2.cbzx_cost_avg,
- t1.cost_total/t2.cbzx_cost_avg as cost_rate
- from (
- select
- a.station_sid,a.station_sname,a.city_name,a.area_name,a.property_type,a.station_level,a.tower_code,
- c.yuezujin as hetong_yuezujin,--合同系统月租金(由合同总月份数和总金额计算得出)
- c.cname,c.cfname,c.cbzx as hetong_cbzx,c.sid,c.sname,
- d.sdate,e.cbzx as tower_cbzx,d.service_tracking_id,d.site_addr_id,d.stype,d.is_duxiang,d.total_cost as tower_cost,
- (coalesce(c.yuezujin,0)+coalesce(d.total_cost,0)) as cost_total,--总费用(合同月租金使用合同系统值)
- case when c.cbzx is null then e.cbzx else c.cbzx end as cbzx
- from sqmdb_cost.station3 a
- left join sqmdb_cost.station_hetong b on a.station_sid=b.station_sid
- left join sqmdb_cost.hetong2 c on c.sid=b.hetong_sid||E'\t'
- left join sqmdb_cost.tower2 d on a.tower_code=d.site_addr_id
- left join sqmdb_cost.dict_tower_cbzx e on a.tower_code=e.tower_code) t1
- left join
- --本单位铁塔单站租赁费
- (select sdate,cbzx,avg(total_cost) as cbzx_cost_avg from(
- select a.sdate,b.cbzx,a.site_addr_id,a.total_cost
- from sqmdb_cost.tower2 a
- left join sqmdb_cost.dict_tower_cbzx b
- on a.site_addr_id=b.tower_code) c
- group by sdate,cbzx) t2
- on t1.cbzx=t2.cbzx
- where property_type in ('租用铁塔','租用第三方')
- and t1.cost_total/t2.cbzx_cost_avg>1.5
- and t1.sdate='202203'
- and t2.sdate='202203') tb1
- left join
- (select distinct city_code,city_name from sqmdb_cost.dict_grid) tb2
- on tb1.city_name=tb2.city_name
- left join
- (select distinct city_code,city_name,area_code,area_name from sqmdb_cost.dict_grid) tb3
- on tb1.city_name=tb3.city_name and tb1.area_name=tb3.area_name
- order by tb2.city_code,tb3.area_code
- --电费单价(新)使用局站级的电费预提明细
- --(chengben_exception_2_grid_202203.csv)
- select yn.smonth,yn.city_name,yn.area_name,s.station_sid,s.station_sname,yn.meter_type_name,yn.accrued_cost,yn.accrued_power,
- case when accrued_power=0 then 0 else yn.accrued_cost/yn.accrued_power end as eprice,yn.cost_unit_name,
- dg1.city_code*100 as city_code,dg2.area_code
- from sqmdb_cost.ytmx_new yn
- left join sqmdb_cost.station_nenghao sn on yn.build_code_short=sn.nh_sid_short
- left join sqmdb_cost.station3 s on sn.station_sid=s.station_sid
- left join sqmdb_cost.dict_grid dg on yn.city_name=dg.city_name and yn.area_name=dg.area_name
- left join (select distinct city_code,city_name from sqmdb_cost.dict_grid) dg1
- on yn.city_name=dg1.city_name
- left join
- (select distinct city_code,city_name,area_code,area_name from sqmdb_cost.dict_grid) dg2
- on yn.city_name=dg2.city_name and yn.area_name=dg2.area_name
- where yn.meter_type_name in ('12-01 | 转供手抄表','21-01 | 定额协议表','21-02 | 铁塔包干表')
- and s.station_sid is not null
- and (case when accrued_power=0 then 0 else yn.accrued_cost/yn.accrued_power end)>0.8
- and yn.smonth='202203'
- order by dg1.city_code,dg2.area_code
- --select distinct meter_type_name from sqmdb_cost.ytmx_new yn
- --局站功耗
- ---rru先关联机房 关联局站
- select *,ri.room_sid from
- (select rru_id,bbu_id,city_name,district_name,unnest(string_to_array(cell_oid,'|')) as cid,room from sqmdb_cost.rru where room is not null) r
- left join sqmdb_cost.roomid ri
- on r.room=ri.room_id
- where ri.room_sid is not null
- select *,ri.room_sid from
- (select bbu_id,city_name,district_name,vendor,site_id,room,net_type from sqmdb_cost.bbu) b
- left join sqmdb_cost.roomid ri
- on b.room=ri.room_id
- where ri.room_sid is not null
- ---(RRU先和BBU关联,再通过BBU关联到机房,再关联到局站)
- select * from
- (select r2.city_code,r2.area_code,r.city_name,r.district_name,r2.station_sid,r2.station_name,r.cid,
- (regexp_split_to_array(r.cid,'\.'))[2] as enbid,
- (regexp_split_to_array(r.cid,'\.'))[3] as cellid
- from
- (select rru_id,bbu_id,city_name,district_name,unnest(string_to_array(cell_oid,'|')) as cid,room from sqmdb_cost.rru) r
- join
- (select bbu_id,room from sqmdb_cost.bbu) b
- on r.bbu_id=b.bbu_id
- join sqmdb_cost.roomid ri
- on b.room=ri.room_id
- join sqmdb_cost.room r2
- on ri.room_sid=r2.room_sid) a
- left join
- (select smonth,siteid,ci,sum(nh_kwh) as nh_kwh from sqmdb_cost.nenghao_rru group by smonth,siteid,ci) b
- on a.enbid=b.siteid and a.cellid=b.ci
- --在用(专业网管先算出本站所有RRU功耗,通过站号关联BBU,再关联机房-局站-预提明细)
- --(chengben_exception_3_grid_202203.csv)
- select t1.smonth,t1.city_code*100 as city_code,t1.area_code,t2.station_sid,t2.station_name,
- t1.nh_total,t2.accrued_power,t2.cost_unit_name
- from
- (select nr.smonth,r2.city_code,r2.area_code,r2.station_sid,sum(nh_rru+nh_bbu) as nh_total from
- (select bbu_id,site_id,(regexp_split_to_array(site_id,'\.'))[2] as enbid,room from sqmdb_cost.bbu) b
- join
- (select smonth,siteid,sum(nh_kwh) as nh_rru from sqmdb_cost.nenghao_rru group by smonth,siteid) nr
- on b.enbid=nr.siteid
- join
- (select smonth,siteid,sum(nh_kwh) as nh_bbu from sqmdb_cost.nenghao_bbu group by smonth,siteid) nb
- on nr.smonth=nb.smonth and nr.siteid=nb.siteid
- join sqmdb_cost.roomid ri
- on b.room=ri.room_id
- join sqmdb_cost.room r2
- on ri.room_sid=r2.room_sid
- group by nr.smonth,r2.city_code,r2.area_code,r2.station_sid) t1--专业网管能耗
- join
- (select smonth,station_sid,station_name,cost_unit_name,sum(accrued_cost) as accrued_cost,sum(accrued_power) as accrued_power from
- (select yn.smonth,sn.station_sid,station_name,yn.accrued_cost,yn.accrued_power,yn.cost_unit_name from sqmdb_cost.ytmx_new yn
- join sqmdb_cost.station_nenghao sn
- on yn.build_code_short=sn.nh_sid_short) tp group by smonth,station_sid,station_name,cost_unit_name) t2 --能耗表能耗
- on t1.smonth=t2.smonth and t1.station_sid=t2.station_sid
|