PostgreSQL:设置一列，其中行的序号通过另一个字段排序

Question

我有一个表 segnature 描述了一个带有 varchar 字段 deno 和数字字段 ord 的项目。外键 fk_collection 告诉该行属于哪个集合。

我想更新字段 ord 以便它包含每个集合的该行的序号，按字段 deno 排序。

例如如果我有类似的东西

[deno]     ord   [fk_collection]
abc              10
aab              10
bcd              10
zxc              20
vbn              20

然后我想要这样的结果

[deno]     ord   [fk_collection]
abc          1   10
aab          0   10
bcd          2   10
zxc          1   20
vbn          0   20

我试过类似的东西

update segnature s1 set ord = (select count(*) 
    from segnature s2 
    where s1.fk_collection=s2.fk_collection and s2.deno<s1.deno
)

但查询确实很慢:每大约 30000 个项目中有 150 个集合在大约 10 分钟内更新。

有什么建议可以加快这个过程吗？

谢谢!

Answer 1

您可以使用窗口函数生成“序数”数:

with numbered as (
   select deno, fk_collection, 
          row_number() over (partition by fk_collection order by deno) as rn, 
          ctid as id
   from segnature
) 
update segnature
  set ord = n.rn
from numbered n
where n.id = segnature.ctid;

这使用内部列 ctid以唯一标识每一行。 ctid 比较非常慢，因此如果您在该表中有真正的主(或唯一)键，请改用该列。

或者没有 common table expression :

update segnature
  set ord = n.rn
from (
    select deno, fk_collection, 
           row_number() over (partition by fk_collection order by deno) as rn, 
           ctid as id
    from segnature
) as n
where n.id = segnature.ctid;

SQLFiddle 示例:http://sqlfiddle.com/#!15/e997f/1