Hive中collect_list()排序问题详解

有个用户好友表：字段如下
uid  fans_uid   score
返回：uid, fans_uid_list【fans_uid的拼接串，按照score降序拼接】

create temporary table tb_user_fans as 
select 1 as uid,'a' as fans_uid,3 as score
union all
select 1 as uid,'b' as fans_uid,1 as score
union all
select 1 as uid,'c' as fans_uid,4 as score
union all
select 1 as uid,'d' as fans_uid,3 as score
union all
select 1 as uid,'e' as fans_uid,2 as score
union all
select 2 as uid,'a' as fans_uid,4 as score
union all
select 2 as uid,'b' as fans_uid,3 as score
union all
select 2 as uid,'c' as fans_uid,1 as score
union all
select 2 as uid,'d' as fans_uid,2 as score
union all
select 2 as uid,'e' as fans_uid,5 as score
union all
select 3 as uid,'a' as fans_uid,6 as score
union all
select 3 as uid,'b' as fans_uid,3 as score
union all
select 3 as uid,'c' as fans_uid,5 as score
;

select
    uid,
    collect_list(fans_uid) as fans_uid_list
from (
select 
    uid
    ,fans_uid
    ,score
    ,row_cnt
from (
    select
        uid
        ,fans_uid
        ,score
        ,row_number() over (partition by uid order by score desc ) as row_cnt
    from tb_user_fans
    ) 
order by row_cnt asc
)
group by uid
order by uid

select 
    uid
    ,regexp_replace(
        concat_ws(
            ','
            ,sort_array(
                collect_list(
                    conact_ws(':' ,lpad(cast(rank_num as string),5,'0') ,fans_uid)
                )
            )
        )
        ,'\\d+\:'
        ,''
    ) as fans_uid_list
from (
    select
        uid
        ,fans_uid
        ,score
        ,row_number() over (partition by uid order by score desc ) as row_cnt
    from tb_user_fans
)
group by uid

select
    uid,
    collect_list(fans_uid) as fans_uid_list
from (
  select 
      uid
      ,fans_uid
      ,score
  from tb_user_fans distribute by uid sort by uid,score desc 
)
group by uid