Unique Visitors SQL Query 1

[Giorgio79]

Hi I have a table with the following columns and some sample data:


IP Timestamp Page
123.12.12 2006.05.13. 18:22:27 Index
123.12.12 2006.05.13. 18:23:24 Contact
123.12.12 2006.05.13. 18:24:07 Index
123.12.12 2006.05.13. 18:25:45 Page1
92.12.1.2 2006.05.13. 18:26:00 Index
92.12.12 2006.05.13. 18:26:02 Page1
32.12.1.1 2006.05.13. 18:30:02 Page1

Basically, this is a log from my webstats, and I would like to make the following SQL query:

Count distinct IP addresses for a day, and if an IP is repeated within thirty minutes of its first occurrence, count it only as one. If this IP address occurs again after this thirty minutes count it as new visit and perform the same check.

Basically, with this query, I would get the unique visitors to my site.

Can someone please help?

Thank you,
Gyuri

 

[r937]

... if an IP is repeated within thirty minutes of its first occurrence, count it only as one.

are you sure?


what if the visits were like this --

V...7min...V...7min...V...7min...V...7min...V...7min...V

the way i would look at it, that's only one visit

by your definition, it's two

r937.com | rudy.ca

 

[Giorgio79]

Hi,

I would like to thank you for your kind response.

You are right that it can be considered as one visit as well. In fact that would make my life easier as I would just count distinct IPs for the entire day and that is it.

Yet, most webstatistics companies put this 30 minute limit for 1 unique visit, and even they admit that it is not perfect.

Please let me know if you have any questions.

Giorgio

http://www.bannerkozpont.hu

 

[Giorgio79]

Hi,

Would someone know if this is even possible in mysql?

Is there a time function that I can use in the select statements perhaps, like datediff?

Or could I use "interval" to group identical IPs into 30 minute groups and count the number of these groups?

 

[cheerio]

This is unlikely to be pure ANSI SQL and it may need tweaking for mysql but it should indicate a possible approach.

SELECT IP, TimeStamp,
IsNull(
(SELECT MAX(Timestamp)
 FROM Webstats W2
WHERE W2.IP=W1.IP
AND W2.Timestamp < W1.Timestamp), ‘1 Jan 1901’)
As PrevVisit
FROM Webstats W1
WHERE Timestamp – PrevVisit > ’30:0’

What we are doing is finding the datetime of the previous visit and deeming that to have been 1 Jan 1901 if there has not been a previous visit.

Depending on syntax I would probably use DATEDIFF in the final WHERE clause.

The query returns a list of "unique" visits which you could then choose to count.

 

[Giorgio79]

Dear cheerio,

Thank you very much for your kind response. I truly appreciate it. I will start experimenting with it now.

Thank you,

Giorgio
Bannerközpont

http://www.bannerkozpont.hu

 

[cheerio]

good luck and thanks for the star