---------------------------------------------------------------------------------------------------------------hive
--------------------customerid
select s_customer_profile_day.customerid
from (select customerid, devset.deviceid
from deviceprofile.s_customer_profile_day
LATERAL VIEW explode(devids) devset AS deviceid) s_customer_profile_day
left join
(select deviceid
from deviceprofile.s_ups_deviceprofile_day) s_ups_deviceprofile_day
on s_customer_profile_day.deviceid = s_ups_deviceprofile_day.deviceid
---------------------------------------------------------------------------------------------------------------presto
--------------------customerid
select s_customer_profile_day.customerid
from (select customerid, devset.deviceid
from deviceprofile.s_customer_profile_day
cross join unnest(devids) as devset(deviceid)) s_customer_profile_day
left join
(select deviceid
from deviceprofile.s_ups_deviceprofile_day) s_ups_deviceprofile_day
on s_customer_profile_day.deviceid = s_ups_deviceprofile_day.deviceid
hive 行转列使用LATERAL VIEW explode(devids) devset AS deviceid
presto行转列使用cross join unnest(devids) as devset(deviceid)
