Redshift递归查询问题求助：6个月后计算逻辑失效

安全阿岳

2026-6-13

问题修正请求

测试数据

CREATE TABLE products_su 
(
    country varchar(2), 
    intprd varchar(20), 
    period date, 
    su int 
);

INSERT INTO products_su (country, intprd, "period", su)
VALUES('GL', 'med', '2024-02-01', 7);
INSERT INTO products_su (country, intprd, "period", su)
VALUES('GL', 'med', '2024-03-01', 15);
INSERT INTO products_su (country, intprd, "period", su)
VALUES('GL', 'med', '2024-04-01', 35);
INSERT INTO products_su (country, intprd, "period", su)
VALUES('GL', 'med', '2024-05-01', 105);
INSERT INTO products_su (country, intprd, "period", su)
VALUES('GL', 'med', '2024-06-01', 140);
INSERT INTO products_su (country, intprd, "period", su)
VALUES('GL', 'med', '2024-07-01', 180);
INSERT INTO products_su (country, intprd, "period", su)
VALUES('GL', 'med', '2024-08-01', 261);
INSERT INTO products_su (country, intprd, "period", su)
VALUES('GL', 'med', '2024-09-01', 211);
INSERT INTO products_su (country, intprd, "period", su)
VALUES('GL', 'med', '2024-10-01', 187);
INSERT INTO products_su (country, intprd, "period", su)
VALUES('GL', 'med', '2024-11-01', 318);
INSERT INTO products_su (country, intprd, "period", su)
VALUES('GL', 'med', '2024-12-01', 208);

COMMIT;

需求说明

需要编写SQL实现指定计算逻辑，核心规则为：部分字段的计算逻辑在第6个月后（第7个月及以后）发生变化，需通过递归SQL实现。当前编写的递归SQL前6个月结果正常，但第7个月后计算结果不符合预期。

现有问题SQL

WITH RECURSIVE su AS 
(
    SELECT 
        country, 
        period, 
        su::FLOAT AS su,
        ROW_NUMBER() OVER (PARTITION BY country ORDER BY period) - 1 AS rn
    FROM products_su
),
roll 
(
    country,
    rn,
    period,
    tsu,
    rep_pat,
    new_pat,
    tpe,
    peq,
    np_0,
    np_1,
    np_2,
    np_3,
    np_4,
    np_5,
    np_6,
    np_7,
    np_8,
    np_9,
    np_10,
    np_11,
    np_12,
    np_13
) AS (
    -- Anchor (first month)
    SELECT 
        country,
        rn,
        period,
        su,
        CAST(0.0 AS FLOAT),
        ROUND(su / 4.0, 4),
        ROUND(su / 4.0, 4),
        CAST(NULL AS FLOAT),
        ROUND(su / 4.0, 4),
        CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT),
        CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT),
        CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT),
        CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT), CAST(0.0 AS FLOAT),
        CAST(0.0 AS FLOAT)
    FROM su
    WHERE rn = 0

    UNION ALL

    -- Recursive rows
    SELECT 
        s.country,
        s.rn,
        s.period,
        s.su,
        CASE 
            WHEN s.rn < 6 THEN 0.0
            ELSE ROUND((r.np_1 + r.np_2 + r.np_3) / 3.0, 4)
        END,
        CASE 
            WHEN s.rn < 6 THEN ROUND(s.su / 4.0, 4)
            ELSE ROUND((s.su - ((r.np_1 + r.np_2 + r.np_3) / 3.0 * 6.0)) / 6.0, 4)
        END,
        CASE 
            WHEN s.rn < 6 THEN ROUND(s.su / 4.0, 4)
            ELSE ROUND((s.su - ((r.np_1 + r.np_2 + r.np_3) / 3.0 * 6.0)) / 6.0, 4)
        END,
        CASE 
            WHEN s.rn < 13 THEN NULL
            ELSE ROUND((
                CASE 
                    WHEN s.rn < 6 THEN ROUND(s.su / 4.0, 4)
                    ELSE ROUND((s.su - ((r.np_1 + r.np_2 + r.np_3) / 3.0 * 6.0)) / 6.0, 4)
                END
                + r.np_0 + r.np_1 + r.np_2 + r.np_3 + r.np_4 + r.np_5 + r.np_6 +
                  r.np_7 + r.np_8 + r.np_9 + r.np_10 + r.np_11 + r.np_12
            ), 4)
        END,
        -- Shift new_pat history
        CASE 
            WHEN s.rn < 6 THEN ROUND(s.su / 4.0, 4)
            ELSE ROUND((s.su - ((r.np_1 + r.np_2 + r.np_3) / 3.0 * 6.0)) / 6.0, 4)
        END,
        r.np_0, r.np_1, r.np_2, r.np_3, r.np_4, r.np_5, r.np_6,
        r.np_7, r.np_8, r.np_9, r.np_10, r.np_11, r.np_12
    FROM roll r
    JOIN su s ON s.country = r.country AND s.rn = r.rn + 1
)
SELECT 
    country,
    period,
    ROUND(tsu, 2) AS su,
    tpe,
    rep_pat,
    new_pat,
    peq
