chengben/Scripts/大屏_看趋准.sql

---1-a.转供电+定额包干累计成本占比（%）
---chengben_quzhun_1_a_grid_202203.csv
select t1.*,t2.city_code*100 as city_code,t3.area_code
from
(select smonth,city_name,area_name,
sum(accrued_cost) as total_cost,
sum(case when y.meter_type_name in ('12-01 | 转供手抄表','21-01 | 定额协议表','21-02 | 铁塔包干表') then accrued_cost else 0 end) as zgd_cost,
sum(case when y.meter_type_name in ('12-01 | 转供手抄表','21-01 | 定额协议表','21-02 | 铁塔包干表') then accrued_cost else 0 end)/sum(accrued_cost) as zgd_cost_rate
from sqmdb_cost.ytmx_new y 
group by smonth,city_name,area_name) t1
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) t2
on t1.city_name=t2.city_name
left join 
(select distinct city_code,city_name,area_code,area_name from sqmdb_cost.dict_grid) t3
on t1.city_name=t3.city_name and t1.area_name=t3.area_name
order by t2.city_code,t3.area_code

---1-b.铁塔独享站成本占比
---chengben_quzhun_1_b_grid_202203.csv
----网格
select t1.*,t2.city_code*100 as city_code,t3.area_code from
(select sdate,city_operator,area_operator,
sum(total_cost) as cost_total,
sum(case when is_duxiang='独享' then total_cost else 0 end) as cost_du,
sum(case when is_duxiang='独享' then total_cost else 0 end)/sum(total_cost) as cost_du_rate
from sqmdb_cost.tower2 where sdate='202203'
group by sdate,city_operator,area_operator) t1
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) t2
on t1.city_operator=t2.city_name
left join 
(select distinct city_code,city_name,area_code,area_tower from sqmdb_cost.dict_grid) t3
on t1.city_operator=t3.city_name and t1.area_operator=t3.area_tower
order by t2.city_code,t3.area_code


---1-c.大于2倍铁塔单站租赁费三方站点占比
---chengben_quzhun_1_c_grid_202203.csv
----网格
select t1.*,t2.city_code*100 as city_code,t3.area_code from
(select sdate,city_name,area_name,
sum(case when property_type='租用第三方' then 1 else 0 end) as cost_over2b_3rd_num,
count(*) as station_num,
sum(case when property_type='租用第三方' then 1 else 0 end)/count(*)::numeric as over_rate_3rd
from(
select *,
cost_site.cost_tower/cost_cbzx.cbzx_cost_avg as cost_rate
from
(select station_sid,city_name,area_name,property_type,tower_code from sqmdb_cost.station3 s where s.property_type in ('租用铁塔','租用第三方')) st
join
(select sdate,site_addr_id,sum(total_cost) as cost_tower from sqmdb_cost.tower2 t group by sdate,site_addr_id) cost_site
on st.tower_code=cost_site.site_addr_id
join sqmdb_cost.dict_tower_cbzx dtc on st.tower_code=dtc.tower_code
join 
(select sdate as cbzx_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 
join sqmdb_cost.dict_tower_cbzx b
on a.site_addr_id=b.tower_code) c
group by sdate,cbzx) cost_cbzx
on cost_site.sdate=cost_cbzx.cbzx_sdate and dtc.cbzx=cost_cbzx.cbzx
where cost_site.cost_tower/cost_cbzx.cbzx_cost_avg>2
and cost_site.sdate='202203') a
group by sdate,city_name,area_name) t1
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) t2
on t1.city_name=t2.city_name
left join 
(select distinct city_code,city_name,area_code,area_name from sqmdb_cost.dict_grid) t3
on t1.city_name=t3.city_name and t1.area_name=t3.area_name
order by t2.city_code,t3.area_code


---1-d.单载频载扇能耗
----同【比别人】第2项指标
---chengben_quzhun_1_d_city_202203.csv
---chengben_quzhun_1_d_grid_202203.csv


