计算月平均消耗量

Question

我正在Laravel 4中进行一个自我改进的库存管理项目，但无法找到执行平均月消耗量(AMC(计算的最佳方法。

我有两个表，即商品表(id,item_name,price(和库存卡(id, item_id, qty_in, qty_out,transaction_date(，我应该从中导出AMC计算。

FORMULA = (sum of current month's qty_out + sum of previous two month's qty_out) / 3 

有人能告诉我如何用简单的PHP和mysql来完成这项工作吗？

Answer 1

您应该能够使用条件聚合来实现这一点，例如

drop table if exists t;
create table t(item int,qty_out int , dt date);
insert into t values
(1,1,'2018-09-01'),(1,1,'2018-10-01'),(1,1,'2018-11-01');
select item,
sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) thismm,
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end) last2mm,
(sum(case when year(dt)*12 + month(dt) = year(now()) * 12 + month(now()) then qty_out else 0 end) +
sum(case when year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -1) or
year(dt)*12 + month(dt) = (year(now()) * 12 + month(now()) -2) then qty_out else 0 end)
) / 3  amc
from t
where year(dt)*12 + month(dt) >= (year(now()) * 12 + month(now()) -2)
group by item ;
+------+-----------+-------+--------+
| item | thismonth | last2 | amc    |
+------+-----------+-------+--------+
|    1 |         1 |     2 | 1.0000 |
+------+-----------+-------+--------+
1 row in set (0.01 sec)

请注意月数的转换，以简化日期在年末的位置。当然，如果你正在寻找一个3个月的滚动平均数，那将是一个不同的问题。