123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246 |
- --1能耗缺少成本中心占比
- select
- smonth as "月份",
- city_name as "地市",
- has_num as "有成本中心局站数",
- no_num as "无成本中心局站数",
- total_num as "局站总数",
- err_rate as "能耗系统无成本中心局站占比"
- from
- (select
- t1.*,
- case when t1.city_name='未知地市' then 15 else coalesce(dc.sort_id,14) end as sort_id
- from
- (select smonth,
- coalesce(city_name_short(city_name),'未知地市') as city_name,
- sum(case when cost_unit_name is not null then 1 else 0 end) as has_num,
- sum(case when cost_unit_name is null then 1 else 0 end) as no_num,
- count(*) as total_num,
- round(sum(case when cost_unit_name is null then 1 else 0 end)/count(*)::numeric,4) as err_rate
- from sqmdb_cost.ytmx_new where smonth='202207'
- group by smonth,city_name)t1
- left join dict_city dc on t1.city_name=dc.city_name
- union all
- select smonth,'全省' as city_name,
- sum(case when cost_unit_name is not null then 1 else 0 end) as has_num,
- sum(case when cost_unit_name is null then 1 else 0 end) as no_num,
- count(*) as total_num,
- round(sum(case when cost_unit_name is null then 1 else 0 end)/count(*)::numeric,4) as err_rate,
- 16 as sort_id
- from sqmdb_cost.ytmx_new where smonth='202207'
- group by smonth) a
- order by sort_id
- --2既租铁塔又租三方局站占比
- select
- smonth as "月份",
- city_name as "地市",
- zu_tw_num as "租用铁塔局站数",
- zu_3rd_num as "租用三方局站数",
- err_num as "既租铁塔又租三方局站数",
- total_num as "局站总数",
- err_rate as "既租铁塔又租三方局站占比"
- from
- (select
- smonth,city_name_short(city_name) as city_name,
- coalesce(sort_id_city,15) as sort_id_city,
- sum(case when zulin_type='租用铁塔' then 1 else 0 end) as zu_tw_num,
- sum(case when zulin_type='租用第三方' then 1 else 0 end) as zu_3rd_num,
- sum(case when zulin_type='租用铁塔+租用第三方' then 1 else 0 end) as err_num,
- count(*) as total_num,
- round(sum(case when zulin_type='租用铁塔+租用第三方' then 1 else 0 end)/count(*)::numeric,4) as err_rate
- from sqmdb_cost.err_station_has_tw_3rd
- group by smonth,city_name,sort_id_city
- union all
- select smonth,'全省' as city_name,16 as sort_id_city,
- sum(case when zulin_type='租用铁塔' then 1 else 0 end) as zu_tw_num,
- sum(case when zulin_type='租用第三方' then 1 else 0 end) as zu_3rd_num,
- sum(case when zulin_type='租用铁塔+租用第三方' then 1 else 0 end) as err_num,
- count(*) as total_num,
- round(sum(case when zulin_type='租用铁塔+租用第三方' then 1 else 0 end)/count(*)::numeric,4) as err_rate
- from sqmdb_cost.err_station_has_tw_3rd
- group by smonth)a
- order by sort_id_city
- --3有合同无局站占比
- select
- smonth as "月份",
- city_name as "地市",
- has_num as "有合同有局站",
- no_num as "有合同无局站",
- total_num as "合同总数",
- err_rate as "有合同无局站占比"
- from
- (select
- smonth,city_name,coalesce(sort_id_city,15) as sort_id_city,
- sum(case when stype='有合同有局站' then 1 else 0 end) as has_num,
- sum(case when stype!='有合同有局站' then 1 else 0 end) as no_num,
- count(*) as total_num,
- round(sum(case when stype!='有合同有局站' then 1 else 0 end)/count(*)::numeric,4) as err_rate
- from sqmdb_cost.err_has_ht_no_station
- group by smonth,city_name,sort_id_city
- union all
- select smonth,'全省' as city_name,16 as sort_id_city,
- sum(case when stype='有合同有局站' then 1 else 0 end) as has_num,
- sum(case when stype!='有合同有局站' then 1 else 0 end) as no_num,
- count(*) as total_num,
- round(sum(case when stype!='有合同有局站' then 1 else 0 end)/count(*)::numeric,4) as err_rate
- from sqmdb_cost.err_has_ht_no_station
- group by smonth)a
- order by sort_id_city
- --4有能耗无局站占比
- select
- smonth as "月份",
- city_name as "地市",
- has_num as "有能耗有局站",
- no_num as "有能耗无局站",
- total_num as "能耗系统总数",
- err_rate as "有能耗无局站占比"
- from
- (select
- smonth,coalesce(city_name_short(city_name),'未知地市') as city_name,
- case when city_name is null then 15 else coalesce(sort_id_city,14) end as sort_id_city,
- sum(case when stype!='有能耗无索引' then 1 else 0 end) as has_num,
- sum(case when stype='有能耗无索引' then 1 else 0 end) as no_num,
- count(*) as total_num,
- round(sum(case when stype='有能耗无索引' then 1 else 0 end)/count(*)::numeric,4) as err_rate
- from sqmdb_cost.err_has_nh_no_station
- group by smonth,city_name,sort_id_city
- union all
- select smonth,'全省' as city_name,16 as sort_id_city,
- sum(case when stype!='有能耗无索引' then 1 else 0 end) as has_num,
- sum(case when stype='有能耗无索引' then 1 else 0 end) as no_num,
- count(*) as total_num,
- round(sum(case when stype='有能耗无索引' then 1 else 0 end)/count(*)::numeric,4) as err_rate
- from sqmdb_cost.err_has_nh_no_station
- group by smonth)a
- order by sort_id_city
- --5有45G实时能耗无设备占比
- --5能耗系统无关联网管能耗占比
- select
- smonth as "月份",
- city_name as "地市",
- has_num as "能耗系统有关联网管能耗局站",
- no_num as "能耗系统无关联网管能耗局站",
- total_num as "能耗系统总数",
- err_rate as "能耗系统无关联网管能耗局站占比"
- from
- (select
- smonth,coalesce(city_name_short(city_name),'未知地市') as city_name,
- case when city_name is null then 15 else coalesce(sort_id_city,14) end as sort_id_city,
- sum(case when stype='校验正常' then 1 else 0 end) as has_num,
- sum(case when stype!='校验正常' then 1 else 0 end) as no_num,
- count(*) as total_num,
- round(sum(case when stype!='校验正常' then 1 else 0 end)/count(*)::numeric,4) as err_rate
- from sqmdb_cost.err_has_nh_no_station
- group by smonth,city_name,sort_id_city
- union all
- select smonth,'全省' as city_name,16 as sort_id_city,
- sum(case when stype='校验正常' then 1 else 0 end) as has_num,
- sum(case when stype!='校验正常' then 1 else 0 end) as no_num,
- count(*) as total_num,
- round(sum(case when stype!='校验正常' then 1 else 0 end)/count(*)::numeric,4) as err_rate
- from sqmdb_cost.err_has_nh_no_station
- group by smonth)a
- order by sort_id_city
- --6高能耗站点占比
- select
- smonth as "月份",
- city_name as "地市",
- over_num_power as "高电量站点数",
- over_num_cost as "高电费站点数",
- total_num as "能耗系统总数",
- round((over_num_power+over_num_cost)/total_num::numeric,4) as "高能耗站点占比"
- from
- (select
- o1.*,o2.over_num_cost
- from
- (select
- smonth,coalesce(city_name_short(city_sname),'未知地市') as city_name,
- case when city_sname is null then 15 else coalesce(sort_id_city,14) end as sort_id_city,
- sum(case when over_lilun_power>=2.5 then 1 else 0 end) as over_num_power,
- count(*) as total_num
- from sqmdb_cost.rpt_baobiao_6_cgnh
- where smonth='202207' and lilun_power>1000
- group by smonth,city_sname,sort_id_city) o1
- left join
- (select
- smonth,coalesce(city_name_short(city_sname),'未知地市') as city_name,
- case when city_sname is null then 15 else coalesce(sort_id_city,14) end as sort_id_city,
- sum(case when over_lilun_cost>=3.5 then 1 else 0 end) as over_num_cost,
- count(*) as total_num
- from sqmdb_cost.rpt_baobiao_6_cgnh
- where smonth='202207' and lilun_power>1000 and over_lilun_power<2.5
- group by smonth,city_sname,sort_id_city) o2
- on o1.smonth=o2.smonth and o1.city_name=o2.city_name and o1.sort_id_city=o2.sort_id_city
- union all
- select
- o1.*,o2.over_num_cost
- from
- (select
- smonth,'全省' as city_name,16 sort_id_city,
- sum(case when over_lilun_power>=2.5 then 1 else 0 end) as over_num_power,
- count(*) as total_num
- from sqmdb_cost.rpt_baobiao_6_cgnh
- where smonth='202207' and lilun_power>1000
- group by smonth) o1
- left join
- (select
- smonth,'全省' as city_name,16 as sort_id_city,
- sum(case when over_lilun_cost>=3.5 then 1 else 0 end) as over_num_cost,
- count(*) as total_num
- from sqmdb_cost.rpt_baobiao_6_cgnh
- where smonth='202207' and lilun_power>1000 and over_lilun_power<2.5
- group by smonth) o2
- on o1.smonth=o2.smonth and o1.city_name=o2.city_name and o1.sort_id_city=o2.sort_id_city)a
- order by sort_id_city
- --7高租费站点占比(相同场景下)
- select
- smonth as "月份",
- city_sname as "地市",
- over_tower_num as "高铁塔租费局站数",
- over_hetong_num as "高合同租金局站数",
- (over_tower_num+over_hetong_num) as "高租费局站数",
- total_num as "局站总数",
- round((over_tower_num+over_hetong_num)/total_num::numeric,4) as "高租费局站占比"
- from(
- select
- smonth,city_sname,sort_id_city,
- sum(case when tower_cost_grid_scene_bs>1.5 then 1 else 0 end) as over_tower_num,--铁塔租费大于本场景铁塔租费均值
- sum(case when ht_cost_grid_scene_bs>1.5 then 1 else 0 end) as over_hetong_num,--合同租金大于本场景合同租金均值
- count(*) as total_num
- from sqmdb_cost.rpt_baobiao_4_cgzf rbc
- where smonth='202207'
- group by smonth,city_sname,sort_id_city) a
- order by sort_id_city
- --8超高租费站点占比(相同场景下)
- select
- smonth as "月份",
- city_sname as "地市",
- over_tower_num as "超高铁塔租费局站数",
- over_hetong_num as "超高合同租金局站数",
- (over_tower_num+over_hetong_num) as "超高租费局站数",
- total_num as "局站总数",
- round((over_tower_num+over_hetong_num)/total_num::numeric,4) as "超高租费局站占比"
- from(
- select
- smonth,city_sname,sort_id_city,
- sum(case when tower_cost_grid_scene_bs>2 then 1 else 0 end) as over_tower_num,--铁塔租费大于本场景铁塔租费均值
- sum(case when ht_cost_grid_scene_bs>2 then 1 else 0 end) as over_hetong_num,--合同租金大于本场景合同租金均值
- count(*) as total_num
- from sqmdb_cost.rpt_baobiao_4_cgzf rbc
- where smonth='202207'
- group by smonth,city_sname,sort_id_city) a
- order by sort_id_city
|