Error message here!

Hide Error message here!

忘记密码?

Error message here!

请输入正确邮箱

Hide Error message here!

密码丢失?请输入您的电子邮件地址。您将收到一个重设密码链接。

Error message here!

返回登录

Close

Spark SQL中列转行(UNPIVOT)的两种方法

氢氦 2019-01-23 18:14:00 阅读数:412 评论数:0 点赞数:0 收藏数:0

行列之间的互相转换是ETL中的常见需求,在Spark SQL中,行转列有内建的PIVOT函数可用,没什么特别之处。而列转行要稍微麻烦点。本文整理了2种可行的列转行方法,供参考。

 

本文链接:https://www.cnblogs.com/hhelibeb/p/10310369.html

测试数据准备

本文的环境是Windows 10, Spark 2.4,开发语言是Python。首先构建一点初始测试数据,

from pyspark.sql import SparkSession
spark = SparkSession.builder.appName('TestAPP').enableHiveSupport().getOrCreate()
df = spark.createDataFrame([('数学','张三',88), ('语文','张三',92), ('英语','张三',77),
('数学','王五',65), ('语文','王五',87), ('英语','王五',90),
('数学','李雷',67), ('语文','李雷',33), ('英语','李雷',24),
('数学','宫九',77), ('语文','宫九',87), ('英语','宫九',90)
], ['科目','姓名','分数']).orderBy('科目')
df.show()

执行程序,可以看到数据如下,

 +----+----+----+
|科目|姓名|分数|
+----+----+----+
|数学|张三| 88|
|数学|李雷| 67|
|数学|宫九| 77|
|数学|王五| 65|
|英语|张三| 77|
|英语|宫九| 90|
|英语|李雷| 24|
|英语|王五| 90|
|语文|李雷| 33|
|语文|宫九| 87|
|语文|张三| 92|
|语文|王五| 87|
+----+----+----+

行转列

如上述,使用PIVOT函数即可实现行转列,

df.createOrReplaceTempView('scores')
sql_content = '''select * from scores
pivot
 ( sum(`分数`) for `姓名` in ('张三','王五','李雷','宫九') )
'''
df_pivot = spark.sql(sql_content)
df_pivot.show()

 得到结果,

+----+----+----+----+----+
|科目|张三|王五|李雷|宫九|
+----+----+----+----+----+
|数学| 88| 65| 67| 77|
|英语| 77| 90| 24| 90|
|语文| 92| 87| 33| 87|
+----+----+----+----+----+

列转行

本文整理的两种办法是使用Spark中的stack函数lateral view + explode函数

stack()

stack(n, expr1, ..., exprk) - 会将expr1, ..., exprk 分割为n行.

df_pivot.createOrReplaceTempView('v_pivot')
sql_content = '''select `科目`, stack(4, '张三', `张三`, '王五', `王五`, '李雷', `李雷`, '宫九', `宫九`) as (`姓名`, `分数` ) from  v_pivot 
'''
df_unpivot1 = spark.sql(sql_content)
df_unpivot1.show()

可以看到,结果的结构和初始数据的结构相同,

+----+----+----+
|科目|姓名|分数|
+----+----+----+
|数学|张三| 88|
|数学|王五| 65|
|数学|李雷| 67|
|数学|宫九| 77|
|英语|张三| 77|
|英语|王五| 90|
|英语|李雷| 24|
|英语|宫九| 90|
|语文|张三| 92|
|语文|王五| 87|
|语文|李雷| 33|
|语文|宫九| 87|
+----+----+----+

 

lateral view + explode()

explode函数可以把数组分割为多行,比如,

> SELECT explode(array(10, 20));
10
20

lateral view使用表生成函数将每个输入行转换为0或多个输出行。最常见的用法是和explode函数一起使用。

sql_content = '''select `科目`, split(temp1, ':')[0] as `姓名`, split(temp1, ':')[1] as `分数`
from(
select `科目`, concat(
'张三:', `张三`, ',',
'王五:', `王五`, ',',
'李雷:', `李雷`, ',',
'宫九:', `宫九`
) temp
from v_pivot
) lateral view explode(split(temp, ',')) as temp1
'''
df_unpivot2 = spark.sql(sql_content)
df_unpivot2.show()

结果同上,

+----+----+----+
|科目|姓名|分数|
+----+----+----+
|数学|张三| 88|
|数学|王五| 65|
|数学|李雷| 67|
|数学|宫九| 77|
|英语|张三| 77|
|英语|王五| 90|
|英语|李雷| 24|
|英语|宫九| 90|
|语文|张三| 92|
|语文|王五| 87|
|语文|李雷| 33|
|语文|宫九| 87|
+----+----+----+

 

参考链接:Spark SQL, Built-in Functions

Spark实现行列转换pivot和unpivot

hive lateral view 与 explode详解

SQL Guide

 

版权声明
本文为[氢氦]所创,转载请带上原文链接,感谢
https://www.cnblogs.com/hhelibeb/p/10310369.html

编程之旅,人生之路,不止于编程,还有诗和远方。
阅代码原理,看框架知识,学企业实践;
赏诗词,读日记,踏人生之路,观世界之行;

支付宝红包,每日可领