应使用explode还是Unpivot？表格列转行操作步骤求解

PySpark 实现步骤 #

• 构造分类数组：将Past/Current/Expired每一列的列名与对应值打包成结构体，再组合成数组，每行生成包含3个结构体的数组。
• 拆分数组为多行：用explode函数把数组拆分为单独行，每行对应一个分类结构体。
• 过滤无效行：仅保留值为X的行（即原表中标记过的分类）。
• 提取目标列：选择No.Cont和分类名称字段，得到最终结果。

示例代码 #

from pyspark.sql import SparkSession
from pyspark.sql.functions import explode, array, struct, col, lit

# 初始化Spark会话
spark = SparkSession.builder.appName("WideToLong").getOrCreate()

# 模拟原表数据
raw_data = [
    (113, "X", "", ""),
    (114, "", "X", ""),
    (115, "X", "", "X")
]
df = spark.createDataFrame(raw_data, ["No.Cont", "Past", "Current", "Expired"])

# 执行转换逻辑
result_df = df.withColumn(
    "category_info",
    array(
        struct(lit("Past").alias("Category"), col("Past").alias("flag")),
        struct(lit("Current").alias("Category"), col("Current").alias("flag")),
        struct(lit("Expired").alias("Category"), col("Expired").alias("flag"))
    )
).withColumn("category_info", explode(col("category_info"))) \
 .filter(col("category_info.flag") == "X") \
 .select(col("No.Cont"), col("category_info.Category")) \
 .orderBy("No.Cont")

# 查看转换结果
result_df.show()

Spark SQL 实现方式 #

如果习惯用SQL语句，可直接执行以下查询：

示例SQL #

WITH raw_table AS (
    SELECT 113 AS `No.Cont`, 'X' AS Past, '' AS Current, '' AS Expired UNION ALL
    SELECT 114 AS `No.Cont`, '' AS Past, 'X' AS Current, '' AS Expired UNION ALL
    SELECT 115 AS `No.Cont`, 'X' AS Past, '' AS Current, 'X' AS Expired
)
SELECT 
    `No.Cont`,
    Category
FROM raw_table
LATERAL VIEW EXPLODE(
    ARRAY(
        STRUCT('Past' AS Category, Past AS flag),
        STRUCT('Current' AS Category, Current AS flag),
        STRUCT('Expired' AS Category, Expired AS flag)
    )
) exploded AS category_info
WHERE category_info.flag = 'X'
ORDER BY `No.Cont`;

核心逻辑说明：用array将列名与对应值打包成数组，explode拆分数组实现宽表转长表，最后过滤掉未标记X的无效行，提取所需字段即可得到目标结构。

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