提取订购关系.docVIP

提取订购关系 ----------------------订购关系统计流程,为保证时间因素的绝对准确,所以必须通过以下复杂的数据抽取来进行.主要要先替换下时间及历史表 --每月10日中午运行如下脚本 /*干掉索引*/ drop index JH.IDX_DATA_LNSUB_CENTER_TJ_1 / drop index JH.IDX_DATA_LNSUB_CENTER_TJ_2 / /*抽取订购关系当前表中生效时间小于统计时间的纪录*/ truncate table JH.DATA_LNSUB_CENTER_TJ; insert /*+ APPEND */ /*+ NOLOGGING */ into JH.DATA_LNSUB_CENTER_TJ (msisdn,sp_code,service_code,trd_msisdn,charge_type,sub_status,eff_time,EXP_TIME,service_type,last_order_time) select msisdn,sp_code,service_code,trd_msisdn,charge_type,sub_status,eff_time,EXP_TIME,service_type,last_order_time from CBOSS.DATA_LNSUB_CENTER@LNK_CBBCV where eff_time = 20111210000000; commit; /*抽取以上统计时排除掉的用户但在订购关系历史表中生效时间小于统计时间的纪录*/ insert /*+ APPEND */ /*+ NOLOGGING */ into JH.DATA_LNSUB_CENTER_TJ (msisdn,sp_code,service_code,trd_msisdn,charge_type,sub_status,eff_time,EXP_TIME,service_type,last_order_time) select msisdn,sp_code,service_code,trd_msisdn,charge_type,sub_status,eff_time,EXP_TIME,service_type,last_order_time from CBOSS.DATA_LNSUB_CENTER_H_201112@LNK_CBBCV where (msisdn,sp_code,service_code) in (select msisdn,sp_code,service_code from CBOSS.DATA_LNSUB_CENTER@LNK_CBBCV where eff_time 20111210000000 ) and 20111210000000 between eff_time and exp_time; commit; /*创建索引*/ CREATE INDEX JH.IDX_DATA_LNSUB_CENTER_TJ_1 ON JH.DATA_LNSUB_CENTER_TJ (MSISDN,SP_CODE,SERVICE_CODE) NOLOGGING TABLESPACE TS_JH_IDX_1M_01 / CREATE INDEX JH.IDX_DATA_LNSUB_CENTER_TJ_2 ON JH.DATA_LNSUB_CENTER_TJ (EFF_TIME) NOLOGGING TABLESPACE TS_JH_IDX_1M_01 / --订购关系 --wap及手机动画的免费包月不统计:AND (SERVICE_TYPE, CHARGE_TYPE) NOT IN ((03, 0), (13, 0)) --12580未考核之前过虑的:AND SP_CODE NOT IN (801174, 901808),现在已考核,所以不用注释了. /*按照如下脚本统计*/ SELECT /*+ parallel(a,32) */ DECODE(SUB_STATUS, 04, 暂停, 05, 正常, 06, 正常, 07, 退订, 异常) as TJ, COUNT(*) FROM jh.DATA_LNSUB_CENTER_TJ a WHERE EFF_TIME 20111210000000 AND EXP_TIME 20111210000000 AND (SE