problem extracting count of visits

**Atli** · Mar 23 '10, 05:17 AM

Hey.

This is actually kind of tricky, because to do this you need to programmaticall y compare each row with previous rows. Which means you will need to be able to store data from a previous row to compare it with the current row. - That can be done using User Defined variables. They allow you to set a variable pretty much anywhere in a session and call them later in the same session.

So what you need to do is iterate through all the IP's, and for each IP you need to run through all the dates, counting each date that was recorded 30 minutes after the previous date for that IP.

The first step is to set up a query to iterate through all the IP's.
[code=sql]SELECT
ol.`ip`
FROM `logs` AS ol
GROUP BY ol.`ip`;[/code]
And then we add a user-defined variable to hold the Unix time-stamp (seconds since January 1'st, 1970) for the first date recorded for the current IP.
(Note that this all assumes the dates are in order, from oldest to newest!)
[code=sql]SELECT
ol.`ip`,
@last_date := UNIX_TIMESTAMP( ol.`date`) AS `initial_stamp`
FROM `logs` AS ol
GROUP BY ol.`ip`;[/code]
Note that the line I added there not only returns the value, but it also sets the @last_date to that value. The ":=" operator does that; it sets a variable, whereas a single "=" compares values.

Now we need to go through all the dates for each IP. That will require the use of a Correlated Subquery. Those are subqueries that reference tables outside the current query. (See the manual for examples.) -- Here we use it to create a subquery that only runs through the rows of the table, where the IP matches the IP of the outer query.
[code=sql]SELECT
ol.`ip`,
@last_date := UNIX_TIMESTAMP( ol.`date`) AS `initial_stamp` ,
(
SELECT
COUNT(il.`ip`)
FROM `logs` AS il
WHERE il.`ip` = ol.`ip`
) AS 'visits'
FROM `logs` AS ol
GROUP BY ol.`ip`;[/code]

And now, for the tricky part. To compare two values and perform different actions based on the results, we can use the IF function. - We compare the @last_date, who's initial value should always be the first date for that IP, to the date of the current entry for the IP. If it is more than 1800 seconds (30 minutes) away from the previous date, we have the IF() function return 1. If not, we have it return 0. - Then we use the SUM() function to add up the total result.
[code=sql]SELECT
ol.`ip`,
@last_date := UNIX_TIMESTAMP( ol.`date`) AS `initial_stamp` ,
(
SELECT
1 + SUM(
IF(UNIX_TIMESTA MP(il.`date`) - @last_date > 1800,
1, 0
)
)
FROM `logs` AS il
WHERE il.`ip` = ol.`ip`
) AS 'visits'
FROM `logs` AS ol
GROUP BY ol.`ip`;[/code]

Now there is only one thing left to do. Because we need the @last_date variable to always hold the date for the last row we examined, we need to set it somewhere in the subquery; somewhere after the IF() has been completed. - The only place we can do that is in the return statements in the IF itself. -- We just need to manupulate it so that the expression that sets the variable returns 1 on TRUE or 0 on FALSE.
[code=sql]SELECT
ol.`ip`,
@last_date := UNIX_TIMESTAMP( ol.`date`) AS `initial_stamp` ,
(
SELECT
1 + SUM(
IF(UNIX_TIMESTA MP(il.`date`) - @last_date > 1800,
(@last_date := UNIX_TIMESTAMP( il.`date`)) > 0,
(@last_date := UNIX_TIMESTAMP( il.`date`)) < 0
)
)
FROM `logs` AS il
WHERE il.`ip` = ol.`ip`
) AS 'visits'
FROM `logs` AS ol
GROUP BY ol.`ip`;[/code]

And there you have it. According to my tests on a very limited data-set, this works fine. You may want to do some more testing on a larger set if you plan on using this somewhere.

problem extracting count of visits

problem extracting count of visits

Comment