sort_array函数解决collet_list列表排序混乱问题

1. 数据准备

插入数据

with temp  as 
(select  '三国演义' as book, '诸葛亮' as character,  '8' as score 
union all 
select  '三国演义' as book, '曹操' as character,  '8' as score 
union all 
select  '三国演义' as book, '周瑜' as character,  '6' as score 
union all 
select  '三国演义' as book, '孙权' as character,  '5' as score 
union all 
select  '三国演义' as book, '刘备' as character,  '6' as score 
union all 
select  '三国演义' as book, '关羽' as character,  '9' as score 
union all 
select  '三国演义' as book, '张飞' as character,  '7' as score 
union all 
select  '三国演义' as book, '蒙恬' as character,  '6' as score 
union all 
select  '三国演义' as book, '陆逊' as character,  '8' as score 
union all 
select  '三国演义' as book, '夏侯惇' as character,  '7' as score 
union all 
select  '三国演义' as book, '赵云' as character,  '8' as score 
union all 
select  '三国演义' as book, '司马懿' as character,  '6' as score 
union all 
select  '三国演义' as book, '鲁肃' as character,  '5' as score 
union all 
select  '三国演义' as book, '小乔' as character,  '7' as score 
union all 
select  '三国演义' as book, '貂蝉' as character,  '8' as score 
union all 
select  '三国演义' as book, '孙尚香' as character,  '8' as score 
union all 
select  '三国演义' as book, '甄姬' as character,  '9' as score 
union all 
select  '三国演义' as book, '典韦' as character,  '7' as score 
union all 
select  '水浒传' as book, '宋江' as character,  '6' as score 
union all 
select  '水浒传' as book, '高俅' as character,  '5' as score 
union all 
select  '水浒传' as book, '鲁智深' as character,  '7' as score 
union all 
select  '水浒传' as book, '武松' as character,  '8' as score 
union all 
select  '水浒传' as book, '林冲' as character,  '8' as score 
union all 
select  '水浒传' as book, '潘金莲' as character,  '9' as score 
union all 
select  '水浒传' as book, '扈三娘' as character,  '7' as score
)

select * from temp;

数据如下所示：未展示完

2. 使用collect_list和concat_ws进行行转列

在HQL中，我们有这样的需求，需要根据评分筛选出四大名著里面观众最喜欢的人物，并根据评分进行排序，这时我们可以先对每本书开窗，然后求出前5的数据，再以书名为主键进行聚合，使用collect_list函数将对应的行转换成一列，然后使用collect_list进行拼接即可，代码如下所示：

select
    book,
    concat_ws(',', collect_list(character)) as character
from (
    SELECT
        book
        , character
        , score
        , row_number() over(partition by book order by score desc) as row_number_score
    FROM temp
) tt 
where  row_number_score <= 5
group by book;

此时求出的结果如下所示：

3. 使用sort_array函数解决collet_list列表排序混乱问题

当结果求出后可能会存在乱序问题（上述例子数据太少，所以没发现），原因是当不同的reduce进行数据聚合时，即一个省份不同的市在不同的分区中时，会先将这个分区的市排序，然后将所有分区组合在一起，这样使用collet_list就不能达到我们想要的效果了，这时可以先将需要的字段和开窗得出的排序先组合在一起（在此例子中为city和row_number_score组合在一起），然后使用sort_array先排序，然后再将对应的排序去掉，就可以实现此功能，代码如下：

select
    book,
    concat_ws(',', collect_list(character)) as character_original ,
    concat_ws(
        ',',
        sort_array(
            collect_list(
                concat_ws(':', lpad(row_number_score, 5, '0'), character)
            )
        )
    ) as character_mid,
    regexp_replace(
        concat_ws(
            ',',
            sort_array(
                collect_list(
                    concat_ws(':', lpad(row_number_score, 5, '0'), character)
                )
            )
        ),
        '\\d+:',
        ''
    ) as character_last
from (
    SELECT
        book
        , character
        , score
        , row_number() over(partition by book order by score desc) as row_number_score
    FROM temp   
)  tt  
where  row_number_score <= 5
group by book
; 

求出的结果如下所示：

上述代码中的注意事项：

lpad()函数：这个是对排序值（也就是row_number_score）来补位的，当要排序的值过大时，因为sort_array是按顺序对字符进行排序（即11会在2的前面），所以可以使用此函数补位（即将1,2,3,44变成01,02,03,44），这样就能正常排序了。
补充：lpad函数的基本用法
lpad函数将左边的字符串填充一些特定的字符其语法格式如下：lpad(string,n,[pad_string])。

string：字符或者参数；
n：字符的长度，是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符;
pad_string：必选参数（hql中），这个字符串是要粘贴到string的左边，若这个参数未写，lpad函数将会在string的左边粘贴空格（其他语法）。

regexp_replace()函数：这个是替换的，即排序之前将序号使用：跟需要的字段拼接，而排序之后，需要将序号和：去掉。
另外还要注意的是sory_array默认是升序排序，但其中可以带参数，默认为true，即按升序，如果输入false，就会按照降序排序。