Databricks Spark SQL：替换UNION ALL提升多列NULL/零值统计效率

针对你有200列（V1到V200）的数据集，无需写200个UNION ALL，可以用逆透视（UNPIVOT）或者Map展开的方式实现，只需要扫描一次表，性能和代码简洁性都远优于原方案。

方法1：使用Spark SQL UNPIVOT语法（推荐） #

Spark SQL 3.0+（Databricks默认支持）提供了UNPIVOT语法，配合INCLUDE NULLS可以保留原表中的NULL值，再分组统计即可：

SELECT 
  column_name,
  SUM(CASE WHEN value IS NULL THEN 1 ELSE 0 END) AS n_null,
  SUM(CASE WHEN value = 0 THEN 1 ELSE 0 END) AS n_zero,
  SUM(CASE WHEN value < 0 THEN 1 ELSE 0 END) AS n_below_zero
FROM 
  your_table_name  -- 替换成你的表名
UNPIVOT INCLUDE NULLS (
  value FOR column_name IN (
    V1, V2, V3, ..., V200  -- 这里可以用脚本自动生成列名列表
  )
)
GROUP BY column_name
ORDER BY column_name

简化列名输入的技巧 #

因为列名是规律的V1到V200，你可以在Databricks的Notebook中用Python快速生成列名字符串：

# 生成V1到V200的列名列表
columns = [f'V{i}' for i in range(1, 201)]
# 转成SQL需要的逗号分隔格式
unpivot_columns = ', '.join(columns)
print(unpivot_columns)

把打印出的结果直接替换到SQL中的IN (...)部分即可，不用手动输入200个列名。

方法2：使用LATERAL VIEW EXPLODE + Map #

如果你的Spark版本较低不支持UNPIVOT，可以用Map将列名和对应值打包，再通过EXPLODE展开：

SELECT 
  key AS column_name,
  SUM(CASE WHEN value IS NULL THEN 1 ELSE 0 END) AS n_null,
  SUM(CASE WHEN value = 0 THEN 1 ELSE 0 END) AS n_zero,
  SUM(CASE WHEN value < 0 THEN 1 ELSE 0 END) AS n_below_zero
FROM 
  your_table_name
LATERAL VIEW EXPLODE(
  map(
    'V1', V1,
    'V2', V2,
    ...,
    'V200', V200  -- 同样可以用Python自动生成这部分内容
  )
) kv AS key, value
GROUP BY key
ORDER BY key

方案优势 #

• 性能提升：原方案用200个UNION ALL会扫描表200次，新方案只扫描一次表，大数据集下性能差异极大。
• 代码简洁：无需重复编写200段几乎相同的统计逻辑，维护成本低。
• 可扩展性：如果后续列数变化（比如新增到V300），只需修改列名列表即可，无需大幅调整代码。

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