mysql - 我想找到一个在 5 分钟内尝试了 3 次登录的用户。在 MySQL 5.7 中

Question

此数据在mysql5.7中

我想找到在 5 分钟内尝试 3 次登录的用户。

这可以通过 mysql 查询实现吗？

+----+--------+----------------------+
| id |  type  |         date         |
+----+--------+----------------------+
| a  | login  | 2019-10-17 10:01:00  |
| a  | login  | 2019-10-17 10:07:00* |
| a  | login  | 2019-10-17 10:10:00* |
| b  | login  | 2019-10-17 10:00:00  |
| b  | login  | 2019-10-17 10:10:00  |
| b  | logout | 2019-10-17 10:20:00  |
| a  | logout | 2019-10-17 10:11:00* |
| a  | login  | 2019-10-17 10:11:30* |
| b  | login  | 2019-10-17 10:10:00  |
| c  | login  | 2019-10-17 10:08:00  |
| c  | login  | 2019-10-17 10:27:00* |
| b  | logout | 2019-10-17 10:30:00  |
| c  | login  | 2019-10-17 10:30:00* |
| c  | login  | 2019-10-17 10:31:00* |
+----+--------+----------------------+

我想得到以下结果:

+----+--------+-------+
| id | type   | count |
+----+--------+-------+
| a  | login  |   4   |
| c  | login  |   3   |
+----+--------+-------+

以下是表结构和数据示例；

CREATE TABLE `a` (
`id` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`type` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


    --
    -- Dumping data for table `a`
    --


INSERT INTO `a` VALUES 
('a','login','2019-10-17 10:01:00'),
('a','login','2019-10-17 10:07:00'),
('a','login','2019-10-17 10:10:00'),
('b','login','2019-10-17 10:00:00'),
('b','login','2019-10-17 10:10:00'),
('b','logout','2019-10-17 10:20:00'),
('a','logout','2019-10-17 10:11:00'),
('a','login','2019-10-17 10:11:30'),
('b','login','2019-10-17 10:10:00'),
('c','login','2019-10-17 10:08:00'),
('c','login','2019-10-17 10:27:00'),
('b','logout','2019-10-17 10:30:00'),
('c','login','2019-10-17 10:30:00'),
('c','login','2019-10-17 10:31:00');

Answer 1

完整查询

select t1.id, t1.type, count(*) ct
from a t1
left join a t2 on t2.id = t1.id and t2.date >= date_add(t1.date, interval 5 minute)
where t2.type = 'login' and t1.type = 'login'   
group by t1.id, t1.type
having count(1) >= 3

参见dbfiddle