优化更新用户首次、末次及倒数第二次事件时间的SQL查询

原语句存在的问题 #

• 冗余计算：CTE中同时计算正反两个rank窗口函数，且SET部分使用3个关联子查询，每个用户需要重复扫描3次CTE结果，性能损耗极大
• 逻辑隐患：使用rank()计算排名时，若同一用户存在多条created_at完全相同的事件，会出现多个排名为1/2的结果，子查询会直接抛出「返回多行」的报错
• 冗余扫描：CTE结果中每个用户会返回对应所有事件的行，更新时关联重复行，浪费IO资源

优化方案 #

1. 核心逻辑改写（完全移除子查询） #

改用分组聚合+数组函数的方式，一次分组直接计算出单个用户所需的三个时间字段，仅需单次扫描history表数据（适配PostgreSQL语法，和原语句语法环境一致）：

with user_event_arr as (
  select
    user_id,
    array_agg(created_at order by created_at desc) as times
  from history
  where type = 'SomeType'
    and user_id between $1 and $2
  group by user_id
),
user_event_stats as (
  select
    user_id,
    times[1] as latest_at,
    times[2] as previous_at,
    times[cardinality(times)] as first_at
  from user_event_arr
)
update users u
set
  latest_at = s.latest_at,
  previous_at = s.previous_at,
  first_at = s.first_at
from user_event_stats s
where u.id = s.user_id;

如果数据库不支持数组聚合，也可以用窗口函数+行转列的方式提前按用户聚合为单行，同样可以移除子查询：

with ranked as (
  select
    user_id,
    created_at,
    row_number() over (partition by user_id order by created_at desc) as rn_desc,
    row_number() over (partition by user_id order by created_at asc) as rn_asc
  from history
  where type = 'SomeType'
    and user_id between $1 and $2
)
, user_event_stats as (
  select
    user_id,
    max(case when rn_desc = 1 then created_at end) as latest_at,
    max(case when rn_desc = 2 then created_at end) as previous_at,
    max(case when rn_asc = 1 then created_at end) as first_at
  from ranked
  group by user_id
)
update users u
set
  latest_at = s.latest_at,
  previous_at = s.previous_at,
  first_at = s.first_at
from user_event_stats s
where u.id = s.user_id;

2. 索引优化 #

新增联合覆盖索引btree (type, user_id, created_at)，可以完全匹配查询的过滤条件、分组逻辑、排序需求，无需回表访问history的原始数据，上亿行表的查询速度可以提升数倍。

额外优化建议 #

• 批量执行时尽量选择连续的user_id范围，减少索引扫描的随机IO
• 若业务允许同一时间存在多个事件的场景，统一使用row_number()替代rank()避免重复排名问题
• 可以在批量更新前先确认对应user_id范围的history数据量，避免单次批量处理数据过多导致锁等待

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