Grouping MySQL results to only allow a record to add to the total if the last one found is over two hours old

I am currently tracking actions performed by employees in a table, which has three rows: id, user_id, and action_time, customer_id. In order to track performance, I can simply pick an employee on a date, and count the actions they've performed, easy peasy.

SELECT COUNT(DISTINCT `customer_id`) AS `action_count` 
FROM   `actions` 
WHERE  `user_id` = 1 
AND    DATE(`action_time`) = DATE(NOW()) 

However, I now wish to make it so that actions performed more than two hours apart will class as two actions towards the total. I've looked into grouping by HOUR() / 2 but an action performed at 9:59 and 10:01 will count as two, not quite what I want开发者_运维问答.

Anyone have any ideas?

---

You must self-JOIN the actions table, try something like this:

SELECT COUNT(DISTINCT id) FROM (
  SELECT a1.id, ABS(UNIX_TIMESTAMP(a1.action_time) - UNIX_TIMESTAMP(a2.action_time))>=7200 
  AS action_time_diff FROM actions a1 JOIN actions a2 ON a1.user_id=a2.user_id) AS t 
WHERE action_time_diff = 1

Not sure if this works, perhaps you should provide more exact details about the table structure.