更新带有整数序列的Postgres列，以供唯一对值

Question

我有postgres表user_team具有列user_pk，team，练习，nistion> nistioned_at我想自动填充order列，其中包括user_pk and team 的唯一对，并由<代码>分配了： 因此，我拥有的列是：

|  user_pk    |  team          |  exercise_pk  | assigned_at  |  
|-------------------------------------------------------------|
| 111         | blue           |  "exercise"   | 2022-03-01   |
| 111         | blue           |  "exercise"   | 2022-03-02   |
| 222         | blue           |  "exercise"   | 2022-03-01   |
| 222         | blue           |  "exercise"   | 2022-03-02   |
| 222         | blue           |  "exercise"   | 2022-03-03   |
| 111         | green          |  "exercise"   | 2022-03-01   |
| 111         | green          |  "exercise"   | 2022-03-02   |
| 111         | green          |  "exercise"   | 2022-03-03   |
| 333         | green          |  "exercise"   | 2022-03-01   |
| 333         | green          |  "exercise"   | 2022-03-02   |

我想

|  user_pk    |  team          |  exercise_pk  | assigned_at  | order|
|--------------------------------------------------------------------|
| 111         | blue           |  "exercise"   | 2022-03-01   |1     |
| 111         | blue           |  "exercise"   | 2022-03-02   |2     |
| 222         | blue           |  "exercise"   | 2022-03-01   |1     |
| 222         | blue           |  "exercise"   | 2022-03-02   |2     |
| 222         | blue           |  "exercise"   | 2022-03-03   |3     |
| 111         | green          |  "exercise"   | 2022-03-01   |1     |
| 111         | green          |  "exercise"   | 2022-03-02   |2     |
| 111         | green          |  "exercise"   | 2022-03-03   |3     |
| 333         | green          |  "exercise"   | 2022-03-01   |1     |
| 333         | green          |  "exercise"   | 2022-03-02   |2     |

在一个查询中有任何方法可以做到这一点吗？ 我尝试了不同的user_pk，team和答案来自：用整数序列更新Postgres列：

    update bar b
   set id = b2.new_id
   from (select b.*, row_number() over (order by id) as new_id
         from bar
        ) b2;
   where b.pk = b2.pk;

但仍然无法弄清楚它

Answer 1

您寻找一个类似的问题是很好的。但是，您实际上是在您的问题上尝试了什么，您不能仅从那里复制答案，并期望它甚至与您需要的内容相关。这只是提供了可能适用的格式。

您需要的是识别唯一的列或一组唯一的列来识别特定行，从那里使用row_number为每个唯一值/集合分配所需的值，最终匹配生成/提取的标识符以获取所需的结果列。在下面的CTE使用列user_pk，Team，AndissEd_at以识别特定行和目标的新列值。然后，主要部分与这些列匹配以进行更新。 （请参阅

with ord_num( user_pk, team, assigned_at, order_seq)  as 
     ( select user_pk, team, assigned_at
            , row_number() over (partition by  user_pk, team 
                                     order by  user_pk, team, assigned_at
                                )
         from user_team 
      )
update user_team ut
   set order_seq = 
       (select order_seq 
          from ord_num os 
         where (ut.user_pk, ut.team, ut.assigned_at) = 
               (os.user_pk, os.team, os.assigned_at)
       ) ;

d63adbc16c171f643b7520666666e24242424240 >用户order作为列名称是极为糟糕的实践。它既是Postgres又是SQL Standard nofollow noreferrer“>保留词。虽然您可以摆脱它，但最好的结果就是通过做不正确的事情而没有提供任何信息来混淆，最坏的处理失败，而处理得最糟糕的失败。导致完整的数据损坏。也适用于任何保留的单词。