Amazon QuickSight不支持子查询递归CTE，求日期映射表生成方案

我需要用递归CTE生成日期序列，再和位置表交叉连接得到每个位置对应每日的映射表。这段SQL在DBeaver里能正常运行，但在Amazon QuickSight里报错：

Invalid operation: Recursive CTE in subquery are not supported.

原SQL如下：

with recursive date_range(planned_date) as (
     select date(dateadd(day, -49, date(date_trunc('week', dateadd(day, 1, current_date)) - 1))) as planned_date
     union all
     select date(dateadd(day, 1, planned_date))
     from date_range
     where planned_date < date(dateadd(day, 1, current_date))
)
select * from date_range

我发现Tableau也有类似问题，但没找到公开解决方案。想问除了创建物理日历表之外，有没有其他办法在QuickSight里实现这个需求？

以下几种方法可以替代递归CTE生成日期序列，适配QuickSight的限制：

1. 手动构造数字序列生成日期 #

通过UNION ALL手动生成足够数量的数字行，再基于起始日期计算出完整的日期序列，适合所有支持标准SQL的数据源：

WITH numbers AS (
    SELECT 0 AS n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
    UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
    UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14
    UNION ALL SELECT 15 UNION ALL SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19
    UNION ALL SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23 UNION ALL SELECT 24
    UNION ALL SELECT 25 UNION ALL SELECT 26 UNION ALL SELECT 27 UNION ALL SELECT 28 UNION ALL SELECT 29
    UNION ALL SELECT 30 UNION ALL SELECT 31 UNION ALL SELECT 32 UNION ALL SELECT 33 UNION ALL SELECT 34
    UNION ALL SELECT 35 UNION ALL SELECT 36 UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39
    UNION ALL SELECT 40 UNION ALL SELECT 41 UNION ALL SELECT 42 UNION ALL SELECT 43 UNION ALL SELECT 44
    UNION ALL SELECT 45 UNION ALL SELECT 46 UNION ALL SELECT 47 UNION ALL SELECT 48 UNION ALL SELECT 49
),
date_range AS (
    SELECT DATEADD(day, n, DATE(DATEADD(day, -49, DATE(DATE_TRUNC('week', DATEADD(day, 1, CURRENT_DATE)) - 1)))) AS planned_date
    FROM numbers
)
SELECT * FROM date_range

如果是PostgreSQL或支持generate_series的数据库，还可以简化：

WITH date_range AS (
    SELECT generate_series(
        DATE(DATEADD(day, -49, DATE(DATE_TRUNC('week', DATEADD(day, 1, CURRENT_DATE)) - 1))),
        DATE(DATEADD(day, 1, CURRENT_DATE)),
        INTERVAL '1 day'
    )::DATE AS planned_date
)
SELECT * FROM date_range

2. 借助现有业务表生成日期 #

如果数据库里有一张行数足够多的业务表（比如超过50行），可以用窗口函数生成连续数字，再转换为日期：

WITH numbers AS (
    SELECT ROW_NUMBER() OVER () - 1 AS n
    FROM your_large_business_table
    LIMIT 50 -- 对应需要的50天范围
),
date_range AS (
    SELECT DATEADD(day, n, DATE(DATEADD(day, -49, DATE(DATE_TRUNC('week', DATEADD(day, 1, CURRENT_DATE)) - 1)))) AS planned_date
    FROM numbers
)
SELECT * FROM date_range

注意要确保业务表行数不小于需要生成的日期数量，按需调整LIMIT值。

3. 在QuickSight内部补全日期 #

如果使用SPICE作为数据源，可以直接在QuickSight内处理：

• 先导入包含位置信息的表和现有日期数据到SPICE
• 创建覆盖目标区间（-49天到当前日）的日期范围参数
• 利用数据准备中的「生成日期」功能（部分区域支持）生成完整日期序列
• 将生成的日期序列和位置表做交叉连接，得到所需映射表

这种方法不需要修改底层SQL，直接在可视化层或数据准备阶段完成。

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