BigQuery/SQL需求：满足条件后维持列值至后续行

核心逻辑梳理 #

计算new_TARIFF_ALLOWANCE_DATA的规则：

• 当gift_given != 0 或 gift_received != 0时，当前行取值为 TARIFF_ALLOWANCE_DATA - gift_given + gift_received
• 不满足上述条件时，当前行直接继承上一行已计算出的new_TARIFF_ALLOWANCE_DATA值

常见实现方案（以SQL为例） #

这种带"状态延续"的条件计算，递归CTE是通用的解决方式，也可利用部分数据库的窗口函数扩展能力实现。

方案1：递归CTE实现（支持所有带递归功能的数据库）

假设你的表名为your_table，先给数据生成可排序的行号（如果已有连续自增主键可跳过这一步）：

WITH ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY your_sort_column) AS row_num
    FROM your_table
),
RECURSIVE cte AS (
    -- 初始化第一行的计算值
    SELECT 
        row_num,
        TARIFF_ALLOWANCE_DATA,
        gift_given,
        gift_received,
        CASE 
            WHEN gift_given != 0 OR gift_received != 0 
            THEN TARIFF_ALLOWANCE_DATA - gift_given + gift_received
            ELSE TARIFF_ALLOWANCE_DATA
        END AS new_TARIFF_ALLOWANCE_DATA
    FROM ranked_data
    WHERE row_num = 1

    UNION ALL

    -- 递归处理后续行，继承或更新值
    SELECT 
        r.row_num,
        r.TARIFF_ALLOWANCE_DATA,
        r.gift_given,
        r.gift_received,
        CASE 
            WHEN r.gift_given != 0 OR r.gift_received != 0 
            THEN r.TARIFF_ALLOWANCE_DATA - r.gift_given + r.gift_received
            ELSE c.new_TARIFF_ALLOWANCE_DATA
        END AS new_TARIFF_ALLOWANCE_DATA
    FROM ranked_data r
    JOIN cte c ON r.row_num = c.row_num + 1
)
SELECT * FROM cte ORDER BY row_num;

方案2：PostgreSQL专属简化写法

PostgreSQL支持链式窗口函数计算，可结合LAG()实现：

SELECT 
    *,
    CASE 
        WHEN gift_given != 0 OR gift_received != 0 
        THEN TARIFF_ALLOWANCE_DATA - gift_given + gift_received
        ELSE LAG(new_TARIFF_ALLOWANCE_DATA) OVER (ORDER BY your_sort_column)
    END AS new_TARIFF_ALLOWANCE_DATA
FROM your_table
WINDOW w AS (ORDER BY your_sort_column);

注：这种写法依赖数据库对"自引用计算列"的支持，稳定性不如递归CTE。

你之前方法失效的原因 #

普通的"前后行对比"通常用LAG()取原始表的列值，但你需要的是上一行计算后的new_TARIFF_ALLOWANCE_DATA，而非原始列数据。常规窗口函数无法直接引用当前查询中正在计算的列，这也是递归CTE成为这类需求通用方案的原因。

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