使用PostgreSQL更新前N个值 [英] Update top N values using PostgreSQL

问题描述

我想更新表中列的前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个值时，才需要附加

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:

• PostgreSQL按日期时间asc排序，是否为null?

如果可以同时进行写访问，则上面的查询将受竞争条件的约束.考虑:

If there can be concurrent write access, the above query is subject to a race condition. Consider:

• Postgres中的原子更新.. SELECT
• Postgres更新…限制1

• 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个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！