一、基础数据
有学生成绩表,包含学生id、语文、数学、英语三科成绩
+-------------+--------+---------+---------+
| student_id | yuwen | shuxue | yingyu |
+-------------+--------+---------+---------+
| 001 | 89 | 95 | 77 |
| 002 | 92 | 83 | 97 |
| 003 | 81 | 94 | 88 |
+-------------+--------+---------+---------+
二、函数介绍
三、多列转多行(横表变竖表)
原始数据为一张横表,分别有三列成绩列,想要转成竖表,需要转换成三列分别为 学生id、学科、成绩,转换完成之后学生id将不再是主键。
期望结果
+-------------+----------+--------+
| student_id | subject | score |
+-------------+----------+--------+
| 001 | 语文 | 89 |
| 001 | 数学 | 95 |
| 001 | 英语 | 77 |
| 002 | 语文 | 92 |
| 002 | 数学 | 83 |
| 002 | 英语 | 97 |
| 003 | 语文 | 81 |
| 003 | 数学 | 94 |
| 003 | 英语 | 88 |
+-------------+----------+--------+
1.union all 完成数据
使用union all 对不同学科的数据进行组合,得到最终结果。
执行SQL
--语文成绩
select student_id,
'语文' as subject,
yuwen as score
from t_student_score_02
union all
--数学成绩
select student_id,
'数学' as subject,
shuxue as score
from t_student_score_02
union all
--英语成绩
select student_id,
'英语' as subject,
yingyu as score
from t_student_score_02
执行结果
+-------------+----------+--------+
| student_id | subject | score |
+-------------+----------+--------+
| 001 | 语文 | 89 |
| 002 | 语文 | 92 |
| 003 | 语文 | 81 |
| 001 | 数学 | 95 |
| 002 | 数学 | 83 |
| 003 | 数学 | 94 |
| 001 | 英语 | 77 |
| 002 | 英语 | 97 |
| 003 | 英语 | 88 |
+-------------+----------+--------+
2.数据拼接后炸裂开
2.1拼接数据
使用concat对科目和科目对应的分数进行拼接,然后使用concat_ws把不同科目数据拼接到一起
执行SQL
select student_id,
concat_ws(',', concat('语文:', yuwen), concat('数学:', shuxue), concat('英语:', yingyu)) as sub_scores
from t_student_score_02
执行结果
+-------------+--------------------+
| student_id | sub_scores |
+-------------+--------------------+
| 001 | 语文:89,数学:95,英语:77 |
| 002 | 语文:92,数学:83,英语:97 |
| 003 | 语文:81,数学:94,英语:88 |
+-------------+--------------------+
2.2 lateral view explode 将成绩列转行
使用lateral view explode 将成绩列转行,然后使用split将科目和分数分开。
执行SQL
select student_id,
split(sub_score, ':')[0] as subject,
split(sub_score, ':')[1] as score
from (select student_id,
concat_ws(',', concat('语文:', yuwen), concat('数学:', shuxue), concat('英语:', yingyu)) as sub_scores
from t_student_score_02)
lateral view explode(split(sub_scores, ',')) t as sub_score
执行结果
+-------------+----------+--------+
| student_id | subject | score |
+-------------+----------+--------+
| 001 | 语文 | 89 |
| 001 | 数学 | 95 |
| 001 | 英语 | 77 |
| 002 | 语文 | 92 |
| 002 | 数学 | 83 |
| 002 | 英语 | 97 |
| 003 | 语文 | 81 |
| 003 | 数学 | 94 |
| 003 | 英语 | 88 |
+-------------+----------+--------+
四、数据准备
--建表语句
CREATE TABLE IF NOT EXISTS t_student_score_02
(
student_id string, -- 学生id
yuwen bigint,--语文成绩
shuxue bigint, --数学成绩
yingyu bigint --英语成绩
)
COMMENT '学生成绩表';
--数据插入语句
insert into t_student_score_02
select student_id,
sum(case when subject = '语文' then score end) as yuwen,
sum(case when subject = '数学' then score end) as shuxue,
sum(case when subject = '英语' then score end) as yingyu
from t_student_score
group by student_id