Shulou(Shulou.com)06/01 Report--

This article is about how MySQL calculates the difference between two adjacent rows. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

MySQL calculates the difference between two adjacent rows by [r1.rownum = r2.rownum-1] to determine whether the two records are forward and backward, and then uses the TIMEDIFF function to calculate the time difference.

MySQL's method of calculating the difference of a column between two adjacent rows:

First of all, the blogger has a table on the server to record the GPS click information reported by the driver. The table structure is as follows:

Driver GPS collection table CREATE TABLE captainad_driver_gps_position (id BIGINT NOT NULL auto_increment COMMENT '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 'creation time', update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'modification time', PRIMARY KEY (id), KEY `idx_business_ id` (`business_ id`) USING BTREE) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = 'driver GPS collection'

The data recorded in the table is roughly as follows:

Now calculate the difference between the two records after the capture_time is sorted by time when the GPS location is obtained. In order to calculate the difference between the two, then we must need to get the first and the last two records, here we can skillfully use a variable to record the number of rows of the current row, and then stack the number of rows each time with the circular query to achieve the purpose of row recording. in this way, we can know which two records are one after the other.

The SQL statement that prints the line number:

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

Based on this, we write 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.tdiffFROM (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 '% Ymuri% mMel% 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 '% YMel% mMel% 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) tWHERE t.tdiff > '00lv 0015'

In the above code, we use r1.rownum = r2.rownum-1 to determine whether the two records are forward and backward, and then use the TIMEDIFF function to calculate the time difference, and our goal is achieved.

Thank you for reading! So much for MySQL's method of calculating the difference between two adjacent rows. I hope the above content can be helpful to you so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.