Postgres UPDATE 使用等级窗口函数 [英] Postgres UPDATE using rank window function

问题描述

我有一个名为 medias 的表，我最近在其中添加了一个名为 sort_order 的新列，类型为 Int.

I have a table called medias where I've just recently added a new column to called sort_order of type Int.

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

The row values will not be unique to the entire table, but to their own respective owner_user_id fields. Regardless, I don't even care about the uniqueness of them tbh.

这一切的目的是允许用户设置他们上传的照片的排序顺序(最多 10 张，他们可以拖动和重新排序等).当用户删除"一张照片时，我不会删除记录，我只是在该行上将 visible 字段设置为 false.

The point of all this is to allow users to set the sort order of the photos they upload (up to 10, and they can drag and re-order, etc). When the user "deletes" a photo, I don't remove the record, I simply set a visible field to false on that row.

Aaaanyway，我正在引入添加 sort_order 的迁移(他们过去无法对照片进行排序，它们只会根据 order by created_at asc>).

Aaaanyway, I'm introducing a migration that adds sort_order (they used to not be able to order the photos, they would just be sorted according to order by created_at asc).

自从添加新字段后，我将新的 sort_order 的默认值设置为 10(以便它向后兼容没有更新申请).

Since adding the new field, I've made the new sort_order have a default value of 10 (so that it's backwards compatible for folks who haven't updated the application).

我能够提出这个查询:

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;

这会吐出如下所示的内容:

This spits out something that looks like the following:

 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().关于如何做到这一点的任何想法?

at this point all i really want to do is set that sort_order to that rank(). Any thoughts on how to do this?

推荐答案

由于您没有唯一键，请使用 ctid:

As you do not have a unique key, use 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() 更好，因为第一个永远不会给出重复.

Note, row_number() may be better than rank() as the first never gives duplicates.

这篇关于Postgres UPDATE 使用等级窗口函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！