动态更新滚动余额：SQL计算预算下可购买商品的判定问题

普通窗口函数的滚动求和会累加所有商品成本，不会跳过预算不足未购买的商品，因此无法正确更新剩余预算传递给后续行计算。

使用递归CTE逐行继承前置剩余预算状态，按id顺序判断每轮是否可购买，代码如下：

WITH RECURSIVE purchase_flow AS (
    -- 锚点：处理第一条商品
    SELECT 
        id,
        alowance,
        cost,
        alowance AS remaining_before_purchase,
        CASE WHEN cost <= alowance THEN 1 ELSE 0 END AS can_purchase,
        CASE WHEN cost <= alowance THEN alowance - cost ELSE alowance END AS remaining_after_purchase
    FROM budget
    WHERE id = 1
    UNION ALL
    -- 递归处理后续商品
    SELECT 
        b.id,
        b.alowance,
        b.cost,
        pf.remaining_after_purchase AS remaining_before_purchase,
        CASE WHEN b.cost <= pf.remaining_after_purchase THEN 1 ELSE 0 END AS can_purchase,
        CASE WHEN b.cost <= pf.remaining_after_purchase THEN pf.remaining_after_purchase - b.cost ELSE pf.remaining_after_purchase END AS remaining_after_purchase
    FROM budget b
    JOIN purchase_flow pf ON b.id = pf.id + 1
)
SELECT 
    id,
    alowance,
    cost,
    remaining_before_purchase AS true_current_limit,
    can_purchase
FROM purchase_flow
ORDER BY id;

如果需要返回布尔类型的购买判定，直接把CASE WHEN返回值改成TRUE/FALSE即可。

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