Fork me on GitHub

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

 

正文

行列之间的互相转换是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

 

posted @   氢氦  阅读(23181)  评论(0编辑  收藏  举报
编辑推荐:
· Java 中堆内存和栈内存上的数据分布和特点
· 开发中对象命名的一点思考
· .NET Core内存结构体系(Windows环境)底层原理浅谈
· C# 深度学习:对抗生成网络(GAN)训练头像生成模型
· .NET 适配 HarmonyOS 进展
阅读排行:
· 如何给本地部署的DeepSeek投喂数据,让他更懂你
· 从 Windows Forms 到微服务的经验教训
· 李飞飞的50美金比肩DeepSeek把CEO忽悠瘸了,倒霉的却是程序员
· 超详细,DeepSeek 接入PyCharm实现AI编程!(支持本地部署DeepSeek及官方Dee
· 用 DeepSeek 给对象做个网站,她一定感动坏了
点击右上角即可分享
微信分享提示