在BigQuery中生成每月29日至次月28日的自定义月度日期区间

需求规则 #

需要生成的自定义月度区间规则为：每个统计周期从当月29日开始，到次月28日结束，对应参考示例如下：

实现逻辑 #

核心计算逻辑如下：

• 若输入日期的日部分≥29，该日期归属于当月29日开启的统计区间
• 若输入日期的日部分<29，该日期归属于上月29日开启的统计区间
• 区间结束日期统一为起始日期加1个月后减1天，自动兼容闰年2月等特殊日期场景

可用代码（BigQuery标准SQL） #

WITH sample_input AS (
  -- 替换为你的实际日期表/字段即可
  SELECT DATE('2020-03-13') AS input_date UNION ALL
  SELECT DATE('2020-06-30') AS input_date UNION ALL
  SELECT DATE('2021-01-01') AS input_date UNION ALL
  SELECT DATE('2021-11-11') AS input_date
)
SELECT
  input_date,
  -- 计算区间起始日期
  IF(
    EXTRACT(DAY FROM input_date) >= 29,
    DATE_TRUNC(input_date, MONTH) + INTERVAL 28 DAY,
    DATE_SUB(DATE_TRUNC(input_date, MONTH), INTERVAL 1 MONTH) + INTERVAL 28 DAY
  ) AS period_start,
  -- 计算区间结束日期
  DATE_ADD(
    IF(
      EXTRACT(DAY FROM input_date) >= 29,
      DATE_TRUNC(input_date, MONTH) + INTERVAL 28 DAY,
      DATE_SUB(DATE_TRUNC(input_date, MONTH), INTERVAL 1 MONTH) + INTERVAL 28 DAY
    ),
    INTERVAL 1 MONTH
  ) - INTERVAL 1 DAY AS period_end
FROM sample_input

封装为UDF（可选） #

如果需要高频使用该逻辑，可以封装为自定义函数，调用更简洁：

CREATE OR REPLACE FUNCTION your_dataset.get_custom_month_period(input_date DATE)
RETURNS STRUCT<period_start DATE, period_end DATE>
AS (
  STRUCT(
    IF(
      EXTRACT(DAY FROM input_date) >= 29,
      DATE_TRUNC(input_date, MONTH) + INTERVAL 28 DAY,
      DATE_SUB(DATE_TRUNC(input_date, MONTH), INTERVAL 1 MONTH) + INTERVAL 28 DAY
    ) AS period_start,
    DATE_ADD(
      IF(
        EXTRACT(DAY FROM input_date) >= 29,
        DATE_TRUNC(input_date, MONTH) + INTERVAL 28 DAY,
        DATE_SUB(DATE_TRUNC(input_date, MONTH), INTERVAL 1 MONTH) + INTERVAL 28 DAY
      ),
      INTERVAL 1 MONTH
    ) - INTERVAL 1 DAY AS period_end
  )
);

-- 调用示例
SELECT 
  input_date,
  your_dataset.get_custom_month_period(input_date).period_start,
  your_dataset.get_custom_month_period(input_date).period_end
FROM your_table

内容的提问来源于stack exchange，提问作者CPA Random Stuff