BigQuery SQL过滤合法datetime格式避免日期解析报错

核心问题：使用parse_datetime严格解析last_modified_date字段时，不符合%Y-%m-%d %H:%M格式的脏值会直接抛出Failed to parse错误，需要在保留全部原始返回行、不改变原有业务计算逻辑的前提下解决报错。
不建议直接用WHERE子句过滤合法值：这种方式会把携带脏last_modified_date的整行记录剔除，和逐行返回所有id、date记录的需求冲突。

推荐方案：使用SAFE安全解析函数 #

BigQuery所有类型转换、解析类函数都支持SAFE.前缀，解析失败时会直接返回NULL而非中断查询抛出错误。NULL值会在原有IF判断中自动被排除，不参与MAX窗口计算，完全匹配现有业务逻辑。
可以把重复的解析逻辑提前到CTE中，既避免重复写解析代码提升可维护性，也能减少重复计算提升性能，优化后代码如下：

WITH parsed_source AS (
  SELECT
    id,
    date,
    -- 加SAFE前缀，脏值解析返回NULL不报错
    DATE(
      TIMESTAMP(
        SAFE.PARSE_DATETIME('%Y-%m-%d %H:%M', last_modified_date),
        'America/New_York'
      )
    ) AS parsed_modified_date
  FROM `my_table`
)
SELECT
  id,
  date,
  MAX(
    IF(parsed_modified_date <= date, parsed_modified_date, NULL)
  ) OVER (PARTITION BY id, date) AS custom_last_modified_date
FROM parsed_source

针对提到的"2021-0-3 05:50"这类格式错误值，SAFE.PARSE_DATETIME会直接返回NULL，IF判断中NULL <= date的结果为NULL，会被判定为不满足条件返回NULL，不会参与最大值计算，查询不会中断。

备选方案：正则预过滤合法格式值 #

如果你需要明确剔除格式非法的记录、后续单独排查脏数据，可以用正则先校验last_modified_date的格式，只对格式匹配的值做解析。
匹配%Y-%m-%d %H:%M格式的正则规则为^\d{4}-\d{2}-\d{2} \d{2}:\d{2}$，对应WHERE过滤写法如下：

SELECT 
  id, 
  date,
  MAX(
    IF(
      DATE(TIMESTAMP(DATETIME(PARSE_DATETIME('%Y-%m-%d %H:%M', last_modified_date)), 'America/New_York')) <= date,
      DATE(TIMESTAMP(DATETIME(PARSE_DATETIME('%Y-%m-%d %H:%M', last_modified_date)), 'America/New_York')),
      NULL
    )
  ) OVER (PARTITION BY date, id) as custom_last_modified_date
FROM `my_table`
-- 只保留格式符合要求的记录
WHERE REGEXP_CONTAINS(last_modified_date, r'^\d{4}-\d{2}-\d{2} \d{2}:\d{2}$')

注意：正则仅能校验字符串格式是否匹配，无法识别逻辑非法的日期（比如2022-02-30 12:00这类格式正确但日期不存在的值），这类值依然会触发解析错误，因此优先选择SAFE解析方案，兼容性更强。

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