如何在PostgreSQL中为每个Entity分组分配唯一标识值

当然可以。你之前尝试的row_number()和rank()是用于组内行的排序编号，而不是给分组本身分配全局唯一ID。要实现每个不同的entity分组对应一个唯一编号，可以用以下几种方法：

方法1：用DENSE_RANK()生成排序式分组ID #

这种方法会根据entity的排序值生成连续的唯一编号，适合需要按entity名称排序的场景。

为唯一Entity生成编号 #

SELECT
  entity,
  DENSE_RANK() OVER (ORDER BY entity) AS group_id
FROM (SELECT DISTINCT entity FROM your_table) AS unique_entities;

关联回原表获取完整数据 #

如果需要将分组ID附加到原表的每一行，用JOIN关联：

SELECT
  t.*,
  r.group_id
FROM your_table t
JOIN (
  SELECT
    entity,
    DENSE_RANK() OVER (ORDER BY entity) AS group_id
  FROM (SELECT DISTINCT entity FROM your_table) AS u
) r ON t.entity = r.entity;

方法2：按Entity首次出现顺序生成编号 #

如果需要按entity在表中首次出现的顺序分配编号（而非按名称排序），可以结合GROUP BY和ROW_NUMBER()：

SELECT
  t.*,
  r.group_id
FROM your_table t
JOIN (
  SELECT
    entity,
    ROW_NUMBER() OVER (ORDER BY MIN(created_at)) AS group_id -- 替换成你的自增主键/首次出现时间列
  FROM your_table
  GROUP BY entity
) r ON t.entity = r.entity;

示例效果 #

假设你的表数据如下：

执行方法1的JOIN语句后，结果会是：

每个entity分组都得到了唯一的group_id。

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