chengben/Scripts/数据校验/check_01_nenghao_test.sql

--能耗
select 
distinct
yn.smonth as 月份,
yn.build_code_short as 能耗系统_局站短ID,
yn.city_name as 能耗系统_地市,
yn.area_name as 能耗系统_区县,
yn.build_name as 能耗系统_局站名称,
yn.accrued_power as 能耗系统_电量,
yn.accrued_cost as 能耗系统_成本,
sn.build_id as 索引表_局站短ID,
sn.station_sid as 索引表_局站ID,
wg.station_sid as 网管能耗_局站ID,
wg.nh_total as 网管能耗,
(case 
when sn.build_id is not null and yn.accrued_cost!=0 and yn.accrued_cost is not null and wg.station_sid is not null then '校验正常'
when sn.build_id is null then '有能耗无索引'
when yn.accrued_cost is null or yn.accrued_cost=0 then '有能耗无成本'
when yn.accrued_cost!=0 and wg.station_sid is null then '有能耗有成本，校验有问题'
end) as 问题分类
from 
(select * from sqmdb_cost.ytmx_new where build_code_short is not null and smonth='202204') yn 
left join sqmdb_cost.station_nenghao_new sn on yn.build_code_short=sn.build_id
left join
(select * from sqmdb_cost.v_station_omc_nh_month where smonth='202204') wg
on sn.station_sid=wg.station_sid


--地市能耗索引表与新对应关系对比
select *,
case when sn.station_sid=snn.station_sid then '对应关系相同' else '对应关系不同' end as check_result
from sqmdb_cost.station_nenghao sn 
full outer join sqmdb_cost.station_nenghao_new snn on sn.nh_sid_short=snn.build_id