如何获得MySQL中连续行之间的区别? [英] How to get the difference between consecutive rows in MySQL?

问题描述

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

有人知道如何在mysql查询中执行此操作吗?我希望diff和avg列的第一个值为0，其余为差异.

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.

推荐答案

对于 MySQL 8 ，然后使用

For MySQL 8 then use Lag window function.

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变量做这个工作.考虑您的表名是"test".

We can use the MySQL variable to do this job. Consider your table name is 'test'.

MySQL Query:

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 ，以便下一行可以使用它.

• (SELECT @prev_number:= 0 AS num) AS b we initialized variable @prev_number to zero in FROM clause and joined with each row of the test table.
• @diff:= IF(@prev_number = 0, 0, test.number - @prev_number) AS diff First we are generating difference and then created another variable diff to reuse it for average calculation. Also we included one condition to make the diff for first row as zero.
• @prev_number:= test.number as dummy we are setting current number to this variable, so it can be used by next row.

注意:我们必须首先在"差异"和"平均值"中使用此变量，然后将其设置为新值，因此下一行可以访问上一行的值.

Note: We have to use this variable first, in both 'difference' as well as 'average' and then set to the new value, so next row can access value from the previous row.

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

You can skip/modify order by clause as per your requirements.

这篇关于如何获得MySQL中连续行之间的区别?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！