融合主角实名登记，过户缺陷



--目标：
融合主角实名登记异常数据刷新分两部分，

--前期缺陷是融合群主角实名登记，群和宽带没有过户；
--后期缺陷是融合群主角色实名登记，群内所有用户均过户至新客户。

-- 1 查询融合产品的主题服务类型
select /*+parallel(a,10)*/* from pd_master_dict a where a.master_serv_name like '%融合%';-- 1009
--2 根据主体服务类型为1009提取所有的融合群用户,并且建立临时表
create table denggh_smz_user1009 tablespace tbs_data_interface as
select /*+parallel(a,10)*/* from ur_user_info a where a.master_serv_id ='1009';
-- 3 计算融合产品的数据量
select /*+parallel(a,10)*/count (*) from denggh_smz_user1009 a ;--264516
-- 4 查询群实例表
select /*+parallel(a,10)*/* from ur_usergroup_info a;--群实例表

-- 5 根据融合群用户的id_no 去群实例表中查询满足条件的组 ：group_id，
select/*+parallel(a,10)*/ a.* from ur_usergroup_info a ,denggh_smz_user1009 b where a.id_no =b.id_no ;   
--错误的写法： select  /*+parallel(a,10) parallel(b,10)*/count(*) from ur_usergroup_info a ,denggh_smz_user1009 b where a.id_no =b.id_no(+) ; --2129814  

-- 6 确定需要的字段信息,并且建立临时表
drop table denggh_smz_usergroup1009;
create table denggh_smz_usergroup1009 tablespace tbs_data_interface as
select  /*+parallel(a,10) parallel(b,10)*/b.id_no,b.phone_no,b.cust_id,a.group_id,a.group_type  from ur_usergroup_info a ,denggh_smz_user1009 b where a.id_no = b.id_no;
-- 7计算满足条件的group_id 的数据量
select /*+parallel(a,10) parallel(b,10)*/ count(*) from denggh_smz_usergroup1009;--264501

-- 8 验证群实例中群组组类型
select a.group_type,count(*) from denggh_smz_usergroup1009 a group by a.group_type;
-- 9 查看群组类型表：pd_grouptype_dict 进行验证，验证结果都是群融合
select * from pd_grouptype_dict where object_id in ('F1','F3','FD','FB');
-- 10 根据 6临时表中的 group_id 去群成员表：ur_usergroupmbr_info 提取融合群实例成员（主卡）
select /*+parallel(a,10) parallel(b,10)*/ * from ur_usergroupmbr_info a, denggh_smz_usergroup1009 b where a.group_id = b.group_id
                                                                                                    and a.member_role_id='10047'
                                                                                                    and sysdate between a.eff_date and a.exp_date
                                                                                                   
--11 确定融合群实例成员的主卡相关信息，并且建立临时表
create table denggh_smz_usergroupmbr1009 tablespace tbs_data_interface as
select /*+parallel(a,10) parallel(b,10)*/  b.*, to_number(a.object_id) object_id  from ur_usergroupmbr_info a, denggh_smz_usergroup1009 b
                                                                                                             where a.group_id = b.group_id
                                                                                                              and a.member_role_id='10047'
                                                                                                              and sysdate between a.eff_date and a.exp_date;  
                                                                                                             
--12 对群成员表中字段分析
   select * from  ur_usergroupmbr_info; 
   --  MEMBER_ROLE_ID融合成员角色id:10047 为主卡，10048 为副卡  宽带为10050  
   --object_id 为 1时，object_id 对应的值为 id_no 
   --object_id 为 2时，object_id 对应的值为 phone_no
   --object_id 为 3时，object_id 对应的值为 cust_id ??????
  
  -- select object_type,count(*) from ur_usergroupmbr_info group by object_type ;
  --select* from ur_usergroupmbr_info where object_type='2';
  -- select* from ur_usergroupmbr_info where object_type='3';
  
                                                      
-- 13 计算用户量 主卡的用户量
select count(*) from denggh_smz_usergroupmbr1009;--234935

--14 检查数据是否是正确的数据，可以采取抽查的方法 ：从提取融合群实例成员（主卡）的剩余信息里面，抽选出相关信息的group_id，去群成员表中进行判断
select * from denggh_smz_usergroup1009 a where not exists(select 1 from denggh_smz_usergroupmbr1009 b where a.group_id=b.group_id);
--select * from denggh_smz_usergroup1009 a where a.group_id not in (select  b.group_id from denggh_smz_usergroupmbr1009 b );
select * from ur_usergroupmbr_info where group_id=10009801611;

select * from ur_user_info where id_no ='21310010458356';
select * from bs_unicodedef_dict where code_class='196' and code_value like '%sjf000013%'  --for update

                                                                  
select * from denggh_smz_usergroup1009;
select * from ur_usergroup_info;
select * from ur_usergroupmbr_info;