使用PostgreSQL更新前N个值

Question

我想要更新表中一列的前十个值。我的表有三列：id、account和accountrank。要获取前十个值，我可以使用以下查询：

SELECT  * FROM accountrecords    
ORDER BY account DESC
LIMIT 10;

我想做的是根据 account 的大小，将 accountrank 中的值设置为一系列的1-10。在PostgreSQL中是否可能实现这个功能？

- djq

---

如果您的Postgres版本为8.4或更高版本，则可以使用窗口函数+rank()或row_number()。 - wildplasser

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;

在表达式中加入连接通常比关联子查询更快，也更短。

使用窗口函数 row_number() 可以保证唯一数字。如果您希望对于 account 具有相等值的行共享相同的编号，请使用 rank()（或可能是 dense_rank()）。

仅当 account 中可能存在 NULL 值时，您需要附加 NULLS LAST 以进行降序排序，或者将 NULL 值排在最前面:

• 按列升序排序，但首先为 NULL 值排序？

如果存在并发写访问，则上述查询容易出现 竞争条件。请考虑以下内容：

• Postgres 中原子 UPDATE .. SELECT
• Postgres UPDATE … LIMIT 1

但是，如果是这种情况，那么硬编码前十名的整个概念本来就是一个可疑的方法。

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

- Erwin Brandstetter

Answer 2

当然，你可以在子查询中使用选择语句。生成排名并不是一件容易的事情，但这里至少有一种方法可以实现。我还没有测试过这个方法，但就我个人的理解：

update accountrecords
set accountrank =
    (select count(*) + 1 from accountrecords r where r.account > account)
where id in (select id from accountrecords order by account desc limit 10);

特别之处在于，如果两个记录对account具有相同的值，则它们将获得相同的排名。你可以把它看作是一种特性... :-)

- Jamey Sharp