一、多行转多列（竖表转横表）
原始数据中是一个竖表，每个学生的每个学科一行数据，对其转换成一张横表，即表中学生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.相关知识
sparksql-pivot子句介绍
今天给大家带来spark的一个新的知识点，pivot，这个不是函数，是一个子句。
描述
PIVOT 子句用于数据透视。我们可以根据特定列的值获取聚合值，这些值将转变成在 SELECT 子句中使用的多个列。PIVOT 子句可以在表名或子查询之后指定。
语法
PIVOT ( { aggregate_expression [ AS aggregate_expression_alias ] } [ , ... ]
FOR column_list IN ( expression_list ) )
参数
-
aggregate_expression 一个聚合表达式（例如 sum(a),count(distinct b) 等等
-
aggregate_expression_alias 聚合表达式的别名
-
column_list 包含 FROM 子句中的列，这些列是我们想要用新列替换的列。我们可以使用括号来包围这些列，例如 (c1, c2)。
-
expression_list 指定新列，这些新列用于将 column_list 中的值作为聚合条件进行匹配。我们还可以为它们添加别名。
样例
CREATE TABLE person (id INT, name STRING, age INT, class INT, address STRING);
INSERT INTO person VALUES
(100, 'John', 30, 1, 'Street 1'),
(200, 'Mary', NULL, 1, 'Street 2'),
(300, 'Mike', 80, 3, 'Street 3'),
(400, 'Dan', 50, 4, 'Street 4');
SELECT * FROM person
PIVOT (
SUM(age) AS a, AVG(class) AS c
FOR name IN ('John' AS john, 'Mike' AS mike)
);
+------+-----------+---------+---------+---------+---------+
| id | address | john_a | john_c | mike_a | mike_c |
+------+-----------+---------+---------+---------+---------+
| 200 | Street 2 | NULL | NULL | NULL | NULL |
| 100 | Street 1 | 30 | 1.0 | NULL | NULL |
| 300 | Street 3 | NULL | NULL | 80 | 3.0 |
| 400 | Street 4 | NULL | NULL | NULL | NULL |
+------+-----------+---------+---------+---------+---------+
SELECT * FROM person
PIVOT (
SUM(age) AS a, AVG(class) AS c
FOR (name, age) IN (('John', 30) AS c1, ('Mike', 40) AS c2)
);
+------+-----------+-------+-------+-------+-------+
| id | address | c1_a | c1_c | c2_a | c2_c |
+------+-----------+-------+-------+-------+-------+
| 200 | Street 2 | NULL | NULL | NULL | NULL |
| 100 | Street 1 | 30 | 1.0 | NULL | NULL |
| 300 | Street 3 | NULL | NULL | NULL | NULL |
| 400 | Street 4 | NULL | NULL | NULL | NULL |
+------+-----------+-------+-------+-------+-------+sparksql-unpivot子句介绍
今天给大家带来spark的一个新的知识点，UNPIVOT，这个不是函数，是一个子句。
描述
UNPIVOT 子句将多个列转换为多行，用于 SELECT 子句中。UNPIVOT 子句可以在表名或子查询之后指定。
语法
UNPIVOT [ { INCLUDE | EXCLUDE } NULLS ] (
{ single_value_column_unpivot | multi_value_column_unpivot }
) [[AS] alias]
single_value_column_unpivot:
values_column
FOR name_column
IN (unpivot_column [[AS] alias] [, ...])
multi_value_column_unpivot:
(values_column [, ...])
FOR name_column
IN ((unpivot_column [, ...]) [[AS] alias] [, ...])
参数
-
unpivot_column
在 FROM 子句中包含列，这指定了我们想要进行反透视的列。
-
name_column
用于存放反透视列名的列的名称。
-
values_column
用于存放反透视列值的列的名称。
样例
CREATE TABLE sales_quarterly (year INT, q1 INT, q2 INT, q3 INT, q4 INT);
INSERT INTO sales_quarterly VALUES
(2020, null, 1000, 2000, 2500),
(2021, 2250, 3200, 4200, 5900),
(2022, 4200, 3100, null, null);
-- 列名被用作反透视列
SELECT * FROM sales_quarterly
UNPIVOT (
sales FOR quarter IN (q1, q2, q3, q4)
);
+------+---------+-------+
| year | quarter | sales |
+------+---------+-------+
| 2020 | q2 | 1000 |
| 2020 | q3 | 2000 |
| 2020 | q4 | 2500 |
| 2021 | q1 | 2250 |
| 2021 | q2 | 3200 |
| 2021 | q3 | 4200 |
| 2021 | q4 | 5900 |
| 2022 | q1 | 4200 |
| 2022 | q2 | 3100 |
+------+---------+-------+
-- 默认情况下，NULL 值会被排除在外，但可以选择包含它们
-- 反透视列可以起别名
-- 可以通过别名引用反透视结果
SELECT up.* FROM sales_quarterly
UNPIVOT INCLUDE NULLS (
sales FOR quarter IN (q1 AS Q1, q2 AS Q2, q3 AS Q3, q4 AS Q4)
) AS up;
+------+---------+-------+
| year | quarter | sales |
+------+---------+-------+
| 2020 | Q1 | NULL |
| 2020 | Q2 | 1000 |
| 2020 | Q3 | 2000 |
| 2020 | Q4 | 2500 |
| 2021 | Q1 | 2250 |
| 2021 | Q2 | 3200 |
| 2021 | Q3 | 4200 |
| 2021 | Q4 | 5900 |
| 2022 | Q1 | 4200 |
| 2022 | Q2 | 3100 |
| 2022 | Q3 | NULL |
| 2022 | Q4 | NULL |
+------+---------+-------+
-- 多列值反透视
SELECT * FROM sales_quarterly
UNPIVOT EXCLUDE NULLS (
(first_quarter, second_quarter)
FOR half_of_the_year IN (
(q1, q2) AS H1,
(q3, q4) AS H2
)
);
+------+------------------+---------------+----------------+
| id | half_of_the_year | first_quarter | second_quarter |
+------+------------------+---------------+----------------+
| 2020 | H1 | NULL | 1000 |
| 2020 | H2 | 2000 | 2500 |
| 2021 | H1 | 2250 | 3200 |
| 2021 | H2 | 4200 | 5900 |
| 2022 | H1 | 4200 | 3100 |
+------+------------------+---------------+----------------+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 |
+-------------+----------+--------+
注意
-
因为subject中的别名要作为subject的内容，我们需要使用汉字，同时在语法上是别名，所以需要时`` ，不是用''。
-
注意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万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
