MySQL如何計算相鄰兩行某列差值-mysql教程-PHP中文網

MySQL如何計算相鄰兩行某列差值

coldplay.xixi

發布： 2020-09-03 11:46:02

原創

4082 人瀏覽過

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

MySQL如何計算相鄰兩行某列差值

【相關學習推薦：#mysql教學##(影片)】

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函數來計算時間差，到此，我們的目標就達成了。