---1-e.基站单站租金合理性（局站维度）与【看异常】租赁费匹配顺序相同
---chengben_quzhun_1_e_grid_202203.csv
----网格
----判断标准：大于2倍铁塔成本中心平均租费
select tb1.*,tb2.city_code*100 as city_code,tb3.area_code from
(select sdate,city_name,area_name,
count(*) as station_num,
sum(case when cost_rate>2 then 1 else 0 end) as over2_num,
sum(case when cost_rate>2 then 1 else 0 end)/count(*)::numeric as over2_rate
from
(select distinct t1.sdate,t1.city_name,t1.area_name,t1.station_sid,t1.cost_total as cost_station,
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.sdate='202203'
and t2.sdate='202203') tmp
group by sdate,city_name,area_name) 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


---2.有合同无局站
---chengben_quzhun_2_city_202203.csv
with vt as (select a.sid,a.sname,a.cname,a.cfname,a.cbzx,a.yuezujin,
b.city_name,b.city_code,b.district_code,b.hetong_sid,b.station_sid as station_sid_i,
c.station_sid as station_sid_s,
(case 
when b.hetong_sid is not null and c.station_sid is not null then '有合同有局站'--'合同系统有，资源系统没'
when b.hetong_sid is null or (b.hetong_sid is not null and c.station_sid is null) then '有合同无局站'--'合同系统有，资源系统没有' 
else '其他'
end) as check_result
from sqmdb_cost.hetong2 a
left join sqmdb_cost.station_hetong b on a.sid=b.hetong_sid||E'\t'
left join sqmdb_cost.station3 c on b.station_sid=c.station_sid)
select '202203' as sdate,t1.*,t2.city_code*100 as city_code from
(select substr(cname,5,length(cname)-7) as city,sid,sname,yuezujin as cost_hetong,cbzx
from vt where check_result='有合同无局站') t1
left join 
(select distinct city_code,city_sname from sqmdb_cost.dict_grid) t2 
on t1.city=t2.city_sname
where t2.city_code is not null
order by t2.city_code,t1.cost_hetong desc


---3.有能耗无局站
---chengben_quzhun_3_city_202203.csv
with vt as (select nh.smonth,nh.city_name,nh.build_code,nh.build_name,nh.accrued_cost,nh.cost_unit_name,s.station_sid,
(case when s.station_sid is not null then '有能耗有局站' else '有能耗无局站' end) as check_result
from
(select smonth,city_name,area_name,build_code_short,build_code,build_name,cost_unit_name,accrued_cost from sqmdb_cost.ytmx_new) nh
left join
(select distinct station_sid,nh_sid_short from sqmdb_cost.station_nenghao) sy
on nh.build_code_short=sy.nh_sid_short
left join
(select station_sid,station_sname from sqmdb_cost.station3) s
on sy.station_sid=s.station_sid
where nh.smonth='202203')
select t1.*,t2.city_code*100 as city_code from 
(select smonth,city_name,build_code,build_name,accrued_cost,cost_unit_name from vt where check_result='有能耗无局站') t1
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) t2 
on t1.city_name=t2.city_name
order by t2.city_code,t1.cost_unit_name



---4.有铁塔订单无局站
---chengben_quzhun_4_city_202203.csv
with vt as (select t.sdate,t.city_operator,t.site_addr_id,dtc.tower_name,t.total_cost,dtc.cbzx,tf.fzd_id,
(case when tf.fzd_id is not null then '有铁塔订单有局站' else '有铁塔订单无局站' end) as check_result
from
(select sdate,city_operator,site_addr_id,sum(total_cost) as total_cost from sqmdb_cost.tower2 
group by sdate,city_operator,site_addr_id) t
left join sqmdb_cost.dict_tower_cbzx dtc 
on t.site_addr_id=dtc.tower_code 
left join sqmdb_cost.tower_fzd tf 
on t.city_operator=tf.city_name and t.site_addr_id||E'\t'=tf.tower_code
where t.sdate='202203')
select t1.*,t2.city_code*100 as city_code from 
(select sdate,city_operator,site_addr_id,tower_name,total_cost,cbzx from vt where check_result='有铁塔订单无局站') t1
left join
(select distinct city_code,city_name from sqmdb_cost.dict_grid) t2 
on t1.city_operator=t2.city_name
order by t2.city_code,t1.tower_name,t1.total_cost desc