如何在Spark SQL中实现行转列PIVOT透视且缺失值填充null

你当前的需求属于典型的非聚合类长表转宽表场景，和教材中常见的聚合透视的差异在于：每个Date + User_ID + Data_Desc组合最多仅存在1条有效记录，不需要做数值类聚合计算，仅需将键值对摊平为独立列即可。

核心实现逻辑 #

透视操作的三个核心要素：

• 分组维度：Date、User_ID，两个字段值相同的记录会被合并到同一行
• 透视展开字段：Data_Desc，字段的每个唯一取值会生成一个独立列
• 列取值：匹配对应分组、对应Data_Desc的Data_Value值，无匹配记录时自动填充为null

Spark SQL的PIVOT语法强制要求传入聚合函数，由于你的场景下分组+透视键的组合粒度唯一，不存在多条重复记录，使用first()/max()这类轻量聚合即可得到正确结果，不会产生额外计算误差。

实现代码 #

写法1：原生PIVOT语法（推荐） #

代码简洁易维护，适合Spark 2.4及以上版本使用：

SELECT 
  Date,
  User_ID,
  `Submission Time`,
  `E-mail Address`,
  `Phone Number`
FROM (
  -- 传入透视所需的基础字段即可
  SELECT Date, User_ID, Data_Desc, Data_Value FROM 你的源表名
)
PIVOT (
  first(Data_Value, true)
  FOR Data_Desc IN (
    'Submission Time' AS `Submission Time`,
    'E-mail Address' AS `E-mail Address`,
    'Phone Number' AS `Phone Number`
  )
)
ORDER BY Date, User_ID;

写法2：条件聚合写法（兼容性更强） #

如果使用的Spark版本不支持PIVOT语法，可以用CASE WHEN加分组聚合的方式实现，效果完全一致：

SELECT
  Date,
  User_ID,
  max(CASE WHEN Data_Desc = 'Submission Time' THEN Data_Value END) AS `Submission Time`,
  max(CASE WHEN Data_Desc = 'E-mail Address' THEN Data_Value END) AS `E-mail Address`,
  max(CASE WHEN Data_Desc = 'Phone Number' THEN Data_Value END) AS `Phone Number`
FROM 你的源表名
GROUP BY Date, User_ID
ORDER BY Date, User_ID;

注意事项 #

• 若Data_Desc的取值动态变化，无法提前枚举，可以先执行SELECT DISTINCT Data_Desc FROM 你的源表名拿到所有唯一取值，再动态拼接进SQL的对应位置即可。
• 带空格、特殊字符的字段名需要用反引号`包裹，避免SQL解析报错。
• 上述两种写法执行后，缺失字段的取值默认就是null，和你要求的填充规则完全匹配。

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