如何基于前一行条件计算factor_adj列？窗口函数实现遇阻

要实现这种依赖前一行计算结果的逻辑，**递归CTE（Common Table Expression）**是合适的选择——普通窗口函数无法引用自身计算出的上一行结果，而递归CTE可以逐行迭代计算。

解决方案SQL #

WITH RECURSIVE factor_calc AS (
    -- 锚点：处理第一行，初始化factor_adj为1
    SELECT 
        row_number,
        "type",
        factor,
        CAST(1 AS FLOAT) AS factor_adj
    FROM factor_test
    WHERE row_number = 1
    
    UNION ALL
    
    -- 递归：基于上一行结果计算当前行的factor_adj
    SELECT 
        ft.row_number,
        ft."type",
        ft.factor,
        CASE
            WHEN fc."type" = 'related' THEN fc.factor_adj * 0.1
            ELSE fc.factor_adj * 0.2
        END AS factor_adj
    FROM factor_test ft
    JOIN factor_calc fc ON ft.row_number = fc.row_number + 1
)
SELECT * FROM factor_calc ORDER BY row_number;

逻辑说明 #

1. 锚点成员：先筛选出row_number = 1的行，直接设置factor_adj = 1，满足初始条件。
2. 递归成员：通过row_number关联上一行的计算结果（fc.row_number + 1 = ft.row_number），根据上一行的type执行分支逻辑：
   • 若上一行type为related，则当前factor_adj = 上一行factor_adj * 0.1
   • 其他情况，当前factor_adj = 上一行factor_adj * 0.2
3. 最终查询递归CTE的结果并按行号排序，即可得到与示例一致的factor_adj列。

测试验证 #

使用你提供的测试表数据：

CREATE TABLE factor_test (
    row_number integer,
    "type" varchar(256),
    "factor" float
);

INSERT INTO factor_test (row_number, "type", factor)
VALUES  
    (1, 'regular', 1),
    (2, 'regular', 0.6226),
    (3, 'regular', 0.4626319149),
    (4, 'regular', 0.3492),
    (5, 'regular', 0.2662688742),
    (6, 'regular', 0.2038145695),
    (7, 'regular', 0.1587655629),
    (8, 'related', 0.1229311258),
    (9, 'regular', 0.09119205297),
    (10, 'regular', 0.06457218542);

执行解决方案SQL后，输出的factor_adj列将完全匹配你给出的预期结果。

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