问题描述
我想更新表中列的前10个值.我有三栏； id，account和accountrank.要获取前10个值，我可以使用以下代码:
I want to update the top 10 values of a column in table. I have three columns; id, account and accountrank. To get the top 10 values I can use the following:
SELECT * FROM accountrecords
ORDER BY account DESC
LIMIT 10;
我想基于account的大小将accountrank中的值设置为一系列1 - 10.在PostgreSQL中可以做到吗?
What I would like to do is to set the value in accountrank to be a series of 1 - 10, based on the magnitude of account. Is this possible to do in 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;
加入表表达式通常比相关子查询快.它也更短.
Joining in a table expression is typically faster than correlated subqueries. It is also shorter.
使用窗口函数row_number() 时，不同的数字是保证的.如果希望account的值相等的行共享相同的数字，请使用rank()(或可能是dense_rank()).
With the window function row_number() distinct numbers are guaranteed. Use rank() (or possibly dense_rank()) if you want rows with equal values for account to share the same number.
仅当account中可以有NULL个值时，才需要附加 NULLS LAST 用于降序排列，或者NULL值排在最前面:
Only if there can be NULL values in account, you need to append NULLS LAST for descending sort order, or NULL values sort on top:
如果可以同时进行写访问，则上面的查询将受竞争条件的约束.考虑:
If there can be concurrent write access, the above query is subject to a race condition. Consider:
- Atomic UPDATE .. SELECT in Postgres
- Postgres UPDATE … LIMIT 1
但是，如果是这样的话，硬编码前十名的整个概念从一开始就是一种可疑的方法.
However, if that was the case, the whole concept of hard-coding the top ten would be a dubious approach to begin with.
使用CTE代替普通的子查询(如我刚开始的那样)来可靠地强制执行LIMIT.参见上面的链接.
Use a CTE instead of a plain subquery (like I had at first) to enforce the LIMIT reliably. See links above.
这篇关于使用PostgreSQL更新前N个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！