hwpok
- 浏览: 250476 次
- 性别:
- 来自: 上海
-
文章分类
最新评论
-
无它唯勤：
...
Spring Ioc AOP -
dubaopeng：
挺好的，拿去改改！
java 解析csv文件 -
灵程大哥：
先试一下，看行不行
java 判断文本文件编码 -
pxjianke：
谢谢兄弟。我现在在测试插入千万级数据。做测试。这个太有用了。
Oracle 高速批量速插入数据 解决方案
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;
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;
分享到：
发表评论
-
Oracle insert into select 序列
2010-03-16 14:46 2415"INSERT INTO AUDIT_TASK_LO ... -
Oracle 存储过程
2007-11-07 11:53 610存储过程是指数据库中已编译的可调程序，它作为PL/SQ ... -
Oracle 安装注意事项
2007-11-07 22:17 680如果你的机器上存在其它版本的Oracle,请先删除以前版本的安 ... -
Oracle 游标
2007-11-11 15:31 1503Oracle游标，从字面理解就是游动的光标。用数据库语言来描述 ... -
ORACLE 异常汇集
2007-11-15 11:53 616------------------------------- ... -
存储过程 Update 返回改更后的 结果
2007-12-18 19:54 1358今天做邮件群发系统开发,遇到这样一个问题:更改数据库一 ... -
Oracle 返回 结果集
2007-12-19 19:26 700过程返回记录集： CREAT ... -
错误的
2007-12-20 19:47 557CREATE OR REPLACE PACKAGE BODY ... -
随机 返回 一条符合条件的记录
2008-01-21 10:29 710SELECT * FROM anti_shields SAMP ... -
ORACLE NOCOPY
2008-01-21 15:23 834PL/SQL中对out，in out参数使用的？默认形 ... -
ORACLE 存储过程 邮件服务器 反屏蔽
2008-01-22 12:09 699邮件群发反屏蔽的原理是这样的: 1.每封邮件内容不同( ... -
Oracle dblink
2008-02-20 14:06 626Create Database Link LinkName C ... -
让oracle做定时任务
2008-03-11 14:42 1091今天用oracle的定时任务来实现定时检测。于是goole了一 ... -
ORACL 查看连接
2008-03-14 18:16 639select schemaname,osuser,machin ... -
Oracle 常用函数
2008-03-28 14:42 6121.SUBSTR(string,start[,end]) ... -
Oracle 定时任务 JOB
2008-04-02 17:00 944众所周知，一般操作系统会提供定时执行任务的方法，例如：Uni ... -
copy表时丢失索引
2008-04-07 20:24 639今天发现了一个大问题,copy表时,会丢失索引.这导制 ... -
Oracle decode用法
2008-04-23 13:18 17151、Windows NT4.0+ORACLE 8.0.4 ... -
存储过程-a
2008-05-11 23:51 640create or replace PROCEDURE EMA ... -
存储过程-c
2008-05-11 23:52 729create or replace Procedure Pro ...
相关推荐
在这个名为“ADS-B-demodulated-code.rar_ADS-B DF17_ADS-B DF17_ADS_b_df17_民航”的压缩包中，包含的是针对ADS-B协议中DF17帧类型的解码器代码，专门针对民航应用。 DF17是ADS-B Out标准中的一个数据格式（Data ...
eMMC是一种高度集成的存储解决方案，它将闪存和控制器封装在一个小型BGA封装中，简化了设计过程并提高了系统的可靠性。eMMC 5.1规范兼容性意味着这些设备遵循JEDEC（固态技术协会）制定的eMMC 5.1标准，该标准定义了...
`.tar`文件用于将多个文件打包成一个单一文件，方便管理和传输，而`.gz`则是GNU zip的压缩格式，用于减小文件大小，提高下载和存储效率。用户在升级过程中，首先需要解压这个文件，然后按照宇视提供的升级指南进行...
16.存储过程 - 创建调用查询删除语法 17.存储过程 - 语法 - 变量 18.存储过程 - 语法 - if判断 19.存储过程 - 语法 - 输入参数 20.存储过程 - 语法 - 输出参数 21.存储过程 - 语法 - case结构 22.存储过程 - 语法 - ...
2. **内存配置**：通常包含闪存和RAM，用于存储程序和运行时数据。STM32C8T6的闪存容量和RAM大小可能因具体型号而异，支持快速读取和写入操作。 3. **丰富的外设接口**：如串行通信接口（SPI, I2C, UART）、定时器、...
`radar.cpp`可能包含了读取Radarcape设备的数据流、解码ADS-B报文以及将解析后的信息输出或存储的功能。 `dump1090`是一个流行的开源软件，专门用于接收和解码ADS-B信号。它可以从串行端口或网络接口获取数据，并将...
在SQL的学习过程中，索引、触发器和存储过程是三个重要的概念，它们对于数据库的性能优化和业务逻辑处理起着至关重要的作用。本复习资料涵盖了这些主题，旨在帮助你全面理解和掌握SQL的基础到进阶知识。 首先，让...
压缩包中的文件名“HG680-l-s905l-b线刷包.rar”暗示这是一个RAR格式的压缩文件，包含了线刷过程所需的所有文件，如固件镜像、驱动程序、刷机工具等。RAR是一种流行的压缩格式，能够有效地存储和传输大量数据。 另...
操作前的检查和准备工作，如设备检查、产品外观、搬运、储存等步骤都需要依照手册指导进行。在驱动器使用过程中，还需按照说明书中的指导进行运转前的检查、运转方法以及试运转，以确保设备正常运行。 在处理异常时...
在TDS510USB-B的实现过程中，涉及到了几个关键的技术点： 1. **USB接口**：USB（Universal Serial Bus）是当今最常见的设备连接方式之一，提供了高速的数据传输能力，使得示波器捕获的波形数据能够快速地传送到...
L-BFGS-B则在此基础上加入了边界约束处理，使得优化过程可以在指定的参数空间范围内进行，这对许多实际问题如物理、化学、工程中的优化计算非常有用。 在FORTRAN实现中，L-BFGS-B代码通常包括以下几个核心部分： 1...
实施公司 sap b1 9.2存储过程 --------------------------------------------------------【1】物料主数据--------------------------------------------------------- ------------------------1.1物料主数据中默认...
2. **刷机工具**：这些工具可以帮助你将固件文件安全地传输到设备的存储中，并执行刷机过程。常见的工具有Fastboot、SP Flash Tool、Odin等，具体取决于设备的制造商和型号。 3. **刷机教程**：这个教程应该详细指导...
1、备份Oracle数据库表时添加动态时间戳，解决了... 本存储过程解决备份表名称时动态添加时间戳，例如表名称 TB_B20240106； 8、资源内容有： 8.1、P_TIMER_BAK_TABLE定时备份表 表名动态拼接时间戳.sql 8.2、Readme
- **环境条件**：环境温度应在0℃～55℃之间（运转时），-20℃~65℃（存储时）；环境湿度不超过90%RH（运转时），并在室内使用，远离阳光直射；海拔高度限制在1000米以下；振动限制在5.9m/s²以下（10Hz～55Hz）。 ...
- **拆卸步骤**：与安装过程相反，先拆卸上盖，松开涨环紧固螺钉，然后通过特殊工具逐步拆卸编码器。 5. **电缆屏蔽处理**： 编码器的电缆屏蔽线应通过金属屏蔽环与外壳接触，以保证良好的电磁兼容性。 此说明书...
对于KLM4G1FETE-B041，这可能包括其存储容量（例如4GB），数据传输速率（如HS400模式下的最大速度），读写速度，以及工作温度范围。这些参数直接影响到设备的运行效率和存储能力。电源电压也是一个关键指标，它规定...
测试设备（如卡笼、测试板、插座、接线、存储容器等）的离子污染释放应得到控制，以避免测试结果出现误差。 2.5 去离子水 使用至少在室温下具有1兆欧-厘米电阻率的去离子水，以确保测试过程的纯净度。 3. 测试条件...
标题“SV1-H611A-B”暗示我们正在讨论一款特定的工控机主板的固件，该主板型号为SV1-H611A-B。固件是控制硬件设备运行的基础软件，它驻留在硬件内部，负责初始化、管理和协调硬件资源。在工控机领域，固件的重要性...
【TC58NC6690-SSS6692-B4-Toshiba(东芝) 量产工具】是一款专为Toshiba（东芝）生产的SSS6692存储芯片设计的量产工具。在理解这个工具之前，我们需要先了解几个基本概念。 **一、SSS6692存储芯片** SSS6692是Toshiba...