Spark SQL中列转行（UNPIVOT）的两种方法

2023-12-26 06:08| 来源: 网络整理| 查看: 265

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

1 测试数据准备本文的环境是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| +----+----+----+

2 行转列如上述，使用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| +----+----+----+----+----+

3 列转行本文整理的两种办法是使用Spark中的stack函数和lateral view + explode函数，

3.1 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| +----+----+----+

3.2 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()

结果同上，

【本文地址】

公司简介

联系我们