如何与雅典娜unnest函数连接?

Question

我有一个关于雅典娜的查询

trip.id as tripid
, segment.id as segmentid
, segment.distance as mileage
, segment.maxspeed as maxspeed
, segment.duration as duration
, segment.roadtype as roadtype
, segment.timeslotdata as timeslots
, extract( week from (from_unixtime(trip.referencedate /1000))) as weekyear
, extract( year from (from_unixtime(trip.referencedate /1000))) as year
, extract( month from (from_unixtime(trip.referencedate/1000))) as month
, unn.firstpositionlat
, unn.firstpositionlong
from
trip
, UNNEST(segments) as t(segment)
left join
(
select
position.latitude as firstpositionlat
, position.longitude as firstpositionlong
, position.id as id
from
trip
, UNNEST(segments) as t(segment)
, UNNEST(segment.positions) as t(position)
where
anno = 2021
and mese = 01
and giorno = 01
and tscid = 'XXX'
)
unn
on
segment.startpositionid = unn.id
where
anno = 2021
and mese = 01
and giorno = 01
and tscid = 'XXX'

问题是我不能加入，因为在16:19行有一个错误，我有这个错误

SYNTAX_ERROR: line 16:19: Column 'segments' cannot be resolved

我不知道问题出在哪里。如果不使用ON语句，则可以正常工作。

提前谢谢你

Answer 1

Presto (Athena的底层SQL引擎)只在CROSS JOIN的右侧支持UNNEST。例如:

-- sample data
WITH dataset (id, animals) AS (
values (1, ARRAY['dog', 'cat', 'bird']),
(2, ARRAY['cow', 'pig'])
) 
-- query
SELECT id, animals, a
FROM dataset
CROSS JOIN UNNEST(animals) AS t (a);

输出:

<表类>id动物tbody><<tr>1(狗,猫,鸟)狗1(狗,猫,鸟)猫1(狗,猫,鸟)鸟2(牛、猪)牛2(牛、猪)猪