更新重复列值以从单独的列追加排序索引

Question

我有一个包含name和popularity列的person表。我正在尝试找出我可以运行哪个name命令来使用它们的相对popularity值的附加排序索引来更新重复的SQL值。如果可能的话，如果具有顶级排序索引1的重复names保持原样并且不进行更新，那也是很好的。我一直在研究ARRAY_AGG、GROUP BY、HAVING COUNT(*) > 1、RANK () OVER和PARTITION BY，但缺乏将它们整合成有效解决方案的SQL专业知识。我怀疑我的想法可能完全错了。任何帮助或指导都将不胜感激！

示例

id    name    popularity
0     jane    1.5
1     joe     0.5
2     jane    0.0
3     jane    0.0
4     joe     1.4
5     emily   0.5

期望的结果

id    name    popularity
0     jane    1.5
1     joe-2   0.5
2     jane-2  0.0
3     jane-3  0.0
4     joe     1.4
5     emily   0.5

Answer 1

您可以使用row_number()枚举重复的名称，然后使用join将此信息附加到每一行：

update person p
    set name = name || '-' || seqnum
    from (select p.*,
                 row_number() over (partition by name order by popularity desc, id) as seqnum
          from person p
         ) pp
    where pp.id = p.id and seqnum > 1;

Answer 2

select
    *,
    row_number() over (partition by name order by popularity) as num,
    name || '-' || (row_number() over (partition by name order by popularity))::text as name_new
from person;

使用UPDATE ... FROM ...