Skip to main content

12.行列转换-横表竖表互相转换

一、多行转多列（竖表转横表）​

原始数据中是一个竖表，每个学生的每个学科一行数据，对其转换成一张横表，即表中学生id为主键，包含语文、数学、英语三列，列值为对应学科分数。

1、基础数据​

有学生成绩表，包含学生id、学科、成绩

+-------------+----------+--------+
| student_id  | subject  | score  |
+-------------+----------+--------+
| 001         | 语文       | 89     |
| 001         | 数学       | 95     |
| 001         | 英语       | 77     |
| 002         | 语文       | 92     |
| 002         | 数学       | 83     |
| 002         | 英语       | 97     |
| 003         | 语文       | 81     |
| 003         | 数学       | 94     |
| 003         | 英语       | 88     |
+-------------+----------+--------+

期望结果

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

2.相关知识​

3.SQL​

我们之前使用case when+sum的方式，现在使用pivot的方式进行转换。

执行SQL

select *
from t_student_score
pivot(
    sum(score) as score
    for subject in('语文' as yuwen,'数学' as shuxue,'英语' as yingyu)
)

执行结果

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

4、数据准备​

--建表语句
CREATE TABLE IF NOT EXISTS t_student_score
(
    student_id string, -- 学生id
    subject    string, -- 学科
    score      bigint  -- 分数
)
    COMMENT '学生成绩表';

insert into t_student_score
values ('001', '语文', 89),
       ('001', '数学', 95),
       ('001', '英语', 77),
       ('002', '语文', 92),
       ('002', '数学', 83),
       ('002', '英语', 97),
       ('003', '语文', 81),
       ('003', '数学', 94),
       ('003', '英语', 88);

二、多列转多行（横表转竖表）​

原始数据为一张横表，分别有三列成绩列，想要转成竖表，需要转换成三列分别为 学生id、学科、成绩，转换完成之后学生id将不再是主键。

1、基础数据​

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

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

期望结果

+-------------+----------+--------+
| student_id  | subject  | score  |
+-------------+----------+--------+
| 001         | 语文       | 89     |
| 001         | 数学       | 95     |
| 001         | 英语       | 77     |
| 002         | 语文       | 92     |
| 002         | 数学       | 83     |
| 002         | 英语       | 97     |
| 003         | 语文       | 81     |
| 003         | 数学       | 94     |
| 003         | 英语       | 88     |
+-------------+----------+--------+

2.相关知识​

3.SQL​

我们之前使用case when+sum的方式，现在使用pivot的方式进行转换。

执行SQL

SELECT * FROM t_student_score_02
    UNPIVOT INCLUDE NULLS (
        score FOR subject IN (yuwen AS `语文`, shuxue AS `数学`,  yingyu AS `英语`)
    )

执行结果

+-------------+----------+--------+
| student_id  | subject  | score  |
+-------------+----------+--------+
| 001         | 语文       | 89     |
| 001         | 数学       | 95     |
| 001         | 英语       | 77     |
| 002         | 语文       | 92     |
| 002         | 数学       | 83     |
| 002         | 英语       | 97     |
| 003         | 语文       | 81     |
| 003         | 数学       | 94     |
| 003         | 英语       | 88     |
+-------------+----------+--------+

注意

  1. 因为subject中的别名要作为subject的内容，我们需要使用汉字，同时在语法上是别名，所以需要时`` ，不是用''。
  2. 注意score 和 subject的顺序。

4、数据准备​

--建表语句
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

相关推荐​

  1. 行转列-collect_list,collect_set进行简单行转列
  2. 行转列-使用transform进行有序行转列
  3. 行转列-使用transform进行有序行转列-多列一一对应
  4. 行转列-多行转多列（竖表转横表）
  5. 列转行-多列转多行（横表变竖表）
  6. 列转行-lateral view explode列转行
  7. 列转行-explode_outer和lateral view outer
  8. 列转行-posexplode多列对应转行
  9. 列转行-lateral view outer posexplode及posexplode_outer多列对应转行
  10. 行转列-sparksql-pivot子句
  11. 列转行-sparksql-unpivot子句
  12. 行列转换-横表竖表互相转换