MySQL - 检索超过 12 个月的所有记录(最新记录除外)

Question

我需要在 MySQL 中编写一个查询，从 Downloads 表中检索每个唯一引用号 (Ref) 的 Downloaded_at 字段早于 12 个月的所有行，但该数据集中的最新行除外。因此它必须按引用号 (Ref) 而不是 Id 进行分组。下载表大致如下所示:

*-----Id-----Ref--------Downloaded_At----------------File_Name----- *

  1      4WP    2009-08-14 10:13:58    test.txt
  2      3K8    2009-05-21 11:11:10    test2.txt
  3      3K8    2011-12-12 08:12:09    test3.txt

例如，存在 ref ABC 的下载，下载时间戳为 01Dec12 8.30、01Dec12 9.30、01Dec12 10.30。那么 2012 年 12 月 1 日 10.30 的记录不应出现在查询中，因为它是最新的记录。

我认为如果我按 Id 而不是 Ref 分组来获取结果，那么编写查询会很容易，但就目前情况而言，我有点挣扎。到目前为止，我有这个查询，(理论上)应该返回正确的结果，但在我的测试数据库上花费的时间太长 - 超过 400 秒，然后我才取消它并放弃。

SELECT * FROM downloads 
WHERE Downloaded_At < DATE_SUB(NOW(), INTERVAL 1 YEAR)
    AND Id NOT IN 
       (SELECT Id FROM downloads INNER JOIN (select Id, Ref, MAX(Downloaded_At) 
        FROM downloads
        WHERE Downloaded_At < DATE_SUB(NOW(), INTERVAL 1 YEAR)
        GROUP BY Ref) dl on dl.id = downloads.id);

任何人都可以建议一种更好/更有效的方法来获得我想要的结果吗？

Answer 1

如果我理解正确的话，您想要过滤每个引用的最新下载记录。我认为您的查询不会执行此操作。

SELECT *
FROM downloads d
WHERE Downloaded_At < DATE_SUB(NOW(), INTERVAL 1 YEAR) and
      Downloaded_At < (SELECT max(Downloaded_At)
                       FROM downloads d2
                       WHERE d2.ref = d.ref
                      );

使用 downloads(ref, Downloaded_At) 上的索引，此查询将运行得更快。