2008-09-27##00:00:22##202.86.178.41##-##GET##/en/web/models/TIIDA/Colors and Trims/color.htm####200##7631##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/images/leftnav.swf##-##202.86.178.41.1222444822787113##-##www.nissan.com.hk
上面是一段Apache server 的log信息,其中用##隔开的每一项基本都对应数据库里面的一列,记录了这次page view的各种客户端信息。
上例中,2008-09-27##00:00:22 是访问时间,202.86.178.41.1222444822787113是用户的cookie id.
需求是,每个相同的cookie id在30分钟内访问的记录算作一个visit。
sql语句该怎么写呢?
上面是一段Apache server 的log信息,其中用##隔开的每一项基本都对应数据库里面的一列,记录了这次page view的各种客户端信息。
上例中,2008-09-27##00:00:22 是访问时间,202.86.178.41.1222444822787113是用户的cookie id.
需求是,每个相同的cookie id在30分钟内访问的记录算作一个visit。
sql语句该怎么写呢?
Column Name ID Pk Null? Data Type DefaultID 1 1 N NUMBER (19)
VISIT_DATE 2 N DATE
VISIT_TIME 3 N DATE
C_IP 4 N VARCHAR2 (20)
CS_USERNAME 5 Y VARCHAR2 (20)
CS_METHOD 6 Y VARCHAR2 (20)
CS_URI_STEM 7 Y VARCHAR2 (500)
CS_URI_QUERY 8 Y VARCHAR2 (1000)
SC_STATUS 9 Y VARCHAR2 (20)
SC_BYTES 10 Y VARCHAR2 (20)
CS_VERSION 11 Y VARCHAR2 (20)
USER_AGENT 12 Y VARCHAR2 (1000)
REFERER 13 Y VARCHAR2 (1500)
PAGE_ID 14 Y VARCHAR2 (1000)
UNIQUE_ID 15 Y VARCHAR2 (50)
USER_ID 16 Y VARCHAR2 (50)
SESSION_ID 17 Y VARCHAR2 (50)
IMPORT_TIME 18 Y DATE sysdate
SOURCE 19 Y VARCHAR2 (50)
NSC_CODE 20 Y VARCHAR2 (50)
WEBSITE_TYPE 21 Y VARCHAR2 (20)
LANGUAGE_CODE 22 Y VARCHAR2 (20)
SQL> SELECT TO_DATE(REPLACE(SUBSTR('##' || STR,
2 INSTR('##' || STR, '##', 1, 1) + 2,
3 INSTR('##' || STR, '##', 1, 3) -
4 INSTR('##' || STR, '##', 1, 1) - 2),
5 '##',
6 ' '),
7 'YYYY-MM-DD HH24:MI:SS') MYDATE,
8 SUBSTR('##' || STR,
9 INSTR('##' || STR, '##', -1, 3) + 2,
10 INSTR('##' || STR, '##', -1, 2) -
11 INSTR('##' || STR, '##', -1, 3) - 2) COOKIE_ID
12 FROM (SELECT '2008-09-27##00:00:22##202.86.178.41##-##GET##/en/web/models/TIIDA/Colors and Trims/color.htm####200##7631##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/images/leftnav.swf##-##202.86.178.41.1222444822787113##-##www.nissan.com.hk' STR
13 FROM DUAL);MYDATE COOKIE_ID
------------------------------ ------------------------------
9/27/2008 12:00:22 AM 202.86.178.41.1222444822787113
每访问一个页面,或者叫page view,会有一条这样的log记录。
比如你登陆了一个网站,访问了该网站的10个网页,log中会生成10条记录,时间是不同的,但cookie id是相同的。而需求中需要计算的visit的概念,不是一个visit,而是从你访问第一个页面开始,30分钟内,在该网站所有的访问,都算作一个visit.
现在的需求是统计visit,而不是page view.
30分钟内同一个cookie id不管有多少page view,都算作一个visit
visit 和page view是你的需求术语,不是都明白的。最简单的办法就是贴出你的源数据和相应你想要的结果。
2008-09-27##00:00:17##202.86.178.41##-##GET##/en/web/models/TIIDA/Safety/10200.htm####200##17396##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/images/leftnav.swf##-##202.86.178.41.1222444817661591##-##www.nissan.com.hk
2008-09-27##00:00:17##202.86.178.41##-##GET##/en/web/models/TIIDA/Safety/10200.htm####200##17396##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/images/leftnav.swf##-##202.86.178.41.1222444817902324##-##www.nissan.com.hk
2008-09-27##00:00:19##202.86.178.41##-##GET##/en/web/models/TIIDA/Safety/10200_40503.htm####200##2806##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/models/TIIDA/Safety/10200.htm##-##202.86.178.41.1222444819167708##-##www.nissan.com.hk
2008-09-27##00:00:22##202.86.178.41##-##GET##/en/web/models/TIIDA/Colors and Trims/color.htm####200##7631##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/images/leftnav.swf##-##202.86.178.41.1222444822787113##-##www.nissan.com.hk
2008-09-27##00:00:23##202.86.178.41##-##GET##/en/web/models/TIIDA/Colors and Trims/color.htm####206##7631##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/images/leftnav.swf##-##202.86.178.41.1222444823006564##-##www.nissan.com.hk
2008-09-27##00:00:24##202.86.178.41##-##GET##/en/web/models/TIIDA/Colors and Trims/6273_24250.htm####200##11730##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/models/TIIDA/Colors%20and%20Trims/color.htm##-##202.86.178.41.1222444824255217##-##www.nissan.com.hk
2008-09-27##00:00:27##203.198.94.222##-##GET##/eng/model_main.php##?id=32##302##305##-##Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Foxy/1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)##-##-##203.198.94.222.1222444827270751##-##www.nissan.com.hk
2008-09-27##00:00:28##203.198.94.222##-##GET##/error/error.html####200##1038##-##Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; Foxy/1; .NET CLR 1.1.4322; .NET CLR 2.0.50727)##-##-##203.198.94.222.1222444828648986##-##www.nissan-core.com
2008-09-27##00:00:48##219.95.213.67##-##GET##/index.htm####200##802##-##Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; SIMBAR={3E417C3D-643E-4634-A07B-4C3F625D047C}; InfoPath.2)##http://www.google.com/search?hl=zh-TW&q=nissan&lr=##-##219.95.213.67.1222444848382710##-##www.nissan.com.hk
2008-09-27##00:00:49##219.95.213.67##-##GET##/en/web/homepage/index.htm####200##3331##-##Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; SIMBAR={3E417C3D-643E-4634-A07B-4C3F625D047C}; InfoPath.2)##-##-##219.95.213.67.1222444849009742##-##www.nissan.com.hk
2008-09-27##00:00:53##202.86.178.41##-##GET##/en/web/models/TIIDA/Specifications/121297715894384230.htm####200##11647##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/images/leftnav.swf##-##202.86.178.41.1222444853820037##-##www.nissan.com.hk
2008-09-27##00:00:54##202.86.178.41##-##GET##/en/web/models/TIIDA/Specifications/121297715894384230_1.htm####200##5363##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/models/TIIDA/Specifications/121297715894384230.htm##-##202.86.178.41.1222444854662643##-##www.nissan.com.hk
2008-09-27##00:45:09##202.86.178.41##-##GET##/en/web/models/TIIDA/Gallery/gallery.htm####304##-##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/images/leftnav.swf##-##202.86.178.41.1222444869508304##-##www.nissan.com.hk
2008-09-27##00:45:10##202.86.178.41##-##GET##/en/web/models/TIIDA/Gallery/####403##308##-##Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; Alexa Toolbar; mxie; .NET CLR 1.1.4322)##http://www.nissan.com.hk/en/web/models/TIIDA/Gallery/gallery.htm##-##202.86.178.41.1222444870266677##-##www.nissan.com.hk上面是部分源数据
关注一下ip为202.86.178.41这个访问者他第一登陆是在2008-09-27##00:00:09,那么从这个时间起的30分钟,也就是截止到2008-09-27##00:30:09,不管有多少条关于这个访问者的记录,都算作一个Visit在数据中我们可以看到,倒数第三条数据,时间已经是2008-09-27##00:45:09,超出了刚才的那个30分钟,那就要以这个为起点,重新计算30分钟,也就是到01:15:09这段时间内,所有关于这个访问者的访问,我们都记录为下一个visit
按照这种方法计算,在这段数据中,202.86.178.41这个访问者访问了2次,即两个visit
203.198.94.222这个访问者有一个visit
219.95.213.67这个访问者有一个visit所以统计结果为4 是正确的
-- 总共的VISIT应该是5,因为还有个IP你没看到: 203.198.94.41SQL> SELECT SUM(DIFF_MINUTES) TOTAL_VISIT
2 FROM (
3 SELECT MYIP,
4 DECODE(CEIL((MAX(MYDATE)-MIN(MYDATE))*24*60*60/(60*30)),0,1,CEIL((MAX(MYDATE)-MIN(MYDATE))*24*60*60/(60*30))) DIFF_MINUTES
5 FROM (
6 SELECT TO_DATE(REPLACE(SUBSTR('##' || STR,
7 INSTR('##' || STR, '##', 1, 1) + 2,
8 INSTR('##' || STR, '##', 1, 3) -
9 INSTR('##' || STR, '##', 1, 1) - 2),
10 '##',
11 ' '),
12 'YYYY-MM-DD HH24:MI:SS') MYDATE,
13 SUBSTR('##' || STR,
14 INSTR('##' || STR, '##', 1, 3) + 2,
15 INSTR('##' || STR, '##', 1, 4) -
16 INSTR('##' || STR, '##', 1, 3) - 2) MYIP,
17 SUBSTR('##' || STR,
18 INSTR('##' || STR, '##', -1, 3) + 2,
19 INSTR('##' || STR, '##', -1, 2) -
20 INSTR('##' || STR, '##', -1, 3) - 2) COOKIE_ID
21 FROM TABLE_NAME TT
22 )ZZ
23 GROUP BY MYIP
24 )YY;TOTAL_VISIT
-----------
5