SQLite新增每日访客排名列 如何用子查询优化UPDATE更新效率

前置操作：新增rank列 #

首先执行语句新增排名列：

ALTER TABLE mytable ADD COLUMN rank INTEGER;

最高效方案（SQLite 3.33.0+ 支持） #

SQLite 3.33.0及以上版本支持UPDATE FROM语法，可以一次性完成全表排名更新，无需任何循环，效率最高：

UPDATE mytable
SET rank = t.Day_Rank
FROM (
    SELECT 
        day,
        place,
        -- 按天分组，访客数降序排名
        ROW_NUMBER() OVER (PARTITION BY day ORDER BY visitors DESC) AS Day_Rank
    FROM mytable
) AS t
-- 关联匹配当前更新的行
WHERE mytable.day = t.day 
  AND mytable.place = t.place;

原错误写法问题说明 #

你之前的写法有两个核心问题：

• 子查询返回的是当前天所有地点的排名结果，是一个多行结果集，不能直接赋值给单行的rank字段
• 没有将子查询的排名和外层待更新的行做关联匹配，数据库不知道你要取哪一行的排名值

兼容低版本SQLite方案 #

如果你的SQLite版本低于3.33.0，不支持UPDATE FROM，可以用相关子查询实现，同样无需循环：

UPDATE mytable
SET rank = (
    SELECT COUNT(*) + 1
    FROM mytable t2
    WHERE 
        -- 同天对比
        t2.day = mytable.day 
        -- 统计比当前行访客数更高的行数，加1就是当前行的排名
        AND t2.visitors > mytable.visitors
);

如果存在同天访客数相同的情况，想要实现和ROW_NUMBER一致的不重复排名，可以加排序规则，比如按地点名称排序：

UPDATE mytable
SET rank = (
    SELECT COUNT(*) + 1
    FROM mytable t2
    WHERE 
        t2.day = mytable.day 
        AND (
            t2.visitors > mytable.visitors 
            OR (t2.visitors = mytable.visitors AND t2.place < mytable.place)
        )
);

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