月度数据转日度数据：SQL实现报错及解决方案咨询

问题背景 #

现有月度数据结构如下（extraction_date为日期类型）：

需要将每个月末的yield值填充到对应月份的所有日期中，转换为如下日度数据：

错误代码问题分析 #

你提供的递归CTE代码报错AnalysisException: Could not resolve table reference: 'cte'，核心问题包括：

• 递归CTE的初始查询与递归查询字段数量不匹配：初始查询返回3个字段，递归部分返回4个字段
• 字段名错误：原表字段是extraction_date，代码中误用为extraction_dt
• 递归逻辑混乱：生成日期的条件和逻辑不符合需求，且daily_yield的计算逻辑完全错误（用字符串类型的isin做除法无意义）

正确实现方案 #

方案1：日期序列生成法（适配Spark SQL、BigQuery等支持序列函数的引擎） #

通过生成目标日期范围的序列，再关联月度数据匹配对应月份的yield：

WITH date_range AS (
    -- 生成覆盖所有数据月份的日期序列
    SELECT sequence(
        date_trunc('month', MIN(extraction_date)),
        MAX(extraction_date),
        INTERVAL 1 DAY
    ) AS all_dates
    FROM datashop
),
expanded_dates AS (
    -- 将日期序列展开为单行记录
    SELECT explode(all_dates) AS extraction_dt
    FROM date_range
),
monthly_mapping AS (
    -- 给月度数据标记对应月份的起始和结束日期
    SELECT 
        isin,
        date_trunc('month', extraction_date) AS month_start,
        extraction_date AS month_end,
        yield
    FROM datashop
)
-- 关联日期与对应月份的yield数据
SELECT 
    m.isin,
    e.extraction_dt,
    m.yield
FROM expanded_dates e
JOIN monthly_mapping m 
    ON e.extraction_dt BETWEEN m.month_start AND m.month_end
ORDER BY m.isin, e.extraction_dt;

方案2：递归CTE法（适配支持递归的SQL引擎） #

修正递归逻辑，保证字段一致性，逐天生成当月日期：

WITH RECURSIVE cte AS (
    -- 初始查询：获取月度数据并标记当月第一天
    SELECT 
        isin,
        extraction_date AS month_end,
        date_trunc('month', extraction_date) AS extraction_dt,
        yield
    FROM datashop
    UNION ALL
    -- 递归生成当月剩余日期
    SELECT 
        isin,
        month_end,
        date_add(extraction_dt, INTERVAL 1 DAY) AS extraction_dt,
        yield
    FROM cte
    WHERE extraction_dt < month_end
)
SELECT isin, extraction_dt, yield
FROM cte
ORDER BY isin, extraction_dt;

注意事项 #

• 不同SQL引擎的日期函数语法略有差异：比如MySQL用DATE_FORMAT(date, '%Y-%m-01')替代date_trunc('month', date)，用DATE_ADD(date, INTERVAL 1 DAY)替代date_add
• 递归CTE必须保证初始查询和递归查询的字段数量、数据类型完全一致，否则会触发解析错误

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