MySQL の 2 つの隣接する行間の列の差を計算する方法

列内の 2 つの隣接する行の差を計算する MySQL の方法: 最初に [r1.rownum = r2.rownum - 1] を使用して 2 つのレコードが前の行と次の行であるかどうかを判断し、次に TIMEDIFF を使用します。時差を計算する関数 以上です。

【関連する学習の推奨事項: mysql チュートリアル(ビデオ)】

隣り合う 2 行間の列の差分を計算する 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收集';

テーブルに記録されたデータはおおよそ次のとおりです:

さて、時間、capture_time でソートした後、 GPS位置を取得するまでの時間を前後2回記録し、その差を計算します。 2 つの差を計算するには、必ず前後の 2 つのレコードを取得する必要があります。ここでは、変数を巧みに使用して現在の行の行数を記録し、そのレコードを重ね合わせることができます。行記録の目的を達成するために、ループ クエリを使用して毎回行数を取得し、どの 2 つのレコードが連続しているかを知ることができます。

行番号を出力する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 を使用して 2 つのレコードが前の行と次の行であるかどうかを判断し、TIMEDIFF を使用します。 関数 時差を計算してみましょう。この時点で目的は達成されています。

プログラミング学習について詳しく知りたい方は、php training のコラムに注目してください！

以上がMySQL の 2 つの隣接する行間の列の差を計算する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。