Amazon RedShift超大规模表中高效查询各分组最新行的解决方案

现有两张表：

• 大表Users (user_id, status, timestamp, ...)，数据量超500亿行
• 小表SpecialUsers (user_id, ...)，数据量仅60万行

需求为查询每位特殊用户的最新状态，当前使用的数据库为Amazon RedShift，不支持SELECT DISTINCT ON()语法。

已尝试的失败方案 #

此前采用自连接方案执行时触发磁盘已满错误，SQL如下：

with users_with_status (user_id, status, timestamp)
as (
        select su.user_id, u.instance_type, u.timestamp
        from specialusers su
        join users u on su.user_id = u.user_id
)
select l.instance_id, l.instance_type
from users_with_status l
left outer join users_with_status r
     on l.user_id = r.user_id and l.timestamp < r.timestamp
where r.timestamp is null;

该方案核心问题是关联后的数据自连接会产生极高的IO开销，即使先通过小表过滤了部分数据，资源消耗依然超出集群负载。

采用窗口函数单遍扫描关联后的数据集即可，无需自连接，SQL如下：

SELECT user_id, status, timestamp
FROM (
    SELECT 
        su.user_id,
        u.status,
        u.timestamp,
        ROW_NUMBER() OVER (PARTITION BY su.user_id ORDER BY u.timestamp DESC) AS rn
    FROM SpecialUsers su
    INNER JOIN Users u ON su.user_id = u.user_id
    -- 可选优化：如果业务允许时间范围过滤，加上该条件可进一步减少数据扫描量
    -- WHERE u.timestamp >= DATEADD(month, -3, CURRENT_DATE)
) AS user_status_ranked
WHERE rn = 1;

方案优势 #

1. 仅需一次关联大表和小表，直接过滤掉所有非特殊用户的无关数据，无需全表扫描500亿行全量数据
2. 窗口函数ROW_NUMBER()为单遍聚合计算，不会像自连接那样产生大量临时中间数据，磁盘和内存消耗降低90%以上
3. 适配RedShift的MPP架构，分区排序逻辑可分布式并行执行，执行效率远高于自连接方案

拓展说明 #

• 若同一个用户存在多条相同最大时间戳的记录，需要全部返回时，将ROW_NUMBER()替换为RANK()即可
• 若Users表的分布键（dist key）设为user_id，排序键（sort key）设为timestamp，该查询的执行速度会提升数倍

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