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

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进行简单行转列 (opens in a new tab)
  2. 行转列-使用transform进行有序行转列 (opens in a new tab)
  3. 行转列-使用transform进行有序行转列-多列一一对应 (opens in a new tab)
  4. 行转列-多行转多列(竖表转横表) (opens in a new tab)
  5. 列转行-多列转多行(横表变竖表) (opens in a new tab)
  6. 列转行-lateral view explode列转行 (opens in a new tab)
  7. 列转行-explode_outer和lateral view outer (opens in a new tab)
  8. 列转行-posexplode多列对应转行 (opens in a new tab)
  9. 列转行-lateral view outer posexplode及posexplode_outer多列对应转行 (opens in a new tab)
  10. 行转列-sparksql-pivot子句 (opens in a new tab)
  11. 列转行-sparksql-unpivot子句 (opens in a new tab)
  12. 行列转换-横表竖表互相转换 (opens in a new tab)