sql 去重脚本与回滚

select memberid,count(1)

from (
select
tmp1.member_id,
tmp2.workgroup,tmp2.roulette_player_tag_365lpd,
tmp2.roulette_player_tag_365yd,tmp2.consent_status_all,tmp2.contact_status_address,
tmp2.exclusion_order_flag,tmp2.contact_status_sms,tmp2.contact_status_email,
tmp2.restricted_area,tmp2.genting_points_balance,
tmp2.member_type,tmp2.preference_language_l30d,
tmp2.prevbar_from_casion_flag,tmp2.suspend_reason_id,
tmp2.segment_code,tmp2.record_actual_winloss_flag,
tmp2.genting_dollars_balance,tmp2.age,tmp2.preferred_language,
tmp2.reviewdate,tmp2.barred,tmp2.deactivated,tmp2.race_code,tmp2.memberid,
tmp2.maritalstatusid,tmp2.email,tmp2.genting_points_qualifying,
tmp2.site_id,tmp2.suspension,tmp2.discreet,tmp2.segment_code_member_class,
tmp2.active_status,tmp2.race_id,tmp2.children_no,tmp2.ds,tmp2.chinese_name,
tmp2.segment_code_region,tmp2.gaming_frequency_code,tmp2.sur_name,
tmp2.identification_no,tmp2.gender_code,tmp2.maritalstatus_code,
tmp2.segment_code_residential_status,tmp2.gender_id,
tmp2.issuance_country_id,tmp2.code_date,tmp2.sensitive,
tmp2.dormant,tmp2.date_of_birth,tmp2.enrolment_date,
tmp2.annual_levy_status,tmp2.genting_points_next_tier,
tmp2.segment_code_adt,tmp2.residential_status_id,
tmp2.occupation_category_id,tmp2.member_no,
tmp2.premium_player,tmp2.days_from_enrolment,
tmp2.age_band,tmp2.player_status,tmp2.region_state,
tmp2.nationality_group,tmp2.nationality_country,
tmp2.days_to_premium_player_expiry,tmp2.class_tier,
tmp2.class_group,tmp2.expiring_gd_for_current_mth,
tmp2.rating_code,tmp2.exempted_from_downgrade,
tmp2.given_name,tmp2.identification_type_id,
tmp2.slot_player_tag_365lpd,tmp2.nationality_id,
tmp2.expiring_gd_for_next_mth,tmp2.expiring_gd_for_following_mth,
tmp2.expiring_gd_for_next_following_mth,tmp2.region_group,
tmp2.region_country,tmp2.last_play_date,tmp2.ima_player,
tmp2.review_date,tmp2.rm_code,tmp2.rolling_player_tag_365lpd,
tmp2.rolling_player_tag_365yd,tmp2.nrl_tg_player_tag_365lpd,
tmp2.nrl_tg_player_tag_365yd,tmp2.etg_player_tag_365lpd,
tmp2.etg_player_tag_365td,tmp2.bacc_player_tag_365lpd,
tmp2.bacc_player_tag_365yd
from(
select member_id,count(1) from LD_RWS_PRD.dim_gr_mbr where ds='20180701' group by member_id having count(1)>1
)tmp1 left outer join(
select * from LD_RWS_PRD.dim_gr_mbr t where ds='20180701'
)tmp2 on tmp1.member_id=tmp2.member_id;


)tmp2 group by

tmp2.member_id,
tmp2.workgroup,tmp2.roulette_player_tag_365lpd,
tmp2.roulette_player_tag_365yd,tmp2.consent_status_all,tmp2.contact_status_address,
tmp2.exclusion_order_flag,tmp2.contact_status_sms,tmp2.contact_status_email,
tmp2.restricted_area,tmp2.genting_points_balance,
tmp2.member_type,tmp2.preference_language_l30d,
tmp2.prevbar_from_casion_flag,tmp2.suspend_reason_id,
tmp2.segment_code,tmp2.record_actual_winloss_flag,
tmp2.genting_dollars_balance,tmp2.age,tmp2.preferred_language,
tmp2.reviewdate,tmp2.barred,tmp2.deactivated,tmp2.race_code,tmp2.memberid,
tmp2.maritalstatusid,tmp2.email,tmp2.genting_points_qualifying,
tmp2.site_id,tmp2.suspension,tmp2.discreet,tmp2.segment_code_member_class,
tmp2.active_status,tmp2.race_id,tmp2.children_no,tmp2.ds,tmp2.chinese_name,
tmp2.segment_code_region,tmp2.gaming_frequency_code,tmp2.sur_name,
tmp2.identification_no,tmp2.gender_code,tmp2.maritalstatus_code,
tmp2.segment_code_residential_status,tmp2.gender_id,
tmp2.issuance_country_id,tmp2.code_date,tmp2.sensitive,
tmp2.dormant,tmp2.date_of_birth,tmp2.enrolment_date,
tmp2.annual_levy_status,tmp2.genting_points_next_tier,
tmp2.segment_code_adt,tmp2.residential_status_id,
tmp2.occupation_category_id,tmp2.member_no,
tmp2.premium_player,tmp2.days_from_enrolment,
tmp2.age_band,tmp2.player_status,tmp2.region_state,
tmp2.nationality_group,tmp2.nationality_country,
tmp2.days_to_premium_player_expiry,tmp2.class_tier,
tmp2.class_group,tmp2.expiring_gd_for_current_mth,
tmp2.rating_code,tmp2.exempted_from_downgrade,
tmp2.given_name,tmp2.identification_type_id,
tmp2.slot_player_tag_365lpd,tmp2.nationality_id,
tmp2.expiring_gd_for_next_mth,tmp2.expiring_gd_for_following_mth,
tmp2.expiring_gd_for_next_following_mth,tmp2.region_group,
tmp2.region_country,tmp2.last_play_date,tmp2.ima_player,
tmp2.review_date,tmp2.rm_code,tmp2.rolling_player_tag_365lpd,
tmp2.rolling_player_tag_365yd,tmp2.nrl_tg_player_tag_365lpd,
tmp2.nrl_tg_player_tag_365yd,tmp2.etg_player_tag_365lpd,
tmp2.etg_player_tag_365td,tmp2.bacc_player_tag_365lpd,
tmp2.bacc_player_tag_365yd
having count(1)=1

 

 

 

 

 

 

 

