PostgreSQL中如何为每个唯一name分组生成确定性递增的group_id

我有一张名为student的数据表，表中分组标识列name的类型为TEXT。需要编写可稳定复现、输出结果完全确定的查询，为每一个唯一的name值所属的分组分配全局递增的group_id，同一name对应的所有行使用同一个group_id值。
期望的输出效果参考：

------+----------
 name | group_id
------+----------
  A   |    1
  A   |    1
  B   |    2
  C   |    3
  D   |    4
  D   |    4
  D   |    4

当前使用的数据库版本为PostgreSQL 13.2，此前尝试使用窗口函数实现需求未成功，需要可落地的实现思路。

核心函数选型 #

需求本质是给全表内的唯一name值分配连续的全局序号，PostgreSQL中的DENSE_RANK()窗口函数刚好匹配这个特性：

• 相同值的所有行会获得完全一致的排名值
• 不同值之间的排名为连续递增的整数，不会产生排名空隙
• 相比RANK()函数，它不会因为同值行占位数导致排名跳号，完全匹配示例中的group_id表现

确定性输出的保障 #

很多人使用窗口函数得到的结果不稳定，核心原因是没有对TEXT列的排序规则做固定约束：默认TEXT列的排序会跟随数据库实例的本地化collation配置，一旦配置变更、底层排序规则版本升级，相同数据的排序顺序可能发生变化，最终group_id的分配结果无法复现。

SELECT
  name,
  DENSE_RANK() OVER (
    -- 强制按原始字节序排序，彻底排除collation配置对排序结果的影响
    ORDER BY name COLLATE "C"
  ) AS group_id
FROM student
-- 可选：固定最终返回结果的行顺序，避免全表扫描带来的返回顺序波动
ORDER BY group_id;

• 不要把窗口的ORDER BY写漏，更不要误用PARTITION BY name当排序条件：如果写DENSE_RANK() OVER (PARTITION BY name)，相当于每个分组内部单独算排名，最终所有行的group_id都会是1，完全不符合需求
• 不要用ROW_NUMBER() OVER (ORDER BY name)生成group_id：这个函数会给每一行分配不重复的序号，即使两行的name完全一致，得到的序号也会不同，不符合同组同id的要求
• 不要不加collation约束直接按name排序生成排名：默认排序规则会跟随数据库实例的本地化配置，一旦配置变更、底层排序规则版本升级，相同数据的排序顺序可能发生变化，两次执行相同查询得到的group_id映射关系不一致，达不到确定性输出的要求
• 不需要额外做去重关联：很多人会先查出所有唯一的name值、生成序号后再关联回原表，这种写法在PostgreSQL 13下会产生额外的嵌套查询和关联开销，直接使用窗口函数的执行效率更高

内容的提问来源于stack exchange，提问作者MoonlightSolace