MySQL中基于两张表计算加权平均并更新items表价格

需求说明 #

根据entry_voucher表中的入库记录，按关联的items条目计算加权平均价格，并更新items表的price字段。

核心SQL语句（以MySQL为例） #

UPDATE items i
JOIN (
    -- 按item分组计算加权平均价格：总金额/总数量
    SELECT 
        id_table1,
        SUM(quantity * price) / SUM(quantity) AS weighted_avg_price
    FROM entry_voucher
    GROUP BY id_table1
) ev ON i.id = ev.id_table1
SET i.price = ev.weighted_avg_price;

其他数据库适配写法 #

PostgreSQL

UPDATE items i
SET price = ev.weighted_avg_price
FROM (
    SELECT 
        id_table1,
        SUM(quantity * price) / SUM(quantity) AS weighted_avg_price
    FROM entry_voucher
    GROUP BY id_table1
) ev
WHERE i.id = ev.id_table1;

SQL Server

UPDATE i
SET i.price = ev.weighted_avg_price
FROM items i
INNER JOIN (
    SELECT 
        id_table1,
        SUM(quantity * price) / SUM(quantity) AS weighted_avg_price
    FROM entry_voucher
    GROUP BY id_table1
) ev ON i.id = ev.id_table1;

补充说明 #

• 上述语句通过分组聚合直接计算每个item对应的所有入库记录的加权平均价格，替代循环实现，执行效率更高。
• 若需要模拟逐次入库更新加权价（即每次入库后基于当前库存重新计算），需结合入库记录的顺序（如添加时间字段），使用窗口函数累计计算，示例如下（MySQL 8.0+）：

WITH running_calc AS (
    SELECT 
        id_table1,
        quantity,
        price,
        -- 累计总金额
        SUM(quantity * price) OVER (PARTITION BY id_table1 ORDER BY entry_time) AS total_amount,
        -- 累计总数量
        SUM(quantity) OVER (PARTITION BY id_table1 ORDER BY entry_time) AS total_quantity
    FROM entry_voucher
)
UPDATE items i
JOIN (
    SELECT 
        id_table1,
        -- 取最后一次累计的加权平均作为最终价格
        LAST_VALUE(total_amount / total_quantity) OVER (PARTITION BY id_table1) AS final_weighted_avg
    FROM running_calc
) ev ON i.id = ev.id_table1
SET i.price = ev.final_weighted_avg;

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