CODEWITHSUNDEEP
mysqlgroup-by
Jimmyb
Jimmyb

Reputation: 890

Query-clicks per 5 minutes

I am building a report to identify the number of visitors that click on specific buttons on my site. After analyzing my logs I'm finding that visitors will click the same button multiple times (sometimes up to 10 times) within a minute or two, which totally skews the results.

I am trying to create a query that will count each visitor (by ip) only once within a 5 minute timeframe. I have come up with the following, which gives me the total count, but I can't seem to find the right combination that will break it down into chunks of time. (datetime is of type date)

SELECT count(ip), ip, link, datetime  FROM `clicks` group by ip, link

Is there a simple way to accomplish this, or am I thinking about this incorrectly? I suppose I could group it by day if necessary, but I would really like to break it down more granularly if possible.

Any pointers in the right direction will be much appreciated!

Upvotes: 3

Views: 136

Answers (1)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

If you want counts of IPs per 5 minute interval today, try this:

SELECT COUNT(ip) ipcount,dt FROM
(
    SELECT
    ip,(datetime - INTERVAL MOD(UNIX_TIMESTAMP(datetime),300)) SECOND dt
    FROM `clicks`
    WHERE datetime >= (DATE(NOW()) + INTERVAL 0 SECOND)
) A
GROUP BY dt;

The expression DATE(NOW()) + INTERVAL 0 SECOND is midnight today

mysql> select DATE(NOW()) + INTERVAL 0 SECOND;
+---------------------------------+
| DATE(NOW()) + INTERVAL 0 SECOND |
+---------------------------------+
| 2012-06-05 00:00:00             |
+---------------------------------+
1 row in set (0.00 sec)

mysql>

Upvotes: 2

Related Questions