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_score
      from t17_student_score
      group by student) t

执行结果