Hive:在Hive sql中转置的方法

Question

我正在尝试转置Hive中的数据集，它的结构如下：

Id1  Id2 Event
 1    1   7
 2    2   3
 2    2   7
 3    3   8
 3    3   7
 1    2   3
 1    2   7

一些id组合有许多事件(接近20个左右的唯一事件)，我需要为Id1和Id2的每个唯一组合将它们转置为20列，例如：

Id1 Id2 event1  event2  event3 event4 event5.......event20
1    1    7       
2    2    3        7
3    3    8        7
1    2    3        7

如果可能，我还想知道如何在不使用20 max()函数的情况下以以下形式转置：(这里的事件值将是后缀，并且每次出现都将被计数为1)

Id1 Id2 event_7 event_3  event_8 ........
1    1    1       
2    2    1        1
3    3    1                1
1    2    1        1

非常感谢!

Answer 1

你能试试这个吗?看看这个能不能用？我要做的是，首先对Id1的数据进行排序，因此id1的重复值的排序将是1,2。然后使用rank和'event_‘连接，形成类似'event_1'，'event_2’之类的东西。

下面有两个选项。1)使用named_struct 2)使用to_map我还没有尝试过，所以可能有一些语法问题，但希望你能理解。

with data as (
    select 
        id1, id2, event, 
        row_number() over (partition by id1 order by id1) as rnk
    from table
    ),
collect_data as (   
    select id1, id2, collect_set(named_struct(concat("event_', rnk), event)) kv
    from data
    group by id1,id2
    )
select id1, id2, kv[0]['event_1'], kv[0]['event_2']...
from collect_data;

或

with data as (
    select 
        id1, id2, event, 
        row_number() over (partition by id1 order by id1) as rnk
    from table
    ),
collect_data as (   
select id1, id2, to_map(concat('event_',rnk), event) as kv
from data
group by id1, id2
    )
select id1, id2, 
  kv['c1'] AS c1,
  kv['c2'] AS c2,
  kv['c3'] AS c3    
from collect_data;