Hive和Presto行轉列、列轉行

• 1、行轉列

資料源：

要實作的效果：

Hive：collect_set轉為數組并去重，concat_ws将數組用逗号間隔連接配接成字元串

select user_id
    , concat_ws(',', collect_set(order_id)) as order_ids
from tmp.tmp_row_to_col
where 1 = 1
group by user_id ;
           

Presto：array_agg轉為數組，array_distinct去重，array_join将數組用逗号間隔連接配接成字元串

select user_id
    , array_join(array_distinct(array_agg(order_id)), ',') as order_ids
from tmp.tmp_row_to_col
where 1 = 1
group by user_id ;
           

• 2、列轉行

即将上面的兩張圖順序調換一下

Hive：split将order_ids拆分成數組，lateral view explode将數組炸裂開

select a.user_id
    , b.order_id
from tmp.tmp_col_to_row a
lateral view explode(split(order_ids, ',')) b as order_id ;
           

Presto：split将order_ids拆分成數組，cross join unnest将數組炸裂開，要注意一下兩種文法的表名縮寫位置

select a.user_id
    , b.order_id
from tmp.tmp_col_to_row a
cross join unnest(split(order_ids, ',')) as b(order_id) ;