Athena中不使用ROW_NUMBER实现每组Top1的高效方案咨询

针对10亿行的table_a，按column_a、column_b、column_c分组后取每组column_d排序的Top1，以下是几种比ROW_NUMBER()更高效的实现方式，核心思路是减少不必要的排序和全表扫描：

1. 聚合函数+关联查询（通用SQL方案） #

如果要取column_d最大值对应的行（同理可改MIN取最小值），先通过分组聚合拿到每组的极值，再关联原表获取完整行数据：

SELECT t1.*
FROM table_a t1
INNER JOIN (
    SELECT column_a, column_b, column_c, MAX(column_d) AS top_d
    FROM table_a
    GROUP BY column_a, column_b, column_c
) t2 
    ON t1.column_a = t2.column_a 
    AND t1.column_b = t2.column_b 
    AND t1.column_c = t2.column_c 
    AND t1.column_d = t2.top_d;

注意：如果同一分组内有多个行的column_d等于极值，会返回多行。若需唯一Top1，可额外加入唯一标识列（如主键）到关联条件中。

2. 数据库原生语法优化（针对特定数据库） #

PostgreSQL：DISTINCT ON #

PostgreSQL的DISTINCT ON是专门为分组取首行设计的语法，性能远优于窗口函数，尤其是配合合适索引时：

SELECT DISTINCT ON (column_a, column_b, column_c) *
FROM table_a
ORDER BY column_a, column_b, column_c, column_d DESC;

数据库会按ORDER BY的顺序扫描，遇到每组的第一条记录就直接返回，无需处理全部分组数据。

MySQL：子查询排序+GROUP BY（需注意SQL模式） #

在MySQL中，若关闭ONLY_FULL_GROUP_BY模式，可通过先排序再分组的方式取每组首行：

SELECT *
FROM (
    SELECT *
    FROM table_a
    ORDER BY column_a, column_b, column_c, column_d DESC
) t
GROUP BY column_a, column_b, column_c;

风险提示：该方式依赖MySQL的非标准行为，升级版本或修改SQL模式可能导致结果异常，需结合业务场景验证。

3. 核心优化手段：复合索引 #

无论用哪种方案，必须建立复合索引(column_a, column_b, column_c, column_d DESC)，这是10亿行数据场景下性能提升的关键：

• 索引可以让数据库直接按分组+排序的顺序扫描，避免全表扫描和内存排序
• 聚合查询时，数据库可以通过索引快速计算每组的极值，无需遍历全表
• DISTINCT ON或窗口函数可以直接利用索引定位每组的Top1，大幅减少IO开销

性能对比 #

• ROW_NUMBER()窗口函数：默认需要全表扫描并排序（无索引时），即使有索引，部分数据库仍需生成行号，开销略高于聚合或原生语法
• 聚合+关联：有索引时，分组聚合和关联都能高效执行，适合多数据库通用场景
• 数据库原生语法（如DISTINCT ON）：是特定数据库下的最优解，执行计划最精简

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