来看一道互联网公司的面试题：

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

给出数据源：每个uid，有很多对应的fans_uid，每个fans_uid 都对应一个score，我们需要按uid分组，将fans_uid 的score按降序排序，将fans_uid 放在一个列表中，做好友推荐

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 ;

我想要的结果：按照uid分组，按照score降序排序，取出fans_uid放到列表里

1 [c,d,a,e,b] 2 [e,a,b,d,c] 3 [a,c,b]

解法一：

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

如果数据量大不推荐使用，因为使用到了全局排序order by，只有一个reducer，那么数据量大计算要很长时间。

解法二：

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

这里将row_cnt放在了fans_uid之前，用冒号分隔，然后用sort_array函数对collect_list之后的结果进行排序（只支持升序）。特别注意，rank必须要在高位补足够的0对齐，因为排序的是字符串而不是数字，如果不补0的话，按字典序排序就会变成1, 10, 11, 12, 13, 2, 3, 4…，又不对了。 将排序的结果拼起来之后，用regexp_replace函数替换掉冒号及其前面的数字，大功告成。

解法三：最优解，写法简洁

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

诀窍是使用带有DISTRIBUTE BY和SORT BY语句的子查询

---

以上内容仅供参考学习，如有侵权请联系我删除！ 如果这篇文章对您有帮助，左下角的大拇指就是对博主最大的鼓励。 您的鼓励就是博主最大的动力！

---

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，会注明原创字样,如未注明都非原创，如有侵权请联系删除！；3.作者投稿可能会经我们编辑修改或补充；4.本站不提供任何储存功能只提供收集或者投稿人的网盘链接。