I've got a table named going to that appears such as this:
id | visitor_id | visit_time ------------------------------------- 1 | 1 | 2009-01-06 08:45:02 2 | 1 | 2009-01-06 08:58:11 3 | 1 | 2009-01-06 09:08:23 4 | 1 | 2009-01-06 21:55:23 5 | 1 | 2009-01-06 22:03:35
I wish to exercise a sql that may get the number of occasions a person visits within one session(successive visit's interval under one hour).
So, for that example data, I wish to get following result:
visitor_id | count ------------------- 1 | 3 1 | 2
BTW, I personally use postgresql 8.3. Thanks!
UPDATE: up-to-date the timestamps within the example data table. sorry for that confusion.
UPDATE: I do not care much when the option would be just one sql query, using store procedure, subquery etc. I only care how to make it happen :)
Now you ask , slightly ambiguous because you are making the assumption or needing the hrs are likely to start in a set point, i.e. an all natural query would also indicate that you have a result record of (1,2) for the visits between your hour of 08:58 and 09:58. You would need to "tell" your query the start occasions are for many determinable reason visits 1 and 4, or you'd obtain the natural result set:
visitor_id | count -------------------- 1 | 3 1 | 2 <- extra result starting at visit 2 1 | 1 <- extra result starting at visit 3 1 | 2 1 | 1 <- extra result starting at visit 5
That extra logic will probably be costly and too complicated for my fragile mind today, somebody much better than me at postgres can most likely solve this.
I'd normally wish to solve this by getting a sessionkey column within the table I possibly could inexpensively group by for perforamnce reasons, but gleam logical problem I believe. Drawing session info from timings appears harmful in my experience because I do not think that the consumer is going to be certainly drenched out after an hrs activity. Most session systems work by expiring the session over time of lack of exercise, i.e. it is extremely likely that the visit after 9:45 will probably be within the same session since your hourly period will probably be totally reset at 9:08.
The issue appears just a little fuzzy.
It will get more difficult as id 3 is at an hour or so of id 1 and a pair of, but when the consumer had visited at 9:50 then that could have been inside an hour of two although not 1.
You appear to become following a smoothed total - for any given visit, the number of visits are inside the following hour?
Possibly you ought to be requesting the number of visits possess a succeeding visit under an hour or so distant? If your visit is under an hour or so in the preceeding one then should it 'count'?
What exactly you most likely want is the number of chains have you got in which the links are under a random amount (therefore the hypothetical 9:50 visit could be incorporated within the chain that begins with id 1).