1.清空RECHARGER_REPORT_YYS2 表内容
truncate table recharger_report_tmp
2.按桩编号关联是否公用、是否高速、站编号和规范的站名
update recharger_report_tmpt set (t.special_or_public,t.high_speed,t.station_num,t.station_nameyys)=(select p.special_or_public,p.high_speed ,p.station_num , p.statiom_nameyys from piles p where t.pile_num=p.pile_num)
where t.high_speed is null
3.按桩编号关联高速名称、高速编号和高速站
update recharger_report_tmpt set (t.speed_name,t.speed_number,t.speed_station_name)=(select p.speed_name,p.speed_number,p.station_name from high_speed p where t.pile_num=p.pile_num)
where t.speed_number is null
4.将地市中设施管理部改为国网湖州供电公司
update recharger_report_tmp t set t.trade_company='国网湖州供电公司' where t.trade_company like '%设施管理部%'
5.充电卡和手机号合并
update recharger_report_tmp t set t.card_id=t.charging_card||t.cell_phone_number where t.card_id is null;
6.按充电桩关联生产厂家
update recharger_report_tmp t set t.manufactruer=(select p.manufactruer from PILES p where p.pile_num=t.pile_num)
7.将RECHARGER_REPORT_YYS2 数据导入RECHARGER_REPORT
insert into RECHARGER_REPORT(card_id, service_type ,trade_type, ending_time,trade_company, trade_num ,pile_num,ending_reason ,if_check ,if_pay , dt ,high_speed ,
special_or_public ,station_name,user_name,user_id, build_city ,manufactruer ,trade_electricity , trade_money,charging_card ,cell_phone_number ,
pay_num ,electricity_fees ,service_charge ,top_electricity ,peak_electricity ,flat_electricity ,valley_electricity ,record_electricity ,start_value ,
ending_value ,freezing_amount ,balance_before_deducting ,balance_after_deducting , order_state ,check_num , check_state ,check_person ,if_or_not_invoice ,
merchant_num, storage_time ,order_creation_time,check_time ,starting_time ,urban_district ,speed_name , speed_number , speed_station_name ,trade_timegap , station_num ,
station_nameyys ) select card_id , service_type ,trade_type ,ending_time , trade_company ,trade_num ,pile_num ,ending_reason , if_check ,if_pay ,dt , high_speed ,
special_or_public, station_name , user_name ,user_id ,build_city ,manufactruer , trade_electricity ,trade_money ,charging_card, cell_phone_number ,
pay_num ,electricity_fees ,service_charge ,top_electricity,peak_electricity ,flat_electricity ,valley_electricity ,record_electricity,start_value ,
ending_value ,freezing_amount , balance_before_deducting ,balance_after_deducting , order_state , check_num , check_state , check_person ,if_or_not_invoice ,
merchant_num ,storage_time , order_creation_time ,check_time ,starting_time ,urban_district , speed_name , speed_number ,speed_station_name , trade_timegap , station_num ,
station_nameyys from recharger_report_tmp
9.检查是否有新的充电桩不存在与国网充电桩明细表
select * from RECHARGER_REPORT t where t.special_or_public is null;
select count(*) from RECHARGER_REPORT t where t.special_or_public is null;
select distinct t.pile_num from RECHARGER_REPORT t where t.special_or_public is null;
select count(*) from (select distinct t.pile_num from RECHARGER_REPORT t where t.special_or_public is null)
10.检查充电数据是否异常
select * from RECHARGER_REPORT t order by t.trade_electricity desc
truncate table recharger_report_tmp
2.按桩编号关联是否公用、是否高速、站编号和规范的站名
update recharger_report_tmpt set (t.special_or_public,t.high_speed,t.station_num,t.station_nameyys)=(select p.special_or_public,p.high_speed ,p.station_num , p.statiom_nameyys from piles p where t.pile_num=p.pile_num)
where t.high_speed is null
3.按桩编号关联高速名称、高速编号和高速站
update recharger_report_tmpt set (t.speed_name,t.speed_number,t.speed_station_name)=(select p.speed_name,p.speed_number,p.station_name from high_speed p where t.pile_num=p.pile_num)
where t.speed_number is null
4.将地市中设施管理部改为国网湖州供电公司
update recharger_report_tmp t set t.trade_company='国网湖州供电公司' where t.trade_company like '%设施管理部%'
5.充电卡和手机号合并
update recharger_report_tmp t set t.card_id=t.charging_card||t.cell_phone_number where t.card_id is null;
6.按充电桩关联生产厂家
update recharger_report_tmp t set t.manufactruer=(select p.manufactruer from PILES p where p.pile_num=t.pile_num)
7.将RECHARGER_REPORT_YYS2 数据导入RECHARGER_REPORT
insert into RECHARGER_REPORT(card_id, service_type ,trade_type, ending_time,trade_company, trade_num ,pile_num,ending_reason ,if_check ,if_pay , dt ,high_speed ,
special_or_public ,station_name,user_name,user_id, build_city ,manufactruer ,trade_electricity , trade_money,charging_card ,cell_phone_number ,
pay_num ,electricity_fees ,service_charge ,top_electricity ,peak_electricity ,flat_electricity ,valley_electricity ,record_electricity ,start_value ,
ending_value ,freezing_amount ,balance_before_deducting ,balance_after_deducting , order_state ,check_num , check_state ,check_person ,if_or_not_invoice ,
merchant_num, storage_time ,order_creation_time,check_time ,starting_time ,urban_district ,speed_name , speed_number , speed_station_name ,trade_timegap , station_num ,
station_nameyys ) select card_id , service_type ,trade_type ,ending_time , trade_company ,trade_num ,pile_num ,ending_reason , if_check ,if_pay ,dt , high_speed ,
special_or_public, station_name , user_name ,user_id ,build_city ,manufactruer , trade_electricity ,trade_money ,charging_card, cell_phone_number ,
pay_num ,electricity_fees ,service_charge ,top_electricity,peak_electricity ,flat_electricity ,valley_electricity ,record_electricity,start_value ,
ending_value ,freezing_amount , balance_before_deducting ,balance_after_deducting , order_state , check_num , check_state , check_person ,if_or_not_invoice ,
merchant_num ,storage_time , order_creation_time ,check_time ,starting_time ,urban_district , speed_name , speed_number ,speed_station_name , trade_timegap , station_num ,
station_nameyys from recharger_report_tmp
8.更新充电时间段
update RECHARGER_REPORT t set t.trade_timegap=trunc(to_number(to_char(t.ending_time, 'hh24'))) where t.trade_timegap is null9.检查是否有新的充电桩不存在与国网充电桩明细表
select * from RECHARGER_REPORT t where t.special_or_public is null;
select count(*) from RECHARGER_REPORT t where t.special_or_public is null;
select distinct t.pile_num from RECHARGER_REPORT t where t.special_or_public is null;
select count(*) from (select distinct t.pile_num from RECHARGER_REPORT t where t.special_or_public is null)
10.检查充电数据是否异常
select * from RECHARGER_REPORT t order by t.trade_electricity desc