Google BigQuery SQL：基于cycle_base和cycle_interval生成指定日期

核心思路 #

生成符合要求的日期，关键是锚定每条产品记录的两个核心规则：

• 日期必须和cycle_base是同一个星期几
• 日期与cycle_base的天数差，必须是cycle_interval的整数倍
  同时要确保日期落在2022年范围内，且处于valid_from和valid_to的有效期区间内。

具体实现步骤 #

1. 预处理规则参数 #

先为每条产品记录提取关键的周期标识：

• 提取cycle_base对应的星期几（名称+数字标识，适配BigQuery的日期函数）
• 保留cycle_interval、valid_from、valid_to等核心参数

2. 生成2022年候选日期池 #

用BigQuery的GENERATE_DATE_ARRAY生成2022年全年日期，再展开为单行数据，同时标记每个日期的星期几信息。

3. 筛选符合规则的日期 #

对每个候选日期，依次验证：

• 星期几与cycle_base匹配
• 日期在有效期范围内
• 日期与cycle_base的天数差是cycle_interval的整数倍

完整示例查询 #

WITH product_rules AS (
  SELECT
    product,
    cycle_base,
    valid_from,
    valid_to,
    cycle_interval,
    FORMAT_DATE('%A', cycle_base) AS target_dayname,
    -- 用ISOWEEKDAY保证周一=1、周日=7，适配业务常见的星期定义
    EXTRACT(ISOWEEKDAY FROM cycle_base) AS target_weekday
  FROM
    `你的项目ID.你的数据集.你的表名`
),
year_2022_dates AS (
  SELECT
    date AS candidate_date,
    FORMAT_DATE('%A', date) AS dayofweek,
    EXTRACT(ISOWEEKDAY FROM date) AS weekday
  FROM
    UNNEST(GENERATE_DATE_ARRAY('2022-01-01', '2022-12-31', INTERVAL 1 DAY)) AS date
)
SELECT
  y.candidate_date AS date,
  p.product,
  y.dayofweek,
  p.cycle_interval
FROM
  product_rules p
CROSS JOIN
  year_2022_dates y
WHERE
  -- 匹配星期几
  y.weekday = p.target_weekday
  -- 日期在有效期内
  AND y.candidate_date >= p.valid_from
  AND y.candidate_date <= p.valid_to
  -- 满足周期间隔要求
  AND MOD(DATE_DIFF(y.candidate_date, p.cycle_base, DAY), p.cycle_interval) = 0
ORDER BY
  p.product,
  y.candidate_date

关键细节提示 #

• 星期几标识：如果业务中使用周日为一周起始，可替换ISOWEEKDAY为DAYOFWEEK（BigQuery中DAYOFWEEK返回1=周日、7=周六）
• 性能优化：如果产品数据量较大，可先过滤出valid_to >= '2022-01-01'的记录，减少关联计算量
• 边界验证：如果cycle_base本身在2022年且在有效期内，会自动被纳入结果集

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