------------------回滚

 

insert overwrite table dtp_cacprrptdbo_XS_GATE_CHECK partition(ds)
  select
   xs_id           
 ,xs_checked_datetime 
 ,xs_gate_check_result 
 ,xs_remark       
 ,xs_temp_levy_id 
 ,xs_gate_id      
 ,xs_movement_id  
 ,xs_upload_datetime 
 ,xs_is_permit_entry 
 ,xs_barcode      
 ,xs_levy_less    
 ,xs_active_levy_for_less 
 ,xs_movement_check_result 
 ,xs_user_name    
 ,xs_date_time_last_modify 
 ,xs_is_handheld  
 ,xs_movement_indicator 
 ,xs_gate_type    
 ,xs_gate_group_id 
 ,xs_document_remark 
 ,xs_overwrite_reason_code 
 ,xs_overwrite_remark 
 ,xs_updated_by   
 ,xs_date_time_create 
 ,xs_exclusion_source 
 ,to_char(xs_checked_datetime,'yyyymmdd')
from dtp_cacprrptdbo_XS_GATE_CHECK
where ds=100031
and xs_checked_datetime is not null
and to_char(xs_checked_datetime,'yyyymmdd')<${bizdate}
and to_char(xs_checked_datetime,'yyyymmdd')>=to_char(dateadd(to_date(${bizdate},'yyyymmdd'),-4,'yyyy'),'yyyymmdd');

insert overwrite table dtp_cacprrptdbo_XS_GATE_CHECK_FOR partition(ds)
  select
    xs_id           
 ,xs_checked_datetime 
 ,xs_gate_check_result 
 ,xs_remark       
 ,xs_gate_id      
 ,xs_gate_mac     
 ,xs_random_check_id 
 ,xs_upload_datetime 
 ,xs_document_number 
 ,xs_primary_name 
 ,xs_secondary_name 
 ,xs_dob          
 ,xs_sex          
 ,xs_nationality  
 ,xs_created_by   
 ,xs_date_time_create 
 ,xs_updated_by   
 ,xs_date_time_last_modify 
 ,xs_movement_id  
 ,xs_is_handheld  
 ,xs_movement_indicator 
 ,xs_gate_type    
 ,xs_gate_group_id 
 ,xs_document_remark 
 ,xs_overwrite_reason_code 
 ,xs_overwrite_remark 
 ,xs_exclusion_source 
 ,xs_is_dob_auto_complete 
 ,xs_doc_expired_datetime 
 ,xs_pass_status  
 ,xs_valid_age    
 ,to_char(xs_checked_datetime,'yyyymmdd')
from dtp_cacprrptdbo_XS_GATE_CHECK_FOR
where ds=100031
and xs_checked_datetime is not null
and to_char(xs_checked_datetime,'yyyymmdd')<${bizdate}
and to_char(xs_checked_datetime,'yyyymmdd')>=to_char(dateadd(to_date(${bizdate},'yyyymmdd'),-4,'yyyy'),'yyyymmdd');

insert overwrite table dtp_cacprrptdbo_XS_MOVEMENT_PAIR partition(ds)
  select
    xs_id           
 ,xs_nric         
 ,xs_entry_date_time 
 ,xs_exit_date_time 
 ,xs_levy_less    
 ,xs_user_name    
 ,xs_date_time_last_modify 
 ,xs_is_uploaded_by_handheld 
 ,xs_updated_by   
 ,xs_date_time_create 
 ,to_char(xs_exit_date_time,'yyyymmdd')
