如何在BigQuery中高效获取值为NULL的列名？

我在BigQuery中有如下表格：

需求：针对每条记录，将值为NULL的列名以逗号分隔的形式取出，预期输出如下：

我尝试了如下SQL语句：

SELECT STRING_AGG(missing_feature, ','), VEHICLE_ID
FROM (
    SELECT VEHICLE_ID, 'ACCELERATION' AS missing_feature
    FROM VEHICLES
    WHERE ACCELERATION IS NULL
    UNION ALL
    SELECT VEHICLE_ID, 'WEIGHT_UNLOADED' AS missing_feature
    FROM VEHICLES
    WHERE WEIGHT_UNLOADED IS NULL
    UNION ALL
    SELECT VEHICLE_ID, 'WEIGHT_LOADED' AS missing_feature
    FROM VEHICLES
    WHERE WEIGHT_LOADED IS NULL
) t
GROUP BY VEHICLE_ID

但因涉及约20个字段，该写法冗长且不确定效率，请问是否有更高效简便的实现方式？

针对BigQuery的场景，推荐两种更简洁高效的实现方式：

方法1：利用TO_JSON_STRING与正则替换 #

通过将每行数据转为JSON字符串，匹配出值为null的键名，再拼接成目标格式。这种方式无需为每个字段单独编写判断逻辑，新增字段时也无需修改SQL，适合字段较多的场景：

SELECT
  VEHICLE_ID,
  CASE 
    WHEN ARRAY_LENGTH(missing_features_list) > 0
    THEN STRING_AGG(missing_feature, ', ') 
    ELSE NULL 
  END AS MISSING_FEATURES
FROM (
  SELECT 
    VEHICLE_ID,
    REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"([^"]+)":null') AS missing_features_list
  FROM `your-project.your-dataset.VEHICLES` t
), UNNEST(missing_features_list) AS missing_feature
GROUP BY VEHICLE_ID

方法2：条件拼接（适合字段固定的场景） #

使用CONCAT_WS结合IF条件判断直接拼接空值列名，CONCAT_WS会自动忽略NULL值，最后用NULLIF将空字符串转为NULL，完全符合预期输出：

SELECT
  VEHICLE_ID,
  NULLIF(CONCAT_WS(', ',
    IF(ACCELERATION IS NULL, 'ACCELERATION', NULL),
    IF(WEIGHT_UNLOADED IS NULL, 'WEIGHT_UNLOADED', NULL),
    IF(WEIGHT_LOADED IS NULL, 'WEIGHT_LOADED', NULL)
    -- 后续字段按此格式依次添加即可
  ), '') AS MISSING_FEATURES
FROM `your-project.your-dataset.VEHICLES`

效率说明 #

• 方法1只需扫描一次表，避免了原方法多次UNION ALL带来的重复扫描，效率更高且扩展性强。
• 方法2是单表扫描加简单条件判断，性能最优，代码可读性也更强，适合字段数量固定的场景。

内容的提问来源于stack exchange，提问作者E. Zeytinci