| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2002-11-15 : 09:51:33
|
| The table CREATE TABLE PageHits(UserID INT ,URL VARCHAR (300) ,URLDATE DATETIME )tracks what web pages users load from on online course. The time a user spends visiting any single page is the delta T between URLDATE for that page and URLDATE for the next page loaded by that user.I'd like to build a query that adds another column "T2" which is the time that a user loads the next page. (I can compute T2-T1 to get the time spent on a page).Here's what I've come up with, but it seems to me there may be a faster way???select P.*, Pnext.UrlDate as T2, datediff(minute, P.UrlDate, Pnext.UrlDate) as DeltaT from PageHits P, PageHits Pnext Where P.Userid = Pnext.Userid and Pnext.UrlDate = (SELECT min(UrlDate) FROM PageHits WHERE UrlDate > P.UrlDate and UserID=P.UserID)A view could be created based on this query to enable other stored procedures to produce reports by user or reports on average page viewing time.Any suggestions on a better way to arrive at T2 for each row?Sam |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-11-15 : 09:58:53
|
| Not really, but a couple of flaws are that if a visitor doesn't visit the next page, or if he visits a page next week, or next year, then the time spent on the page becomes a bit meaningless. |
 |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-15 : 10:07:33
|
You really should move away from the t-sql joins and begin using the ansi syntax. The old t-sql style is no longer supported.I would write the query like ...select userid, url, urldate, nexturldate, deltafrom ( select userid, url, urldate, (select min(urldate) from pagehits where urldate > p.urldate and userid = p.userid) as nexturldate from pagehits p) as a EDIT: and what mist said ...Jay White{0}Edited by - Page47 on 11/15/2002 10:09:05 |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-11-15 : 10:10:56
|
| Yes. But it's a course, and there are several pages tagged as required. Each required page is followed by several not required.Even if a required page were off, the information is interesting for statistical reasons, or to identify users who click through rapidly.------------I can see this is an opportunity for me to study execution plans.WHERE P.Userid = Pnext.UserID suggests an inner join as another technique with possibly better performance?Or adding a sequential ID column per user (time ordered) so any user next record is where Pnext.Seq = P.Seq+1 ??Sam |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-11-15 : 10:18:33
|
| If you're recording some kind of user session information in the database, then it can be possible to log the datestamp whenever you update the session information for that user, for that session. If you then ensure that a session is only valid for a certain amound of time and within one login then you could have the beginnings of a page-logging thing. You could, I suppose, in theory have a trigger that updated page click times for a user and session whenever their details where logged to the database. All of that of course assumest that the database is queried /updated every time someone clicks through a page. Statistically though, whatever way you do it I think for any kind of useful information you would need to include within your code a method to check for a maximum amount of time between clicks, as well as a minimum. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-11-15 : 10:19:31
|
| Jay,The query you posted has a better execution plan.Thanks,Sam |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2002-11-15 : 10:29:32
|
| You're right and I understand about the pitfalls tracking session oriented webs. I'm just a little ahead of a public web in that this is a private course which means I have a minor dose of this problem as opposed to a megadose.The query gives us statistical informaition. Removing the tails (like 90 minutes spent on a page when someone goes to lunch) is a problem. Even if a session expiration writes a log entry to provide a termination time, you don't know if the user spent the time reading the page or talking on the phone.The only way to know if a user really retains page content is to test them (which we don't do). We're working on a peripheral device to keep their eyes on the monitor in the meantime. Sam |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2002-11-15 : 10:53:47
|
quote:
The only way to know if a user really retains page content is to test them (which we don't do). We're working on a peripheral device to keep their eyes on the monitor in the meantime.
I believe one of these features in A Clockwork Orange. You could perhaps copy that design, though you may need to adapt it to allow control of the mouse whilst not moving the head. |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-11-15 : 11:17:52
|
| "I was cured all right."Jay White{0} |
|
|
|
|
Interesting sort of query