SQL面试题练习 —— 有序行转列

1 题目

有学生各学科分数表，记录了学生的各科分数，请按照学生粒度，生成两列数据分别为学科和分数，要求学科内的顺序与分数顺序一致。

样例数据

期望结果

2 建表语句

--建表语句
create table if not exists t17_student_score
(student string,subject string,score   bigint
);--插入数据insert into t17_student_score(student, subject, score)
values ('张三', '语文', 95),('张三', '数学', 80),('张三', '英语', 82),('李四', '语文', 90),('李四', '数学', 90),('李四', '英语', 93),('王五', '语文', 88),('王五', '数学', 92),('王五', '英语', 88),('赵六', '语文', 77),('赵六', '数学', 84),('赵六', '英语', 68);

3 题解

1. 拼接学科和成绩

使用struct将学科和成绩转换为结构体，这样绑定了学科和成绩。

select student,struct(subject, score) as subject_score
from t17_student_score

执行结果

2. 使用collect_list将结构体转换为数组

使用collect_list函数，将数据进行行转列，得到数组。注意：不同学科顺序是不能保证有序的，有序是巧合。

select student,collect_list(struct(subject, score)) as subject_score
from t17_student_score
group by student

执行结果

+----------+----------------------------------------------------+
| student  |                   subject_score                    |
+----------+----------------------------------------------------+
| 张三       | [{"subject":"语文","score":95},{"subject":"数学","score":80},{"subject":"英语","score":82}] |
| 李四       | [{"subject":"语文","score":90},{"subject":"数学","score":90},{"subject":"英语","score":93}] |
| 王五       | [{"subject":"语文","score":88},{"subject":"数学","score":92},{"subject":"英语","score":88}] |
| 赵六       | [{"subject":"语文","score":77},{"subject":"数学","score":84},{"subject":"英语","score":68}] |
+----------+----------------------------------------------------+

3. 将结构体数组转换为map

select student,map_from_entries(collect_list(struct(subject, score)))  as subject_score
from t17_student_score
group by student

执行结果

4. 从map中分别取出keys和values作为subjects和scores,得到最终结果。

select student,concat_ws(',', map_keys(subject_score))   as subjects,concat_ws(',', map_values(subject_score)) as scores
from (select student,map_from_entries(collect_list(struct(subject, score))) as subject_scorefrom t17_student_scoregroup by student) t

执行结果