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 (`姓名`, `分數` ) fromv_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| +----+----+----+