Athena十亿行表分组取首行避免资源耗尽的优化方案咨询

有一张包含10亿行数据的表，希望为每行添加对应分组的首行数据，但在Athena执行查询时遇到Query exhausted resources at this scale factor.错误。

表结构（MY_TABLE） #

+--------+---------+-------+
| COL_A  |  COL_B  | COL_C |
+--------+---------+-------+
| item_a | group_a |     1 |
| item_a | group_b |     2 |
| item_b | group_a |     1 |
+--------+---------+-------+

期望结果 #

+--------+---------+-------+----------------+
| COL_A  |  COL_B  | COL_C | FIRST_ITEM_SEQ |
+--------+---------+-------+----------------+
| item_a | group_a |     1 |              1 |
| item_a | group_b |     2 |              1 |
| item_b | group_a |     1 |              1 |
+--------+---------+-------+----------------+

当前执行的查询语句 #

SELECT ITEMS.*,
       FIRST_ITEMS.COL_C AS FIRST_ITEM_DATE
FROM   MY_TABLE ITEMS
       LEFT JOIN (SELECT COL_A, COL_B, MIN(COL_C)
                  FROM   MY_TABLE
                  GROUP BY COL_A, COL_B) FIRST_ITEMS
            ON    ITEMS.COL_A = FIRST_ITEMS.COL_A
            AND   ITEMS.COL_B = FIRST_ITEMS.COL_B
; 

请问有没有更优的实现方案？

原查询通过子查询分组后再做JOIN，对于10亿行的大表来说，JOIN操作会产生大量中间数据，极易触发资源耗尽问题。改用窗口函数可以避免JOIN，直接在原表上计算分组内的首行数据，效率更高：

SELECT 
    COL_A,
    COL_B,
    COL_C,
    MIN(COL_C) OVER (PARTITION BY COL_A, COL_B) AS FIRST_ITEM_SEQ
FROM MY_TABLE;

为什么这个方案更优？ #

• 窗口函数无需对原表做两次扫描（原查询需要扫一次分组，再扫一次主表做JOIN），只需一次扫描即可完成计算，减少IO开销
• 避免了大表JOIN带来的中间数据爆炸，大幅降低资源占用
• 逻辑更简洁，符合Athena对分布式查询的优化逻辑，更适合处理超大规模数据集

如果你的首行数据是指按特定顺序（比如COL_C排序后的第一行），而非单纯取MIN值，可以调整窗口函数的排序规则，结果与原逻辑完全一致：

SELECT 
    COL_A,
    COL_B,
    COL_C,
    FIRST_VALUE(COL_C) OVER (
        PARTITION BY COL_A, COL_B 
        ORDER BY COL_C ASC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS FIRST_ITEM_SEQ
FROM MY_TABLE;

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