【presto】行转列和列转行

行转列

数据源:

select *
  from test.tmp_row_to_col
;
568810800017635002	210100026066367206
568810800017635002	210100026070369019
568810800017635002	210100026038823395
568810800017635002	210100026038921817
568811800170738001	210100016048730468
568811800170738001	210100016034424946
568811800170738001	210100016034424947
568811800170738001	210100016034424948
568817000105010000	210100006034670622
568817000105010000	210100006045431694

最终结果是这样
在这里插入图片描述
hive语法
collect_set转为数组并去重,concat_ws将数组用逗号间隔连接成字符串

select user_id, concat_ws(',', collect_set(order_id)) as order_ids
  from test.tmp_row_to_col
 group by user_id
;

+---------------------+------------------------------------------------------------------------------+
|       user_id       |                                  order_ids                                   |
+---------------------+------------------------------------------------------------------------------+
| 568811800170738001  | 210100016034424946,210100016048730468,210100016034424947,210100016034424948  |
| 568810800017635002  | 210100026066367206,210100026070369019,210100026038823395,210100026038921817  |
| 568817000105010000  | 210100006045431694,210100006034670622                                        |
+---------------------+------------------------------------------------------------------------------+

presto语法
array_agg转为数组,array_distinct去重,array_join将数组用逗号间隔连接成字符串

select user_id, array_join(array_distinct(array_agg(order_id)), ',') as order_ids
  from test.tmp_row_to_col
 group by user_id
;

在这里插入图片描述

列转行

即将上面的两张图顺序调换一下

hive语法
split将````order_ids拆分成数组,lateral view explode```将数组炸裂开


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

+---------------------+---------------------+
|       user_id       |      order_id       |
+---------------------+---------------------+
| 568811800170738001  | 210100016048730468  |
| 568811800170738001  | 210100016034424947  |
| 568811800170738001  | 210100016034424948  |
| 568811800170738001  | 210100016034424946  |
| 568817000105010000  | 210100006034670622  |
| 568817000105010000  | 210100006045431694  |
| 568810800017635002  | 210100026066367206  |
| 568810800017635002  | 210100026070369019  |
| 568810800017635002  | 210100026038823395  |
| 568810800017635002  | 210100026038921817  |
+---------------------+---------------------+

presto语法
splitorder_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) ;

在这里插入图片描述

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值