All records within x minutes of each other

[Scott Marquardt]

Consider a table that holds Internet browsing history for users/machines,
date/timed to the minute. The object is to tag all times that are separated
by previous and subsequent times by x number of minutes or less (it could
vary, and wouldn't necessarily be a convenient round number). This will
enable reporting "active time" for users (a dubious inference, but hey).

There are a lot of derivative ways of seeing this information that might be
good to get to. What's the fist and last of these sets of times? What
percentage of a given period is spanned by active times, and not? What is
the average duration of such periods? What is the average interval between
web hits during such periods? During other times?

Blah, blah. The basic problem is my principal problem. I don't have much
experience with cursors, but from what I understand it would be very good
indeed to spare them, given the number of records I anticipate working
with.

I'd be glad of any pointers.

--

Scott

[--CELKO--]

Basics: Time comes in durations, so evetns have a start and stop time.
The really good stuff can be found at the University of Arizona
website where they have a PDF copy of the Rick Snodgrass book and his
research paper.

[AK]

There are many way to accomplish this, for instance:

get a set of beginnings of active periods:

select ...
from events where not exists(
---- no events in the preceding ... minutes
)
and exists(
---- events in the followinging ... minutes
)

get a set of endings of active periods:

select ...
from events where not exists(
---- no events in the following ... minutes
)
and exists(
---- events in the preceding ... minutes
)

That done, you need to match every beginning to its corresponding end.
This is very simple using row_number() available in SQL 2005
In earlier versions, you can either emulate row_number() using
identity() column in a result set, or use a join condition like this:

....
from beginnings b join ends e on b.time<e.time
where not exists(select 1 from beginnings b1 where b.time< b1.time and
b1.time<e.time)
and not exists(select 1 from endings e1 where b.time< e1.time and
e1.time<e.time)

[Ross Presser]

On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:

Basics: Time comes in durations, so evetns have a start and stop time.
The really good stuff can be found at the University of Arizona
website where they have a PDF copy of the Rick Snodgrass book and his
research paper.

Joe, without passing judgement on your basic assertion "Time comes in
durations", you must be aware that web requests, like most other events in
computing, are not ever logged as durations, but as instants. Unless you
intend to win over the writers and administrators of every web server on
the planet, we're going to have to damn well DEAL with them as events, not
durations.

[Scott Marquardt]

Ross Presser opined thusly on Aug 30:

On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:

Basics: Time comes in durations, so evetns have a start and stop time.
The really good stuff can be found at the University of Arizona
website where they have a PDF copy of the Rick Snodgrass book and his
research paper.

Joe, without passing judgement on your basic assertion "Time comes in
durations", you must be aware that web requests, like most other events in
computing, are not ever logged as durations, but as instants. Unless you
intend to win over the writers and administrators of every web server on
the planet, we're going to have to damn well DEAL with them as events, not
durations.

Well, OTOH the telos of the click is to digest content, which consumes time
(duration). But we don't have eyeball trackers on our desktops yet, so
we're left to infer from events that there's subsequent eyeball activity --
users don't do http GETs for no reason.

But that's an abstraction -- a problematic one -- whereas indeed these are
events. Still, like vertices on a triangle, to get from one to another of
these moments you have to traverse the length of a side.

Grats to Joe for the sensible reply. I'm always slapping my forehead. Just
now I'm having trouble even with that simplicity. :-/

--

Scott

[Greg D. Moore \(Strider\)]

"Ross Presser" <rp******@NOSPA Mgmail.com.inva lid> wrote in message
news:1w******** ********@rosspr esser.dyndns.or g...

On 30 Aug 2005 13:28:36 -0700, --CELKO-- wrote:

Basics: Time comes in durations, so evetns have a start and stop time.
The really good stuff can be found at the University of Arizona
website where they have a PDF copy of the Rick Snodgrass book and his
research paper.

Joe, without passing judgement on your basic assertion "Time comes in
durations", you must be aware that web requests, like most other events in
computing, are not ever logged as durations, but as instants. Unless you
intend to win over the writers and administrators of every web server on
the planet, we're going to have to damn well DEAL with them as events, not
durations.

You might as well give up. Joe and I had this argument I think it was a
year ago and he was just as wrong then as he is now.

[Scott Marquardt]

AK opined thusly on Aug 30:

There are many way to accomplish this, for instance:

Thanks. It's been my only clue, and was sure sensible. I thanked Joe
earlier, remiss. However, I owe him thanks for leading me toward that big
paper on temporal SQL -- that stuff's dynamite. It certainly gives an
answer to the duration/event argument: "yes." ;-)

NOW my fun is that the times I'm working with have only minute precision,
so I often get several identical times for a given user (the most logical
grouping). This presents all kinds of problems for the kinds of reporting
I'm looking at. It's fine for determining periods of activity when one's
after a minute mark, but anything beyond that starts getting hairy. Sure
wish I had even second precision. The client-side use of shdoc401.dll
namespace(34) seems to preclude this, sadly.

--

Scott

[--CELKO--]

>> you must be aware that web requests, like most other events in computing, are not ever logged as durations, but as instants.<<

Do not confuse the recording of the data with the data model. Think
about a sign-in and sign-out sheet or timeclock. Each line is "half a
fact"; the whole fact is the duration spent on the job. In this case,
the user logs onto a site, stays there for x-minutes. He is not there
for a Chronon (that is the term for a point in time in temporal
databases). So his table MIGHT look like this:

CREATE TABLE Browsing
(user_id VARCHAR(30) NOT NULL,
website VARCHAR(255) NOT NULL,
login_time TIMESTAMP DEFAULT CURRENT_TIMESTA MP NOT NULL,
logout_time TIMESTAMP, -- null means still there
CHECK (login_time <= logout_time), -- less than one minute problem
PRIMARY KEY (user_id, website, login_time));

The real problem in this situaiton is having to round to the minute.
We can force a convention on the stopping time to keep it away from the
starting point by a bit less than one munute -- ('yyyy-mm-ddThh:mm:00'
to 'yyyy-mm-ddThh:mm:59.997 ').
Ever read the paradoxes of Zeno? He went thru what happens when you
believe in Chronons. He lived in a time when Greek math was "integer
only" and without a continuum.

[Scott Marquardt]

--CELKO-- opined thusly on Aug 31:

The real problem in this situaiton is having to round to the minute.
We can force a convention on the stopping time to keep it away from the
starting point by a bit less than one munute -- ('yyyy-mm-ddThh:mm:00'
to 'yyyy-mm-ddThh:mm:59.997 ').

As in my further woes (see recent reply in thread), at issue is the
adequacy of the data in describing the phenomena In this case, users cannot
simultaneously click a mouse on more than one thing -- though it's not
impossible to identify ways that http GETs can occur concurrently on one
machine under one user's context. At any rate, it should be obvious that
minute precision for web browsing event recording is insanely coarse. But I
doubt anyone planned for local Internet History to be used for purposes I'm
stretching it to. A proxy server is a choke-point that allows for more
precise dating, because it's an ideal platform for doing so. A Microsoft
DLL is not necessarily designed to meet needs its coders never had in mind,
alas.

Dang, if they'd only gone to second precision. I'd be content with that, I
swear! ;-)

--

Scott

[Anith Sen]

You are fabricating an example to suit your definitions while makeing no
effort to distingush between instants and intervals. For someone who
recommends Snodgrass' work, you should really read chapters 3 and 11 of his
book.

--
Anith