collect_list保持顺序

当需要collect_list(item_id) groupby user_id需要保持item_list的顺序时，用辅助列拼接item_id，sort_array做排序
lpad左补齐相同位数，保证字符串排序不会错

select device_id,
       regexp_replace(
         concat_ws(',',
           sort_array(
             collect_list(
               concat_ws(':',lpad(cast(click_order as string),5,'0'),cast(item_id as string))
             )
           )
         ),
       '\\d+\:','')
from user_click_history
where click_order >= 1 and click_order <= 1000
group by device_id;

参考博客