HiveQL collect_list保持顺序小记

有以下Hive表的定义：

这张表是我们业务里话题推荐分值表的简化版本。category_id代表分类ID，topic_id是话题ID，score是评分值。rank代表每个分类下话题分值的排名，用开窗函数计算出来的：

row_number() over(partition by t.category_id order by t.score desc)

在对外提供推荐结果时，我们会将每个小组下排名前1000的话题ID取出，拼成一个逗号分隔的字符串，处理之后送入HBase供调用方查询。拼合的SQL语句如下：

看起来没什么问题？但实际上是错误的。输出结果中总会有一些category_id对应的列表顺序异常，比如本来排名正数与排名倒数的两批ID调换了位置，即rank变成了 n-3, n-2, n-1, n, 5, 6, 7, ..., n-4, 1, 2, 3, 4 。

产生这个问题的根本原因自然在MapReduce，如果启动了多于一个mapper/reducer来处理数据，select出来的数据顺序就几乎肯定与原始顺序不同了。考虑把mapper数固定成1比较麻烦（见 我之前写的那篇Hive调优文章 ），也不现实，所以要迂回地解决问题：把rank加进来再进行一次排序，拼接完之后把rank去掉。如下：

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

将排序的结果拼起来之后，用regexp_replace函数替换掉冒号及其前面的数字，大功告成。

顺便看一下Hive源码中collect_list和collect_set函数对应的逻辑吧。

当需要collect_list(item_id) groupby user_id需要保持item_list的顺序时，用辅助列拼接item_id，sort_array做排序

lpad左补齐相同位数，保证字符串排序不会错

参考博客

第一种方法：在布局图里面对行标签或列标签里面的字段进行拖动即可，

需要排在前面的拖到横标签或者列标签的最上面位置，以此类推。

第二种方法：在数据透视表字段列表行列标签中，鼠标左键单击，选择上移或者下移执行

---