求助：将Excel公式转换为Redshift SQL（替代Oracle MODEL函数）

由于Redshift不支持Oracle的MODEL函数，我们可以利用**递归CTE（Common Table Expression）**来实现这种依赖往期月份计算结果的逻辑，递归CTE能够按顺序迭代每个月份，复用前序月份的计算值。

逻辑对应说明 #

先明确Excel公式对应的递归规则（假设D列为当前月，C列为上月，B列为上上月）：

• 当前月D2 = 当月SU值
• 当前月D3 = 上月的D7值
• 当前月D4 = 上月的D4值 + 上上月的D7值
• 当前月D5 = D3 * 1.54 + D4
• 当前月D6 = D2 - D5
• 当前月D7 = D6 / 2.38
• 当前月D8 = D3 + D4 + D7（最终需要的计算值）

完整Redshift SQL代码 #

WITH ordered_months AS (
    -- 将月份排序并生成序号，确保递归按时间顺序处理
    SELECT 
        PERIOD_MONTH,
        SU,
        ROW_NUMBER() OVER (ORDER BY TO_DATE(PERIOD_MONTH, 'YYYY-MM')) AS month_seq
    FROM TESTOSS
),
anchor AS (
    -- 锚点CTE：初始化前两个月份的计算值（无往期数据的初始值可根据业务调整）
    SELECT 
        month_seq,
        PERIOD_MONTH,
        SU AS D2,
        CASE WHEN month_seq = 1 THEN 0 ELSE LAG(D7) OVER (ORDER BY month_seq) END AS D3,
        CASE WHEN month_seq = 1 THEN 0 ELSE LAG(D4) OVER (ORDER BY month_seq) + COALESCE(LAG(D7, 2) OVER (ORDER BY month_seq), 0) END AS D4,
        0::NUMERIC AS D5,
        0::NUMERIC AS D6,
        CASE WHEN month_seq = 1 THEN 0 ELSE (SU - (LAG(D7) OVER (ORDER BY month_seq)*1.54 + (LAG(D4) OVER (ORDER BY month_seq) + COALESCE(LAG(D7,2) OVER (ORDER BY month_seq),0))))/2.38 END AS D7,
        CASE WHEN month_seq = 1 THEN 0 ELSE LAG(D7) OVER (ORDER BY month_seq) + (LAG(D4) OVER (ORDER BY month_seq) + COALESCE(LAG(D7,2) OVER (ORDER BY month_seq),0)) + ((SU - (LAG(D7) OVER (ORDER BY month_seq)*1.54 + (LAG(D4) OVER (ORDER BY month_seq) + COALESCE(LAG(D7,2) OVER (ORDER BY month_seq),0))))/2.38) END AS D8
    FROM ordered_months
    WHERE month_seq <= 2
),
recursive_cte AS (
    -- 递归CTE：迭代计算后续每个月份的结果
    SELECT * FROM anchor
    UNION ALL
    SELECT 
        om.month_seq,
        om.PERIOD_MONTH,
        om.SU AS D2,
        rc.D7 AS D3,
        rc.D4 + COALESCE(rc_prev.D7, 0) AS D4,
        rc.D7 * 1.54 + (rc.D4 + COALESCE(rc_prev.D7, 0)) AS D5,
        om.SU - (rc.D7 * 1.54 + (rc.D4 + COALESCE(rc_prev.D7, 0))) AS D6,
        (om.SU - (rc.D7 * 1.54 + (rc.D4 + COALESCE(rc_prev.D7, 0)))) / 2.38 AS D7,
        rc.D7 + (rc.D4 + COALESCE(rc_prev.D7, 0)) + ((om.SU - (rc.D7 * 1.54 + (rc.D4 + COALESCE(rc_prev.D7, 0)))) / 2.38) AS D8
    FROM ordered_months om
    JOIN recursive_cte rc ON om.month_seq = rc.month_seq + 1
    LEFT JOIN recursive_cte rc_prev ON om.month_seq = rc_prev.month_seq + 2
    WHERE om.month_seq > 2
)
-- 查询最终结果，获取每个月份的D8计算值
SELECT 
    PERIOD_MONTH,
    ROUND(D8, 2) AS calculated_D8_value
FROM recursive_cte
ORDER BY month_seq;

关键调整提示 #

1. 初始值修改：锚点CTE中第一个无往期数据月份的D3、D4等值设为0，需根据实际业务规则调整这些初始值。
2. 精度控制：使用ROUND函数可控制计算结果的小数位数，按需调整即可。
3. 月份排序：通过TO_DATE将字符串月份转为日期类型，避免字符串排序导致的时间顺序错误（比如2022-01排在2021-12之后）。

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