from dtp_cacprrptdbo_XS_MOVEMENT_PAIR
where ds=100031
and xs_exit_date_time is not null
and to_char(xs_exit_date_time,'yyyymmdd')<${bizdate}
and to_char(xs_exit_date_time,'yyyymmdd')>=to_char(dateadd(to_date(${bizdate},'yyyymmdd'),-4,'yyyy'),'yyyymmdd');

insert overwrite table dtp_cacprrptdbo_XS_MOVEMENT_PAIR_FOR partition(ds)
  select
     xs_id           
 ,xs_document_number 
 ,xs_entry_date_time 
 ,xs_exit_date_time 
 ,xs_is_uploaded_by_handheld 
 ,xs_user_name    
 ,xs_updated_by   
 ,xs_date_time_create 
 ,xs_date_time_last_modify 
 ,to_char(xs_exit_date_time,'yyyymmdd')
from dtp_cacprrptdbo_XS_MOVEMENT_PAIR_FOR
where ds=100031
and xs_exit_date_time is not null
and to_char(xs_exit_date_time,'yyyymmdd')<${bizdate}
and to_char(xs_exit_date_time,'yyyymmdd')>=to_char(dateadd(to_date(${bizdate},'yyyymmdd'),-4,'yyyy'),'yyyymmdd');

insert overwrite table dtp_cacprrptdbo_XS_MOVEMENT_PAIR_FOR_HIST partition(ds)
  select
      xs_id           
 ,xs_document_number 
 ,xs_entry_date_time 
 ,xs_exit_date_time 
 ,xs_is_uploaded_by_handheld 
 ,xs_user_name    
 ,xs_updated_by   
 ,xs_date_time_create 
 ,xs_date_time_last_modify  
 ,to_char(xs_exit_date_time,'yyyymmdd')
from dtp_cacprrptdbo_XS_MOVEMENT_PAIR_FOR_HIST
where ds=100031
and xs_exit_date_time is not null
and to_char(xs_exit_date_time,'yyyymmdd')<${bizdate}
and to_char(xs_exit_date_time,'yyyymmdd')>=to_char(dateadd(to_date(${bizdate},'yyyymmdd'),-4,'yyyy'),'yyyymmdd');

insert overwrite table dtp_cacprrptdbo_XS_MOVEMENT_PAIR_HIST partition(ds)
  select
      xs_id           
 ,xs_nric         
 ,xs_entry_date_time 
 ,xs_exit_date_time 
 ,xs_levy_less    
 ,xs_user_name    
 ,xs_date_time_last_modify 
 ,xs_is_uploaded_by_handheld 
 ,xs_updated_by   
 ,xs_date_time_create   
 ,to_char(xs_exit_date_time,'yyyymmdd')
from dtp_cacprrptdbo_XS_MOVEMENT_PAIR_HIST
where ds=100031
and xs_exit_date_time is not null
and to_char(xs_exit_date_time,'yyyymmdd')<${bizdate}
and to_char(xs_exit_date_time,'yyyymmdd')>=to_char(dateadd(to_date(${bizdate},'yyyymmdd'),-4,'yyyy'),'yyyymmdd');

insert overwrite table dtp_cacprrptdbo_XS_MOVEMENT partition(ds)
  select
      xs_id           
 ,xs_direction    
 ,xs_is_buzzer_on 
 ,xs_moved_datetime 
 ,xs_guest_count_by_gate_id 
 ,xs_gate_id      
 ,xs_upload_datetime 
 ,xs_type         
 ,xs_gate_check_id 
 ,xs_movement_pair_id 
 ,xs_is_in        
 ,xs_user_name    
 ,xs_date_time_last_modify 
 ,xs_updated_by   
 ,xs_date_time_create  
 ,to_char(xs_moved_datetime,'yyyymmdd')
from dtp_cacprrptdbo_XS_MOVEMENT
where ds=100031
and xs_moved_datetime is not null
and to_char(xs_moved_datetime,'yyyymmdd')<${bizdate}
and to_char(xs_moved_datetime,'yyyymmdd')>=to_char(dateadd(to_date(${bizdate},'yyyymmdd'),-4,'yyyy'),'yyyymmdd');

insert overwrite table dtp_cacprrptdbo_XS_MOVEMENT_FOR partition(ds)
  select
       xs_id           
 ,xs_direction    
 ,xs_is_buzzer_on 
 ,xs_moved_datetime 
 ,xs_guest_count_by_gate_id 
 ,xs_gate_id      
 ,xs_upload_datetime 
 ,xs_type         
 ,xs_gate_check_id 
 ,xs_movement_pair_id 
 ,xs_is_in        
 ,xs_user_name    
 ,xs_date_time_last_modify 
 ,xs_updated_by   
 ,xs_date_time_create   
 ,to_char(xs_moved_datetime,'yyyymmdd')
from dtp_cacprrptdbo_XS_MOVEMENT_FOR
where ds=100031
and xs_moved_datetime is not null
and to_char(xs_moved_datetime,'yyyymmdd')<${bizdate}
and to_char(xs_moved_datetime,'yyyymmdd')>=to_char(dateadd(to_date(${bizdate},'yyyymmdd'),-4,'yyyy'),'yyyymmdd');