mysql - 如何统计事件间隔在 5 分钟内发生的事件

Question

我有像这样的表格

--------------------------
身份证 |事件 |用户|时间
1 |购买| jack | 12:00
2 |返回 | jack | 12:01
3 |购买|约翰 | 12:01
4 |购买| jack | 12:07
5 |返回 |约翰 | 12:07
6 |返回 |约翰 | 12:08
---------------------------

我想按 5 分钟间隔对事件进行分组

--------------------------------
用户|计数事件 |来自|到
jack | 2 | 12:00| 12:01
约翰 | 1 | 12:01| 12:01
jack | 1 | 12:07| 12:07
约翰 | 2 | 12:07| 12:08
--------------------------------

我试过了

select User,Count(Event),Min(Time),Max(Time) 
from User_Events 
group by (4 * HOUR(Time) + FLOOR( MINUTE(Time) / 5));`

但它给了我错误的结果。我也尝试过

select User,Count(Event),Min(Time),Max(Time) 
from User_Events 
group by UNIX_TIMESTAMP(ADDDATE(TransactionTime,INTERVAL 2 Hour)) DIV 300;`

Answer 1

注意:我不确定我是否回答了正确的问题，因为我的输出与您的不同，但我确实认为我的输出是“正确的”，据我所知。或者失败了，可能就足以解决剩下的问题了。

CREATE TABLE Events (ID INT,  Evnt VARCHAR(10),  Usr VARCHAR(10), Tm TIME);
INSERT INTO Events SELECT 1,'Buy','Jack','12:00';
INSERT INTO Events SELECT 2,'Return','Jack','12:01';
INSERT INTO Events SELECT 3,'Buy','John','12:01';
INSERT INTO Events SELECT 4,'Buy','Jack','12:07';
INSERT INTO Events SELECT 5,'Return','John','12:07';
INSERT INTO Events SELECT 6,'Return','John','12:08';

SELECT DATE_FORMAT(a.tm,'%H:%i') 'User'
      ,COUNT(1) CountEvents
      ,DATE_FORMAT(a.tm,'%H:%i') 'From'
      ,DATE_FORMAT(MAX(b.tm),'%H:%i') 'To'
  FROM (SELECT DISTINCT tm FROM Events) a
  JOIN Events b ON b.tm >= a.tm 
               AND b.tm <= ADDDATE(a.tm, INTERVAl 5 MINUTE)
 GROUP BY a.tm           

SQLFiddle

结果:

User    CountEvents From    To
12:00   3           12:00   12:01
12:01   2           12:01   12:01
12:07   3           12:07   12:08
12:08   1           12:08   12:08