PostgreSQL生成View计算SKU加权平均落地成本的实现方案

问题分析 #

你遇到的recursive reference to query "lc" must not appear within a subquery错误，是因为递归CTE的递归成员中在子查询里引用了递归CTE的别名，这是PostgreSQL不允许的写法。要计算每个SKU的日期维度加权平均成本，需要调整递归CTE的结构，用直接关联的方式替代子查询引用。

可行解决方案 #

以下是创建符合需求的视图的SQL代码，采用递归CTE实现移动加权平均成本计算，同时生成landed_unit_cost和average_cost字段：

CREATE OR REPLACE VIEW lc_weighted_avg AS
WITH ranked_data AS (
    -- 先按SKU分组、日期排序，给每条记录分配序号，方便递归关联上一条数据
    SELECT
        date,
        sku,
        incoming_unit_qty,
        incoming_unit_cost,
        landed_unit_qty,
        ROW_NUMBER() OVER (PARTITION BY sku ORDER BY date) AS rn
    FROM lc_history
),
recursive_avg AS (
    -- 锚点成员：每个SKU的最早日期记录，初始化累积值
    SELECT
        date,
        sku,
        incoming_unit_qty,
        incoming_unit_cost,
        landed_unit_qty,
        -- 计算当前批次的到岸单位成本（总成本/到岸数量）
        CASE WHEN landed_unit_qty > 0 THEN (incoming_unit_qty * incoming_unit_cost) / landed_unit_qty ELSE NULL END AS landed_unit_cost,
        incoming_unit_cost AS average_cost,
        incoming_unit_qty AS cumulative_qty,
        incoming_unit_qty * incoming_unit_cost AS cumulative_cost,
        rn
    FROM ranked_data
    WHERE rn = 1

    UNION ALL

    -- 递归成员：关联上一条记录，计算累积加权平均
    SELECT
        rd.date,
        rd.sku,
        rd.incoming_unit_qty,
        rd.incoming_unit_cost,
        rd.landed_unit_qty,
        CASE WHEN rd.landed_unit_qty > 0 THEN (rd.incoming_unit_qty * rd.incoming_unit_cost) / rd.landed_unit_qty ELSE NULL END AS landed_unit_cost,
        -- 加权平均成本 = (历史累积总成本 + 当前批次总成本) / (历史累积数量 + 当前批次数量)
        (ra.cumulative_cost + rd.incoming_unit_qty * rd.incoming_unit_cost) / (ra.cumulative_qty + rd.incoming_unit_qty) AS average_cost,
        ra.cumulative_qty + rd.incoming_unit_qty AS cumulative_qty,
        ra.cumulative_cost + rd.incoming_unit_qty * rd.incoming_unit_cost AS cumulative_cost,
        rd.rn
    FROM ranked_data rd
    -- 直接通过SKU和序号关联上一条递归记录，避免子查询引用
    JOIN recursive_avg ra ON rd.sku = ra.sku AND rd.rn = ra.rn + 1
)
-- 输出最终需要的字段
SELECT
    date,
    sku,
    landed_unit_cost,
    average_cost,
    incoming_unit_qty,
    incoming_unit_cost,
    landed_unit_qty
FROM recursive_avg
ORDER BY sku, date;

关键说明 #

1. landed_unit_cost计算逻辑：这里按当前入库批次的总成本（incoming_unit_qty * incoming_unit_cost）除以landed_unit_qty得到到岸单位成本，若你需要直接用incoming_unit_cost作为该字段值，直接替换CASE表达式为incoming_unit_cost即可。
2. average_cost计算逻辑：采用移动加权平均公式，每次入库后更新累积数量和总成本，再计算新的平均成本。
3. 避免递归错误的核心：用ROW_NUMBER()给每个SKU的记录按日期排序，递归时通过rd.rn = ra.rn + 1直接关联上一条记录，完全规避了子查询中引用递归CTE的情况。

额外优化（可选） #

如果同一SKU在同一天有多条入库记录，建议先按sku和date分组汇总，避免同一日期内的递归顺序问题，修改ranked_data部分即可：

ranked_data AS (
    SELECT
        date,
        sku,
        SUM(incoming_unit_qty) AS incoming_unit_qty,
        -- 同一天内的平均入库单位成本
        SUM(incoming_unit_qty * incoming_unit_cost) / SUM(incoming_unit_qty) AS incoming_unit_cost,
        SUM(landed_unit_qty) AS landed_unit_qty,
        ROW_NUMBER() OVER (PARTITION BY sku ORDER BY date) AS rn
    FROM lc_history
    GROUP BY sku, date
)

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