sql - 如何删除 MySQL 中每个用户的所有不是最新 N 的行？

Question

假设我有一个表foo，其中有这样的东西:

id, user_id, timestamp, some_value

我想要做的是删除所有不是最新的 N per user 的行。

删除本身可以由以下人员处理:

DELETE FROM foo WHERE id NOT IN (...)

因此您可以将问题改写为:如何为每个用户获取最新的 N(可能更少)行。这意味着如果我有 U 个用户，我可能会得到 N*U 行，所以 LIMIT 不会真正起作用。

Answer 1

实际上，可以通过一个查询来完成:

DELETE  l.*
FROM    foo l
JOIN    (
        SELECT  user_id,
                COALESCE(
                (
                SELECT  timestamp
                FROM    foo li
                WHERE   li.user_id = dlo.user_id
                ORDER BY
                        li.user_id DESC, li.timestamp DESC
                LIMIT 2, 1
                ), CAST('0001-01-01' AS DATETIME)) AS mts,
                COALESCE(
                (
                SELECT  id
                FROM    foo li
                WHERE   li.user_id = dlo.user_id
                ORDER BY
                        li.user_id DESC, li.timestamp DESC, li.id DESC
                LIMIT 2, 1
                ), -1) AS mid
        FROM    (
                SELECT  DISTINCT user_id
                FROM    foo dl
                ) dlo
        ) lo
ON      l.user_id = lo.user_id
        AND (l.timestamp, l.id) < (mts, mid)

在这里查看详细的解释:

• Keeping rows (如何删除除TOP N之外的所有行)
• Advanced row sampling (如何为每个 GROUP 选择 TOP N 行)
• Keeping latest rows for a group (如何结合使用上述方法)