Athena: Reference left-hand row in right-hand subquery? (correlated subquery, CROSS JOIN LATERAL)

0

I have a usecase where I need to support recursive relationship fetching, with a possible LIMIT clause on each depth of relationship.

Something like:

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

In Postgres/MySQL etc, you can do this with 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`

Athena says that it supports CROSS JOIN LATERAL:

However if you try to use this with a correlated subquery, it wigs out Is there any way to emulate the behavior of LATERAL/CROSS APPLY with the variant of SQL that Athena V2 supports?

  • Enter image description here
已提問 2 年前檢視次數 109 次
1 個回答
0

Correlated Subqueries don't support LIMIT

SELECT *
FROM (VALUES 1, 2, 3) table1(a)
LEFT JOIN LATERAL
(SELECT b FROM (VALUES 2,4) table2(b) WHERE b > a limit 1) ON TRUE

https://github.com/trinodb/trino/issues/4673

已回答 2 個月前

您尚未登入。 登入 去張貼答案。

一個好的回答可以清楚地回答問題並提供建設性的意見回饋,同時有助於提問者的專業成長。

回答問題指南