mysql - 如何从 MySQL 中的表中选择特定数据并将其从后续行中可用的下一个日期中减去？

Question

这个问题是从How to create a query in MySQL to subtract consecutive rows based on the date and a distinctive field?演变而来的;我有下表:

+--------+------------+----------+
| animal | date       | quantity |
+--------+------------+----------+
| dog    | 2015-01-01 |      400 |
| cat    | 2015-01-01 |      300 |
| dog    | 2015-01-02 |      402 |
| rabbit | 2015-01-01 |      500 |
| cat    | 2015-01-02 |      304 |
| rabbit | 2015-01-02 |      508 |
| rabbit | 2015-01-03 |      524 |
| rabbit | 2015-01-04 |      556 |
| rabbit | 2015-01-05 |      620 |
| rabbit | 2015-01-06 |      748 |
+--------+------------+----------+

感谢做出贡献的用户(特别是 https://stackoverflow.com/users/1816093/drew )，现在我可以运行以下查询:

select
animal,
date,
quantity,
quantity_diff
from
(   SELECT
a.animal,
a.Date AS actual_date,
past_date.Date AS date,
a.quantity AS quantity,
(a.quantity - past_date.quantity) AS quantity_diff,
1 as drewOrder
FROM inventory a
JOIN
(SELECT b.animal, b.date AS date1,
(SELECT MAX(c.date)
FROM inventory c
WHERE c.date < b.date AND c.animal = b.animal
GROUP BY c.animal)
AS date2 
FROM inventory b)
AS original_date ON original_date.animal = a.animal
AND original_date.date1 = a.date
JOIN
inventory past_date
ON past_date.animal = a.animal
AND past_date.date = original_date.date2
union
select distinct null,animal,null,null,null,2 as drewOrder from inventory
) x
where x.animal='rabbit' group by quantity_diff
order by x.animal,x.drewOrder,x.actual_date;

这是我得到的:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| rabbit | 2015-01-01 |      508 |             8 |
| rabbit | 2015-01-02 |      524 |            16 |
| rabbit | 2015-01-03 |      556 |            32 |
| rabbit | 2015-01-04 |      620 |            64 |
| rabbit | 2015-01-05 |      748 |           128 |
+--------+------------+----------+---------------+

http://sqlfiddle.com/#!9/c77d8/121

...我非常接近，但我就是无法如愿以偿；是这样的:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| rabbit | 2015-01-01 |      500 |             8 |
| rabbit | 2015-01-02 |      508 |            16 |
| rabbit | 2015-01-03 |      524 |            32 |
| rabbit | 2015-01-04 |      656 |            64 |
| rabbit | 2015-01-05 |      620 |           128 |
| rabbit | 2015-01-06 |      748 |          null |
+--------+------------+----------+---------------+

我还应该能够更改“动物”并获得其相应的输出:

猫:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| cat    | 2015-01-01 |      300 |             4 |
| cat    | 2015-01-02 |      304 |          null |
+--------+------------+----------+---------------+

狗:

+--------+------------+----------+---------------+
| animal | date       | quantity | quantity_diff |
+--------+------------+----------+---------------+
| dog    | 2015-01-01 |      400 |             2 |
| dog    | 2015-01-02 |      402 |          null |
+--------+------------+----------+---------------+

Answer 1

好吧，有一种更简单的方法可以做到这一点，而无需使用连接和联合进行大查询。你需要一个 Window Function对于您的具体问题，这将是一个 LEAD 函数。

LEAD(`quantity`) over (Partition By `date` order by `date`)

但遗憾的是 MySql 不支持这些函数，因此您必须使用查询变量来模拟它。

您想要的解决方案是:

select animal,
       `date`,
       quantity,
       lead-quantity quantity_diff
from ( select i.animal, 
              i.`date`,
              @qt as lead,
              @qt := i.quantity as quantity
        from inventory i
       where i.animal = 'rabbit' 
       order by `date` desc
     ) tab
order by `date`;

你可以在这里检查它的工作情况:http://sqlfiddle.com/#!9/c77d8/182

对于猫:http://sqlfiddle.com/#!9/c77d8/183

对于狗:http://sqlfiddle.com/#!9/c77d8/184

在我找到这篇文章之前，我很沮丧地知道 MySql 不支持窗口函数:Analytic functions: FIRST_VALUE, LAST_VALUE, LEAD, LAG这让我学会了如何模仿它。我建议你像我一样喜欢它!

希望对您有所帮助!