How does MySQL calculate the difference of a certain column in two adjacent rows

MySQL calculates the difference between two adjacent rows of a certain column: First, use [r1.rownum = r2.rownum – 1] to determine whether the two records are consecutive rows; then use the TIMEDIFF function to calculate the time difference That’s it.

【Related learning recommendation: mysql tutorial(video)】

MySQL calculates the difference between two adjacent rows and a column:

First, the blogger has a table on the server side to record the GPS reported by the driver Point information, the table structure is as follows:

-- driver GPS collection table
 CREATE TABLE captainad_driver_gps_position (
     id BIGINT NOT NULL auto_increment COMMENT 'primary key',
     business_id BIGINT DEFAULT NULL COMMENT 'Business ID',
     device_mac VARCHAR (64) DEFAULT NULL COMMENT 'Device MAC address',
     device_imei VARCHAR (64) DEFAULT NULL COMMENT 'Device IMEI',
     lat_lng VARCHAR (64) DEFAULT NULL COMMENT 'latitude and longitude',
     capture_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'capture time',
     create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Create Time',
     update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modify time',
     PRIMARY KEY (id),
     KEY `idx_business_id` (`business_id`) USING BTREE
 ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = & # 39; driver GPS collection & # 39;; 

The data recorded in the table is roughly as follows:

Click to get GPS now After the time capture_time of the location is sorted by time, the difference between the two records before and after is calculated. In order to calculate the difference between the two, then we definitely need to get two records, one before and one after, here we can use a variable to record the number of rows in the current row, and then superimpose the number of rows each time with the loop query , in order to achieve the purpose of line records, so that we can know which two records are one after the other.

SQL statement to print the line number:

SELECT
     (@rownum := @rownum + 1) AS rownum,
     tab. business_id,
     tab.device_mac,
     tab.capture_time
 FROM
     captainad_driver_gps_position tab,
     (SELECT @rownum := 0) r -- declare variable
 WHERE
     1 = 1
 AND DATE_FORMAT(
     tab.capture_time,
     '%Y-%m-%d'
 ) = '2019-06-28'
 ORDER BY
     tab.capture_time

Based on this, we write out the target SQL. Here I sort out the statement according to our actual business. The script is roughly as follows:

SELECT
     t. business_id,
     t.device_mac,
     t.capture_time,
     t.tdiff
 FROM
     (
         SELECT
             r1. business_id,
             r1.device_mac,
             r1.capture_time,
             TIMEDIFF(
                 r2.capture_time,
                 r1.capture_time
             ) AS 'tdiff'
         FROM
             (
                 SELECT
                     (@rownum := @rownum + 1) AS rownum,
                     tab. business_id,
                     tab.device_mac,
                     tab.capture_time
                 FROM
                     captainad_driver_gps_position tab,
                     (SELECT @rownum := 0) r
                 WHERE
                     1 = 1
                 AND DATE_FORMAT(
                     tab.capture_time,
                     '%Y-%m-%d'
                 ) = '2019-06-28'
                 ORDER BY
                     tab.capture_time
             ) r1
         LEFT JOIN (
             SELECT
                 (@INDEX := @INDEX + 1) AS rownum,
                 tab. business_id,
                 tab.device_mac,
                 tab.capture_time
             FROM
                 captainad_driver_gps_position tab,
                 (SELECT @INDEX := 0) r
             WHERE
                 1 = 1
             AND DATE_FORMAT(
                 tab.capture_time,
                 '%Y-%m-%d'
             ) = '2019-06-28'
             ORDER BY
                 tab.capture_time
         ) r2 ON r1.business_id = r2.business_id
         AND r1.device_mac = r2.device_mac
         AND r1.rownum = r2.rownum - 1
     ) t
 WHERE
     t.tdiff > '00:00:15'

In the above code, we judge the two by r1.rownum = r2.rownum - 1 Whether the record is a front or back row, and then use the TIMEDIFF function to calculate the time difference, so far, our goal has been achieved.

Want to learn more programming learning, please pay attention to the php training column!

The above is the detailed content of how MySQL calculates the difference between two adjacent rows of a certain column. For more information, please pay attention to other related articles on 1024programmer.com!