SQL ServerでエンジンON/OFFログから走行距離・稼働時間を算出するSQL（自己結合とOUTER APPLY）

車両のテレマティクスや運行管理では、イグニッション（エンジン）ON/OFFのイベントログから「区間ごとの走行距離」と「稼働時間（運転・稼働していた時間）」を正確に算出したい場面が頻繁にあります。本記事ではSQL Server（T-SQL）で、ONに対応する最初のOFFを正しくペアリングして集計する実用SQLを、落とし穴と改善策まで含めて解説します。

イベントログから「ON〜OFF区間」を作るときに難しいポイント

一見シンプルに見える要件でも、実際にSQLで区間を作ろうとすると次のような難しさが出てきます。

ONの直後にOFFが来るとは限らない（間に別イベントが挟まる、あるいは重複する）
ONが連続する、OFFが連続する、あるいは片方が欠損する（通信切断・端末不調など）
同一車両のイベントが大量に蓄積し、単純な自己結合で性能が出ない
走行距離メーター（オドメータ）がリセット/交換/巻き戻りで単純差分が負になる
時刻計算の関数仕様（DATEDIFFの丸め方）や表示フォーマットが想定とズレる

だからこそ「ONとOFFをどうペアにするか（ペアリング戦略）」が、この種の集計の品質を決めます。

前提テーブルとサンプルデータ

今回の前提は、車両イベントが1テーブルに蓄積される構造です。

CREATE TABLE Vehicle(
    Veh       varchar(10),  -- 車両ID
    VDatetime datetime2,    -- イベント発生日時
    Eventtype varchar(10),  -- 'ign on' / 'ign off'
    Odometer  int           -- 走行距離メーター値
);

データ例は次のとおりです（同一車両V1で、ON→OFFが2回発生）。

INSERT INTO Vehicle(Veh,VDatetime,Eventtype,Odometer) VALUES
('V1','2025-01-01 10 AM','ign on', 100),
('V1','2025-01-01 01 PM','ign off',200),

('V1','2025-01-01 04 PM','ign on', 200),
('V1','2025-01-01 10 PM','ign off',300);

やりたい出力（ON〜OFFの区間ごとに距離と稼働時間）

車両ごとに、エンジンON（開始）〜対応するエンジンOFF（終了）を1セットにして、区間の走行距離（オドメータ差分）と稼働時間（経過時間）を求めます。

vehicle	datetimefrom	datetimeto	distance	duration
V1	10 AM	1 PM	100	3 HRS
V1	4 PM	10 PM	100	6 HRS

ここで最重要なのは「ONに対応するOFFは、同じ車両でONの後に出現する最初のOFFである」というルールを、SQLで確実に満たすことです。

採用された解決策：自己結合＋“間にイベントがない”ことの保証で最初のOFFを確定する

採用された方法は、同一テーブルを3回使う自己結合のパターンです。発想は次のとおりです。

別名	役割	条件	狙い
a	出発（ign on）	a.Eventtype = ‘ign on’	開始点を確定
b	到着候補（ign off）	b.Eventtype = ‘ign off’ かつ b.VDatetime > a.VDatetime	開始後にあるOFFを候補として列挙
c	aとbの間に挟まるイベント	a < c < b	「間に何もない」＝bが最初のOFFだと保証

ポイントは3つ目です。a（ON）とb（OFF候補）の間に別イベントcが存在するなら、bは「最初のOFF」ではありません。逆に、間に何もなければ、そのbはaに対応する最初のOFFとみなせます。これをLEFT JOINとNULL判定で実現します（いわゆるアンチ結合の考え方です）。

実際のSQL（採用されたクエリ）

まずは採用されたSQLを、そのまま掲載します。

SELECT
    a.Veh,
    FORMAT(a.VDatetime, 'h tt') AS datetimefrom,     -- 開始時刻(10 AM など)
    FORMAT(b.VDatetime, 'h tt') AS datetimeto,       -- 終了時刻(1 PM など)
    b.Odometer - a.Odometer AS distance,             -- 走行距離
    CONCAT(DATEDIFF(HOUR, a.VDatetime, b.VDatetime), ' HRS') AS duration  -- 稼働時間
