查询销户用户



要求：查询销户用户
drop table ur_user_info_xiaohu_his;

--1 查询销户的用户 ：
           --根据op_code 为 1050 或 4190 和 update_type 为 I 的 用户 去用户信息历史表ur_user_info_his 中 查找销户用户，并建立销户用户历史信息表ur_user_info_xiaohu_his
create table ur_user_info_xiaohu_his  tablespace tbs_data_interface as
select /*+parallel(d,10)*/ cust_id userhis_cust_id , update_time userhis_update_time ,op_code userhis_op_code  from ur_user_info_his d 
                           where op_code in ('1050','4190') and update_type='I';

-- 2 计算 销户用户信息历史表ur_user_info_xiaohu_his中的用户量  --36136
select /*+parallel(d,10)*/ count(1) from ur_user_info_xiaohu_his d

-- 3 查询销户用户的客户信息：
         --根据销户用户历史信息表ur_user_info_xiaohu_his的客户ID和客户信息表的客户ID 相等 和 销户信息历史表中的更新时间的小于客户信息表中的操作时间，并建立销户客户信息表ct_cust_info_xiaohu_cust
drop table ct_cust_info_xiaohu_cust;
create table ct_cust_info_xiaohu_cust  tablespace tbs_data_interface as
select /*+parallel(a,10) parallel(b,10)*/  b.cust_id cust_cust_id ,b.op_time cust_op_time ,b.login_no cust_login_no ,b.id_iccid cust_id_iccid ,b.id_address cust_id_address  from ur_user_info_xiaohu_his a ,ct_cust_info b  where a.userhis_cust_id=b.cust_id and a.userhis_update_time<b.op_time;

-- 4 计算 销户用户的客户量--18644
select  /*+parallel(a,10) */ count(1) from ct_cust_info_xiaohu_cust a

-- 5 关联出各月份客户身份信息表    ct_cust_scanrecord_info_201711

drop table ct_cust_scanrecord_info_201711;
create table ct_cust_scanrecord_info_201711  tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10) */* from ct_scanrecord_info_201711  a ,   ct_cust_info_xiaohu_cust  b
        where  a.login_no=b.cust_login_no
        and b.cust_cust_id = a.cust_id
         and  a.op_time  between b.cust_op_time-(1/60/24)*5 and b.cust_op_time+(1/60/24)*5 
           

--------10
drop table ct_cust_scanrecord_info_201710;
create table ct_cust_scanrecord_info_201710  tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10) */* from ct_scanrecord_info_201710  a ,   ct_cust_info_xiaohu_cust  b
        where  a.login_no=b.cust_login_no
        and b.cust_cust_id = a.cust_id
         and  a.op_time  between b.cust_op_time-(1/60/24)*5 and b.cust_op_time+(1/60/24)*5 
-------9
create table ct_cust_scanrecord_info_201709  tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10) */* from ct_scanrecord_info_201709  a ,   ct_cust_info_xiaohu_cust  b
        where  a.login_no=b.cust_login_no
        and b.cust_cust_id = a.cust_id
         and  a.op_time  between b.cust_op_time-(1/60/24)*5 and b.cust_op_time+(1/60/24)*5 
----08
create table ct_cust_scanrecord_info_201708  tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10) */* from ct_scanrecord_info_201708  a ,   ct_cust_info_xiaohu_cust  b
        where  a.login_no=b.cust_login_no
        and b.cust_cust_id = a.cust_id
         and  a.op_time  between b.cust_op_time-(1/60/24)*5 and b.cust_op_time+(1/60/24)*5 
 ----07
create table ct_cust_scanrecord_info_201707  tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10) */* from ct_scanrecord_info_201707  a ,   ct_cust_info_xiaohu_cust  b
        where  a.login_no=b.cust_login_no
        and b.cust_cust_id = a.cust_id
         and  a.op_time  between b.cust_op_time-(1/60/24)*5 and b.cust_op_time+(1/60/24)*5 
 ----06
create table ct_cust_scanrecord_info_201706  tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10) */* from ct_scanrecord_info_201706  a ,   ct_cust_info_xiaohu_cust  b
        where  a.login_no=b.cust_login_no
        and b.cust_cust_id = a.cust_id
         and  a.op_time  between b.cust_op_time-(1/60/24)*5 and b.cust_op_time+(1/60/24)*5  
         
 ----05
create table ct_cust_scanrecord_info_201705  tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10) */* from ct_scanrecord_info_201705  a ,   ct_cust_info_xiaohu_cust  b
        where  a.login_no=b.cust_login_no
        and b.cust_cust_id = a.cust_id
         and  a.op_time  between b.cust_op_time-(1/60/24)*5 and b.cust_op_time+(1/60/24)*5       
 ----04
create table ct_cust_scanrecord_info_201704  tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10) */* from ct_scanrecord_info_201704  a ,   ct_cust_info_xiaohu_cust  b
        where  a.login_no=b.cust_login_no
        and b.cust_cust_id = a.cust_id
         and  a.op_time  between b.cust_op_time-(1/60/24)*5 and b.cust_op_time+(1/60/24)*5          
        
 ----03
