chengben/Scripts/大屏_看异常.sql

--铁塔成本
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