我對雅典娜有這個疑問
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陳述句，這很好用。
先感謝您
uj5u.com熱心網友回復：
Presto（Athena 的底層 SQL 引擎）UNNEST僅支持CROSS JOIN. 例如：
-- 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 | [狗，貓，鳥] | 狗 |
| 1 | [狗，貓，鳥] | 貓 |
| 1 | [狗，貓，鳥] | 鳥 |
| 2 | [牛、豬] | 牛 |
| 2 | [牛、豬] | 豬 |
似乎速記符號 ( from trip, UNNEST(segments) as t(segment)) 后跟另一個帶有on條件的連接時無法正常作業，請嘗試將其擴展為完整形式：
from trip
cross join UNNEST(segments) as t(segment)
left join (...)
on ...
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/472772.html