FROM roll
ORDER BY country, period;

问题分析与修正

现有SQL核心问题在于第7个月及以后的rep_pat计算引用的历史new_pat序列错误，同时new_pat和tpe的计算依赖关系未匹配递归步骤的历史值，导致结果偏差。

修正后的SQL

WITH RECURSIVE su AS 
(
    SELECT 
        country, 
        period, 
        su::FLOAT AS su,
        ROW_NUMBER() OVER (PARTITION BY country ORDER BY period) - 1 AS rn
    FROM products_su
),
roll 
(
    country,
    rn,
    period,
    tsu,
    rep_pat,
    new_pat,
    tpe,
    peq,
    np_0,
    np_1,
    np_2,
    np_3,
    np_4,
    np_5,
    np_6,
    np_7,
    np_8,
    np_9,
    np_10,
    np_11,
    np_12,
    np_13
) AS (
    -- Anchor (first month, rn=0)
    SELECT 
        country,
        rn,
        period,
        su,
        0.0::FLOAT,
        ROUND(su / 4.0, 4),
        ROUND(su / 4.0, 4),
        NULL::FLOAT,
        ROUND(su / 4.0, 4),
        0.0::FLOAT, 0.0::FLOAT, 0.0::FLOAT,
        0.0::FLOAT, 0.0::FLOAT, 0.0::FLOAT,
        0.0::FLOAT, 0.0::FLOAT, 0.0::FLOAT,
        0.0::FLOAT, 0.0::FLOAT, 0.0::FLOAT,
        0.0::FLOAT
    FROM su
    WHERE rn = 0

    UNION ALL

    -- Recursive step: compute next month (rn = previous rn +1)
    SELECT 
        s.country,
        s.rn,
        s.period,
        s.su,
        -- rep_pat: 第7个月及以后取前3个月的new_pat平均值（np_0=上月, np_1=上上月, np_2=上前三月）
        CASE 
            WHEN s.rn < 6 THEN 0.0
            ELSE ROUND((r.np_0 + r.np_1 + r.np_2) / 3.0, 4)
        END,
        -- new_pat: 第7个月及以后用SU减去rep_pat*6再除以6
        CASE 
            WHEN s.rn < 6 THEN ROUND(s.su / 4.0, 4)
            ELSE ROUND((s.su - (
                CASE WHEN s.rn <6 THEN 0.0 ELSE ROUND((r.np_0 + r.np_1 + r.np_2)/3.0,4) END
            ) *6.0)/6.0,4)
        END,
        -- tpe等于当期new_pat
        CASE 
            WHEN s.rn <6 THEN ROUND(s.su/4.0,4)
            ELSE ROUND((s.su - (
                CASE WHEN s.rn <6 THEN 0.0 ELSE ROUND((r.np_0 + r.np_1 + r.np_2)/3.0,4) END
            ) *6.0)/6.0,4)
        END,
        -- peq: 第13个月及以后计算总和
        CASE 
            WHEN s.rn <13 THEN NULL
            ELSE ROUND(
                (CASE WHEN s.rn <6 THEN ROUND(s.su/4.0,4) ELSE ROUND((s.su - (ROUND((r.np_0 + r.np_1 + r.np_2)/3.0,4))*6.0)/6.0,4) END)
                + r.np_0 + r.np_1 + r.np_2 + r.np_3 + r.np_4 + r.np_5 + r.np_6 +
                  r.np_7 + r.np_8 + r.np_9 + r.np_10 + r.np_11 + r.np_12
            ,4)
        END,
        -- 移位new_pat历史：当期new_pat作为新的np_0，历史值依次后移
        CASE 
            WHEN s.rn <6 THEN ROUND(s.su/4.0,4)
            ELSE ROUND((s.su - (ROUND((r.np_0 + r.np_1 + r.np_2)/3.0,4))*6.0)/6.0,4)
        END,
        r.np_0, r.np_1, r.np_2, r.np_3, r.np_4, r.np_5, r.np_6,
        r.np_7, r.np_8, r.np_9, r.np_10, r.np_11, r.np_12
    FROM roll r
    JOIN su s ON s.country = r.country AND s.rn = r.rn +1
)
SELECT 
    country,
    period,
    ROUND(tsu,2) AS su,
    tpe,
    rep_pat,
    new_pat,
    peq
FROM roll
ORDER BY country, period;

关键修正点

rep_pat逻辑修正：将原SQL中r.np_1 + r.np_2 + r.np_3改为r.np_0 + r.np_1 + r.np_2，对应前3个月的new_pat值（np_0为上月数据，np_1为上上月，以此类推）。
依赖一致性调整：确保第7个月及以后new_pat和tpe的计算中，rep_pat的取值与递归步骤中实际计算的rep_pat完全一致，避免重复计算的精度偏差。
历史序列移位确认：保持np_*序列的移位规则，当期new_pat作为新的np_0，历史值依次后移，正确维护时间窗口内的历史数据。

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

Redshift递归查询问题求助：6个月后计算逻辑失效

测试数据 #

需求说明 #

现有问题SQL #

问题分析与修正 #

修正后的SQL #