常见大数据面试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')本文同步在微信公众号”数据仓库技术“和个人博客”数据仓库技术 (opens in a new tab)“发表；