FROM Vehicle a
INNER JOIN Vehicle b
    ON  b.Veh       = a.Veh
    AND b.Eventtype = 'ign off'
    AND b.VDatetime > a.VDatetime
LEFT JOIN Vehicle c
    ON  c.Veh       = a.Veh
    AND c.VDatetime > a.VDatetime
    AND c.VDatetime < b.VDatetime
WHERE
    a.Eventtype = 'ign on'
    AND c.Veh IS NULL
ORDER BY
    a.Veh, datetimefrom;

このSQLが「最初のOFF」だけを取れる理由

処理の流れを、SQLの各ブロックに対応付けて噛み砕きます。

a（ON）を起点にする
a.Eventtype = 'ign on' により、開始点は必ずONイベントになります。
b（OFF候補）を結び付ける
同一車両で、開始より後に発生したOFFを候補として結び付けます。ここではまだ「最初」かどうかは確定していません。
c（間のイベント）で“最初”を保証する
a < c < b を満たすイベントが1件でもあるなら、そのbより前に何かがある＝bは最初ではありません。
LEFT JOINした結果、c.Veh IS NULL になるbだけが「間にイベントが存在しない」＝「aの直後に現れる最初のOFF」と確定します。

このパターンは、ログから「直近の対応レコード」を探すときの定番手法です。とくに、ウィンドウ関数を使わずに“最初の1件”を保証したい場合に分かりやすく、意図が明確です。

距離と稼働時間の計算で知っておきたい注意点

距離（オドメータ差分）は単純でも、実データでは例外が起きる

距離は基本的に b.Odometer - a.Odometer で問題ありません。ただし運用が長いほど、次の例外に遭遇します。

メーター交換や端末の初期化で、オドメータが小さく戻る（差分が負）
端末がGPS距離を独自積算しており、補正で減ることがある
欠損や重複で、ON/OFFの組み合わせがズレて差分が異常値になる

実務では「負の距離は除外」「距離が異常に大きい区間は別途監査」「オドメータの信頼性フラグを持つ」などのルールを入れることが多いです。

稼働時間（DATEDIFF）は“端数切り捨て”ではなく“境界カウント”

サンプルではちょうど時間単位で区切られているため DATEDIFF(HOUR, ...) で見た目通りになります。しかしSQL ServerのDATEDIFFは「指定した単位の境界を何回またいだか」を返す仕様です。

例：10:59 → 11:00 は、1分でも DATEDIFF(HOUR) は1になる
例：10:00 → 10:59 は、59分でも DATEDIFF(HOUR) は0になる

運行時間を厳密に扱うなら、秒や分で取ってから表示側で整形するほうが安全です。たとえば「秒で計算して、時間（小数）にする」なら次のように書けます。

-- 秒で計算（境界の誤差が出にくい）
DATEDIFF(SECOND, a.VDatetime, b.VDatetime) AS duration_sec,

-- 時間（小数）にする例（必要に応じて丸める）
DATEDIFF(SECOND, a.VDatetime, b.VDatetime) / 3600.0 AS duration_hours

表示を「X HRS」にしたい場合も、duration_sec をもとに FLOOR(duration_sec/3600) と MOD（SQL Serverなら % ）で分を出す、という作り方のほうが要件に合わせて調整しやすくなります。

実運用でおすすめ：計算用の値と表示用フォーマットを分離する

採用SQLでは FORMAT を使って「10 AM」形式を作っています。FORMAT は柔軟で読みやすい反面、データ量が増えると重くなりやすい傾向があります（.NETベースのため、単純なCONVERTよりコストが高くなりがちです）。

そこで、実運用では次の方針が堅実です。

DBでは「計算に必要な生のdatetime」「計算結果（秒・メートルなど）」を返す
表示（AM/PM、HH:mm、”HRS”など）はアプリ側、あるいはレポート側で整形する

SQL側を“集計特化”にすると、後から表示要件が変わってもSQLの作り直しが最小で済みます。

計算特化版（datetimeをそのまま返す）

SELECT
    a.Veh,
    a.VDatetime AS datetimefrom,
    b.VDatetime AS datetimeto,
    b.Odometer - a.Odometer AS distance,
    DATEDIFF(SECOND, a.VDatetime, b.VDatetime) AS duration_sec
