BigQuery如何查询上月记录？解决时间戳与DATE类型BETWEEN不匹配报错

• 类型不匹配是报错的直接原因：表内cr.DATE字段为TIMESTAMP类型，查询时BETWEEN运算符拼接的两个边界值为DATE类型。BigQuery官方文档虽然标注BETWEEN支持任意类型参数签名，但引擎内部不会对不兼容的跨类型参数组合做隐式转换，因此直接拿TIMESTAMP和DATE做比较会直接抛出签名匹配失败的错误。
• 现有查询的时间范围逻辑不符合需求：当前代码的时间边界是「当前日期往前推1个月到当日」，属于滚动30天的统计范围，并非严格自然月的上月全量业务记录，比如当日为5月15日时，查询拉取的是4月15日到5月15日的数据，不是整个4月的业务记录。

核心思路是做两侧类型对齐，同时替换为严格的自然月时间边界，两种可选实现可根据表的分区设计选择：

• 方案1：对TIMESTAMP字段做显式转DATE处理，适配边界值类型
  适合表为按DATE分区的场景，转换后可命中分区裁剪，大幅降低查询扫描的数据量。修改后的完整SQL：

  SELECT user_mobile, count(*) as total_customer 
  FROM `Project.Dataset.Table` cr
  WHERE DATE(cr.DATE) BETWEEN 
    DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH) 
    AND 
    DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
  GROUP BY user_mobile
  

  其中时间边界的计算逻辑：DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH)取上月1号作为起始点，DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)取上月最后1天作为结束点，完全覆盖整上月的自然月范围。
• 方案2：将DATE类型的边界值转为TIMESTAMP，适配字段类型
  适合表为按TIMESTAMP字段做分区的场景，不对原始字段做函数处理，可直接命中分区规则。修改后的WHERE条件为：

  WHERE cr.DATE BETWEEN 
    TIMESTAMP(DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH)) 
    AND 
    TIMESTAMP(DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY))
  

• BigQuery中跨TIMESTAMP/DATETIME/DATE/TIME类型的时间比较不会触发隐式转换，所有参与比较的时间字段必须做显式类型对齐，否则会持续触发运算符签名匹配错误。
• 原有查询中HAVING count(*) >=1属于冗余逻辑，WHERE条件过滤后分组的记录计数必然大于等于1，删除后可减少不必要的聚合计算开销。

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