php - 从 my_table 中选择 avg(unit_cost)，其中运行总和(数量)< '$someNumber'

Question

使用PHP与SQL数据库交互

sql表相关列:编号 |产品 ID |数量 |单位成本

我想要实现的目标:当我输入新行时，老板有时会想知道例如最后 10,000 个单位的平均单位成本。

我如何写一些东西来实现这个目标？像这样:

SELECT AVG(unit_cost) FROM records_receiving WHERE CUMULATIVE_SUM(QUANTITY)< '10000';

我尝试过的所有方法最终要么是数量小于 10,000 的一行的结果，要么最终对所有行进行平均，或者什么也得不到。

我尝试过:

SELECT id, total_cost, manual_adjustment, quantity 
FROM records_receiving 
WHERE product = '".$gotTheMaterials['ID']."' 
ORDER BY ID DESC limit 50;

和

SELECT m.id, AVG(m.unit_cost) AS costAvg
FROM   records_receiving m
JOIN   records_receiving m1 ON m1.id <= m.id
WHERE  m.quantity < '".$gotTheMaterials['quantity']."'
AND m.product = '".$gotTheMaterials['ID']."'
GROUP  BY m.id
HAVING SUM(m1.quantity) < '".$gotTheMaterials['quantity']."'
ORDER  BY ID DESC
LIMIT  1"

Answer 1

您需要分两步完成此操作。

首先编写一个查询来计算单位的运行总计。然后将其用作查询中的子查询，将其过滤到最多 10,000 的运行总计，并计算平均成本。

SELECT AVG(unit_cost)
FROM (<subquery>) AS x
WHERE total_quantity <= 10000

现在我们必须弄清楚如何编写子查询来计算销售的总销量，以便您可以将其过滤到最后 10,000 个单位。

在 MySQL 8.0 中，您可以使用窗口函数来完成此操作，但我还没有学会如何使用它们。在 8.0 之前，您需要使用 session 变量来计算运行总计。

SELECT unit_cost, @total := @total + quantity AS total_quantity
FROM (
    SELECT unit_cost, quantity
    FROM records_receiving
    ORDER BY id DESC
) AS t
CROSS JOIN (SELECT @total := 0) AS vars

把这些放在一起，你会得到:

SELECT AVG(unit_cost) as avg_cost
FROM (
    SELECT unit_cost, @total := @total + quantity AS total_quantity
    FROM (
        SELECT unit_cost, quantity
        FROM records_receiving
        ORDER BY id DESC
    ) AS t
    CROSS JOIN (SELECT @total := 0) AS vars
) AS x
WHERE total_quantity <= 10000

DEMO