列转行
函数:concat_ws(’,'collect_set(column))
说明:collect_list 不去重,collect_set 去重,column的数据类型必须为string,若不是可使用cast(column as string)进行转化
表(1)转为表(2)
表(1)
col_lie.user_id col_lie.order_id
104399 1715131
104399 2105395
104399 1758844
104399 981085
104399 2444143
104399 1458638
104399 968412
104400 1609001
104400 2986088
104400 1795054
select user_id,
concat_ws(',',collect_list(order_id)) as order_value
from col_lie
group by user_id
行转列
表(2)转为表(1)
表(2)
ie_col.user_id lie_col.order_value
104408 2909888,2662805,2922438,674972,2877863,190237
104407 2982655,814964,1484250,2323912,2689723,2034331,1692373,677498,156562,2862492,338128
104406 1463273,2351480,1958037,2606570,3226561,3239512,990271,1436056,2262338,2858678
104405 153023,2076625,1734614,2796812,1633995,2298856,2833641,3286778,2402946,2944051,181577,464232
104404 1815641,108556,3110738,2536910,1977293,424564
104403 253936,2917434,2345879,235401,2268252,2149562,2910478,375109,932923,1989353
104402 3373196,1908678,291757,1603657,1807247,573497,1050134,3402420
104401 814760,213922,2008045,3305934,2130994,1602245,419609,2502539,3040058,2828163,3063469
104400 1609001,2986088,1795054,429550,1812893
104399 1715131,2105395,1758844,981085,2444143,1458638,968412
select user_id,order_value,order_id
from lie_col
lateral view explode(split(order_value,','))