- 浏览: 251612 次
- 性别:
- 来自: 上海
文章分类
最新评论
-
无它唯勤:
...
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 2423"INSERT INTO AUDIT_TASK_LO ... -
Oracle 存储过程
2007-11-07 11:53 613存储过程是指数据库中已编译的可调程序,它作为PL/SQ ... -
Oracle 安装注意事项
2007-11-07 22:17 681如果你的机器上存在其它版本的Oracle,请先删除以前版本的安 ... -
Oracle 游标
2007-11-11 15:31 1509Oracle游标,从字面理解就是游动的光标。用数据库语言来描述 ... -
ORACLE 异常汇集
2007-11-15 11:53 622------------------------------- ... -
存储过程 Update 返回改更后的 结果
2007-12-18 19:54 1362今天做邮件群发系统开发,遇到这样一个问题:更改数据库一 ... -
Oracle 返回 结果集
2007-12-19 19:26 700过程返回记录集: CREAT ... -
错误的
2007-12-20 19:47 561CREATE OR REPLACE PACKAGE BODY ... -
随机 返回 一条符合条件的记录
2008-01-21 10:29 713SELECT * FROM anti_shields SAMP ... -
ORACLE NOCOPY
2008-01-21 15:23 837PL/SQL中对out,in out参数使用的?默认形 ... -
ORACLE 存储过程 邮件服务器 反屏蔽
2008-01-22 12:09 703邮件群发反屏蔽的原理是这样的: 1.每封邮件内容不同( ... -
Oracle dblink
2008-02-20 14:06 628Create Database Link LinkName C ... -
让oracle做定时任务
2008-03-11 14:42 1094今天用oracle的定时任务来实现定时检测。于是goole了一 ... -
ORACL 查看连接
2008-03-14 18:16 641select schemaname,osuser,machin ... -
Oracle 常用函数
2008-03-28 14:42 6141.SUBSTR(string,start[,end]) ... -
Oracle 定时任务 JOB
2008-04-02 17:00 945众所周知,一般操作系统会提供定时执行任务的方法,例如:Uni ... -
copy表时丢失索引
2008-04-07 20:24 642今天发现了一个大问题,copy表时,会丢失索引.这导制 ... -
Oracle decode用法
2008-04-23 13:18 17181、Windows NT4.0+ORACLE 8.0.4 ... -
存储过程-a
2008-05-11 23:51 648create or replace PROCEDURE EMA ... -
存储过程-c
2008-05-11 23:52 735create 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)是当今最常见的设备连接方式之一,提供了高速的数据传输能力,使得示波器捕获的波形数据能够快速地传送到...
- **环境条件**:环境温度应在0℃~55℃之间(运转时),-20℃~65℃(存储时);环境湿度不超过90%RH(运转时),并在室内使用,远离阳光直射;海拔高度限制在1000米以下;振动限制在5.9m/s²以下(10Hz~55Hz)。 ...
L-BFGS-B则在此基础上加入了边界约束处理,使得优化过程可以在指定的参数空间范围内进行,这对许多实际问题如物理、化学、工程中的优化计算非常有用。 在FORTRAN实现中,L-BFGS-B代码通常包括以下几个核心部分: 1...
实施公司 sap b1 9.2存储过程 --------------------------------------------------------【1】物料主数据--------------------------------------------------------- ------------------------1.1物料主数据中默认...
1、备份Oracle数据库表时添加动态时间戳,解决了... 本存储过程解决备份表名称时动态添加时间戳,例如表名称 TB_B20240106; 8、资源内容有: 8.1、P_TIMER_BAK_TABLE定时备份表 表名动态拼接时间戳.sql 8.2、Readme
测试设备(如卡笼、测试板、插座、接线、存储容器等)的离子污染释放应得到控制,以避免测试结果出现误差。 2.5 去离子水 使用至少在室温下具有1兆欧-厘米电阻率的去离子水,以确保测试过程的纯净度。 3. 测试条件...
2. **刷机工具**:这些工具可以帮助你将固件文件安全地传输到设备的存储中,并执行刷机过程。常见的工具有Fastboot、SP Flash Tool、Odin等,具体取决于设备的制造商和型号。 3. **刷机教程**:这个教程应该详细指导...
4. **创建数据库**:Tigase服务器通常使用MySQL或PostgreSQL作为后端存储。你需要预先创建数据库并设置相应的用户权限。 5. **初始化数据库**:运行Tigase服务器的初始化脚本,将数据库结构导入到你创建的数据库中...
- **拆卸步骤**:与安装过程相反,先拆卸上盖,松开涨环紧固螺钉,然后通过特殊工具逐步拆卸编码器。 5. **电缆屏蔽处理**: 编码器的电缆屏蔽线应通过金属屏蔽环与外壳接触,以保证良好的电磁兼容性。 此说明书...
对于KLM4G1FETE-B041,这可能包括其存储容量(例如4GB),数据传输速率(如HS400模式下的最大速度),读写速度,以及工作温度范围。这些参数直接影响到设备的运行效率和存储能力。电源电压也是一个关键指标,它规定...
标题“SV1-H611A-B”暗示我们正在讨论一款特定的工控机主板的固件,该主板型号为SV1-H611A-B。固件是控制硬件设备运行的基础软件,它驻留在硬件内部,负责初始化、管理和协调硬件资源。在工控机领域,固件的重要性...