如何在不分组其他列时展开日期列的全局日期区间？

问题描述 #

我想要在不按其他列分组的情况下，展开日期列最小和最大值之间的所有日期区间。目前的代码是按每个job_name分组生成各自的日期范围，但我需要生成覆盖所有job_name的全局日期范围，让每个job_name都展开为2021-08-20至2021-08-22的3行数据。

现有代码 #

WITH 
    dataset AS (
      SELECT * 
      FROM 
        ( VALUES      
            ('A', DATE '2021-08-21'), ('A', DATE '2021-08-22'),
            ('B', DATE '2021-08-20'), ('B', DATE '2021-08-21')
        ) AS d (job_name, run_date)),
    
    nested_dates AS (
        select job_name, sequence(min(run_date), max(run_date), interval '1' day) seq
        from dataset
        group by job_name)

SELECT job_name, dates
FROM nested_dates
CROSS JOIN UNNEST(seq) AS t(dates)

修改后的代码 #

WITH 
    dataset AS (
      SELECT * 
      FROM 
        ( VALUES      
            ('A', DATE '2021-08-21'), ('A', DATE '2021-08-22'),
            ('B', DATE '2021-08-20'), ('B', DATE '2021-08-21')
        ) AS d (job_name, run_date)),
    
    global_dates AS (
        -- 计算全局最小/最大日期，生成统一的日期序列
        SELECT sequence(MIN(run_date), MAX(run_date), INTERVAL '1' day) AS seq
        FROM dataset
    ),
    unique_jobs AS (
        -- 获取所有不重复的job_name
        SELECT DISTINCT job_name
        FROM dataset
    )

SELECT u.job_name, t.dates
FROM unique_jobs u
CROSS JOIN global_dates g
CROSS JOIN UNNEST(g.seq) AS t(dates)
ORDER BY u.job_name, t.dates;

改动说明 #

• global_dates：不再按job_name分组，直接计算整个数据集的最小和最大日期，生成全局统一的日期序列。
• unique_jobs：提取数据集里所有唯一的job_name。
• 交叉连接：让每个job_name与全局日期序列中的每个日期匹配，实现所有Job都展开为完整的全局日期范围。

期望输出 #

#   job_name    dates
1   A   2021-08-20 00:00:00.000
2   A   2021-08-21 00:00:00.000
3   A   2021-08-22 00:00:00.000
4   B   2021-08-20 00:00:00.000
5   B   2021-08-21 00:00:00.000
6   B   2021-08-22 00:00:00.000

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