在BigQuery中转换字符串为日期并处理无效值的技术求助

问题根源 #

直接使用PARSE_DATE会在遇到无法解析的字符串（如-、na、null）时抛出错误，必须使用安全解析函数避免中断查询，同时统一处理无效值。

修改后的查询语句（支持日期过滤+无效值显示） #

WITH
  _0 AS (
    SELECT
      -- 处理无效值，最终展示为'not set'或原格式日期字符串
      CASE
        WHEN follow_up_date IN ('-', 'na', 'null') THEN 'not set'
        WHEN SAFE.PARSE_DATE("%d/%m/%Y", follow_up_date) IS NOT NULL THEN FORMAT_DATE("%d/%m/%Y", SAFE.PARSE_DATE("%d/%m/%Y", follow_up_date))
        ELSE 'not set'
      END AS __follow_up_date__1,
      case_id_url AS __case_id_url__1,
      -- 新增日期字段用于过滤（核心：保留可用于日期范围筛选的DATE类型）
      CASE
        WHEN follow_up_date IN ('-', 'na', 'null') THEN NULL
        ELSE SAFE.PARSE_DATE("%d/%m/%Y", follow_up_date)
      END AS follow_up_date_filter
    FROM x.y.z AS _t
    GROUP BY __follow_up_date__1, __case_id_url__1, follow_up_date_filter
    -- 按实际日期排序，无效值自动排到末尾
    ORDER BY follow_up_date_filter ASC NULLS LAST, __follow_up_date__1 ASC
    LIMIT 30001
  )
SELECT __follow_up_date__1, __case_id_url__1 
FROM _0
-- 示例：筛选2022年的有效日期记录
-- WHERE follow_up_date_filter BETWEEN DATE('2022-01-01') AND DATE('2022-12-31')

简化版（仅需统一显示无效值，无需日期过滤） #

如果不需要按日期范围筛选，可简化为：

WITH
  _0 AS (
    SELECT
      -- 链式处理：先把'-'/'na'转成NULL，再安全解析日期，最后替换无效值为'not set'
      IFNULL(
        FORMAT_DATE("%d/%m/%Y", SAFE.PARSE_DATE("%d/%m/%Y", NULLIF(NULLIF(follow_up_date, '-'), 'na'))),
        'not set'
      ) AS __follow_up_date__1,
      case_id_url AS __case_id_url__1
    FROM x.y.z AS _t
    GROUP BY __follow_up_date__1, __case_id_url__1
    -- 按日期排序，无效值排最后
    ORDER BY SAFE.PARSE_DATE("%d/%m/%Y", NULLIF(NULLIF(follow_up_date, '-'), 'na')) ASC NULLS LAST
    LIMIT 30001
  )
SELECT * FROM _0

关键说明 #

• SAFE.PARSE_DATE：遇到无法解析的字符串时返回NULL，而非抛出错误，是处理脏数据的核心函数
• NULLIF：将指定的无效字符串（-、na）转换为SQL标准NULL，便于统一处理
• CASE WHEN/IFNULL：将所有无效场景（包括解析失败的字符串）统一替换为not set
• 排序逻辑：使用实际转换后的日期字段排序，确保有效日期按时间顺序排列，无效值自动排到末尾

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