我想要更新表中一列的前十个值。我的表有三列:id
、account
和accountrank
。要获取前十个值,我可以使用以下查询:
SELECT * FROM accountrecords
ORDER BY account DESC
LIMIT 10;
我想做的是根据 account
的大小,将 accountrank
中的值设置为一系列的1-10
。在PostgreSQL中是否可能实现这个功能?我想要更新表中一列的前十个值。我的表有三列:id
、account
和accountrank
。要获取前十个值,我可以使用以下查询:
SELECT * FROM accountrecords
ORDER BY account DESC
LIMIT 10;
我想做的是根据 account
的大小,将 accountrank
中的值设置为一系列的1-10
。在PostgreSQL中是否可能实现这个功能?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
值排在最前面:
如果存在并发写访问,则上述查询容易出现 竞争条件。请考虑以下内容:
但是,如果是这种情况,那么硬编码前十名的整个概念本来就是一个可疑的方法。
使用 CTE 而不是普通子查询可靠地强制执行 LIMIT
。请参见上面的链接。
当然,你可以在子查询中使用选择语句。生成排名并不是一件容易的事情,但这里至少有一种方法可以实现。我还没有测试过这个方法,但就我个人的理解:
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
具有相同的值,则它们将获得相同的排名。你可以把它看作是一种特性... :-)