FROM Vehicle a
INNER JOIN Vehicle b
    ON  b.Veh       = a.Veh
    AND b.Eventtype = 'ign off'
    AND b.VDatetime > a.VDatetime
LEFT JOIN Vehicle c
    ON  c.Veh       = a.Veh
    AND c.VDatetime > a.VDatetime
    AND c.VDatetime < b.VDatetime
WHERE
    a.Eventtype = 'ign on'
    AND c.Veh IS NULL
ORDER BY
    a.Veh, a.VDatetime;

この結果をPower BIやExcel、Webアプリで受け取って表示を整えれば、文化圏（12時間表記/24時間表記）や言語設定に強い構成になります。

それでもSQLで時刻表示をしたい場合の現実的な落とし所

WordPressに貼る例や、簡易レポートでSQLだけで完結したい場合は、表示だけ軽量な関数に寄せるのが現実的です。たとえば24時間表記なら CONVERT で足ります。

-- 24時間表記（HH:mm）
CONVERT(varchar(5), a.VDatetime, 108) AS datetimefrom_hhmm,
CONVERT(varchar(5), b.VDatetime, 108) AS datetimeto_hhmm

12時間表記（AM/PM）をどうしてもSQLで作りたい場合は、サーバーの言語設定や文化圏の影響が出ることを理解したうえで FORMAT を使う、という割り切りが必要です。

性能面の重要ポイント：インデックスがないと自己結合は伸びない

自己結合が3回出てくるクエリは、データ量が増えるほどインデックスの有無が効きます。とくに今回の条件は「同一車両（Veh）で、日時（VDatetime）の範囲検索をする」形なので、基本は次の複合インデックスが効きやすいです。

-- まずは王道：車両＋日時で並べ、参照列をINCLUDE
CREATE INDEX IX_Vehicle_Veh_VDatetime
ON Vehicle (Veh, VDatetime)
INCLUDE (Eventtype, Odometer);

さらにデータ量が大きく、イベント種別がON/OFFにほぼ限定されている運用なら、フィルター付きインデックスも有効になることがあります。

-- ON/OFFだけを強く最適化したい場合の一例（運用要件次第）
CREATE INDEX IX_Vehicle_OnOff
ON Vehicle (Veh, VDatetime)
INCLUDE (Odometer)
WHERE Eventtype IN ('ign on', 'ign off');

ただしフィルター付きインデックスは、Eventtypeが増える運用（例：acc on/off、door open、位置更新など）では適用範囲が変わるため、「このテーブルを何に使うか」で選びます。まずは汎用の (Veh, VDatetime) を入れて、実行計画とI/Oを見ながら調整するのが安全です。

データ品質が荒れたときのための“例外処理”設計

ログ集計の現場では「理想的にON/OFFが交互に並ぶ」ことのほうが少なく、むしろ例外が普通に起きます。ここでは、よくある崩れ方と対策の方向性を整理します。

OFFが欠損している（ONだけがある）

採用SQLはINNER JOINでOFFが必須なので、OFFが欠損している区間は出力されません。これを「欠損は欠損として見える化したい」なら、ON起点を残してOFF側をOUTER APPLYで探し、見つからなければNULLにする、という設計が扱いやすいです（後述のAPPLY案が近いです）。

ONが連続する（誤送信・二重送信）

ONが連続すると「最初のONに対するOFF」が曖昧になります。業務ルールで決めるのが先です。

ルール例A：連続ONは同一開始とみなし、最初のONだけを採用する
ルール例B：後から来たONで区切り直し、前のONは無効として扱う

ルール例Bを取り込みやすいのが「次のONまで」を境界にするLEAD＋APPLYの別解です。

距離や時間が異常値になる

業務でよく使う“安全弁”として、次のようなフィルタを入れておくと、レポートを汚す異常値を減らせます。

観点	例	狙い
距離	distance >= 0	オドメータ巻き戻りの影響を抑える
稼働時間	duration_sec BETWEEN 0 AND 606024	丸1日を超える“閉じてない区間”を弾く
速度上限から逆算	distance <= duration_hours * 120 など	現実的でない区間を監査対象に回す

