如何在BigQuery的WITH子句中生成连续月度首日日期列表

问题背景 #

需要生成从2021-01-01开始的未来24个月中每个月的首日日期列表，常规SQL的递归CTE实现如下：

with mnt as (
  select 1 as n, convert(date,'20210101',112) as d
  union all
  select n + 1, dateadd(month,1,d)
  from mnt
  where n < 24
)
select d from mnt

迁移到BigQuery时出现报错：Table "mnt" must be qualified with a dataset (e.g. dataset.table)，用户编写的BigQuery代码及尝试的循环代码分别如下：

报错的BigQuery递归CTE代码 #

with mnt as (
  select 1 as n, CAST('2021-07-01' AS DATE FORMAT 'YYYY-MM-DD') as d
  union all
  select n+1, date_add(CAST('2021-07-01' AS DATE FORMAT 'YYYY-MM-DD'), INTERVAL 1 MONTH)
  from mnt
  where n < 24
)

select d as salesmonth from mnt

尝试的循环代码（无法生成表格结果） #

declare x DATE DEFAULT "2021-07-01";

REPEAT 
  Set x = date_add(x, INTERVAL 1 MONTH);
  SELECT x;
  until x > CAST('2023-07-01' AS DATE FORMAT 'YYYY-MM-DD')
END REPEAT;

解决方法 #

方法1：修正递归CTE（符合BigQuery语法） #

BigQuery要求递归CTE必须显式添加RECURSIVE关键字，同时优化日期转换和递归逻辑：

WITH RECURSIVE mnt AS (
  SELECT 1 AS n, DATE('2021-01-01') AS d
  UNION ALL
  SELECT n + 1, DATE_ADD(d, INTERVAL 1 MONTH)
  FROM mnt
  WHERE n < 24
)
SELECT d AS salesmonth FROM mnt

关键说明：

• 必须添加RECURSIVE关键字，这是解决报错的核心
• 用DATE('2021-01-01')简化日期转换，BigQuery支持直接识别标准日期格式
• 递归计算时引用上一行的d字段，确保生成连续递增的月份日期

方法2：使用GENERATE_DATE_ARRAY（更简洁高效） #

BigQuery提供了专门的日期数组生成函数，无需递归即可实现需求：

SELECT 
  date AS salesmonth
FROM UNNEST(
  GENERATE_DATE_ARRAY(
    DATE('2021-01-01'), 
    DATE_ADD(DATE('2021-01-01'), INTERVAL 23 MONTH), 
    INTERVAL 1 MONTH
  )
)

关键说明：

• GENERATE_DATE_ARRAY生成从起始日期到结束日期的连续日期数组，步长为1个月
• 结束日期设置为DATE('2021-01-01') + 23个月，刚好得到24个月份的首日
• 用UNNEST将数组展开为表格形式，性能比递归CTE更优

内容的提问来源于stack exchange，提问作者Minh Anh Hoang