`
hwpok
  • 浏览: 251612 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

存储过程-b

阅读更多
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;


分享到:
评论

相关推荐

    ADS-B-demodulated-code.rar_ADS-B DF17_ADS-B DF17_ADS_b_df17_民航

    在这个名为“ADS-B-demodulated-code.rar_ADS-B DF17_ADS-B DF17_ADS_b_df17_民航”的压缩包中,包含的是针对ADS-B协议中DF17帧类型的解码器代码,专门针对民航应用。 DF17是ADS-B Out标准中的一个数据格式(Data ...

    KLMAG1JETD-B041 datasheet 规格书

    eMMC是一种高度集成的存储解决方案,它将闪存和控制器封装在一个小型BGA封装中,简化了设计过程并提高了系统的可靠性。eMMC 5.1规范兼容性意味着这些设备遵循JEDEC(固态技术协会)制定的eMMC 5.1标准,该标准定义了...

    宇视B230-B260VMS-R1101.2040.2.201126.zip

    `.tar`文件用于将多个文件打包成一个单一文件,方便管理和传输,而`.gz`则是GNU zip的压缩格式,用于减小文件大小,提高下载和存储效率。用户在升级过程中,首先需要解压这个文件,然后按照宇视提供的升级指南进行...

    MySQL高级教程视频.zip

    16.存储过程 - 创建调用查询删除语法 17.存储过程 - 语法 - 变量 18.存储过程 - 语法 - if判断 19.存储过程 - 语法 - 输入参数 20.存储过程 - 语法 - 输出参数 21.存储过程 - 语法 - case结构 22.存储过程 - 语法 - ...

    IRIG-B编解码合并.zip

    2. **内存配置**:通常包含闪存和RAM,用于存储程序和运行时数据。STM32C8T6的闪存容量和RAM大小可能因具体型号而异,支持快速读取和写入操作。 3. **丰富的外设接口**:如串行通信接口(SPI, I2C, UART)、定时器、...

    radarcape ADS-B 数据解析,Linux下通过

    `radar.cpp`可能包含了读取Radarcape设备的数据流、解码ADS-B报文以及将解析后的信息输出或存储的功能。 `dump1090`是一个流行的开源软件,专门用于接收和解码ADS-B信号。它可以从串行端口或网络接口获取数据,并将...

    sql考试复习资料-索引--触发器-存储过程-详细查询语句

    在SQL的学习过程中,索引、触发器和存储过程是三个重要的概念,它们对于数据库的性能优化和业务逻辑处理起着至关重要的作用。本复习资料涵盖了这些主题,旨在帮助你全面理解和掌握SQL的基础到进阶知识。 首先,让...

    HG680-l-s905l-b线刷救砖包

    压缩包中的文件名“HG680-l-s905l-b线刷包.rar”暗示这是一个RAR格式的压缩文件,包含了线刷过程所需的所有文件,如固件镜像、驱动程序、刷机工具等。RAR是一种流行的压缩格式,能够有效地存储和传输大量数据。 另...

    台达VFD-B说明书.pdf

    操作前的检查和准备工作,如设备检查、产品外观、搬运、储存等步骤都需要依照手册指导进行。在驱动器使用过程中,还需按照说明书中的指导进行运转前的检查、运转方法以及试运转,以确保设备正常运行。 在处理异常时...

    TDS510USB-B

    在TDS510USB-B的实现过程中,涉及到了几个关键的技术点: 1. **USB接口**:USB(Universal Serial Bus)是当今最常见的设备连接方式之一,提供了高速的数据传输能力,使得示波器捕获的波形数据能够快速地传送到...

    三菱MR-J4-B伺服驱动器说明书

    - **环境条件**:环境温度应在0℃~55℃之间(运转时),-20℃~65℃(存储时);环境湿度不超过90%RH(运转时),并在室内使用,远离阳光直射;海拔高度限制在1000米以下;振动限制在5.9m/s²以下(10Hz~55Hz)。 ...

    L-BFGS-B 的FORTRAN代码

    L-BFGS-B则在此基础上加入了边界约束处理,使得优化过程可以在指定的参数空间范围内进行,这对许多实际问题如物理、化学、工程中的优化计算非常有用。 在FORTRAN实现中,L-BFGS-B代码通常包括以下几个核心部分: 1...

    Sap b1 9.2存储过程

    实施公司 sap b1 9.2存储过程 --------------------------------------------------------【1】物料主数据--------------------------------------------------------- ------------------------1.1物料主数据中默认...

    Oracle数据库表定时备份表 表名动态拼接时间戳存储过程-解决了ORA-00922

    1、备份Oracle数据库表时添加动态时间戳,解决了... 本存储过程解决备份表名称时动态添加时间戳,例如表名称 TB_B20240106; 8、资源内容有: 8.1、P_TIMER_BAK_TABLE定时备份表 表名动态拼接时间戳.sql 8.2、Readme

    JESD22-A101-B-1997.pdf

    测试设备(如卡笼、测试板、插座、接线、存储容器等)的离子污染释放应得到控制,以避免测试结果出现误差。 2.5 去离子水 使用至少在室温下具有1兆欧-厘米电阻率的去离子水,以确保测试过程的纯净度。 3. 测试条件...

    [联通版]杰赛s65 S905L-B处理器刷机教程及固件.rar

    2. **刷机工具**:这些工具可以帮助你将固件文件安全地传输到设备的存储中,并执行刷机过程。常见的工具有Fastboot、SP Flash Tool、Odin等,具体取决于设备的制造商和型号。 3. **刷机教程**:这个教程应该详细指导...

    tigase-server-8.0.0-b10083-dist-max.zip

    4. **创建数据库**:Tigase服务器通常使用MySQL或PostgreSQL作为后端存储。你需要预先创建数据库并设置相应的用户权限。 5. **初始化数据库**:运行Tigase服务器的初始化脚本,将数据库结构导入到你创建的数据库中...

    EA53C9.25-B23-TH5N2电梯绝对值编码器使用说明书.pdf

    - **拆卸步骤**:与安装过程相反,先拆卸上盖,松开涨环紧固螺钉,然后通过特殊工具逐步拆卸编码器。 5. **电缆屏蔽处理**: 编码器的电缆屏蔽线应通过金属屏蔽环与外壳接触,以保证良好的电磁兼容性。 此说明书...

    【eMMC芯片datasheet】KLM4G1FETE-B041.pdf

    对于KLM4G1FETE-B041,这可能包括其存储容量(例如4GB),数据传输速率(如HS400模式下的最大速度),读写速度,以及工作温度范围。这些参数直接影响到设备的运行效率和存储能力。电源电压也是一个关键指标,它规定...

    SV1-H611A-B

    标题“SV1-H611A-B”暗示我们正在讨论一款特定的工控机主板的固件,该主板型号为SV1-H611A-B。固件是控制硬件设备运行的基础软件,它驻留在硬件内部,负责初始化、管理和协调硬件资源。在工控机领域,固件的重要性...

Global site tag (gtag.js) - Google Analytics