create table ct_cust_scanrecord_info_201703  tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10) */* from ct_scanrecord_info_201703  a ,   ct_cust_info_xiaohu_cust  b
        where  a.login_no=b.cust_login_no
        and b.cust_cust_id = a.cust_id
         and  a.op_time  between b.cust_op_time-(1/60/24)*5 and b.cust_op_time+(1/60/24)*5  
 ----02
create table ct_cust_scanrecord_info_201702  tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10) */* from ct_scanrecord_info_201702  a ,   ct_cust_info_xiaohu_cust  b
        where  a.login_no=b.cust_login_no
        and b.cust_cust_id = a.cust_id
         and  a.op_time  between b.cust_op_time-(1/60/24)*5 and b.cust_op_time+(1/60/24)*5  
 ----01
create table ct_cust_scanrecord_info_201701  tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10) */* from ct_scanrecord_info_201701  a ,   ct_cust_info_xiaohu_cust  b
        where  a.login_no=b.cust_login_no
        and b.cust_cust_id = a.cust_id
         and  a.op_time  between b.cust_op_time-(1/60/24)*5 and b.cust_op_time+(1/60/24)*5   
        

        
   -- 6 计算各个月份的客户信息表中数据量

select  /*+parallel(a,10) */ count (1) from ct_cust_scanrecord_info_201711 --1041
select  /*+parallel(a,10) */ count (1) from ct_cust_scanrecord_info_201710 --1488
select  /*+parallel(a,10) */ count (1) from ct_cust_scanrecord_info_201709 --1374
select  /*+parallel(a,10) */ count (1) from ct_cust_scanrecord_info_201708 --1272
select  /*+parallel(a,10) */ count (1) from ct_cust_scanrecord_info_201707 --862
select  /*+parallel(a,10) */ count (1) from ct_cust_scanrecord_info_201706 --930
select  /*+parallel(a,10) */ count (1) from ct_cust_scanrecord_info_201705 --820
select  /*+parallel(a,10) */ count (1) from ct_cust_scanrecord_info_201704 --752
select  /*+parallel(a,10) */ count (1) from ct_cust_scanrecord_info_201703 --726
select  /*+parallel(a,10) */ count (1) from ct_cust_scanrecord_info_201702 --643
select  /*+parallel(a,10) */ count (1) from ct_cust_scanrecord_info_201701 --710

--合并各个月份的数据

----09-11
create table ct_cust_scanrecord_info_09_11  tablespace tbs_data_interface as
select * from ct_cust_scanrecord_info_201711
        union all select * from ct_cust_scanrecord_info_201710
        union all select * from ct_cust_scanrecord_info_201709;
---06-08  
 create table ct_cust_scanrecord_info_06_08  tablespace tbs_data_interface as      
  select * from ct_cust_scanrecord_info_201708
        union all select * from ct_cust_scanrecord_info_201707 
        union all select * from ct_cust_scanrecord_info_201706  ;
 --03-05
 create table ct_cust_scanrecord_info_03_05  tablespace tbs_data_interface as          
 select * from ct_cust_scanrecord_info_201705
         union all select * from ct_cust_scanrecord_info_201704 
         union all select * from ct_cust_scanrecord_info_201703  ;
 ---01-02      
 create table ct_cust_scanrecord_info_01_02  tablespace tbs_data_interface as           
    select * from ct_cust_scanrecord_info_201702 
         union all select * from ct_cust_scanrecord_info_201701   ;
   
 ---01-08  
    create table ct_cust_scanrecord_info_01_08  tablespace tbs_data_interface as
select * from ct_cust_scanrecord_info_01_02
        union all select * from ct_cust_scanrecord_info_03_05
        union all select * from ct_cust_scanrecord_info_06_08;    
        
  --01-08-09-11    ct_cust_scanrecord_info_01_08   ct_cust_scanrecord_info_09_11
         
  select * from ct_cust_scanrecord_info_01_08 
 select * from ct_cust_scanrecord_info_09_11  
        

        
        
        
        
        
        
        
        

select count (1) from ct_cust_scanrecord_info_09_11

select * from ct_cust_scanrecord_info_201711
select * from 
select count(*) from ct_scanrecord_info_201700
select *  from ct_cust_info_xiaohu_cust
select * from ur_user_info_xiaohu_his
select * from ct_cust_info
select * from ct_cust_info_his
select * from ct_scanrecord_info_201711
select * from ct_scanrecord_info_201700