如何在Google BigQuery中处理两种格式字符串日期并转换为日期

核心处理逻辑 #

1. 过滤目标格式记录：第二种格式包含-字符，直接用条件排除这类行；也可通过字符串长度（第一种格式固定15位）精准匹配
2. 提取并转换日期：第一种格式的前8位是MMDDYYYY结构，截取后解析为标准日期类型
3. 限定2023年数据：确保仅处理目标年份的记录，满足后续月度计算需求

基础转换SQL示例 #

SELECT
  -- 截取前8位日期段，按MMDDYYYY格式解析为DATE类型
  PARSE_DATE("%m%d%Y", SUBSTR(DateTime, 1, 8)) AS standard_date,
  -- 替换为你实际需要计算差异的数值列
  your_target_numeric_column
FROM
  `你的项目ID.你的数据集ID.你的表名`
WHERE
  -- 排除带横杠的第二种格式，也可替换为CHAR_LENGTH(DateTime) = 15
  NOT REGEXP_CONTAINS(DateTime, '-')
  -- 确保仅保留2023年数据
  AND EXTRACT(YEAR FROM PARSE_DATE("%m%d%Y", SUBSTR(DateTime, 1, 8))) = 2023

关键函数说明 #

• SUBSTR(DateTime, 1, 8)：从字符串起始位置截取8个字符，提取出MMDDYYYY格式的核心日期部分（比如从09042023050000AM中得到09042023）
• PARSE_DATE("%m%d%Y", ...)：将截取后的字符串按「月-日-年」规则解析为BigQuery标准DATE类型
• NOT REGEXP_CONTAINS(DateTime, '-')：快速过滤不符合要求的第二种格式记录，若要更精准匹配第一种格式，可改用REGEXP_CONTAINS(DateTime, r'^\d{8}050000(AM|PM)$')

扩展：计算2023年月度首尾数值差异 #

基于上述转换结果，可进一步计算每个月首尾日期对应的数值差：

WITH parsed_data AS (
  SELECT
    PARSE_DATE("%m%d%Y", SUBSTR(DateTime, 1, 8)) AS standard_date,
    your_target_numeric_column,
    EXTRACT(MONTH FROM PARSE_DATE("%m%d%Y", SUBSTR(DateTime, 1, 8))) AS month_num
  FROM
    `你的项目ID.你的数据集ID.你的表名`
  WHERE
    NOT REGEXP_CONTAINS(DateTime, '-')
    AND EXTRACT(YEAR FROM PARSE_DATE("%m%d%Y", SUBSTR(DateTime, 1, 8))) = 2023
),
month_boundaries AS (
  SELECT
    month_num,
    -- 取每月第一天的数值
    FIRST_VALUE(your_target_numeric_column) OVER (PARTITION BY month_num ORDER BY standard_date) AS month_start_val,
    -- 取每月最后一天的数值
    LAST_VALUE(your_target_numeric_column) OVER (PARTITION BY month_num ORDER BY standard_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS month_end_val
  FROM
    parsed_data
)
-- 去重后计算月度差异
SELECT
  DISTINCT
  month_num,
  month_end_val - month_start_val AS monthly_difference
FROM
  month_boundaries
ORDER BY
  month_num

注意事项 #

• 替换代码中的你的项目ID.你的数据集ID.你的表名为实际的资源路径
• 替换your_target_numeric_column为你需要计算差异的具体数值列名

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