Skip to main content

常见大数据面试SQL-按照顺序进行行转列拼接

一、题目​

已知有表中含有两列数据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进行拼接​

执行SQL

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

查询结果

+------------+
|    _c0     |
+------------+
| 00001:20   |
| 00002:10   |
| 00008:120  |
| 00009:30   |
| 00011:50   |
| 00022:40   |
+------------+

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

执行SQL

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

查询结果

+----------------------------------------------------+
|                        _c0                         |
+----------------------------------------------------+
| ["00001:20","00002:10","00008:120","00009:30","00011:50","00022:40"] |
+----------------------------------------------------+

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

执行SQL

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

查询结果

+---------------------+
|         _c0         |
+---------------------+
| 20,10,120,30,50,40  |
+---------------------+

四、建表语句和数据插入

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