Spark SQL中列轉行（UNPIVOT）的兩種方法

氫氦發表於2019-01-23
SparkSQL

行列之間的互相轉換是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

 

相關文章