如何在雅典娜中加入非最重要的功能？

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行上的错误，并且我有此错误

stytax_error：line 16:19：列“段”无法解析

我无法弄清楚问题在哪里。 如果没有 语句，则效果很好。

先感谢您

Answer 1

presto（雅典娜的基础SQL引擎）支持仅在交叉加入的右侧。例如：

-- 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	动物	1
2	[狗，猫，鸟]	1
[	狗，猫，鸟]	1
[	狗，猫，鸟]	鸟
2	[牛，猪]	牛
狗	[牛，牛，猪]	猪

猫 似乎速记符号（trip，unnest（semgments）作为t（semgment））无法正常工作，然后再加入条件，请尝试将其展开到完整形式：

from trip
cross join UNNEST(segments) as t(segment)
left join (...)
on ...