大数据面试SQL041 按照顺序进行行转列拼接

一、题目

今天这个题目来自群里的小伙伴考我的：已知有表中含有两列数据id，val,数据内容如下，请按照id的大小将val进行拼接。

+-----+------+
| id  | val  |
+-----+------+
| 1   | 20   |
| 2   | 10   |
| 8   | 120  |
| 9   | 30   |
| 11  | 50   |
| 22  | 40   |
+-----+------+

二、分析

首先我们知道collect_list拼接字符串是无序的，所以我们即便按照顺序将原始数据排好，也不能保证结果有序。所以我们将id和val进行拼接，这样对整个字符串进行排序就会按照id的顺序排序。这里需要注意，因为id是数字类型，直接拼接会导致按照字符顺序，即11在2前面，为了解决这个问题，我们需要左补零。然后使用字符串拼接以后，使用sort_array()函数，保证结果有序，然后转化成字符串，然后再将拼接上的id替换掉。

整体考察的collect_list、collect_list结果不保证有序，concat,sort_array,regexp_replace等函数

维度	评分
题目难度	⭐️⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

三、SQL

1.将ID进行左补0保证所有数据结果位数相同，然后与和val进行拼接

select concat_ws(':',lpad(id,5,0),val)
from t_table_041

查询结果

2.将数据进行聚合，并将结果进行排序

select sort_array(collect_list(concat_ws(':',lpad(id,5,0),val)))
from t_table_041

查询结果

3.将结果进行字符串替换，将补的ID去掉。得到最终结果

select regexp_replace(concat_ws(',',sort_array(collect_list(concat_ws(':',lpad(id,5,0),val)))),'\\d+\:','')
from t_table_041

查询结果

四、建表语句和数据插入

--建表语句
create table t_table_041
(
id bigint COMMENT '用户ID',
val string COMMENT '登录日期'
) COMMENT '用户登录记录表'
stored as orc
;
--插入数据
insert into t_table_041(id,val)
values
(1,'20'),
(2,'10'),
(8,'120'),
(9,'30'),
(11,'50'),
(22,'40')