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语句该怎么写呢?

解决方案 »

  1.   

    补充,要算出某段时间内共有多少个visit.
      

  2.   

    要求计算出某一段时间内的visit总数
      

  3.   

    表结构
    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)
      

  4.   

    其中需要按照unique_id来分辨是否是同一个客户端
      

  5.   

    -- IT WILL MAKE SENSE TO YOU?   JUST USE GROUP BY+COUNT(COOKIE_ID) WILL BE OK FOR YOUR REQUIREMENT.
    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
      

  6.   

    楼上可能没明白我的意思。
    每访问一个页面,或者叫page view,会有一条这样的log记录。
    比如你登陆了一个网站,访问了该网站的10个网页,log中会生成10条记录,时间是不同的,但cookie id是相同的。而需求中需要计算的visit的概念,不是一个visit,而是从你访问第一个页面开始,30分钟内,在该网站所有的访问,都算作一个visit.
    现在的需求是统计visit,而不是page view.
      

  7.   

    写错个关键部分,visit 和page view不是同一概念
    30分钟内同一个cookie id不管有多少page view,都算作一个visit
      

  8.   

    妹子,你给我一条数据要我怎么统计visit呢?
    visit 和page view是你的需求术语,不是都明白的。最简单的办法就是贴出你的源数据和相应你想要的结果。
      

  9.   

    2008-09-27##00:00:09##203.198.94.41##-##GET##/en/web/nscuploader/Micro site/NissanTiida/02.html####200##2833##-##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.1222444809396912##-##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.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 是正确的
      

  10.   

    -- 我看得眼睛花了,你要负责。就请吃个饭吧。
    -- 总共的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