mysql - 查找并删除 SQL 记录(如果它们发生在 8 小时内)(最近的记录除外)

Question

我有一个 SQL 表，其中包含一些我想要删除的重复记录。删除应在同时满足 2 个条件的情况下进行:

1. score 列下的记录具有相同的值
2. 记录的发生时间间隔在 8 小时内。

删除的记录应该是所有匹配记录中日期较旧的记录，因此新的查询结果中应该只出现匹配记录中最新的记录。

到目前为止，我只能创建一个代码来删除此类重复项，前提是这些记录发生在该月的同一天，因此它丢失了跨越连续两天以上 - 如何解决这个问题？

原始数据库如下所示:

user_id  score             visited_at           visit_id  
-------  ----------------  -------------------  ----------
     22  75.0              2018-05-14 23:39:14         169
     22  75.0              2018-05-14 18:36:26         168
     22  75.0              2018-05-13 02:04:46         166
      2  55.0              2018-05-12 18:38:24         165
     22  78.0              2018-05-12 18:14:34         164
     22  75.0              2018-05-12 18:45:12         164
     22  55.0              2018-05-08 12:36:12         161

部分删除重复项的 SQL 命令:

SELECT COUNT(*) AS ct
     , it.user_id
     , it.score
     , UNIX_TIMESTAMP(CONVERT_TZ(it.visited_at,'+00:00',@@global.time_zone)) DIV 86400 AS diff
     , it.visited_at
     , it.visit_id
  FROM `vw_items` it
 GROUP 
    BY user_id
     , score
     , diff 
 ORDER 
    BY visited_at DESC

结果:

    ct  user_id            score    diff  visited_at           visit_id  
------  -------  ----------------  ------  -------------------  ----------
     2       22  75.0               17665  2018-05-14 23:39:14         169
     1       22  75.0               17664  2018-05-13 02:04:46         166
     1        2  55.0               17663  2018-05-12 18:38:24         165
     1       22  78.0               17663  2018-05-12 18:14:34         164
     1       22  75.0               17663  2018-05-12 18:45:12         164
     1       22  55.0               17659  2018-05-08 12:36:12         161

但是我需要一个也可以删除记录的命令:

     1       22  75.0               17663  2018-05-12 18:45:12         164

因为它与另一条记录具有相同的分数，即较新的记录，该记录发生在距该记录 8 小时内:

     1       22  75.0               17664  2018-05-13 02:04:46         166

Answer 1

我相信您正在寻找的是 DATE_SUB 函数

DATE_SUB(it.visited_at, INTERVAL 8 HOUR)

这将创建一个日期时间，您可以通过比较该日期时间来查找给定记录 8 小时内的内容。我会写更多答案，但看起来这是您缺少的唯一一 block 拼图。