使用排名窗口功能的Postgres UPDATE

Question

我有一个名为的表medias，最近刚刚在其中添加了一个名为sort_ordertype的新列Int。

行值将不是整个表唯一的，而是它们各自的owner_user_id字段唯一的。无论如何，我什至不在乎它们的独特性。

所有这些的目的是允许用户设置他们上传的照片的排序顺序（最多10张，并且可以拖动和重新排序等）。当用户“删除”照片时，我不会删除记录，我只是在该行上设置了一个visible字段false。

Aaaanyway，我要介绍一个添加的迁移sort_order（它们以前无法订购照片，它们只会根据进行排序order by created_at asc）。

自添加新字段以来，我使新字段sort_order具有默认值10（以便向后兼容尚未更新应用程序的人员）。

我能够提出以下查询：

select
  owner_user_id, 
  sort_order, rank() over (PARTITION BY owner_user_id ORDER BY sort_order asc, created_at asc) as new_sort_order 
from medias 
where visible=true 
order by sort_order asc, created_at asc;

这会吐出类似于以下内容的内容：

 owner_user_id | sort_order | new_sort_order
---------------+------------+---------------
            76 |         10 |      1
            76 |         10 |      2
            76 |         10 |      3
            76 |         10 |      4
            76 |         10 |      5
             9 |         10 |      1
             9 |         10 |      2
             9 |         10 |      3
             9 |         10 |      4
             9 |         10 |      5
            79 |         10 |      1
            79 |         10 |      2
            87 |         10 |      1
            87 |         10 |      2
            87 |         10 |      3
            85 |         10 |      1
            90 |         10 |      1
            90 |         10 |      2
            90 |         10 |      3

在这一点上我真正想要做的是集合sort_order到rank()。有关如何执行此操作的任何想法？

Answer 1

由于您没有唯一的密钥，请使用ctid：

update medias m
    set sort_order = new_sort_order
    from (
        select 
            ctid,
            owner_user_id, 
            sort_order, 
            row_number() over w as new_sort_order 
        from medias 
        where visible
        window w as (partition by owner_user_id order by sort_order asc, created_at asc)
    ) s
    where m.ctid = s.ctid;

请注意，row_number()可能会比rank()第一个绝不提供重复项更好。