社区首页 >专栏 >列转行-多列转多行（横表变竖表）

列转行-多列转多行（横表变竖表）

数据仓库晨曦

发布于 2024-08-19 07:59:06

24800

代码可运行

文章被收录于专栏：数据仓库技术数据仓库技术

运行总次数：0

代码可运行

一、基础数据

有学生成绩表，包含学生id、语文、数学、英语三科成绩

+-------------+--------+---------+---------+
| student_id  | yuwen  | shuxue  | yingyu  |
+-------------+--------+---------+---------+
| 001         | 89     | 95      | 77      |
| 002         | 92     | 83      | 97      |
| 003         | 81     | 94      | 88      |
+-------------+--------+---------+---------+

二、函数介绍

sum
case

三、多列转多行（横表变竖表）

原始数据为一张横表，分别有三列成绩列，想要转成竖表，需要转换成三列分别为学生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