如何在BigQuery标准SQL中将日期范围拆分为单独行？

需求说明 #

需要将数据表my_table中的日期范围（date_from与date_to）拆分为每行对应单个日期的格式，使用BigQuery标准SQL实现。

插入测试数据的SQL语句 #

INSERT INTO my_table (Qty, ID, Source, date_from, date_to)
VALUES
  (null, 101, 'A', '2022-01-10', '2022-01-11'),
  (10, 101, 'A', '2022-01-08', '2022-01-10'),
  (15, 101, 'A', '2022-01-05', '2022-01-08'),
  (null, 101, 'A', '2022-01-03', '2022-01-05'),
  (30, 101, 'A', '2022-01-01', '2022-01-03');

原表结构及数据 #

期望输出表结构及数据 #

问题分析（用户尝试的SQL） #

用户之前的SQL语句因日期范围生成逻辑错误（GENERATE_DATE_ARRAY的起始和结束参数颠倒），且未正确处理日期范围的闭开规则，导致出现重复行：

SELECT DISTINCT
  Qty,
  ID,
  Source
FROM my_table
CROSS JOIN UNNEST(GENERATE_DATE_ARRAY(date_to, IF(date_from > CURRENT_DATE(), CURRENT_DATE(), date_from))) AS date
order by date desc

正确解决方案 #

以下SQL可准确生成期望的单日记录：

SELECT
  Qty,
  ID,
  Source,
  FORMAT_DATE('%Y/%m/%d', date) AS Date
FROM my_table
CROSS JOIN UNNEST(GENERATE_DATE_ARRAY(date_from, DATE_SUB(date_to, INTERVAL 1 DAY), INTERVAL 1 DAY)) AS date
ORDER BY Date ASC;

关键说明 #

1. 日期范围生成：使用GENERATE_DATE_ARRAY(date_from, DATE_SUB(date_to, INTERVAL 1 DAY), INTERVAL 1 DAY)生成从date_from到date_to前一天的所有日期，匹配原数据左闭右开的日期范围规则。
2. 日期格式化：通过FORMAT_DATE('%Y/%m/%d', date)将日期转换为期望的YYYY/MM/DD格式。
3. 去重与排序：无需DISTINCT（原数据日期范围无重叠），按Date升序排序即可得到与期望一致的输出顺序。

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