× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  • Subject: Re: sql comparison between records
  • From: "R. Bruce Hoffman, Jr." <rbruceh@xxxxxxxxxxxxx>
  • Date: Wed, 25 Jul 2001 12:17:17 -0400

-----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
+---

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.