How to Calculate the Difference Between Values in Consecutive Rows in MySQL?

Retrieving Column Differences in a MySQL SELECT Statement

In MySQL, obtaining the difference between values in two rows of a SELECT statement involves using appropriate SELECT and JOIN techniques. Let's examine this problem and explore the available solutions.

The provided table contains rows organized by ID, kilometers, date, car ID, car driver, etc. To sort the table correctly, a SELECT statement with an ORDER BY clause is used, resulting in a sorted table.

Next, the goal is to create a view that includes an additional column, "number_km_since_last_date," providing the number of kilometers since the last recorded date. One approach employs an INNER JOIN, but it faces challenges due to unsorted rows.

Using LEFT JOIN and Subquery:

A more effective method involves using a LEFT JOIN combined with a subquery. The subquery selects the maximum date that is less than the current row's date from the same table. By joining the current row with the maximum previous row, the difference in kilometers can be calculated using the expression "mt1.Kilometers - IFNULL(mt2.Kilometers, 0)". The resulting SQL query is:

SELECT
    mt1.ID,
    mt1.Kilometers,
    mt1.date,
    mt1.Kilometers - IFNULL(mt2.Kilometers, 0) AS number_km_since_last_date   
FROM
    myTable mt1
    LEFT JOIN myTable mt2
        ON mt2.Date = (
            SELECT MAX(Date)
            FROM myTable mt3
            WHERE mt3.Date < mt1.Date
        )
ORDER BY mt1.date

Emulating LAG() Function using MySQL Hack:

Another technique utilizes emulating the lag() function, which is not natively supported in MySQL. A user-defined variable, @kilo, is initialized to zero. As each row is processed in the query, its kilometers are subtracted from the @kilo's current value, and @kilo is then updated to the current kilometers. This effectively provides the difference in kilometers since the previous row. The resulting query is:

SET @kilo=0;

SELECT
    mt1.ID,
    mt1.Kilometers - @kilo AS number_km_since_last_date,
    @kilo := mt1.Kilometers Kilometers,
    mt1.date
FROM myTable mt1
ORDER BY mt1.date

The above is the detailed content of How to Calculate the Difference Between Values in Consecutive Rows in MySQL?. For more information, please follow other related articles on the PHP Chinese website!