仅保留表中分组列的最后两行

Question

我有一个表格"历史记录"，大约有 300.000 行，每天都会填充新数据。我只想保留每个 refSchema/refId 组合的最后两行。

其实我是这样走的：

第一步：

SELECT refSchema,refId FROM History GROUP BY refSchema,refId

通过这个陈述，我得到了所有组合(大约 40.000)。

第二步：

我运行一个 foreach，它查找上述查询的现有行，如下所示：

SELECT id
FROM History
WHERE refSchema = ? AND refId = ? AND state = 'done'
ORDER BY importedAt 
DESC LIMIT 2,2000

请记住，我想保留表中的最后两行，所以我限制 2,2000。如果我找到匹配的行，我会将id放在一个名为 idList 的数组中。

最后一步

我以这种方式从数组中删除所有 id：

DELETE FROM History WHERE id in ($idList)

这一切似乎都不是最好的性能，因为我必须使用额外的查询检查每个组合。有没有办法让一个删除语句神奇地避免 40.000 个额外的查询？

编辑更新：我使用 AWS Aurora DB

Answer 1

如果您使用的是MySQL 8+，那么从概念上讲，继续此处的一种简单方法是使用CTE来识别要保留的每个组的前两行。 然后，删除其架构/ID 对未出现在此白名单中的任何记录：

WITH cte AS (
SELECT refSchema, refId
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY refSchema, refId ORDER BY importedAt DESC) rn
FROM History
) t
WHERE rn IN (1, 2)
)
DELETE
FROM History
WHERE (refSchema, refId) NOT IN (SELECT refSchema, refId FROM cte);

如果您无法使用 CTE，请尝试内联上述 CTE：

DELETE
FROM History
WHERE (refSchema, refId) NOT IN (
SELECT refSchema, refId
FROM
(
SELECT *, ROW_NUMBER() OVER (PARTITION BY refSchema, refId ORDER BY importedAt DESC) rn
FROM History
) t
WHERE rn IN (1, 2)
);