2. 使用collect_list和concat_ws进行行转列
3. 使用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
此时求出的结果如下所示：
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
求出的结果如下所示：
province行转列中间值最终结果广东广州,佛山,东莞,中山00001:广州,00002:佛山,00003:东莞,00004:中山广州,佛山,东莞,中山湖南长沙,株洲,湘潭,娄底,邵阳00001:长沙,00002:株洲,00003:湘潭,00004:娄底,00005:邵阳长沙,株洲,湘潭,娄底,邵阳
上述代码中的注意事项：
Original: https://blog.csdn.net/yang_shibiao/article/details/124968139
Author: 电光闪烁
Title: Hive中使用sort_array函数解决collet_list列表排序混乱问题
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/817079/
转载文章受原作者版权保护。转载请注明原作者出处！