使用 PostgreSQL 更新前 N 个值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13187419/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Update top N values using PostgreSQL
提问by djq
I want to update the top 10 values of a column in table. I have three columns; id, accountand accountrank. To get the top 10 values I can use the following:
我想更新表中一列的前 10 个值。我有三列；id，account和accountrank。要获得前 10 个值，我可以使用以下命令：
SELECT * FROM accountrecords
ORDER BY account DESC
LIMIT 10;
What I would like to do is to set the value in accountrankto be a series of 1 - 10, based on the magnitude of account. Is this possible to do in PostgreSQL?
我想要做的是设置在值accountrank是一个系列的1 - 10基础上的大小account。这可以在 PostgreSQL 中做到吗？
回答by Erwin Brandstetter
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.
加入表表达式通常比相关子查询更快。它也更短。
With the window function row_number()distinct numbers are guaranteed. Use rank()(or possibly dense_rank()) if you want rows with equal values for accountto share the same number.
使用窗口函数row_number()可以保证不同的数字。如果您希望具有相同值的行共享相同的数字，请使用rank()（或可能dense_rank()）account。
Only if there can be NULLvalues in account, you need to append NULLS LASTfor descending sort order, or NULLvalues sort on top:
仅当 中可以有NULL值时account，才需要追加NULLS LAST降序排序，或者NULL值排在最前面：
If there can be concurrent write access, the above query is subject to a race condition. Consider:
如果可以存在并发写入访问，则上述查询会受到竞争条件的影响。考虑：
However, if that was the case, the whole concept of hard-coding the top ten would be a dubious approach to begin with.
然而，如果是这样的话，硬编码前十名的整个概念将是一个可疑的方法。
Use a CTE instead of a plain subquery (like I had at first) to enforce the LIMITreliably. See links above.
使用 CTE 而不是普通子查询（就像我一开始那样）来LIMIT可靠地强制执行。请参阅上面的链接。
回答by Jamey Sharp
Sure, you can use your select statement in a subquery. Generating the rank-order isn't trivial, but here's at least one way to do it. I haven't tested this, but off the top of my head:
当然，您可以在子查询中使用您的 select 语句。生成排名顺序并非易事，但这里至少有一种方法可以做到。我还没有测试过这个，但我的头顶：
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);
This has the quirk that if two records have the same value for account, then they will get the same rank. You could consider that a feature... :-)
这有一个怪癖，如果两条记录具有相同的值account，那么它们将获得相同的排名。你可以认为这是一个功能...... :-)
