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;