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

0. 相关文章链接

开发随笔文章汇总

1. 数据准备

建表语句：

create table temp(
    province string,
    city string,
    score bigint
)
;

插入数据：

INSERT OVERWRITE
    table temp
VALUES
    ('广东', '佛山', 8),
    ('广东', '广州', 10),
    ('广东', '中山', 1),
    ('广东', '东莞', 5),
    ('湖南', '娄底', 6),
    ('湖南', '株洲', 9),
    ('湖南', '湘潭', 7),
    ('湖南', '长沙', 10),
    ('湖南', '邵阳', 5);

数据如下所示：
provincecityscore广东佛山8广东广州10广东中山1广东东莞5湖南株洲9湖南湘潭7湖南长沙10湖南邵阳5湖南娄底6

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

    在HQL中，我们有这样的需求，需要根据评分删选出每个省份排名前5的城市，并根据评分进行排序，这时我们可以先对每个省份开窗，然后求出前5的数据，再以省份为主键进行聚合，使用collect_list函数将对应的行转换成一列，然后使用collect_list进行拼接即可，代码如下所示：

select
    province,
    concat_ws(',', collect_list(city)) as 行转列
from (
    SELECT
        province
        , city
        , score
        , row_number() over(partition by province order by score desc) as row_number_score
    FROM temp
    having row_number_score <= 5
)
group by province
;  

此时求出的结果如下所示：
province行转列广东广州,佛山,东莞,中山湖南长沙,株洲,湘潭,娄底,邵阳

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

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

select
    province,
    concat_ws(',', collect_list(city)) as 行转列,
    concat_ws(
        ',',
        sort_array(
            collect_list(
                concat_ws(':', lpad(row_number_score, 5, '0'), city)
            )
        )
    ) as 中间值,
    regexp_replace(
        concat_ws(
            ',',
            sort_array(
                collect_list(
                    concat_ws(':', lpad(row_number_score, 5, '0'), city)
                )
            )
        ),
        '\\d+:',
        ''
    ) as 最终结果
from (
    SELECT
        province
        , city
        , score
        , row_number() over(partition by province order by score desc) as row_number_score
    FROM temp
    having row_number_score <= 5
)
group by province
; 

求出的结果如下所示：
province行转列中间值最终结果广东广州,佛山,东莞,中山00001:广州,00002:佛山,00003:东莞,00004:中山广州,佛山,东莞,中山湖南长沙,株洲,湘潭,娄底,邵阳00001:长沙,00002:株洲,00003:湘潭,00004:娄底,00005:邵阳长沙,株洲,湘潭,娄底,邵阳
上述代码中的注意事项：

1. lpad()函数：这个是对排序值（也就是row_number_score）来补位的，当要排序的值过大时，因为sort_array是按顺序对字符进行排序（即11会在2的前面），所以可以使用此函数补位（即将1,2,3,44变成01,02,03,44），这样就能正常排序了。
2. regexp_replace()函数：这个是替换的，即排序之前将序号使用：跟需要的字段拼接，而排序之后，需要将序号和：去掉。
3. 另外还要注意的是sory_array默认是升序排序，但其中可以带参数，默认为true，即按升序，如果输入false，就会按照降序排序。

注：****其他相关文章链接由此进 -> 开发随笔文章汇总