在表格中仅保留用户的最后5个搜索结果 [英] Keep only last 5 search results of user in a table

问题描述

我需要将用户的最后5个搜索结果保留在一个表中。

我编写了一个脚本来删除其他行，但没有用：

I need to keep the last 5 search results of a user in a table.
I wrote a script to remove other rows, but it didn't work:

DELETE FROM         
    SELECT
        ROW_NUMBER () OVER (ORDER BY search_time DESC) AS row_number;
    FROM
        history_user
    WHERE
        user_id = 188
WHERE row_number>5

我做错了什么？

推荐答案

正确的语法如手册中所述：

DELETE FROM history_user h
USING (
    SELECT pk_id, row_number() OVER (ORDER BY search_time DESC) AS rn;
    FROM   history_user
    WHERE  user_id = 188
    ) sub
WHERE sub.rn > 5
AND   h.pk_id = sub.pk_id;

其中 pk_id 是任意的（组合） 唯一的列。在您的情况下，可能是 user_id ， search_time -或更方便的是，替代主键。

Where pk_id is any (combination of) column(s) that is unique. Might be user_id, search_time in your case - or, more conveniently, a surrogate primary key.

对于一个单个 user_id ，您可以简化为：

For just a single user_id you can simplify to:

DELETE FROM history_user h
USING (
    SELECT pk_id
    FROM   history_user
    WHERE  user_id = 188
    ORDER  BY search_time DESC
    OFFSET 5
    ) sub
WHERE h.pk_id = sub.pk_id;

另一方面，要一次与多个用户打交道，需要在窗口函数中添加 PARTITION BY ：

On the other hand, to deal with multiple users at once, you need to add PARTITION BY to your window function:

DELETE FROM history_user h
USING (
    SELECT pk_id, row_number() OVER (PARTITION BY user_id
                                     ORDER BY search_time DESC) AS rn;
    FROM   history_user
    ) sub
WHERE sub.rn > 5
AND   h.pk_id = sub.pk_id;

这篇关于在表格中仅保留用户的最后5个搜索结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！