「弾く」か「別枠で出す」かは運用次第ですが、少なくとも“異常値が混ざる前提”で集計設計するほうが、後工程（請求・監査・KPI）で破綻しにくくなります。

参考：別解（LEAD＋OUTER APPLY）で「次のONまでに出た最初のOFF」を取る

質問者側で提示されていた別解は、次の考え方です。

ONの一覧を作る
各ONに対して「次のON時刻」をLEADで持つ（次の開始点＝区切り）
その区間（ON〜次のON）に存在する最初のOFFを拾う

この方法のメリットは、「連続ON」などが起きたときでも区間の境界が明確になりやすいことです。運用上のノイズ（重複送信）に強い構成になります。

考え方を残しつつ、OFF取得部分を“最初の1件”に寄せた、分かりやすい形の例を載せます（GROUP BYでOdometerを分けず、時刻で最初のOFFを確定させます）。

WITH d_on AS
(
    SELECT Veh, VDatetime, Odometer
    FROM Vehicle
    WHERE Eventtype = 'ign on'
),
next_on AS
(
    SELECT
        Veh,
        VDatetime,
        Odometer,
        LEAD(VDatetime, 1, CONVERT(datetime2,'9999-12-31'))
            OVER (PARTITION BY Veh ORDER BY VDatetime) AS nexton
    FROM d_on
)
SELECT
    n.Veh,
    n.VDatetime AS datetimefrom,
    oa.datetimeto,
    oa.Odometer - n.Odometer AS distance,
    DATEDIFF(SECOND, n.VDatetime, oa.datetimeto) AS duration_sec
FROM next_on n
OUTER APPLY
(
    SELECT TOP (1)
        d_off.VDatetime AS datetimeto,
        d_off.Odometer
    FROM Vehicle d_off
    WHERE d_off.Veh = n.Veh
      AND d_off.Eventtype = 'ign off'
      AND d_off.VDatetime > n.VDatetime
      AND d_off.VDatetime < n.nexton
    ORDER BY d_off.VDatetime
) oa
WHERE oa.datetimeto IS NOT NULL
ORDER BY n.Veh, n.VDatetime;

この書き方は、「ONはあるがOFFがない」ケースを自然に扱えるのも利点です（WHERE条件を変えれば、OFFがないONもNULL付きで一覧化できます）。

どちらを選ぶべきか：自己結合パターンとAPPLYパターンの使い分け

現場では、次の基準で選ぶと迷いにくいです。

観点	自己結合＋間イベントなし（採用案）	LEAD＋APPLY（別解）
分かりやすさ	ロジックが直線的で説明しやすい	ウィンドウ関数に慣れていないと読みづらい
連続ONなどのノイズ耐性	ルールを別途足す必要があることがある	「次のONまで」の境界で吸収しやすい
欠損（OFFなし）対応	基本は出力されない（設計で補う）	NULLで残す/除外するを選びやすい
性能	インデックス次第。cの範囲探索が効く	ON件数が多いとAPPLY回数も増える。設計次第

「イベントが綺麗に交互で、まずは正確な集計を短く書きたい」なら採用案が向きます。一方で「イベントが荒れる」「欠損や重複がある」「境界ルールを強く持ちたい」なら、LEAD＋APPLYのほうが運用に寄り添いやすい傾向があります。

結果が合わないときのチェックリスト（実務で効く確認ポイント）

同一時刻のイベントがあるか：同秒・同ミリ秒で複数行が入ると並び順が不定になりやすい（必要ならID列で順序を補強）
VDatetimeのタイムゾーンが混在していないか：端末はUTC、画面はローカル、DBはローカル…の混在はズレの原因
Eventtypeの表記揺れ：’ign on’ と ‘ign on ‘（末尾スペース）や大文字小文字、別名イベントが混在していないか
OFFがONより前に記録されていないか：遅延送信やバッチ登録で、実際の発生順と登録順が逆転することがある
オドメータの仕様：車載機の累積か、GPS推定か、単位（km/mi）や丸め規則は何か
DATEDIFFの仕様を要件と合わせたか：時間単位の境界カウントで良いのか、分・秒で厳密に出すのか

