mysql - 如何获得MySQL中连续行之间的差异？

Question

I have a table in mysql database this data.

id     date         number   qty
114    07-10-2018    200      5   
120    01-12-2018    300      10
123    03-02-2019    700      12
1126   07-03-2019    1000     15

I want to calculate difference between two consecutive rows and i need output format be like:

id     date         number  diff    qty    avg
114    07-10-2018    200     0       5      0
120    01-12-2018    300     100     10     10
123    03-02-2019    700     400     12     33.33
1126   07-03-2019    1000    300     15     20

Any one know how to do this in mysql query? I want first value of diff and avg column to be 0 and rest is the difference.

Answer 1

对于 MySQL 8 然后使用 Lag窗函数。

SELECT 
    test.id, 
    test.date, 
    test.number, 
    test.qty, 
    IFNULL(test.number - LAG(test.number) OVER w, 0) AS diff,
    ROUND(IFNULL(test.number - LAG(test.number) OVER w, 0)/ test.qty, 2) AS 'Avg'
FROM purchases test
WINDOW w AS (ORDER BY test.`date` ASC);

适用于 MySQL 5.7 或更低版本

我们可以使用 MySQL variable做这个工作。假设您的表名称是 test。

SELECT 
    test.id, 
    test.date, 
    test.number, 
    test.qty, 
    @diff:= IF(@prev_number = 0, 0, test.number - @prev_number) AS diff,
    ROUND(@diff / qty, 2) 'avg',
    @prev_number:= test.number as dummy
FROM 
    test, 
    (SELECT @prev_number:= 0 AS num) AS b
ORDER BY test.`date` ASC;

-------------------------------------------------------------------------------
Output:

| id    | date          | number| qty   | diff  | avg   | dummy | 
-----------------------------------------------------------------
| 114   | 2018-10-07    | 200   | 5     | 0     | 0.00  | 200   |   
| 120   | 2018-12-01    | 300   | 10    | 100   | 10.00 | 300   |   
| 123   | 2019-02-03    | 700   | 12    | 400   | 33.33 | 700   |  
| 1126  | 2019-03-07    | 1000  | 15    | 300   | 20.00 | 1000  |

解释:

• (SELECT @prev_number:= 0 AS num) AS b我们在 FROM 子句中将变量 @prev_number 初始化为零，并与 test 表的每一行连接。
• @diff:= IF(@prev_number = 0, 0, test.number - @prev_number) AS diff 首先我们生成差异然后创建另一个变量diff重用它进行平均计算。我们还包括一个条件，使第一行的差异为零。
• @prev_number:= test.number as dummy 我们将当前的number 设置为此变量，以便下一行可以使用它。

注意:我们必须首先使用这个变量，在difference和average中，然后设置为新值，所以下一行可以访问上一行的值。

您可以根据需要跳过/修改order by 子句。