Spark SQL实现DataFrame API的PIVOT效果问题排查

问题核心在于Spark SQL的PIVOT需要严格匹配DataFrame API的分组逻辑：DataFrame中是先按marker分组，再对marker列做透视，如果SQL写法未将marker作为分组键，就会得到全量汇总的单行结果。

正确的Spark SQL写法 #

SELECT
  marker,
  COALESCE(`A`, 0) AS `A`,
  COALESCE(`B`, 0) AS `B`,
  COALESCE(`C`, 0) AS `C`,
  COALESCE(`D`, 0) AS `D`,
  COALESCE(`E`, 0) AS `E`,
  COALESCE(`F`, 0) AS `F`,
  COALESCE(`G`, 0) AS `G`,
  COALESCE(`H`, 0) AS `H`,
  COALESCE(`I`, 0) AS `I`,
  COALESCE(`J`, 0) AS `J`
FROM (
  SELECT marker
  FROM demo
) t
PIVOT (
  COUNT(*)
  FOR marker IN ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J')
)
ORDER BY marker

逻辑说明 #

1. 子查询SELECT marker FROM demo仅保留marker列，PIVOT会自动将该列作为分组键（PIVOT的分组依据是子查询中未被用于透视的列），完全匹配DataFrame groupBy('marker')的分组逻辑。
2. PIVOT子句对marker列做透视，用COUNT(*)统计每个分组内的记录数：每个分组只有当前marker值的数据，因此对应透视列会得到计数（10），其他透视列返回NULL。
3. 用COALESCE将NULL转换为0，对应DataFrame API中的fillna(0)操作。
4. 最后按marker排序，保证输出顺序和DataFrame结果一致。

注意事项 #

Spark SQL的PIVOT要求在IN子句中显式列出所有可能的marker值，如果marker值是动态的，可以先通过查询获取所有值再拼接SQL逻辑（类似DataFrame API中pivot自动推断列的逻辑）。

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