How to Calculate Consumption by Subtracting Previous Row Values in MySQL, Grouped by SN?

Calculating Consumption in MySQL: Subtracting Previous Row Values, Grouped by SN

This guide demonstrates how to calculate consumption by comparing consecutive values within each SN group in a MySQL table. The data includes SN (serial number) and Value columns, representing measurements over time. The goal is to compute the consumption for each SN by subtracting the preceding value from the current value.

Here's a MySQL query leveraging user-defined variables to achieve this:

SELECT
  EL.SN,
  EL.Date,
  EL.Value,
  IF(@lastSN = EL.SN, EL.Value - @lastValue, 0.00) AS Consumption,
  @lastSN := EL.SN,
  @lastValue := EL.Value
FROM
  EnergyLog EL,
  (SELECT @lastSN := NULL, @lastValue := NULL) AS SQLVars
ORDER BY
  EL.SN,
  EL.Date;

Query Explanation:

1. Variable Initialization: The subquery (SELECT @lastSN := NULL, @lastValue := NULL) AS SQLVars initializes the user-defined variables @lastSN (to track the previous SN) and @lastValue (to track the previous Value) to NULL. Using NULL instead of 0 handles the first row of each SN group correctly.

2. Row-by-Row Processing: The main query iterates through the EnergyLog table (aliased as EL), ordered by SN and Date.

3. Consumption Calculation: The IF statement checks if the current SN (EL.SN) matches the previously processed SN (@lastSN).

   • If they match, it calculates the consumption as EL.Value - @lastValue.
   • If they don't match (meaning a new SN group), it sets the consumption to 0.00.

4. Variable Update: @lastSN := EL.SN and @lastValue := EL.Value update the variables for the next row's comparison.

5. Output: The query returns the SN, Date, Value, and calculated Consumption for each row.

This approach efficiently computes consumption within each SN group using a single query, eliminating the need for more complex subqueries or joins. The use of user-defined variables makes the calculation concise and performant.

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