BigQuery中如何动态生成上月与当月的yyyymmdd格式日期用于_TABLE_SUFFIX筛选

嘿，这事儿好办！要在BigQuery里摆脱硬编码日期的麻烦，动态生成上月初到当月指定日期（或当日）的范围来筛选分区表后缀，咱们直接用BigQuery的内置日期函数就能搞定，完全不用手动改数字。

核心思路 #

利用BigQuery的日期函数动态生成yyyymmdd格式的字符串，替换掉原来固定的日期值：

• 上月第一天：先获取当前日期减1个月，再截断到当月月初，最后转成字符串格式
• 当月目标日期：可以是当前系统日期，也可以是当月固定某一天（比如你例子里的7号）

修改后的完整查询语句（到当月今日） #

如果需要筛选从上月初到今天的数据，用这个版本：

select 
  stream_id,
  app_info.id,
  app_info.version, 
  event_date,
  event_name, 
  param1.value.string_value as category, 
  param2.value.string_value as action, 
  param3.value.string_value as label, 
  count(1) as totalevent,
  count(distinct user_pseudo_id ) as uniqueusers 
From `abc_150838411.events_*`, 
  UNNEST(event_params) as param1, 
  UNNEST(event_params) as param2, 
  UNNEST(event_params) as param3 
WHERE 
  _TABLE_SUFFIX BETWEEN 
    -- 生成上月第一天的yyyymmdd格式
    FORMAT_DATE("%Y%m%d", DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH)) 
    AND 
    -- 生成当前日期的yyyymmdd格式
    FORMAT_DATE("%Y%m%d", CURRENT_DATE())
  and param1.key='category' 
  and param2.key='action' 
  and param3.key='label' 
group by 1, 2, 3, 4, 5,6,7,8 
order by totalevent desc

如果需要固定到当月某一天（比如你例子里的7号） #

要是你需要筛选到当月的固定日期（比如每月7号），只需要把右侧的日期生成逻辑改成这样：

-- 生成当月7号的yyyymmdd格式（当月初+6天）
FORMAT_DATE("%Y%m%d", DATE_ADD(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 6 DAY))

函数解释 #

咱们拆解一下用到的关键函数：

• CURRENT_DATE()：获取BigQuery所在时区的当前日期，要是需要指定时区，比如国内时区可以写成CURRENT_DATE('Asia/Shanghai')
• DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH)：得到上个月的今天
• DATE_TRUNC(..., MONTH)：把日期截断到当月的第一天，比如把2024-05-15变成2024-05-01
• FORMAT_DATE("%Y%m%d", ...)：把日期对象转换成yyyymmdd的字符串格式，完美匹配_TABLE_SUFFIX的命名规则

这样每次运行查询，都会自动根据当前时间计算日期范围，再也不用手动改数字啦！

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