Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: avg time function ?

Re: avg time function ?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 01 Jul 1999 16:51:26 GMT
Message-ID: <37899a19.71151710@newshost.us.oracle.com>


A copy of this was sent to h0444vcs_at_rz.hu-berlin.de (if that email address didn't require changing) On Thu, 01 Jul 1999 16:10:47 GMT, you wrote:

>Dear Participants,
>
>I have a table of web log file entries :
>
>weblog (session_id, host, date, url)
>
>The date is stored as DD MON YYYY HH24 MI SS .
>I want to compute the average time spent on a page
>for each user session.
>
>Is there a function in oracle like
>
>time_between (entry(x), entry(x+1)) respectively a function
>that gives me the average time of time_between ?
>
>I'd be grateful for your hints !
>
>Markus Banach

you could do something like this last query:

SQL> create table weblog( session_id int, dt date ); Table created.

SQL> insert into weblog values ( 1, sysdate );
SQL> insert into weblog values ( 1, sysdate+1/24/60/60*4 );
SQL> insert into weblog values ( 1, sysdate+1/24/60/60*7 );

SQL> insert into weblog values ( 2, sysdate );
SQL> insert into weblog values ( 2, sysdate+1/24/60/60*10 ); SQL> insert into weblog values ( 2, sysdate+1/24/60/60*13 );

SQL> alter session set nls_date_format = 'dd-mon-yyyy hh24:mi:ss'; Session altered.

SQL> select * from weblog
  2 /

SESSION_ID DT

---------- --------------------
         1 01-jul-1999 12:46:24
         1 01-jul-1999 12:46:28
         1 01-jul-1999 12:46:31
         2 01-jul-1999 12:46:24
         2 01-jul-1999 12:46:34
         2 01-jul-1999 12:46:37

6 rows selected.

SQL>
SQL> select a.session_id, a.dt, b.dt, (b.dt-a.dt)*24*60*60   2 from weblog a, weblog b

  3   where a.session_id = b.session_id
  4     and a.dt < b.dt
  5     and b.dt = ( select min(dt)
  6                      from weblog c
  7                     where c.session_id = a.session_id
  8                       and c.dt > a.dt )
  9 /
SESSION_ID DT                   DT                   (B.DT-A.DT)*24*60*60
---------- -------------------- -------------------- --------------------
         1 01-jul-1999 12:46:24 01-jul-1999 12:46:28                    4
         1 01-jul-1999 12:46:28 01-jul-1999 12:46:31                    3
         2 01-jul-1999 12:46:24 01-jul-1999 12:46:34                   10
         2 01-jul-1999 12:46:34 01-jul-1999 12:46:37                    3

SQL>
SQL> select a.session_id, avg( (b.dt-a.dt) * 24*60*60 )   2 from weblog a, weblog b

  3   where a.session_id = b.session_id
  4     and a.dt < b.dt
  5     and b.dt = ( select min(dt)
  6                      from weblog c
  7                     where c.session_id = a.session_id
  8                       and c.dt > a.dt )
  9 group by a.session_id
 10 /

SESSION_ID AVG((B.DT-A.DT)*24*60*60)

---------- -------------------------
         1                       3.5
         2                       6.5


>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Jul 01 1999 - 11:51:26 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US