Scala实现表Unpivot逆透视：stack方法转换宽表为长表

问题说明 #

现有宽表包含3个固定维度列FName、SName、Email，以及4个按统计周期存储的数值列Jan 2021、Feb 2021、Mar 2021、Total 2021，需要转换为维度列+统计周期列+数值列的长表结构。
使用stack函数执行失败，90%以上的原因是带空格的列名未用反引号包裹，或是stack的计数参数与传入的列数不匹配。 #

实现代码 #

1. 构造样例测试数据 #

import org.apache.spark.sql.SparkSession
val spark = SparkSession.builder().master("local[*]").appName("UnpivotTest").getOrCreate()
import spark.implicits._

val sourceDF = Seq(
  ("Micheal", "Scott", "scarrel@gmail.com", 4000, 5000, 3400, 50660),
  ("Dwight", "Schrute", "dschrute@gmail.com", 1200, 6900, 1000, 35000),
  ("Kevin", "Malone", "kmalone@gmail.com", 9000, 6000, 18000, 32000)
).toDF("FName", "SName", "Email", "Jan 2021", "Feb 2021", "Mar 2021", "Total 2021")

2. 用stack函数实现逆透视 #

stack语法规则：stack(转换列数, 周期值1, 对应列1, 周期值2, 对应列2, ...) as (维度列名, 数值列名)
注意带空格的列名必须用反引号`包裹，否则会报语法错误。

val resultDF = sourceDF.selectExpr(
  "FName",
  "SName",
  "Email",
  """stack(4,
      'Jan 2021', `Jan 2021`,
      'Feb 2021', `Feb 2021`,
      'Mar 2021', `Mar 2021`,
      'Total 2021', `Total 2021`
    ) as (Period, Sales)"""
)

resultDF.show()

3. 输出结果（完全匹配目标结构） #

+-------+-------+------------------+----------+-----+
|  FName|  SName|             Email|    Period|Sales|
+-------+-------+------------------+----------+-----+
|Micheal|  Scott|scarrel@gmail.com| Jan 2021  | 4000|
|Micheal|  Scott|scarrel@gmail.com| Feb 2021  | 5000|
|Micheal|  Scott|scarrel@gmail.com| Mar 2021  | 3400|
|Micheal|  Scott|scarrel@gmail.com|Total 2021 |50660|
| Dwight|Schrute|dschrute@gmail.com|Jan 2021  | 1200|
| Dwight|Schrute|dschrute@gmail.com|Feb 2021  | 6900|
| Dwight|Schrute|dschrute@gmail.com|Mar 2021  | 1000|
| Dwight|Schrute|dschrute@gmail.com|Total 2021|35000|
|  Kevin| Malone|kmalone@gmail.com| Jan 2021  | 9000|
|  Kevin| Malone|kmalone@gmail.com| Feb 2021  | 6000|
|  Kevin| Malone|kmalone@gmail.com| Mar 2021  |18000|
|  Kevin| Malone|kmalone@gmail.com|Total 2021 |32000|
+-------+-------+------------------+----------+-----+

其他可选方案（Spark 3.4+版本支持） #

如果使用的Spark版本在3.4及以上，可以直接用原生unpivot方法，不需要手动写stack参数，更不容易出错：

val resultDF2 = sourceDF.unpivot(
  Seq($"FName", $"SName", $"Email"), // 保留的固定维度列
  Seq($"Jan 2021", $"Feb 2021", $"Mar 2021", $"Total 2021"), // 待转换的指标列
  "Period", // 生成的周期维度列名
  "Sales" // 生成的数值列名
)

内容的提问来源于stack exchange，提问作者jabeono