如何用SQL高效实现基于上一行结果的行内Max运算？

数据表与预期输出 #

id  col1   col2   col3  result
------------------------------
1   10     30     2     30
2   15     10     8     28
3   25     10     5     25
4   20     25     9     25
5   30     15     4     30

计算规则 #

result 列按以下递推公式计算：

result = MAX(col1, col2, 上一行result - 上一行col3)

举两个实际计算例子：

• 第1行：无前置行，直接取 MAX(10, 30)，结果为30
• 第2行：取 MAX(15, 10, 30-2)，结果为28

当前遇到的问题 #

我已经写出了「上一行result - 上一行col3」的计算代码：

FIRST_VALUE(MAX(col1, col2)) OVER (ORDER BY id)
    - IFNULL(SUM(col3) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0)

但卡在了递推逻辑上——当前行的result需要用上一行的result作为MAX函数的输入，普通窗口函数搞不定这种动态依赖，想知道能不能用SQL分区高效实现这个计算？

解决方案 #

这种依赖前一行计算结果的逻辑属于迭代递推，普通窗口函数（包括分区窗口）没法直接处理，因为窗口函数是基于数据集快照计算，无法引用动态生成的上一行结果。不过有几种可行的实现方式：

1. 递归CTE（最通用高效） #

递归CTE可以逐行迭代计算，完美适配这种递推场景：

WITH RECURSIVE calc_result AS (
    -- 锚点：取第一行的result
    SELECT 
        id,
        col1,
        col2,
        col3,
        MAX(col1, col2) AS result
    FROM your_table
    WHERE id = 1

    UNION ALL

    -- 递归：用上一行结果算当前行
    SELECT 
        t.id,
        t.col1,
        t.col2,
        t.col3,
        MAX(t.col1, t.col2, cr.result - cr.col3) AS result
    FROM your_table t
    JOIN calc_result cr ON t.id = cr.id + 1
)
SELECT * FROM calc_result ORDER BY id;

如果id不是连续整数，先给数据加个连续序号再计算：

WITH numbered_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM your_table
),
RECURSIVE calc_result AS (
    SELECT 
        id,
        col1,
        col2,
        col3,
        rn,
        MAX(col1, col2) AS result
    FROM numbered_data
    WHERE rn = 1

    UNION ALL

    SELECT 
        nd.id,
        nd.col1,
        nd.col2,
        nd.col3,
        nd.rn,
        MAX(nd.col1, nd.col2, cr.result - cr.col3) AS result
    FROM numbered_data nd
    JOIN calc_result cr ON nd.rn = cr.rn + 1
)
SELECT id, col1, col2, col3, result FROM calc_result ORDER BY id;

2. 按分区计算的扩展写法 #

如果需要按某个字段（比如group_id）分区单独递推，只需要在递归逻辑里加上分区匹配条件：

WITH numbered_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY group_id ORDER BY id) AS rn
    FROM your_table
),
RECURSIVE calc_result AS (
    SELECT 
        group_id,
        id,
        col1,
        col2,
        col3,
        rn,
        MAX(col1, col2) AS result
    FROM numbered_data
    WHERE rn = 1

    UNION ALL

    SELECT 
        nd.group_id,
        nd.id,
        nd.col1,
        nd.col2,
        nd.col3,
        nd.rn,
        MAX(nd.col1, nd.col2, cr.result - cr.col3) AS result
    FROM numbered_data nd
    JOIN calc_result cr ON nd.group_id = cr.group_id AND nd.rn = cr.rn + 1
)
SELECT group_id, id, col1, col2, col3, result FROM calc_result ORDER BY group_id, id;

3. 数据库特定方案 #

部分数据库有扩展函数可以处理，但递归CTE是跨数据库通用的最优解：

• PostgreSQL：可以用自定义函数配合LAG()，但递归CTE更直观
• MySQL 8.0+/SQL Server：直接用上面的递归CTE写法即可

为什么原有尝试无效？ #

你写的代码逻辑错误，它假设result是初始最大值减去累计col3，但实际上每一行的result是三个值取最大，递推过程中result可能被col1/col2重置，根本不是单纯的递减，所以累计求和的思路走不通。

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