123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147 |
- --超高租费占比
- 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
|