chengben/Scripts/成本占比地市维度.sql

--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