MySQL計算相鄰兩行某列差值的方法

這篇文章給大家分享的是有關MySQL計算相鄰兩行某列差值的方法的內容。小編覺得挺實用的，因此分享給大家做個參考。一起跟隨小編過來看看吧。

MySQL計算相鄰兩行某列差值的方法：首先通過【r1.rownum = r2.rownum - 1】來判斷兩條記錄是否是前后行；然后再使用TIMEDIFF函數來計算時間差即可。

MySQL計算相鄰兩行某列差值的方法：

首先博主在服務端有一個表來記錄司機上報上來的GPS點位信息，表結構如下：

-- 司機GPS收集表
CREATE TABLE captainad_driver_gps_position (
    id BIGINT NOT NULL auto_increment COMMENT '主鍵',
    business_id BIGINT DEFAULT NULL COMMENT '業務ID',
    device_mac VARCHAR (64) DEFAULT NULL COMMENT '設備MAC地址',
    device_imei VARCHAR (64) DEFAULT NULL COMMENT '設備IMEI',
    lat_lng VARCHAR (64) DEFAULT NULL COMMENT '緯經度',
    capture_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '捕獲時間',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '創建時間',
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改時間',
    PRIMARY KEY (id),
    KEY `idx_business_id` (`business_id`) USING BTREE
) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '司機GPS收集';

表中記錄的數據大致如下：

現在就對按獲取GPS位置的時間capture_time在按照時間排序之后，進行前后兩條記錄計算差值。為了計算兩者的差值，那么我們肯定是需要獲取到一前一后兩條記錄的，這里我們可以巧用一個變量來記錄當前行的行數，然后隨著循環查詢每次將行數疊加，以達到行記錄的目的，這樣一來，我們就能知道哪兩條記錄是一前一后的了。

打印行號的SQL語句：

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

基于此，我們將目標SQL給寫出來，這里我根據我們的實際業務將語句稍微做了整理，腳本大致如下：

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'

在上面的代碼中，我們通過r1.rownum = r2.rownum - 1來判斷兩條記錄是否是前后行，然后再使用TIMEDIFF函數來計算時間差，到此，我們的目標就實現了。

感謝各位的閱讀！關于MySQL計算相鄰兩行某列差值的方法就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！