如何在BigQuery中不硬编码自动获取往年12月31日日期

核心逻辑 #

全程基于日期函数动态计算，不需要写死任何年份值，跨年时会自动适配返回对应日期，不会出错。

各日期计算方式 #

• 去年12月31日：取当前日期减1年的当年最后一天，公式：LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), YEAR)
• 前年12月31日：取当前日期减2年的当年最后一天，公式：LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR), YEAR)
• 三年前12月31日：取当前日期减3年的当年最后一天，公式：LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR), YEAR)

完整查询示例 #

SELECT
  -- 去年12月31日
  LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR), YEAR) AS last_year_dec_31,
  -- 前年12月31日
  LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 YEAR), YEAR) AS year_before_last_dec_31,
  -- 三年前12月31日
  LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 3 YEAR), YEAR) AS three_years_ago_dec_31

业务场景适配说明 #

如果需要基于业务表中的日期字段（而非当前日期）计算，直接将公式中的CURRENT_DATE()替换为对应的日期字段即可。如果需要返回时间戳格式，在公式外层包裹TIMESTAMP()函数即可。

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