用户流失流程--存储过程实现

CREATE OR REPLACE PROCEDURE P_ZHQ_LSTEST(P_STARTTIME IN VARCHAR2) IS

 /*--------------------------------------------------------------------------------------------+

 |程序目的：计算用户的流失率

 | 创建日期：20101-1-11

 | 初始版本   20101-1-11  Ver1.0

 | 说明 ：只是简单的实现，还有很多地方需要优化 

 +-----------------------------------------------------*/

 

 /* create table t_zhq_alstj0111(starttime date,id int,endtime date,synumb int,lsnumb int,lsrate number);

    create table t_zhq_alsyb0111(usermid varchar2(50));

    create table t_zhq_temp(id int,sdate date,usermid varchar2(50));

  */

 

  --SET SERVEROUTPUT ON ;

 

          V_STARTTIME DATE;

          V_YBSDATE DATE;

          V_SDATE DATE;

          V_MONTHS NUMBER := 4 ;--样本点数

          V_INTERVAL NUMBER := 2 ;--计算绝对剩余用户的时间间隔

          V_LSMONTHS NUMBER :=2 ;--流失周期

          V_CMMONTHS NUMBER :=6 ;--计算次数

          V_LOOP1 INT;

          V_LOOP2 INT;

          I INT ;

 

       -- 用户流失统计(2个月为周期：循环)

 

       --(V_STARTTIME--2009/6/1)

 

  BEGIN

          EXECUTE IMMEDIATE 'TRUNCATE TABLE t_zhq_alstj0111';

          V_STARTTIME := TO_DATE(P_STARTTIME,'YYYY-MM-DD HH24:MI:SS');

 

          INSERT INTO T_ZHQ_PLOG VALUES('P_ZHQ_LSTEST',NULL,SYSDATE,'START...');

          COMMIT WORK;

 

          FOR V_LOOP1 IN 1..V_MONTHS LOOP

 

            INSERT /*+ append */ INTO t_zhq_alsyb0111

              select usermid from t_zhq_lstest0111

                     where sdate=ADD_MONTHS(V_STARTTIME,V_LOOP1-1);

            COMMIT WORK;

 

            v_ybsdate := ADD_MONTHS(V_STARTTIME,V_LOOP1-1);

            i :=1;

 

            INSERT /*+ append */ INTO t_zhq_alstj0111

              select v_ybsdate,0,v_ybsdate,count(usermid)synumb,0,0 

                from t_zhq_alsyb0111                

                ;           

 

            commit work;

 

            v_sdate := v_ybsdate;

 

            for V_LOOP2 in 1..V_CMMONTHS loop

 

            INSERT /*+ append */ INTO t_zhq_temp

              select i,ADD_MONTHS(v_sdate,i),usermid

                     from t_zhq_lstest0111

                     where usermid in (select usermid from t_zhq_alsyb0111)

                     and usermid not in

                     (select usermid from t_zhq_lstest0111 where sdate>=ADD_MONTHS(v_sdate,i)

                      and sdate<ADD_MONTHS(v_sdate,V_LSMONTHS+i));

 

            commit work;

 

 

            INSERT /*+ append */ INTO t_zhq_alstj0111

               select v_ybsdate,i,ADD_MONTHS(v_sdate,i),

                 b.uv - count(distinct a.usermid) synumb,

                 count(distinct a.usermid)lsnum,

                 round(count(distinct a.usermid)/b.uv,4)lsrate

                 from t_zhq_temp a,(select count(1)uv from t_zhq_alsyb0111)b

                 group by b.uv

                --where sdate=ADD_MONTHS(v_sdate,1) 

                ;

 

            commit work;

 

            delete from t_zhq_alsyb0111

              where usermid in (select usermid from t_zhq_temp) ;

 

            commit work;

            EXECUTE IMMEDIATE 'TRUNCATE table t_zhq_temp';

 

            --v_sdate:=add_months(v_sdate,V_INTERVAL);

            i:= i+V_INTERVAL;

 

            end loop;

 

            EXECUTE IMMEDIATE 'TRUNCATE table t_zhq_alsyb0111' ;

 

            end loop;

 

        INSERT INTO T_ZHQ_PLOG VALUES('P_ZHQ_LSTEST','',SYSDATE,'END...');

        COMMIT WORK;

 

 

      EXCEPTION

      WHEN OTHERS THEN

        ROLLBACK WORK;

         INSERT INTO T_ZHQ_PLOG VALUES('P_ZHQ_LSTEST',NULL,SYSDATE,'ERROR');

         COMMIT WORK;

         DBMS_OUTPUT.put_line(SQLCODE||'   '||SQLERRM);

        --p_writeerrorlog('P_ZHQ_RESBASE',SQLCODE,SQLERRM);

        --OPERMAN.SEND_MAIL('zenghaiqun@sz5g.com.cn','zenghaiqun@sz5g.com.cn','P_ZHQ_RESBASE Failure!',SQLCODE||'  '||SQLERRM);

   END P_ZHQ_LSTEST;

 

   --EXECUTE P_ZHQ_LSTEST('2009-6-1');