Amazon Redshift数值类型转换错误求助：修正ST_DistanceSphere逻辑

错误根源不是空值，而是str.physical_logitude或str.physical_lattitude列中存在非数字格式的字符串（比如字母、特殊符号、多小数点的无效数字），COALESCE仅处理空值，无法解决转换失败的问题。

解决方案步骤： #

1. 排查脏数据：先定位无法转换为numeric的行，确认数据问题：

SELECT physical_logitude, physical_lattitude
FROM str
WHERE TRY_CAST(physical_logitude AS numeric(12,8)) IS NULL 
   OR TRY_CAST(physical_lattitude AS numeric(12,8)) IS NULL;

2. 修正计算逻辑：使用Redshift内置的TRY_CAST函数（转换失败时返回NULL而非报错），再结合业务需求处理NULL值。

方案1：过滤无效坐标行（推荐，避免无效数据干扰计算）

SELECT 
  ST_DistanceSphere(
    ST_Point(act_coord.longitude, act_coord.latitude),
    ST_Point(
      TRY_CAST(str.physical_logitude AS numeric(12,8)),
      TRY_CAST(str.physical_lattitude AS numeric(12,8))
    )
  ) / 1609.34 AS distance_miles
FROM act_coord
JOIN str ON -- 补充你的表关联条件
  TRY_CAST(str.physical_logitude AS numeric(12,8)) IS NOT NULL
  AND TRY_CAST(str.physical_lattitude AS numeric(12,8)) IS NOT NULL;

方案2：保留行，给无效坐标设置默认值（按需调整默认值）

若业务需要保留所有行，可给无效坐标设置符合场景的默认值（示例用0,0，需根据实际业务调整）：

SELECT 
  ST_DistanceSphere(
    ST_Point(act_coord.longitude, act_coord.latitude),
    ST_Point(
      COALESCE(TRY_CAST(str.physical_logitude AS numeric(12,8)), 0.0),
      COALESCE(TRY_CAST(str.physical_lattitude AS numeric(12,8)), 0.0)
    )
  ) / 1609.34 AS distance_miles
FROM act_coord
JOIN str ON -- 补充你的表关联条件;

关键说明： #

• TRY_CAST是Redshift专为转换失败场景设计的函数，比普通CAST更安全，不会因脏数据中断查询。
• 优先选择方案1过滤无效数据，避免错误坐标产生无意义的计算结果。
• 若必须保留无效行，默认值需结合业务场景设置（比如用户所在区域中心坐标，而非随意的0,0）。

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