SQLでサービスのイベントログを集計する際のTips

（本記事はGO Inc. Advent Calendar 2025 も兼ねています）
こんにちは、AI技術開発部の西川です。
普段は主にタクシーアプリ『GO』の乗務員端末の挙動解析をしています。

今回はSQLでサービスのイベントログを集計する際によく使うTipsとして、ある時点で発生したイベントを有効期間付きの状態に変換する方法と、複数の状態を組み合わせて集計する方法について紹介します。
BigQueryの構文と関数で記載しますが他のRDBでも概ね適用できると思います。

サービスのイベントログの例

サービスによって様々な種類のイベントログが存在すると思います。
例えば『GO』の乗務員端末であれば：

端末にログインした／端末からログアウトした
配車状況が変化した（配車依頼を受けた／乗車地点に到着した／乗車した／降車した）

のようなイベントが考えられます。
また：

車両と乗務員端末を紐づけた／紐づけを替えた
車両の無線番号を変更した

といったマスタの更新もイベントと捉えることもできます。
さらに：

車両があるエリアに入った／あるエリアから出た
車両が停止した／徐行した／走行した

のように、位置や速度のログから集計時に独自のイベントログを作ることもできます。

イベントログの構造

このように、様々なイベントが考えられますが、イベントを構成する情報を整理すると：

イベントの発生対象（誰が）
イベントの発生時刻（いつ）
発生したイベントの内容（何を）

の３種類に集約できます。

ですので、例えば『GO』の乗務員端末のログイン／ログアウトイベントについてテーブルデータの構造で書き出すと：

car_id	jst	event	driver_id
1	12/22 08:30	login	101
2	12/22 09:00	login	102
...
2	12/22 17:30	logout	102
1	12/23 05:30	logout	101

のような形式で整理できます。
（誰が）がcar_id、（いつ）がjst、（何を）がeventとdriver_idです。
（何を）の部分をどう選択するかは集計の目的（どんな状態を作りたいか）によるため以降でまとめます。

Tips 1: イベントを状態に変える

実務では単一のイベントだけを集計するケースは少ないです。
上記のログイン／ログアウトの例であれば、今月の車両別の総ログイン時間が知りたい、といった要求になることが多いです。

この要求に対しては先程のテーブルのデータから、

ログインとログアウトのイベントについて同じ車両でペアを作り、
ペアになったログイン時刻からログアウト時刻までをログイン時間として計算する

と言った加工を行うことになります。
つまり、２点の時刻を組み合わせて期間のあるログイン中という状態を作ることになります。

先程のテーブルをevent_logとして、SQLで処理を書くと以下になります。

with
-- (a) セッションの開始地点（以前がlogoutかつ今回がlogin）に`1`のフラグを立てる
session_start_flagged as (
  select 
    *,
    if (lag(event) over (partition by car_id order by jst) = 'logout' 
          and event = 'login', 1, 0) as is_start,
  from event_log
),

-- (b) 各car_idごとにセッション番号を付与する
session_numbered as (
  select
    *,
    sum(is_start) over (
      partition by car_id order by jst
      rows between unbounded preceding and current row
    ) as session_no,
  from session_start_flagged
),

-- (c) セッションごとにログイン・ログアウト時間とドライバーIDを集計
session_aggregated as (
  select
    car_id,
    session_no,
    min(if(event='login', jst, NULL)) as login_jst,
    max(if(event='logout', jst, NULL)) as logout_jst,
    min(if(event='login', driver_id, NULL)) as login_driver_id,
    max(if(event='logout', driver_id, NULL)) as logout_driver_id,
  from session_numbered
  group by car_id, session_no
  having login_jst is not NULL  -- 最初のログがログアウトを除外
    and login_driver_id = logout_driver_id  -- 簡易な異常値除去
)

-- (d) 各car_idごとにログイン時間を時間単位で集計
select
  car_id,
  sum(datetime_diff(logout_jst, login_jst, second)) / 3600 as login_h,
