chengben/Scripts/成本占比.sql

--超高租费占比
select a.* from
(select 
city_sname as "地市",
sum(case when (coalesce(tower_cost,0)+coalesce(cost_hetong,0))/tower_cost_grid >1.3 then 1 else 0 end) as "超高租费局站",
count(*) as "局站总数",
round(sum(case when (coalesce(tower_cost,0)+coalesce(cost_hetong,0))/tower_cost_grid >1.3 then 1 else 0 end)/count(*)::numeric,4) as "超高租费占比"
from sqmdb_cost.rpt_baobiao_4_cgzf rbc 
where tower_cost_grid is not null 
and smonth='202206'
group by city_sname) a
left join sqmdb_cost.dict_city dc on a."地市"=dc.city_name 
order by dc.sort_id 


--方案1：铁塔＞2倍+三方＞1.5倍
select a.* from
(select 
city_sname as "地市",
over_tower_num as "铁塔租费大于本地市铁塔单站租赁费2倍",
over_hetong_num as "合同租金大于本地市铁塔单站租赁费1.5倍",
(over_tower_num+over_hetong_num) as "超高租费局站数量",
total_num as "局站总数",
round((over_tower_num+over_hetong_num)/total_num::numeric,4) as "超高局站占比"
from(
select 
city_sname,
sum(case when coalesce(tower_cost,0)/tower_cost_city >2 then 1 else 0 end) as over_tower_num,--铁塔租费大于本地市铁塔单站租赁费2倍
sum(case when coalesce(cost_hetong,0)/tower_cost_city >1.5 then 1 else 0 end) as over_hetong_num,--合同租金大于本地市铁塔单站租赁费1.5倍
count(*) as total_num
from sqmdb_cost.rpt_baobiao_4_cgzf rbc 
where tower_cost_grid is not null 
and smonth='202206'
group by city_sname) t1 ) a
left join sqmdb_cost.dict_city dc on a."地市"=dc.city_name 
order by dc.sort_id 


--方案2：铁塔＞2.5倍+三方＞1.5倍
select a.* from
(select 
city_sname as "地市",
over_tower_num as "铁塔租费大于本地市铁塔单站租赁费1.5倍",
over_hetong_num as "合同租金大于本地市铁塔单站租赁费1.5倍",
(over_tower_num+over_hetong_num) as "超高租费局站数量",
total_num as "局站总数",
round((over_tower_num+over_hetong_num)/total_num::numeric,4) as "超高局站占比"
from(
select 
city_sname,
sum(case when coalesce(tower_cost,0)/tower_cost_city >1.5 then 1 else 0 end) as over_tower_num,--铁塔租费大于本地市铁塔单站租赁费2倍
sum(case when coalesce(cost_hetong,0)/tower_cost_city >1.5 then 1 else 0 end) as over_hetong_num,--合同租金大于本地市铁塔单站租赁费1.5倍
count(*) as total_num
from sqmdb_cost.rpt_baobiao_4_cgzf rbc 
where tower_cost_grid is not null 
and smonth='202206'
group by city_sname) t1 ) a
left join sqmdb_cost.dict_city dc on a."地市"=dc.city_name 
order by dc.sort_id 

--铁塔按一塔一表，合同按报表
select 
t1.city_name,
t1.tw_over_num,
t2.ht_over_num,
t1.tw_total_num,
t2.ht_total_num,
(t1.tw_over_num+t2.ht_over_num) as total_over_num,
(t1.tw_total_num+t2.ht_total_num) as total_num,
(t1.tw_over_num+t2.ht_over_num)/(t1.tw_total_num+t2.ht_total_num)::numeric as over_rate
from
--一塔一表
(select 
city_name,
sum(case when cost_rate >2 then 1 else 0 end) as tw_over_num,
count(*) as tw_total_num
from (
select 
tw.*,
tw_city.tower_cost_avg,
tw.total_cost/tw_city.tower_cost_avg as cost_rate
from
(select city_name_short(city_operator) as city_name,site_addr_id,sum(total_cost) as total_cost
from sqmdb_cost.tower3 t 
where sdate='202206' group by city_operator,site_addr_id) tw
left join (select city_name,tower_cost_avg from sqmdb_cost.rpt_tower_cost_city where smonth='202206') tw_city
on tw.city_name=tw_city.city_name)a
group by city_name) t1
left join
--合同金额按局站汇总
(select 
city_sname,
sum(case when cost_rate_ht >1.5 then 1 else 0 end) as ht_over_num,
count(*) as ht_total_num
from (
select 
ht.*,
tw_city.tower_cost_avg,
ht.cost_hetong/tw_city.tower_cost_avg as cost_rate_ht
from
(select city_sname,station_sid,sum(cost_hetong) as cost_hetong 
from sqmdb_cost.rpt_baobiao_4_cgzf rbc 
where smonth='202206' and hetong_sid is not null
group by city_sname,station_sid ) ht
left join (select city_name,tower_cost_avg from sqmdb_cost.rpt_tower_cost_city where smonth='202206') tw_city
on ht.city_sname=tw_city.city_name)a
group by city_sname)t2
on t1.city_name=t2.city_sname
left join sqmdb_cost.dict_city dc on t1.city_name=dc.city_name 
order by dc.sort_id 



--铁塔租金按局站汇总
select 
city_sname,
sum(case when cost_rate_tw >2.5 then 1 else 0 end) as tw_over_num,
count(*) as tw_total_num
from (
select 
tw.*,
tw_city.tower_cost_avg,
tw.tower_cost/tw_city.tower_cost_avg as cost_rate_tw
from
(select city_sname,station_sid,sum(tower_cost) as tower_cost 
from sqmdb_cost.rpt_baobiao_4_cgzf rbc 
where smonth='202206' and site_addr_id is not null
group by city_sname,station_sid ) tw
left join (select city_name,tower_cost_avg from sqmdb_cost.rpt_tower_cost_city where smonth='202206') tw_city
on tw.city_sname=tw_city.city_name)a
group by city_sname


--超高电费占比
select a.* from
(select 
city_sname as "地市",
sum(case when over_lilun_power >3.5 then 1 else 0 end) as "超高电费局站",
count(*) as "局站总数",
round(sum(case when over_lilun_power >3.5 then 1 else 0 end)/count(*)::numeric,4) as "超高电费占比"
from sqmdb_cost.rpt_baobiao_6_cgnh rbc
where city_sname is not null and station_type is not null and lilun_power>=100
and station_type not in ('光跳接入局站','核心局站','汇聚局站','综合业务接入点')
and smonth='202206'
group by city_sname) a
left join sqmdb_cost.dict_city dc on a."地市"=dc.city_name 
order by dc.sort_id