このチェックを通すだけで、「SQLは合っているのに数字が合わない」問題の多くは切り分けできます。

まとめ：ON/OFFログ集計は“最初のOFFをどう確定するか”が肝

車両ログから走行距離・稼働時間を算出する要件は定番ですが、実装の成否はペアリング戦略で決まります。採用された自己結合＋LEFT JOIN（間イベントなし）の方法は、ONに対応する最初のOFFを明確に保証でき、短いSQLで実務の基本要件を満たせます。

一方で、データが荒れる環境や欠損を織り込む運用では、LEAD＋OUTER APPLYで「次のONまで」を境界にする設計が強力です。どちらの方針でも、最終的には「計算と表示を分離」「インデックス設計」「異常値の扱い」をセットで考えることで、日報・請求・KPIなど下流工程でも信頼できる集計基盤になります。

この記事を書いた人

佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

SQL ServerでエンジンON/OFFログから走行距離・稼働時間を算出するSQL（自己結合とOUTER APPLY）

イベントログから「ON〜OFF区間」を作るときに難しいポイント

前提テーブルとサンプルデータ

やりたい出力（ON〜OFFの区間ごとに距離と稼働時間）

採用された解決策：自己結合＋“間にイベントがない”ことの保証で最初のOFFを確定する

実際のSQL（採用されたクエリ）

このSQLが「最初のOFF」だけを取れる理由

距離と稼働時間の計算で知っておきたい注意点

距離（オドメータ差分）は単純でも、実データでは例外が起きる

稼働時間（DATEDIFF）は“端数切り捨て”ではなく“境界カウント”

実運用でおすすめ：計算用の値と表示用フォーマットを分離する

計算特化版（datetimeをそのまま返す）

それでもSQLで時刻表示をしたい場合の現実的な落とし所

性能面の重要ポイント：インデックスがないと自己結合は伸びない

データ品質が荒れたときのための“例外処理”設計

OFFが欠損している（ONだけがある）

ONが連続する（誤送信・二重送信）

距離や時間が異常値になる

参考：別解（LEAD＋OUTER APPLY）で「次のONまでに出た最初のOFF」を取る

どちらを選ぶべきか：自己結合パターンとAPPLYパターンの使い分け

結果が合わないときのチェックリスト（実務で効く確認ポイント）

まとめ：ON/OFFログ集計は“最初のOFFをどう確定するか”が肝

この記事を書いた人

コメント

コメントするコメントをキャンセル

SQL ServerでエンジンON/OFFログから走行距離・稼働時間を算出するSQL（自己結合とOUTER APPLY）

イベントログから「ON〜OFF区間」を作るときに難しいポイント

前提テーブルとサンプルデータ

やりたい出力（ON〜OFFの区間ごとに距離と稼働時間）

採用された解決策：自己結合＋“間にイベントがない”ことの保証で最初のOFFを確定する

実際のSQL（採用されたクエリ）

このSQLが「最初のOFF」だけを取れる理由

距離と稼働時間の計算で知っておきたい注意点

距離（オドメータ差分）は単純でも、実データでは例外が起きる

稼働時間（DATEDIFF）は“端数切り捨て”ではなく“境界カウント”

実運用でおすすめ：計算用の値と表示用フォーマットを分離する

計算特化版（datetimeをそのまま返す）

それでもSQLで時刻表示をしたい場合の現実的な落とし所

性能面の重要ポイント：インデックスがないと自己結合は伸びない

データ品質が荒れたときのための“例外処理”設計

OFFが欠損している（ONだけがある）

ONが連続する（誤送信・二重送信）

距離や時間が異常値になる

参考：別解（LEAD＋OUTER APPLY）で「次のONまでに出た最初のOFF」を取る

どちらを選ぶべきか：自己結合パターンとAPPLYパターンの使い分け

結果が合わないときのチェックリスト（実務で効く確認ポイント）

まとめ：ON/OFFログ集計は“最初のOFFをどう確定するか”が肝

この記事を書いた人

関連記事

コメント

コメントする コメントをキャンセル

コメントするコメントをキャンセル