BigQuery SQL数据清洗：float转整数、异常值置0后转字符串更新原表

你原有写法的核心问题是CAST函数遇到非数字内容、空值时会直接抛出类型转换错误，没有做异常捕获和兜底处理，无法覆盖四类原始值场景。

核心实现逻辑 #

通过BigQuery原生函数组合实现三层处理：

• 用SAFE_CAST做安全类型转换，转换失败（非数字内容）时返回NULL而非报错
• 用COALESCE做兜底，把原始空值、转换失败返回的NULL统一替换为0
• 用TRUNC对浮点数做截断取整（无四舍五入），最终转为STRING类型存储

第一步：先执行查询验证清洗结果 #

更新原表前先跑校验SQL，确认结果符合预期，避免误改数据：

SELECT
  id,
  product,
  CAST(
    TRUNC(
      COALESCE(SAFE_CAST(discount AS FLOAT64), 0)
    ) AS INT64
  ) AS discount_cleaned
FROM `你的项目名.你的数据集名.你的表名`

校验规则参考：

• 原始整数（10、0、5）→ 返回对应字符串格式整数（'10'、'0'、'5'）
• 原始浮点数（10.0、13.3210282172312131）→ 截断取整后返回字符串（'10'、'13'）
• 原始非数字字符（x）、空值 → 返回'0'

第二步：更新原表数据 #

前置检查 #

如果原表discount字段本身不是STRING类型，先执行字段类型修改：

ALTER TABLE `你的项目名.你的数据集名.你的表名`
ALTER COLUMN discount SET DATA TYPE STRING;

执行全表更新 #

BigQuery的UPDATE语句必须携带WHERE条件，全表更新时固定写WHERE TRUE即可：

UPDATE `你的项目名.你的数据集名.你的表名`
SET discount = CAST(
    TRUNC(
      COALESCE(SAFE_CAST(discount AS FLOAT64), 0)
    ) AS INT64
  )
WHERE TRUE;

逻辑说明 #

• 截断取整规则：TRUNC()会直接丢弃小数部分，不会做四舍五入，例如13.999处理后结果为13
• 异常值兜底：所有无法转为数字的内容、空值都会被统一置为0
• 最终存储格式：所有值均为字符串类型的整数，无小数、无异常字符

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