如何用SQL（Impala/Hive）基于列的上一行值推导NET_COST列？

可以在Impala/Hive中实现这个计算逻辑，核心是利用**递归CTE（Common Table Expression）**来处理依赖上一行结果的累计计算，以下是具体实现方案：

原始交易表 #

计算规则回顾 #

• NET_COST：
  • 第一行：初始值0 + 当次cost_of_purchase（仅buy操作生效）
  • 后续行：
    • buy操作：上一行NET_COST + 当前cost_of_purchase
    • sell操作：上一行NET_COST - (上一行NET_COST × 当前sell_ratio)
• AVERAGE_COST = NET_COST / cumulative_quantity

SQL实现代码 #

WITH ranked_transactions AS (
    -- 先给交易行按实际顺序编号（必须用真实排序字段，比如交易时间/流水号）
    SELECT 
        *,
        ROW_NUMBER() OVER (ORDER BY txn_id) AS rn -- 替换txn_id为你的真实排序字段（如txn_timestamp）
    FROM your_transaction_table
),
recursive_calc AS (
    -- 初始化：处理第一笔交易
    SELECT 
        operation,
        txn_quantity,
        cumulative_quantity,
        txn_amount,
        cost_of_purchase,
        sell_ratio,
        CASE WHEN operation = 'buy' THEN cost_of_purchase ELSE 0 END AS NET_COST,
        rn
    FROM ranked_transactions
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归计算后续每一行
    SELECT 
        rt.operation,
        rt.txn_quantity,
        rt.cumulative_quantity,
        rt.txn_amount,
        rt.cost_of_purchase,
        rt.sell_ratio,
        CASE 
            WHEN rt.operation = 'buy' THEN rc.NET_COST + rt.cost_of_purchase
            WHEN rt.operation = 'sell' THEN rc.NET_COST - (rc.NET_COST * rt.sell_ratio)
            ELSE rc.NET_COST
        END AS NET_COST,
        rt.rn
    FROM ranked_transactions rt
    JOIN recursive_calc rc ON rt.rn = rc.rn + 1
)
-- 最终输出含平均成本的结果
SELECT 
    operation,
    txn_quantity,
    cumulative_quantity,
    txn_amount,
    cost_of_purchase,
    sell_ratio,
    NET_COST,
    ROUND(NET_COST / cumulative_quantity, 2) AS AVERAGE_COST -- 保留2位小数，可按需调整
FROM recursive_calc
ORDER BY rn;

关键注意事项 #

1. 排序字段必须真实可靠：代码中ORDER BY txn_id需替换为你的交易表中能唯一确定交易顺序的字段（如交易时间戳txn_timestamp、流水号），否则计算顺序错误会导致结果完全失效。
2. 版本兼容性：递归CTE需要Hive 2.1+、Impala 3.3+版本支持；若使用更低版本，可通过自定义UDAF或变量累加的方式实现，但递归CTE是最简洁可靠的方案。
3. 精度控制：建议将NET_COST定义为DECIMAL类型，避免浮点运算的精度丢失。

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