行转列-多行转多列（竖表转横表)

数据仓库晨曦

发布于 2024-08-19 15:58:09

910

发布于 2024-08-19 15:58:09

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

一、基础数据

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

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

二、函数介绍

sum
case

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

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

期望结果

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

1.生成三个科目的成绩列

使用case when语句，对三个科目依次判断，是对应科目的取对应科目成绩，不是对应科目不取值。

执行SQL

select student_id,
       case when subject = '语文' then score end as yuwen,
       case when subject = '数学' then score end as shuxue,
       case when subject = '英语' then score end as yingyu
from t_student_score

执行结果

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

2.聚合，将每个学生的科目放到同一行

使用聚合函数，将学生不同科目成绩压缩到同一行。

执行SQL

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

执行结果

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

四、数据准备

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