chengben/Scripts/合同能耗校验.sql

--合同校验
--有一个合同对应多个局站，例 CU12-1308-2012-000802-2，校验过程中只保留一条避免结果重复
with check_detail as (
select (case 
when strpos(cname,'石家庄')>0 then '石家庄市'
when strpos(cname,'唐山')>0 then '唐山市'
when strpos(cname,'秦皇岛')>0 then '秦皇岛市'
when strpos(cname,'邯郸')>0 then '邯郸市'
when strpos(cname,'邢台')>0 then '邢台市'
when strpos(cname,'保定')>0 then '保定市'
when strpos(cname,'张家口')>0 then '张家口市'
when strpos(cname,'承德')>0 then '承德市'
when strpos(cname,'沧州')>0 then '沧州市'
when strpos(cname,'廊坊')>0 then '廊坊市'
when strpos(cname,'衡水')>0 then '衡水市'
when strpos(cname,'雄安')>0 then '雄安新区'
when strpos(cname,'本部')>0 then '河北省本部'
else cname
end
) as city_name,*,
(case when sh.hetong_sid is null then '有合同无局站' else '有合同有局站' end) as check_result
from 
(select sid,sname,cname,cbzx from sqmdb_cost.hetong2) h
left join 
(select hetong_sid,station_sid from 
(select hetong_sid,station_sid,
row_number() over(partition by hetong_sid order by station_sid desc) as rn 
from sqmdb_cost.station_hetong) ht_rank
where rn=1) sh 
on h.sid=sh.hetong_sid||E'\t'
--left join 
--(select station_sid from sqmdb_cost.station3) s 
--on sh.station_sid=s.station_sid
)
select city_name as 地市,
count(*) as 合同总数,
sum(case when check_result='有合同有局站' then 1 else 0 end) as 合同关联数,
sum(case when check_result='有合同无局站' then 1 else 0 end) as 合同未关联数,
sum(case when check_result='有合同有局站' then 1 else 0 end)/count(*)::numeric as 合同关联率
from check_detail 
group by city_name
order by sum(case when check_result='有合同有局站' then 1 else 0 end)/count(*)::numeric desc





--能耗校验
--有一个能耗ID对应多个局站，例 2039913，校验过程中只保留一条避免结果重复
with check_detail as (
select *,
(case when sn.nh_sid_short is null then '有能耗无局站' else '有能耗有局站' end) as check_result
from 
(select city_name,area_name,build_code_short,cost_unit_name 
from sqmdb_cost.ytmx_new where smonth='202203' and build_code_short is not null) yn 
left join 
(select nh_sid_short,station_sid from 
(select nh_sid_short,station_sid,
row_number() over(partition by nh_sid_short order by station_sid desc) as rn 
from sqmdb_cost.station_nenghao) nh_rank
where rn=1) sn
on yn.build_code_short=sn.nh_sid_short
--left join 
--(select station_sid from sqmdb_cost.station3) s 
--on sn.station_sid=s.station_sid
)
select city_name as 地市,
count(*) as 能耗总数,
sum(case when check_result='有能耗有局站' then 1 else 0 end) as 能耗关联数,
sum(case when check_result='有能耗无局站' then 1 else 0 end) as 能耗未关联数,
sum(case when check_result='有能耗有局站' then 1 else 0 end)/count(*)::numeric as 能耗关联率
from check_detail 
group by city_name
order by sum(case when check_result='有能耗有局站' then 1 else 0 end)/count(*)::numeric desc