sql - 如何获取数组中元素的索引并返回 Hive 中的下一个元素？

Question

我有两张 table 。

表格1:

id    |   array1
1     |     ['a', 'b', 'c']
2     |     ['b', 'a', 'c']
3     |     ['c', 'b', 'a']

表2:

id    |    value2
1     |     'b'
3     |     'a'

我希望得到下表:

id    |    value3
1     |     'c'
2     |     'b'
3     |     'c'

说明:我想要的是，如果table1 中的id 在table2 中不存在，则返回array1 的第一个元素。如果 table1 中的 id 存在于 table2 中，则返回 array1 中 value2 的下一个元素(在这种情况下，如果 value2 是 array1 中的最后一个元素，则返回 array1 的第一个元素)

我怎样才能实现这个目标？

Answer 1

使用poseexplode分解数组，连接table2，计算连接行的位置，聚合，提取数组元素。

演示:

with table1 as(
select stack(3,
1, array('a', 'b', 'c'),
2, array('b', 'a', 'c'),
3, array('c', 'b', 'a')
) as (id,array1)
),

table2 as(
select stack(2,
1,'b',
3,'a'
) as (id,value2)
)

select s.id, nvl(s.array1[pos], s.array1[0]) value3
from
(
select s.id, s.array1, min(case when t2.id is not null then s.pos+1 end) pos
from
(
select t.id, t.array1, a.pos, a.value1
  from table1 t
       lateral view posexplode(t.array1) a as pos, value1
)s left join table2 t2 on s.id=t2.id and s.value1=t2.value2  
group by s.id, s.array1
)s
order by id

结果:

id  value3
1   c
2   b
3   c