存储过程-b

于 2008-05-11 23:52:00 发布
阅读量82 收藏
点赞数
分类专栏： DataBase 文章标签： QQ Yahoo SQL
create or replace PROCEDURE Pro_Drivemail_log (
                                               Log_Date Varchar2

                                               )
Is
  V_SQL        VARCHAR2(5000);
--===============================================================
--                       Procedure Desc
--
--  Parameter ：年月日并连的字符型参数（YYYYMMDD）
--
--  Desc ：统计参数日发送的数量，和参数日打开的数量;总打开量、各
--         后缀的打开量和总点击量之外的统计，是以参数日的发送量
--         为基准的。
--
--  Result Table    ：LOG_SENDING
--  Transition Table：DRIVEMAIL_SEND_TEMP & DRIVEMAIL_OPEN_TEMP
--  
--================================================================



BEGIN

---当天发送数据提取
     EXECUTE IMMEDIATE 'TRUNCATE TABLE DRIVEMAIL_SEND_TEMP';
     COMMIT;

     FOR C IN (SELECT * FROM TAB WHERE TNAME LIKE 'TASK_EMAILS_%' AND SUBSTR(TNAME,INSTR(TNAME,'_',1,2)+1)>'103000') LOOP
        V_SQL:='INSERT /*+ APPEND */ INTO DRIVEMAIL_SEND_TEMP NOLOGGING
                      (ID,EMAIL,ACTIVE,OPEN,CLICK,OPEN_TIME,SENDING_TIME)
                SELECT ID,EMAIL,ACTIVE,OPEN,CLICK,OPEN_TIME,SENDING_TIME
                  FROM '||C.TNAME||' WHERE TO_CHAR(SENDING_TIME,''YYYYMMDD'') = '||Log_Date;
         EXECUTE IMMEDIATE V_SQL;
         COMMIT;
     END LOOP;
---------------------------------------------------------------------------------------------------------
---当天打开数据提取
     EXECUTE IMMEDIATE 'TRUNCATE TABLE DRIVEMAIL_OPEN_TEMP';
     COMMIT;

     FOR C IN (SELECT * FROM TAB WHERE TNAME LIKE 'TASK_EMAILS_%' AND SUBSTR(TNAME,INSTR(TNAME,'_',1,2)+1)>'103000') LOOP
        V_SQL:='INSERT /*+ APPEND */ INTO DRIVEMAIL_OPEN_TEMP NOLOGGING
                      (ID,EMAIL,ACTIVE,OPEN,CLICK,OPEN_TIME,SENDING_TIME)
                SELECT ID,EMAIL,ACTIVE,OPEN,CLICK,OPEN_TIME,SENDING_TIME
                  FROM '||C.TNAME||' WHERE OPEN>=1 AND TO_CHAR(OPEN_TIME,''YYYYMMDD'') = '||Log_Date ;
         EXECUTE IMMEDIATE V_SQL;
         COMMIT;
     END LOOP;
---------------------------------------------------------------------------------------------------------
     INSERT /*+ APPEND */ INTO LOG_SENDING NOLOGGING


     SELECT A.SENDINGDATE,
            A.SENDINGCOUNT,
            A.SENDSUCCESS,
            B.OPENCOUNT,
            B.CLICKCOUNT,
            A.REBOUND,
            A.SOFTREBOUND,
------------------------------------------------------------------------------------------
----记录的是当天打开的数据（含以往发送的数据）
            B.OPEN_163,
            B.OPEN_126,
            B.OPEN_SINA,
            B.OPEN_TOM,
            B.OPEN_SOHU,
            B.OPEN_YAHOO_COM,
            B.OPEN_YAHOO_COMCN,
            B.OPEN_QQ,
            B.OPEN_HOTMAIL,
            B.OPEN_21CN,
------------------------------------------------------------------------------------------
            A.SOFT_163,
            A.SOFT_126,
            A.SOFT_SINA,
            A.SOFT_TOM,
            A.SOFT_SOHU,
            A.SOFT_YAHOO_COM,
            A.SOFT_YAHOO_COMCN,
            A.SOFT_QQ,
            A.SOFT_HOTMAIL,
            A.SOFT_21CN,
