So berechnen Sie die Differenz zwischen zwei benachbarten Zeilen in einer bestimmten Spalte in MySQL

So berechnet MySQL die Differenz in einer Spalte zwischen zwei benachbarten Zeilen: Verwenden Sie zunächst [r1.rownum = r2.rownum - 1], um zu bestimmen, ob es sich bei den beiden Datensätzen um die vorherige und die nächste Zeile handelt. Verwenden Sie dann die Funktion TIMEDIFF, um die Zeit zu berechnen Unterschied.

【Verwandte Lernempfehlungen: MySQL-Tutorial(Video)】

MySQL-Methode zur Berechnung der Differenz zwischen zwei benachbarten Zeilen in einer Spalte:

Zunächst ist der Blogger Auf der Serverseite gibt es eine Tabelle zum Aufzeichnen der vom Fahrer gemeldeten GPS-Punktinformationen. Die Tabellenstruktur ist wie folgt:

-- 司机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收集';

Die in der Tabelle aufgezeichneten Daten sind ungefähr wie folgt:

Drücken Sie nun „capture_time“, um die GPS-Position zu erhalten . Berechnen Sie nach der Sortierung nach Zeit die Differenz zwischen den Datensätzen. Um die Differenz zwischen den beiden zu berechnen, müssen wir unbedingt zwei Datensätze erhalten, einen vor dem anderen und einen nach dem anderen. Hier können wir geschickt eine Variable verwenden, um die Anzahl der Zeilen in der aktuellen Zeile aufzuzeichnen und diese dann zu überlagern Verwenden Sie die Schleifenabfrage jedes Mal, um die Anzahl der Zeilen zu ermitteln, um den Zweck der Zeilenaufzeichnung zu erreichen, sodass wir wissen können, welche beiden Datensätze nacheinander vorliegen.

SQL-Anweisung zum Drucken der Zeilennummer:

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

Auf dieser Grundlage schreiben wir die Ziel-SQL. Hier habe ich die Anweisung basierend auf unserem tatsächlichen Geschäft leicht organisiert. Das Skript sieht ungefähr wie folgt aus:

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'

Im obigen Code. Wir verwenden die Funktion r1.rownum = r2.rownum - 1来判断两条记录是否是前后行，然后再使用TIMEDIFF, um den Zeitunterschied zu berechnen. An diesem Punkt ist unser Ziel erreicht.

Wenn Sie mehr über das Erlernen des Programmierens erfahren möchten, achten Sie bitte auf die Spalte „PHP-Schulung“!

Das obige ist der detaillierte Inhalt vonSo berechnen Sie die Differenz zwischen zwei benachbarten Zeilen in einer bestimmten Spalte in MySQL. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!