如何利用SQL相关子查询计算库存记录中的移动加权平均值？-SQL-PHP中文网

移动加权平均是按“每次入库数量×对应单价”加权累加后除以累计入库总量，要求每条记录依赖之前所有入库记录（含自身），出库不改变均价分母；直接用avg()忽略数量权重，结果错误；子查询须按时间+事务顺序排序，并添加唯一排序键（如自增id）避免同时间多笔记录顺序混乱。

如何利用sql相关子查询计算库存记录中的移动加权平均值？

什么是移动加权平均，为什么不能直接用 `AVG()`

移动加权平均（Moving Weighted Average）不是对历史价格取算术平均，而是按「每次入库数量 × 对应单价」加权累加后，再除以累计入库总量。它要求每条记录的计算结果依赖于它之前所有入库记录（含自身），且出库不改变均价分母——只影响后续结存数量。直接用 AVG() 会忽略数量权重，得到错误结果。

子查询必须按时间+事务顺序排序，且不能漏掉同时间多笔记录

数据库中事务时间（如 transaction_time）可能重复，仅靠 ORDER BY transaction_time 不足以保证稳定排序。若同一时间有两笔入库，子查询可能随机取其一顺序，导致加权和错乱。

必须添加唯一排序键，例如自增 id 或带毫秒的 transaction_time
子查询 WHERE 条件要写成 t2.transaction_time
只筛选「入库」类型（如 type = 'IN'），出库记录不参与均价计算

子查询里要分别聚合数量和金额，不能只算单价平均

常见错误是写成 (SELECT AVG(unit_price * quantity) FROM ...) —— 这实际算的是“加权单价的平均值”，而非“总金额 / 总数量”。正确逻辑必须在子查询内完成两个独立聚合：

Remusic

Remusic - 免费的AI音乐、歌曲生成工具

下载

SELECT 
  t1.id,
  t1.quantity,
  t1.unit_price,
  t1.type,
  (SELECT SUM(t2.quantity * t2.unit_price) / NULLIF(SUM(t2.quantity), 0)
   FROM inventory t2
   WHERE t2.type = 'IN'
     AND (t2.transaction_time <p><code>NULLIF(SUM(t2.quantity), 0)</code> 防止除零；<code>type = 'IN'</code> 确保只计入入库；子查询返回的是当前行截止的累计加权均价，不是单条记录的属性。</p><h3>性能差是常态，大表必须加复合索引</h3><p>相关子查询对每行都执行一次全表扫描（或范围扫描），1万行就执行1万次子查询。没索引时，响应可能从毫秒级变成秒级甚至超时。</p>

必需索引：CREATE INDEX idx_inv_time_id_type ON inventory (transaction_time, id, type);
如果 type 只有少量值（如 'IN'/'OUT'），把 type 放索引最右列可提升过滤效率
PostgreSQL 用户可考虑用窗口函数替代（SUM(quantity * unit_price) OVER (ORDER BY transaction_time, id) / NULLIF(SUM(quantity) OVER (ORDER BY transaction_time, id), 0)），但需确认是否允许出库记录中断累计（窗口函数默认包含所有行，需配合 FILTER 或 CTE 预过滤）

真正麻烦的不是写法，而是当业务要求「出库也触发重新计算」或「支持反向冲销」时，相关子查询会迅速变得不可维护——这时候该考虑用应用层缓存或专用成本计算服务了。