------------------------------------------------------------------------------------------
            A.R_Open,
            A.R_Open_163,
            A.R_Open_126,
            A.R_Open_Sina,
            A.R_Open_Tom,
            A.R_Open_Sohu,
            A.R_Open_YahooCom,
            A.R_Open_Yahoocomcn,
            A.R_Open_QQ,
            A.R_Open_HOTMAIL,
            A.R_Open_21CN,
------------------------------------------------------------------------------------------
            A.R_Soft,
            A.R_Soft_163,
            A.R_Soft_126,
            A.R_Soft_Sina,
            A.R_Soft_Tom,
            A.R_Soft_Sohu,
            A.R_Soft_YahooCom,
            A.R_Soft_Yahoocomcn,
            A.R_Soft_QQ,
            A.R_Soft_HOTMAIL,
            A.R_Soft_21CN
------------------------------------------------------------------------------------------
           
       FROM
     (
     SELECT LOG_DATE                                                                               SENDINGDATE      ,--发送时间
            COUNT(*)                                                                               SENDINGCOUNT     ,--发送数量
            SUM(CASE WHEN ACTIVE=1 THEN 1 ELSE 0 END)                                              SENDSUCCESS      ,--发送成功数
           -- SUM(OPEN)                                                                              OPENCOUNT        ,--打开数
           -- SUM(CLICK)                                                                             CLICKCOUNT       ,--点击数
            SUM(CASE WHEN ACTIVE<-50 AND ACTIVE>-500 THEN 1 ELSE 0 END)                            REBOUND          ,--硬弹回数
            SUM(CASE WHEN ACTIVE=-1  OR  ACTIVE<-500 THEN 1 ELSE 0 END)                            SOFTREBOUND      ,--软弹回数
-----------------------------------------------------------------
/*
----Count Of Open
            SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END)                     OPEN_163         ,--打开数-163
            SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END)                     OPEN_126         ,--打开数-126
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END)          OPEN_SINA        ,--打开数-Sina
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END)            OPEN_TOM         ,--打开数-Tom
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END)          OPEN_SOHU        ,--打开数-Sohu
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
                     INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END)              OPEN_YAHOO_COM   ,--打开数-YaHoo.com
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@yahoo.com.cn')>0 OR
                     INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END)                            OPEN_YAHOO_COMCN ,--打开数-YaHoo.com.cn
           
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END)              OPEN_QQ          ,--打开数-QQ
            SUM(CASE WHEN OPEN>=1 AND
                         (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
                          INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0 ) THEN 1 ELSE 0 END)      OPEN_HOTMAIL     ,--打开数-Hotmail+MSN
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END)          OPEN_21CN        ,--打开数-21CN
*/
-----------------------------------------------------------------
----Count Of Soft Rebound 
            SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END)  SOFT_163         ,--软弹数-163
            SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END)  SOFT_126         ,--软弹数-126
            SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
                    (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END)          SOFT_SINA        ,--软弹数-Sina
            SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
                    (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END)            SOFT_TOM         ,--软弹数-Tom
            SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
                    (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END)          SOFT_SOHU        ,--软弹数-Sohu
            SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
                    (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
                     INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END)              SOFT_YAHOO_COM   ,--软弹数-YaHoo.com
            SUM(CASE WHEN (ACTIVE=-1 OR ACTIVE<-500) AND
                    (INSTR(EMAIL,'@yahoo.com.cn')>0 OR
                     INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END)                            SOFT_YAHOO_COMCN ,--软弹数-YaHoo.com.cn
            SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND
                    (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END)              SOFT_QQ          ,--软弹数-QQ
            SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND
                    (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
                     INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END)            SOFT_HOTMAIL     ,--软弹数-Hotmail+MSN
            SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND
                    (INSTR(EMAIL,'@21cn')>0 Or INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END)          SOFT_21CN        ,--软弹数-21CN
-----------------------------------------------------------------
----Rate Of Open   
           (Case When SUM(CASE WHEN ACTIVE=1 THEN 1 ELSE 0 END) = 0 Then 0 Else   
            ROUND(SUM(OPEN)/SUM(CASE WHEN ACTIVE=1 THEN 1 ELSE 0 END),4)   End)                        R_Open           ,---打开比例
          
           (Case When SUM(CASE WHEN ACTIVE=1 AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END)
                /SUM(CASE WHEN ACTIVE=1 AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END),4)   End)         R_Open_163       ,---打开比例-163
 
           (Case When SUM(CASE WHEN ACTIVE=1 AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END)=0 Then 0 Else   
            ROUND(SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END)
                /SUM(CASE WHEN ACTIVE=1 AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END),4)   End)          R_Open_126       ,---打开比例-126
                
           (Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END)
                /SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Open_Sina      ,---打开比例-Sina
           (Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END)
                /SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Open_Tom       ,---打开比例-Tom                                                                                   
          
           (Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END)
                /SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Open_Sohu      ,---打开比例-Sohu                                                                                
           (Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
                   INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END) = 0 Then 0 Else
            ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
                   INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END) 
                /SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
                   INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END),4)    End)         R_Open_YahooCom  ,---打开比例-Yahoo.com

           (Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END)
                /SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Open_Yahoocomcn,---打开比例-Yahoo.com.cn
           (Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END)
                /SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Open_QQ        ,---打开比例-QQ
           (Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
                                             INSTR(EMAIL,'@msn')>0     OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
                                             INSTR(EMAIL,'@msn')>0     OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END)
                /SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
                                             INSTR(EMAIL,'@msn')>0     OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Open_HOTMAIL   ,---打开比例-Hotmail
           (Case When SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN OPEN>=1 AND (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END)
                /SUM(CASE WHEN ACTIVE=1 AND (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Open_21CN      ,---打开比例-21CN                                                                                        R_Open_Hotmail   ,---打开比例-Hotmail    
-----------------------------------------------------------------
----Rate Of Soft Rebound    
           (Case When COUNT(*) = 0 Then 0 Else
            ROUND(SUM(CASE WHEN ACTIVE=-1  OR  ACTIVE<-500 THEN 1 ELSE 0 END)
                  /COUNT(*),4)                                                            End)         R_Soft           ,---软弹比例
           (Case When SUM(CASE WHEN  INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END) = 0 Then 0 Else
            ROUND(SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END)
                 /SUM(CASE WHEN  INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END),4)              End)         R_Soft_163       ,---软弹比例-163
                
           (Case When SUM(CASE WHEN  INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END) = 0 Then 0 Else
            ROUND(SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END)
                 /SUM(CASE WHEN  INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END),4)              End)         R_Soft_126       ,---软弹比例-126
                
           (Case When SUM(CASE WHEN (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END) = 0 Then 0 Else
            ROUND(SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END)
                 /SUM(CASE WHEN (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Soft_Sina      ,---软弹比例-Sina
           (Case When SUM(CASE WHEN (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END)
                 /SUM(CASE WHEN (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Soft_Tom       ,---软弹比例-Tom                                                                                   
          
           (Case When SUM(CASE WHEN (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END)
                 /SUM(CASE WHEN (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Soft_Sohu      ,---软弹比例-Sohu                                                                                
           (Case When SUM(CASE WHEN  (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
                   INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END) = 0 Then 0 Else 
            ROUND(SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
                   INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END) 
                 /SUM(CASE WHEN  (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
                   INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END),4)    End)         R_Soft_YahooCom  ,---软弹比例-Yahoo.com

           (Case When SUM(CASE WHEN  (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END)
                 /SUM(CASE WHEN  (INSTR(EMAIL,'@yahoo.com.cn')>0 OR INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Soft_Yahoocomcn,---软弹比例-Yahoo.com.cn
        
           (Case When SUM(CASE WHEN  (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END)=0 Then 0 Else 
            ROUND(SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END)
                 /SUM(CASE WHEN  (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END),4)
                                                                                          End)         R_Soft_QQ        ,---软弹比例-QQ
           (Case When SUM(CASE WHEN  (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
                                      INSTR(EMAIL,'@msn')>0     OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
                                 INSTR(EMAIL,'@msn')>0     OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END)
                 /SUM(CASE WHEN  (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
                                 INSTR(EMAIL,'@msn')>0     OR INSTR(EMAIL,'@MSN')>0) THEN 1 ELSE 0 END),4)
                                                                                           End)        R_Soft_HOTMAIL   ,---软弹比例-Hotmail
                                            
           (Case When SUM(CASE WHEN  (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END)=0 Then 0 Else
            ROUND(SUM(CASE WHEN (ACTIVE=-1  OR  ACTIVE<-500) AND (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END)
                /SUM(CASE WHEN  (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END),4)
                                                                                           End)        R_Soft_21CN       ---软弹比例-21CN     
           
                   
           
       FROM DRIVEMAIL_SEND_TEMP ) A ,
    (SELECT LOG_DATE                                                                               SENDINGDATE      ,
            SUM(CASE WHEN OPEN>=1 THEN 1 ELSE 0 END)                                               OPENCOUNT        ,--打开数
            SUM(CASE WHEN CLICK>=1 THEN 1 ELSE 0 END)                                              CLICKCOUNT       ,--点击数
----Count Of Open
            SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@163')>0 THEN 1 ELSE 0 END)                     OPEN_163         ,--打开数-163
            SUM(CASE WHEN OPEN>=1 AND INSTR(EMAIL,'@126')>0 THEN 1 ELSE 0 END)                     OPEN_126         ,--打开数-126
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@sina')>0 OR INSTR(EMAIL,'@SINA')>0) THEN 1 ELSE 0 END)          OPEN_SINA        ,--打开数-Sina
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@tom')>0 OR INSTR(EMAIL,'@TOM')>0) THEN 1 ELSE 0 END)            OPEN_TOM         ,--打开数-Tom
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@sohu')>0 OR INSTR(EMAIL,'@SOHU')>0) THEN 1 ELSE 0 END)          OPEN_SOHU        ,--打开数-Sohu
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@yahoo')>0 OR INSTR(EMAIL,'@YAHOO')>0) AND
                     INSTR(EMAIL,'.cn')=0 AND INSTR(EMAIL,'.CN')=0 THEN 1 ELSE 0 END)              OPEN_YAHOO_COM   ,--打开数-YaHoo.com
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@yahoo.com.cn')>0 OR
                     INSTR(EMAIL,'@YAHOO.COM.CN')>0) THEN 1 ELSE 0 END)                            OPEN_YAHOO_COMCN ,--打开数-YaHoo.com.cn
           
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@qq')>0 OR INSTR(EMAIL,'@QQ')>0) THEN 1 ELSE 0 END)              OPEN_QQ          ,--打开数-QQ
            SUM(CASE WHEN OPEN>=1 AND
                         (INSTR(EMAIL,'@hotmail')>0 OR INSTR(EMAIL,'@HOTMAIL')>0 OR
                          INSTR(EMAIL,'@msn')>0 OR INSTR(EMAIL,'@MSN')>0 ) THEN 1 ELSE 0 END)      OPEN_HOTMAIL     ,--打开数-Hotmail+MSN
            SUM(CASE WHEN OPEN>=1 AND
                    (INSTR(EMAIL,'@21cn')>0 OR INSTR(EMAIL,'@21CN')>0) THEN 1 ELSE 0 END)          OPEN_21CN         --打开数-21CN
         FROM DRIVEMAIL_OPEN_TEMP) B
   WHERE A.SENDINGDATE = B.SENDINGDATE;
      
      COMMIT;


  
END Pro_Drivemail_log;


hwpokay
关注
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值