如何用PHP+SQL计算对应在手库存数量的平均采购成本

需求核心 #

核算在手库存的实际价值：针对指定商品，按日期/ID降序取最近的采购记录，累计采购数量直到达到或超过在手库存数，最终计算这些对应采购记录的平均成本。比如在手30个叉子，就取能凑够30个的最近采购批次，算出它们的平均采购成本。

最初的低效实现（两步循环法） #

一开始用循环递增$limitNumber的方式，反复查询累计数量，直到满足目标值：

// 第一步：循环确定LIMIT值
$limitNumber = 1;
$targetQty = 30; // 在手库存数量
$productID = 'FORK001'; // 示例商品ID

while(true) {
    $sumQuery = "SELECT sum(Quantity) as theSum
                  FROM Receiving
                  WHERE Product='$productID'
                  ORDER BY ID DESC
                  LIMIT $limitNumber";
    // 执行查询，获取$theSum（省略数据库连接/执行代码）
    if ($theSum >= $targetQty) {
        break;
    }
    $limitNumber++;
}

// 第二步：计算平均成本
$avgQuery = "SELECT ID, AVG((Total+Adjustment) / Quantity) as costAvg
              FROM Receiving
              WHERE Product='$productID'
              ORDER BY ID DESC
              LIMIT $limitNumber";

这种方法的问题很明显：数据量大时会发起N次数据库查询，效率极低。

优化后的高效实现（单查询+PHP计算） #

利用MySQL用户变量计算累计数量，一次性拉取符合条件的记录，再在PHP中完成平均成本计算：

1. SQL查询语句 #

SELECT t.ID,
       t.Quantity,
       t.Cost AS rowTotal,
       @running_total := @running_total + t.Quantity AS cumulative_sum
FROM Receiving t
JOIN (SELECT @running_total := 0) r
WHERE t.Product = '$productID'
HAVING @running_total <= $targetQty
ORDER BY t.Date DESC

2. PHP后续计算逻辑 #

$totalCost = 0;
$totalQty = 0;
// 假设$results是查询返回的结果集
foreach ($results as $row) {
    // 若rowTotal是单位成本则乘数量，若为该行总成本则直接累加
    $totalCost += $row['rowTotal'] * $row['Quantity'];
    $totalQty += $row['Quantity'];
}
// 计算平均成本
$avgCost = $totalCost / $totalQty;

这种方案只需要一次数据库查询，在大数据场景下性能提升非常明显。

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