如何使用Spark SQL实现指定宽表转置为id-value结构长表

Spark SQL宽表转长表可行方案 #

方案1：stack函数方案（全版本Spark兼容）

这个方案适配所有Spark版本，不需要考虑版本兼容性，尤其适合列数多的场景：

SELECT 
  timestamp,
  id,
  value
FROM 你的表名
LATERAL VIEW stack(
  100,
  'value1', value1,
  'value2', value2,
  -- 依次补全value3到value99的对应配置
  'value100', value100
) AS id, value

stack函数第一个参数为要拆分的行数，这里共100个value列所以填100，后续每两个参数为一组：第一个是转置后id列的取值，第二个是对应原表列的数值。
如果不想手动写100组配置，可以通过查询表元数据批量生成SQL片段，PySpark示例如下：

# 获取所有value开头的列名
value_cols = [col for col in df.columns if col.startswith("value")]
# 生成stack函数内部的参数字符串
stack_param = ", ".join([f"'{col}', {col}" for col in value_cols])
# 拼接完整SQL
exec_sql = f"""
SELECT timestamp, id, value 
FROM 你的表名 
LATERAL VIEW stack({len(value_cols)}, {stack_param}) AS id, value
"""
# 执行查询
spark.sql(exec_sql).show()

方案2：UNPIVOT语法（Spark 2.4及以上版本支持）

你觉得UNPIVOT不适用大概率是版本不匹配或者写法问题，Spark 2.4之后原生支持UNPIVOT，写法更简洁：

SELECT timestamp, id, value
FROM 你的表名
UNPIVOT (
  value FOR id IN (
    value1,
    value2,
    -- 补全value3到value99
    value100
  )
)

执行后id列会自动填充IN子句里的列名，value列对应原表对应列的数值，完全匹配你要的输出结构。

内容的提问来源于stack exchange，提问作者D. Caan