如何在SQL/BigQuery中为指定日期区间生成Day 1、Day 2等序列？

需求说明 #

给定起始日期14-09-2022（即2022-09-14）和结束日期23-10-2022（即2022-10-23），需要为区间内的每个日期生成对应的Day 1、Day 2……Day 39序列，示例输出如下：

方案1：BigQuery 实现 #

利用BigQuery内置的GENERATE_DATE_ARRAY函数生成日期序列，再通过日期差值计算Day编号：

WITH date_range AS (
  SELECT
    date AS date_value,
    -- 计算当前日期与起始日期的天数差，加1得到Day编号
    DATE_DIFF(date, DATE('2022-09-14'), DAY) + 1 AS day_num
  FROM UNNEST(GENERATE_DATE_ARRAY(DATE('2022-09-14'), DATE('2022-10-23'), INTERVAL 1 DAY)) AS date
)
SELECT
  -- 按DD-MM-YYYY格式输出日期
  FORMAT_DATE('%d-%m-%Y', date_value) AS Date,
  CONCAT('Day ', day_num) AS Day
FROM date_range
ORDER BY date_value;

方案2：通用SQL（支持递归CTE的数据库，如PostgreSQL、SQL Server等） #

通过递归CTE生成日期序列，并逐步累加Day编号：

WITH recursive date_series AS (
  -- 起始日期，Day 1
  SELECT
    CAST('2022-09-14' AS DATE) AS date_value,
    1 AS day_num
  UNION ALL
  -- 递归生成后续日期，Day编号加1
  SELECT
    DATE_ADD(date_value, INTERVAL 1 DAY),
    day_num + 1
  FROM date_series
  WHERE date_value < CAST('2022-10-23' AS DATE)
)
SELECT
  -- 按DD-MM-YYYY格式输出日期（不同数据库格式函数有差异：PostgreSQL用TO_CHAR，SQL Server用FORMAT）
  TO_CHAR(date_value, 'DD-MM-YYYY') AS Date, -- PostgreSQL写法；SQL Server替换为FORMAT(date_value, 'dd-MM-yyyy')
  CONCAT('Day ', day_num) AS Day
FROM date_series
ORDER BY date_value;

注意事项 #

• 不同数据库的日期函数略有差异，比如日期加减、格式化函数，需根据使用的数据库调整。
• 建议使用YYYY-MM-DD格式传递日期参数，避免解析错误。

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