sql - 使用 PostgreSQL 更新前 N 个值

Question

我想更新表中某列的前 10 个值。我有三列； id , account和 accountrank .要获得前 10 个值，我可以使用以下方法:

SELECT  * FROM accountrecords    
ORDER BY account DESC
LIMIT 10;

我想做的是在 accountrank 中设置值成为一系列 1 - 10 , 基于 account 的大小.在 PostgreSQL 中可以做到这一点吗？

Answer 1

WITH cte AS (
   SELECT id, row_number() OVER (ORDER BY account DESC NULLS LAST) AS rn
   FROM   accountrecords    
   ORDER  BY account DESC NULLS LAST
   LIMIT  10
   )
UPDATE accountrecords a
SET    accountrank = cte.rn
FROM   cte
WHERE  cte.id = a.id;

加入表表达式通常比关联子查询更快。它也更短。

随着window function row_number()不同的数字是有保证的。如果您希望 account 的值相同的行共享相同的数字，请使用 rank()(或者可能是 dense_rank())。

只有当account中可以有NULL值时，才需要追加NULLS LAST对于降序排序，或 NULL 值排序在顶部:

• Sort by column ASC, but NULL values first?

如果可以并发写访问，则上述查询受制于竞争条件。考虑:

• Atomic UPDATE .. SELECT in Postgres
• Postgres UPDATE … LIMIT 1

但是，如果真是这样，那么硬编码前十名的整个概念从一开始就是一个可疑的方法。

使用 CTE 而不是普通子查询来可靠地执行 LIMIT。请参阅上面的链接。