How to Calculate Consumption Values by Subtracting from the Previous Row in MySQL?

Calculate consumption value by group in MySQL: based on the difference of the previous row

Calculating the difference between consecutive values ​​based on a grouping factor is a very common task in MySQL. Suppose you need to calculate the consumption value of each sequence number (SN) based on the previous value.

Data structure:

The sample data contains the EnergyLog table with the following schema:

<code>SN | 日期 | 值</code>

Expected results:

The goal is to extend the table to add a "consumption" column that represents the difference of each SN relative to the "value" of the previous row.

Solution:

MySQL variables provide a convenient way to achieve this. By using user-defined variables (@lastSN and @lastValue) we can keep track of the previous SN and value while iterating through the data.

<code class="language-sql">-- 声明变量
SELECT
  EL.SN,
  EL.日期,
  EL.值,
  IF(@lastSN = EL.SN, EL.值 - @lastValue, 0.00) AS 消耗,
  @lastSN := EL.SN,
  @lastValue := EL.值
-- 来自表
FROM EnergyLog EL,
  (SELECT @lastSN := 0, @lastValue := 0) AS SQLVars
-- 按分组因子SN和日期排序
ORDER BY
  EL.SN,
  EL.日期;</code>

Explanation:

The
• FROM clause declares variables (@lastSN and @lastValue) and initializes them to 0.
• The query iterates the EnergyLog table in the desired order (by SN and date).
• It compares the current SN with @lastSN. If there is a match, calculate the consumption (difference) between the current value and the previous @lastValue.
• If SN is different, assume a new group and reset consumption to 0.
The
• @lastSN and @lastValue variables will be updated to the next iteration.

Output:

SN	日期	值	消耗
2380	2012-10-30	21.01	0.00
2380	2012-10-31	22.04	1.03
2380	2012-11-01	22.65	0.61
2380	2012-11-02	23.11	0.46
20100	2012-10-30	35.21	0.00
20100	2012-10-31	37.07	1.86
20100	2012-11-01	38.17	1.10
20100	2012-11-02	38.97	0.80
20103	2012-10-30	57.98	0.00
20103	2012-10-31	60.83	2.85

The above is the detailed content of How to Calculate Consumption Values by Subtracting from the Previous Row in MySQL?. For more information, please follow other related articles on the PHP Chinese website!