如何在AWS Athena V2中模拟LATERAL/CROSS APPLY实现分层限关联查询

业务需求需要支持递归关联数据获取，且每一层关联都能设置LIMIT子句，逻辑示例如下：

SELECT ... FROM artists LIMIT 5
for artist in artists:

    SELECT ... FROM albums WHERE artist_id = artist.id LIMIT 3
    for album in albums:

        SELECT ... FROM tracks WHERE album_id = albums.id LIMIT 2

在PostgreSQL/MySQL等数据库中，可通过LATERAL/CROSS APPLY实现该逻辑：

select *
from
  lateral (
    select ...
    from `Artist`
    limit 5
  ) as `Artist`,

  lateral (
    select ...
    from `Album`
    where `Artist`.`ArtistId` = `Album`.`ArtistId`
    limit 3
  ) as `Album`

AWS Athena文档显示支持CROSS JOIN LATERAL，但使用关联子查询时出现异常，需在Athena V2支持的SQL中模拟LATERAL/CROSS APPLY的行为。

示例数据 #

{"ArtistId":"1","Name":"AC/DC"}
{"ArtistId":"2","Name":"Accept"}

{"AlbumId":"1","Title":"For Those About To Rock We Salute You","ArtistId":"1"}
{"AlbumId":"2","Title":"Balls to the Wall","ArtistId":"2"}
{"AlbumId":"3","Title":"Restless and Wild","ArtistId":"2"}
{"AlbumId":"4","Title":"Let There Be Rock","ArtistId":"1"}
{"AlbumId":"5","Title":"Third Thing","ArtistId":"2"}

测试SQL（预期逻辑） #

SELECT *
FROM LATERAL (SELECT * FROM Artist LIMIT 2) AS artist,
     LATERAL (SELECT * FROM Album WHERE artist.ArtistId = Album.ArtistId LIMIT 2) AS album

预期输出 #

Athena V2基于Presto引擎，可通过**窗口函数ROW_NUMBER()**结合分组过滤模拟LATERAL/CROSS APPLY的行为，核心逻辑是给每个关联分组内的行分配序号，再筛选出序号不超过指定LIMIT的行。

示例场景实现SQL #

SELECT 
    artist.ArtistId,
    artist.Name,
    album.AlbumId,
    album.Title
FROM (
    -- 筛选目标数量的Artist（LIMIT 2）
    SELECT * FROM Artist LIMIT 2
) artist
JOIN (
    -- 按ArtistId分组，给每个Artist下的Album编号，保留前2条
    SELECT 
        AlbumId,
        Title,
        ArtistId,
        ROW_NUMBER() OVER (PARTITION BY ArtistId ORDER BY AlbumId) AS row_num
    FROM Album
) album ON artist.ArtistId = album.ArtistId
WHERE album.row_num <= 2 -- 对应Album层的LIMIT 2
ORDER BY artist.ArtistId, album.row_num;

多层递归关联扩展（如Artist -> Album -> Track） #

如果需要三层关联并给每层设置LIMIT，可嵌套复用相同逻辑：

SELECT 
    artist.ArtistId,
    artist.Name,
    album.AlbumId,
    album.Title,
    track.TrackId,
    track.Name AS TrackName
FROM (
    SELECT * FROM Artist LIMIT 5
) artist
JOIN (
    SELECT 
        AlbumId,
        Title,
        ArtistId,
        ROW_NUMBER() OVER (PARTITION BY ArtistId ORDER BY AlbumId) AS album_row_num
    FROM Album
) album ON artist.ArtistId = album.ArtistId
JOIN (
    SELECT 
        TrackId,
        Name,
        AlbumId,
        ROW_NUMBER() OVER (PARTITION BY AlbumId ORDER BY TrackId) AS track_row_num
    FROM Track
) track ON album.AlbumId = track.AlbumId
WHERE 
    album.album_row_num <= 3 -- Album层LIMIT 3
    AND track.track_row_num <= 2 -- Track层LIMIT 2
ORDER BY artist.ArtistId, album.album_row_num, track.track_row_num;

关键说明 #

• 该方法依赖ROW_NUMBER()窗口函数，Athena V2完全支持；
• PARTITION BY指定分组关联字段，ORDER BY确保行顺序稳定（可根据业务需求调整排序字段）；
• 相比直接使用LATERAL，这种写法兼容性更强，能规避Athena中LATERAL关联子查询的异常问题。

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