Redshift SQL如何按起止日期拆分每行生成每日记录？

嘿，我来帮你搞定这个日期拆分的问题！你遇到的relation "cte" does not exist错误，其实是因为Redshift对递归CTE的语法有特定要求——你需要加上RECURSIVE关键字才能让递归逻辑生效。不过先别急，我会先帮你修复递归CTE的写法，再给你推荐一个更适合Redshift的高效方案。

一、修复你的递归CTE写法 #

Redshift基于PostgreSQL，递归CTE必须显式声明RECURSIVE，否则数据库会把递归部分当成普通的CTE引用，自然找不到名为cte的关系。修改后的代码如下：

WITH RECURSIVE cte AS (
    SELECT name, start_date, end_date 
    FROM dates
    UNION ALL
    SELECT name, DATEADD(day, 1, start_date), end_date 
    FROM cte 
    WHERE start_date < end_date
)
SELECT name, start_date AS date 
FROM cte
ORDER BY name, start_date;

这个代码可以正常运行，把你的示例数据拆分成每日一行：

• Peter F. 会生成2018-03-01到2018-03-05的5行
• Sam R. 会生成2018-04-17到2018-04-20的4行

不过要注意：递归CTE在Redshift里的性能很差，尤其是当你的表有大量行或者日期范围很长时，逐行递归的方式会拖慢查询速度，所以更推荐下面的方法。

二、更高效的数字序列关联法 #

Redshift更适合用预生成数字序列+关联查询的方式来拆分日期，这种方法是批量处理，性能比递归好很多。

方法1：利用系统表生成数字序列 #

Redshift的系统表（比如stl_scan）包含大量行，我们可以用它来生成连续的数字序列，覆盖你需要的最大日期范围：

-- 生成0到3650的数字（覆盖10年的日期，可按需调整LIMIT值）
WITH numbers AS (
    SELECT ROW_NUMBER() OVER () - 1 AS num
    FROM stl_scan
    LIMIT 3650
)
SELECT 
    d.name,
    DATEADD(day, n.num, d.start_date) AS date
FROM dates d
JOIN numbers n 
    ON DATEADD(day, n.num, d.start_date) <= d.end_date
ORDER BY d.name, date;

方法2：使用generate_series（适合较新版本Redshift） #

如果你的Redshift版本支持generate_series（集群模式下现在也支持了），可以直接用它生成数字序列：

WITH numbers AS (
    SELECT generate_series(0, 3650) AS num
)
SELECT 
    d.name,
    DATEADD(day, n.num, d.start_date) AS date
FROM dates d
JOIN numbers n 
    ON DATEADD(day, n.num, d.start_date) <= d.end_date
ORDER BY d.name, date;

这两种方法的逻辑都是：生成从0开始的连续数字，每个数字对应start_date加上N天，只要结果不超过end_date，就保留这一行，最终得到每日的记录。

总结 #

• 递归CTE适合小数据集快速测试，但性能不佳；
• 数字序列关联法是Redshift的最优解，适合处理大规模数据。

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