Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2006
    Posts
    6

    Unanswered: Unique Visitors SQL Query

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


    HTML Code:
    HTML Code:
    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,
    Giorgio

    -----
    www.bannerkozpont.hu
    Last edited by giorgio79; 05-23-06 at 11:04.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by giorgio79
    ... 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...7m in...V

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

    by your definition, it's two
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2006
    Posts
    6
    Hi r397,

    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.

    Giorgio

    www.bannerkozpont.hu
    Last edited by giorgio79; 05-26-06 at 04:33.

  4. #4
    Join Date
    Mar 2006
    Posts
    6
    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?
    Last edited by giorgio79; 05-26-06 at 04:34.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •