postgresql - 一张表只保留3条最高的正负记录

Question

我是数据库和 postgres 的新手。我有一个名为 names 的表，它有 2 列 name 和 value，每 x 秒更新一次新的名称值对。我的要求是在任何时间点只保留 3 个正值和 3 个负值，并在每次表更新期间删除其余行。我使用以下查询删除旧行并保留按值排序的 3 个正值和 3 个负值。

delete from names
using (select  *,
row_number() over (partition by value > 0, value < 0 order by value desc) as rn
from names ) w
where w.rn >=3

我怀疑在分区语句中使用类似值 > 0 的条件。这种方法正确吗？

例如，

在删除之前像这样的表:

name  | value 
--------------
test  |  10 
test1 |  11
test1 |  12
test1 |  13
test4 |  -1
test4 |  -2

删除后我的表应该是这样的:

name  | value 
--------------
test1 |  13
test1 |  12
test1 |  11
test4 |  -1 
test4 |  -2

Answer 1

demo:db<>fiddle

这通常按预期工作:value > 0将值聚类为所有 > 0 的数字和所有 <= 0 的数字。ORDER BY value按预期对这两个组进行排序。

所以，我唯一要改变的是:

row_number() over (partition by value >= 0 order by value desc) 

1. 删除:, value < 0 (因为:为什么要将正值分为负值和其他值？正值组中没有任何负值，反之亦然。)
2. 更改:value > 0至 value >= 0忽略 0尽可能长

---

对于删除:如果你想保留每个方向的前3个值:

1. 你应该改变w.rn >= 3进入w.rn > 3 (它也保留了第三个元素)
2. 您需要将子查询与表记录连接起来。在实际情况下，您应该为此使用 id 列。在您的示例中，您可以采用值列:where n.value = w.value AND w.rn > 3

所以，最后:

delete from names n
using (select  *,
row_number() over (partition by value >= 0 order by value desc) as rn
from names ) w
where n.value = w.value AND w.rn > 3