PostgreSQL中如何基于item_id列生成递增行号incremental_id

你可以直接使用PostgreSQL内置的窗口函数row_number()生成按item_id排序的连续自增行号，修改后的查询如下：

SELECT 
    row_number() OVER (ORDER BY item_id) AS incremental_id,
    rating,
    item_id,
    user_id
FROM 
    (SELECT 
         (CASE
             WHEN "Review".food IS NOT NULL AND "Review".service IS NOT NULL
                 THEN ("Review".food + "Review".service) / 2
             ELSE COALESCE(service, COALESCE(food, NULL)) END) AS rating,
         "venueId" AS item_id,
         "userId" AS user_id
     FROM "Review") AS data
WHERE rating IS NOT NULL
  AND user_id IS NOT NULL
  AND item_id IS NOT NULL
ORDER BY item_id;

逻辑说明 #

row_number()是标准的窗口函数，会按照OVER子句中指定的排序规则，为每一行分配唯一的连续整数值，和你要求的incremental_id规则完全匹配。如果你的场景中存在相同item_id的行，且希望相同item_id对应同一个incremental_id，把row_number()替换为dense_rank()即可。

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