----- Original Message -----
From: Jeff Small
To: SQL
Sent: Monday, April 19, 2004 12:23 PM
Subject: Help me thru this...
Okay, you dump a logfile into a table...
IP address | Date | Time | Page
1.1.1.1 | 01/01/01 | 08:34:23 | index.cfm
1.1.1.1 | 01/01/01 | 08:34:26 | page2.cfm
1.1.1.2 | 01/01/01 | 08:34:27 | index.cfm
1.1.1.1 | 01/01/01 | 08:34:29 | page3.cfm
1.1.1.2 | 01/01/01 | 08:34:32 | page2.cfm
etc...
Now, let's say you want to perform some SQL on this.
You want to list IP addresses that visit for 15 minutes. (first visit =
start time, last visit = end time)
How would you do this? Query of a Query? Subqueries? How would you determine
what would be the best way to iterate thru this data?
Let's say we've performed a query that groups by IP addresses. Now, the
problem with this is, over a month (we're looking at 30 days of logs, let's
say) an IP address could come back several times. Do you go ahead and group
by first, then query that "grouped by" query? Will the SQL iterate thru
those grouped rows? In other words, let's assume that I've grouped that
output and I've got something similar to the following:
IP address | Date | Time | Page
Group "1"
1.1.1.1 | 01/01/01 | 08:34:23 | index.cfm
1.1.1.1 | 01/01/01 | 08:34:26 | page2.cfm
1.1.1.1 | 01/01/01 | 08:34:29 | page3.cfm
1.1.1.1 | 01/01/01 | 13:16:53 | index.cfm
1.1.1.1 | 01/01/01 | 13:16:56 | page2.cfm
1.1.1.1 | 01/01/01 | 13:16:59 | page3.cfm
1.1.1.1 | 01/02/01 | 09:35:21 | index.cfm
1.1.1.1 | 01/02/01 | 09:35:22 | page2.cfm
1.1.1.1 | 01/02/01 | 09:35:23 | page3.cfm
Group "2"
1.1.1.2 | 01/03/01 | 10:24:23 | index.cfm
1.1.1.2 | 01/03/01 | 10:24:26 | page2.cfm
1.1.1.2 | 01/03/01 | 10:24:29 | page3.cfm
1.1.1.2 | 01/04/01 | 11:45:27 | index.cfm
1.1.1.2 | 01/04/01 | 11:45:28 | page2.cfm
1.1.1.2 | 01/04/01 | 11:45:29 | page3.cfm
Would I perform a subquery on this to determine how many visits from each IP
address spanned 15 minutes?
Okay, so let's say, out of ALL of this, what I'm looking for is the
following result...
1.1.1.1 | 01/01/01 - 2 visits
1.1.1.1 | 01/02/01 - 1 visit - 3 total visits
1.1.1.2 | 01/03/01 - 1 visit
1.1.1.2 | 01/04/01 - 1 visit - 2 total visits
What's the approach for doing something like this? Anyone know of how to
begin?
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
