mysql相邻两行数据相减

CREATE TABLE data_table (
    id INT AUTO_INCREMENT PRIMARY KEY,
    value INT
);

INSERT INTO data_table (value) VALUES (10), (15), (20), (25), (30);

id | value
---+------
1  | 10
2  | 15
3  | 20
4  | 25
5  | 30

SELECT 
    id,
    value,
    value - LAG(value) OVER (ORDER BY id) AS diff
FROM 
    data_table;

id | value | diff
---+-------+-----
1  | 10    | NULL  -- 第一行没有前一行，差值为 NULL
2  | 15    | 5     (15 - 10)
3  | 20    | 5     (20 - 15)
4  | 25    | 5     (25 - 20)
5  | 30    | 5     (30 - 25)

SELECT 
    t1.id,
    t1.value,
    t1.value - t2.value AS diff
FROM 
    data_table t1
LEFT JOIN 
    data_table t2 ON t1.id = t2.id + 1
ORDER BY 
    t1.id;

id | value | diff
---+-------+-----
1  | 10    | NULL  -- t2.id = 0 不存在，所以 t2.value 为 NULL
2  | 15    | 5     (15 - 10)
3  | 20    | 5     (20 - 15)
4  | 25    | 5     (25 - 20)
5  | 30    | 5     (30 - 25)