UPDATE使用PERCENT_RANK()赋值时所有行结果为0.0如何解决

问题根因 #

• 原UPDATE语句中子查询没有和外层待更新的行做关联匹配，执行更新时每一行都会拉取子查询返回的全量结果集，最终只会取第一行的结果赋值给所有行，所以全表ranking都是0.0
• 窗口函数单独运行时的计算结果是每个store_id对应一条排名记录，但UPDATE场景下没有建立store_id的匹配关系，无法将正确的排名赋值到对应行

修复方案 #

通用写法（兼容PostgreSQL、SQL Server、MySQL 8.0+等支持CTE的数据库） #

先用CTE预计算每个store_id的排名，再通过关联更新赋值：

WITH store_rank_result AS (
    SELECT 
        store_id,
        PERCENT_RANK() OVER(PARTITION BY city ORDER BY sales DESC) AS rank_val
    FROM table1
    GROUP BY store_id, city, sales -- 对齐原SQL的分组逻辑，保证每个store_id唯一对应一条结果
)
UPDATE table1 t1
SET ranking = srr.rank_val
FROM store_rank_result srr
WHERE t1.store_id = srr.store_id;

MySQL兼容写法（部分MySQL版本不支持UPDATE+FROM语法，改用JOIN更新） #

UPDATE table1 t1
INNER JOIN (
    SELECT 
        store_id,
        PERCENT_RANK() OVER(PARTITION BY city ORDER BY sales DESC) AS rank_val
    FROM table1
    GROUP BY store_id, city, sales
) srr ON t1.store_id = srr.store_id
SET t1.ranking = srr.rank_val;

注意事项 #

• 若store_id不是表的唯一主键，需要补充额外的关联条件，保证每行待更新记录能匹配到唯一的排名结果
• 若原SQL中的GROUP BY store_id是为了去重，需要先确认每个store_id仅对应唯一的city和sales值，否则窗口函数的计算结果会不符合预期，需调整分组逻辑

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