mysql - 如何从前一行的相同列值中选择列值最近减少的行？

Question

我有一组库存数据，其中数量以给定的速度增加。例如，库存每天增加十个单位。但是，不时会出现任何数量的库存减少。我需要一个查询，它可以找到我最近的库存减少并将该扣除的总和返回给我。

我的表包含许多项目 ID 的日期和金额。从理论上讲，我要做的是选择给定项目 ID 的所有金额和日期，然后找到最近两天库存减少之间的差异。由于跟踪了多个项目，因此不能保证 id 列对于一组项目是连续的。

研究以找到解决此问题的方法完全是压倒性的。似乎窗口函数可能是一个很好的尝试途径，但我从未使用过它们，甚至不知道从哪里开始。

虽然我可以轻松地返回金额并在 PHP 中进行计算，但我觉得在这里做的正确事情是利用 SQL，但我对更复杂查询的经验有限。

ID | ItemID | Date       | Amount 
1      2      2019-05-05   25
7      2      2019-05-06   26
34     2      2019-05-07   14
35     2      2019-05-08   15
67     2      2019-05-09   16
89     2      2019-05-10   5
105    2      2019-05-11   6

鉴于上面的数据，很高兴看到如下结果：

item id | date       | reduction
2         2019-05-10   11

这是因为最近一次库存减少介于 id 67 和 89 之间，减少的数量是 2019 年 5 月 10 日的 11。

标签： mysqlsql

Answer 1

在 MySQL 8+ 中，您可以使用lag()：

select t.*, (prev_amount - amount) as reduction
from (select t.*,
             lag(amount) over (partition by itemid order by date) as prev_amount
      from t
     ) t
where prev_amount > amount
order by date desc
limit 1;