from session_aggregated
where login_jst >= '2025-12-01 00:00:00'
group by car_id;

この login_session がログイン状態を定義したテーブルになり、最後に car_id 単位で集計しています。
実行すると以下の結果になります。

car_id	login_h
1	21.0
2	8.5

処理で特徴的な所は：

(a)の部分でログアウト後のログイン発生時にフラグ１を立てて
(b)の部分でフラグの累積和を取りセッション番号を作成する

部分です。ここで作られたセッション番号が同じ車両でのログインとログアウトのペアになります。

ちなみにこのコードの細かな異常データでの挙動は：

集計期間前にログインしているレコードは全て除外
同じ車両の同じ乗務員IDでログインが連続したら最初を、ログアウトが連続したら最後を取る
同じ車両の重複する期間で別の乗務員IDのログイン／ログアウトがあると特定のケースを除いて全て除外

となっています。
特定のケースに興味がある方は是非データを作って解析してみて下さい。

Tips 2: 2つの状態の組み合わせを作る

イベントを有効期間付きの状態に変換できてしまえば他の状態との組み合わせを作ることも簡単にできます。

例えば車両の特定エリア内でのログイン時間が知りたい、というケースを考えます。
背景として、そのエリア内でしか有効にならない機能がある、あるいはそのエリアが事業成長において重要なため、稼働車両数がどのぐらいかを知りたい、などがありそうです。

やりたい処理を模式図にすると以下のようになります。ログインしているかとエリア内にいるかは独立した事象なので、ログアウトした後にエリアから抜ける、といったケースも考慮しないいけません。ですので２つの異なる期間が重なる部分を取り出す処理と考えることができます。

エリア内にいたかを表す状態テーブル in_area は以下とします：

car_id	enter_jst	exit_jst
1	12/22 10:00	12/22 15:00
1	12/23 04:00	12/23 08:00

Tips 1 で作った login_session を使うと、次のクエリで状態を組み合わせたテーブルができます。

select --どちらも開始が閉区間、終了が開区間
  l.car_id,
  greatest(l.login_jst, a.enter_jst) as start_jst,  -- (d1)
  least(l.logout_jst, a.exit_jst) as end_jst,  -- (d2)
from login_session as l 
inner join in_area as a on l.car_id = a.car_id
   and l.login_jst < a.exit_jst and a.enter_jst < l.logout_jst -- (c)

(c)の条件でログイン期間とエリア内期間が重なっているレコードを抽出しています。そして(d1)で開始の最遅を、(d2)で終了の最早を取ることで両者が重なる期間に変換しています。

なお、(c)の２つの条件のどちらにも等号がつかないのは、共に閉区間である終了時刻が入っているからです。

上記 SQL の実行結果は以下のようになります：

car_id	start_jst	end_jst
1	12/22 10:00	12/22 15:00
1	12/23 04:00	12/23 05:30

エリア内期間からログアウト後の期間が切り落とされていますね。また、エリア内にいなかった car_id = 2 も除外されています。

２つ以上の状態を組み合わせたい場合は２つのテーブルを組み合わせた後に他のテーブルを組み合わせていけば実現できます。

業務でありそうな例として、端末の設定に不備がないか確認したいので、特定の機種の乗務員端末でログイン後に特定のエリア内にいた時の配車依頼の記録が欲しい場合を考えます。
この場合は、先程のログインとエリアを組み合わせたテーブルがすでにあるのなら、追加で車両と端末の紐づけイベントから車両に特定機種が乗っていた期間を作り、この２つを組み合わせた後で、配車依頼と突き合わせます。

おわりに

今回はSQLでサービスのイベントログを集計する際のTipsとして、ある時点で発生したイベントを有効期間付きの状態に変換し、複数の状態を組み合わせて集計するやり方を紹介しました。
サービス側が状態を持っていればこういった加工は不要ですが、把握したい状態というのは事業状況に合わせてどんどん変わっていきますので、イベントログから構築したいケースはそれなりにあると思っています。
そういった際に参考にして頂ければ幸いです。