Re: sql comparison between records

["R. Bruce Hoffman, Jr." <rbruceh@xxxxxxxxxxxxx>]

-----Original Message-----
From: Joel Fritz <JFritz@sharperimage.com>
To: 'MIDRANGE-L@midrange.com' <MIDRANGE-L@midrange.com>
Date: Wednesday, July 25, 2001 11:35 AM
Subject: RE: sql comparison between records


>I need to calculate the average duration between records for records that
>can be grouped by key.  The real world application is mean time between
>clicks in a web site session.  Regardless, it's kind of interesting in the
>abstract, IMHO.

here, here...

and to that end... a brute force approach....

consider the original file as --> create table theFile (key char(1), ts
timestamp)

This file is populated with the appropriate data. We need a temporary table
to handle this

--> create table tempFile (key char(1), ts1 timestamp, ts2 timestamp)

Now we need to create an initial population in the temporary table

--> insert into tempFile (select key, ts, ts from theFile)

The duplicate ts could be omited and the field ts2 filled with nulls if we
want to include the field names in the insert statement. The results will be
the same.

Now we need to get the next highest timestamp from theFile for each
timestamp in tempFile that exists

--> update tempFile t set t.ts2 = (select min(i.ts) from theFile i where
i.ts > t.ts1 and i.key = t.key group by i.key)

When we are done with this statement, each record in the temporary file has
a timestamp (ts1) and then next highest timestamp (ts2) or a null for that
key

Now let's answer our original question:

--> select key, avg(ts2 - ts1) from tempFile where ts2 is not null group by
key


Q.E.D.

===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified Specialist - AS/400 Administrator
 -- IBM Certified Specialist - RPG IV Developer

"America is the land that fought for freedom and then
  began passing laws to get rid of it."

     - Alfred E. Neuman



+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---