php - 如何查询5分钟内添加2次以上且邮箱地址相同的重复数据？

Question

我的示例数据在数据库表中如下。

id   email            created_at
1    e@mail.com       2016-01-01 01:01:30
2    e@mail.com       2016-01-01 01:02:20
3    e@mail.com       2016-01-01 01:03:30
4    new@mail.com     2016-01-01 02:56:00
5    e@mail.com       2016-01-01 01:04:30
6    new@mail.com     2016-01-01 02:59:08
7    new@mail.com     2016-01-01 03:01:24
8    i@mail.com       2016-01-01 04:20:30
9    i@mail.com       2016-01-01 04:23:29
10   new@mail.com     2016-01-01 04:30:08
11   i@mail.com       2016-01-01 04:25:29
12   new@mail.com     2016-01-01 04:32:08
13   e@mail.com       2016-01-01 05:16:30
14   i@mail.com       2016-01-01 06:00:00
15   aa@email.com     2017-07-17 15:03:00
16   aa@email.com     2017-07-17 15:04:00
17   aa@email.com     2017-07-17 15:08:01

我运行 Strawberry 提供的以下 SQL 查询。

SELECT x.* 
FROM my_table x
JOIN my_table y
ON y.id <> x.id
AND y.email = x.email
AND y.created_at 
BETWEEN x.created_at - INTERVAL 5 MINUTE 
AND x.created_at + INTERVAL 5 MINUTE 
GROUP
BY x.id HAVING COUNT(*) >= 2

然后我得到以下记录。

id   email            created_at
1    e@mail.com       2016-01-01 01:01:30
2    e@mail.com       2016-01-01 01:02:20
3    e@mail.com       2016-01-01 01:03:30
5    e@mail.com       2016-01-01 01:04:30
6    new@mail.com     2016-01-01 02:59:08
8    i@mail.com       2016-01-01 04:20:30
9    i@mail.com       2016-01-01 04:23:29
11   i@mail.com       2016-01-01 04:25:29
16   aa@email.com     2017-07-17 15:04:00

我要检索的是以下记录，因为它们具有相同的电子邮件地址。这对我来说意味着相同的记录，并且在 5 分钟内插入了 2 次以上。

id   email            created_at
1    e@mail.com       2016-01-01 01:01:30
2    e@mail.com       2016-01-01 01:02:20
3    e@mail.com       2016-01-01 01:03:30
5    e@mail.com       2016-01-01 01:04:30
8    i@mail.com       2016-01-01 04:20:30
9    i@mail.com       2016-01-01 04:23:29
11   i@mail.com       2016-01-01 04:25:29

如何编写一个sql查询来只获取在5分钟内被添加超过2次的记录？

Answer 1

可能有更简单的方法，但这似乎会返回您正在寻找的结果...

SELECT DISTINCT a.*
           FROM service_request a
           JOIN 
              ( SELECT x.* 
                     , MAX(y.created_at) AS range_end
                  FROM service_request x
                  JOIN service_request y
                    ON y.email = x.email
                   AND y.id >= x.id 
                   AND y.created_at <= x.created_at + INTERVAL 5 MINUTE
                 GROUP
                    BY x.id HAVING COUNT(*) >= 3
              ) b
             ON b.email = a.email 
            AND a.created_at BETWEEN b.created_at AND b.range_end;