如何在Redshift SQL中覆写row_number值并解决UPDATE报错

报错原因 #

Redshift对UPDATE语法有强制要求：FROM子句引入的临时结果集必须和目标更新表做等值关联，原写法既没有在子查询中带出可定位原表行的字段，也没有写关联条件，因此触发Target table must be part of an equijoin predicate报错。

正确实现代码 #

你需要在子查询中带出原表的唯一行标识（有自定义主键就用主键，无主键可使用Redshift内置的行唯一标识ctid），再在外层写等值匹配条件：

UPDATE table1
SET rank = temp.new_rank
FROM (
  SELECT
    ctid, -- 若表有自定义主键，替换为实际主键字段即可，例如log_id、record_id
    ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY date) AS new_rank
  FROM table1
) temp
WHERE table1.ctid = temp.ctid;

注意点 #

• 等值关联条件是Redshift UPDATE的强制要求，不支持无关联直接全表更新的写法
• 若同一session_id下存在多条date值完全一致的记录，建议在窗口函数的ORDER BY后补充第二个排序字段（比如主键），避免生成的行号结果不稳定
• 子查询不要对原表数据做聚合、去重、过滤，否则会出现行匹配不上、单条记录被多次匹配的问题

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