稀によく使うSELECTクエリ - ログデータから何らかのイベント発生を検出する（LAG関数等がなかったとき） #SQL

SQLは「実行できれば正しく抽出できている」と感じることが多く、Pythonなどでデータ抽出コードを書くより好きです。

ここでは個人的にまれによく使うのに忘れがちなSELECTクエリのメモを書いていきます。
「達人に学ぶSQL徹底指南書」（特に 1-6　相関サブクエリで行と行を比較する～SQLで行間比較）を参考に、イベント発生を検出するクエリに拡張できないか考えていきたいと思います。

イベントの発生を検出する

例えば下記のようなデバイスか何かのログデータがあったとき、何かおきたこと（イベント発生）を検出したい場合があります。

   device_id device_state  metrics          ts
0        B-3         idle     41.0  2000-01-02
1        L#2         neko    497.0  2000-01-03
2        B-3         idle    991.0  2000-01-04
..       ...          ...      ...         ...
17       p_0         neko    505.0  2000-01-19
18       L#2         busy    747.0  2000-01-20
19       L#2         neko    891.0  2000-01-21

[20 rows x 4 columns]

↓ device_id ごとに入り乱れているのでソートしてみます：

   device_id device_state  metrics          ts
0        B-3         idle     41.0  2000-01-02
1        B-3         idle    991.0  2000-01-04
2        B-3         idle    597.0  2000-01-05
3        B-3         neko    103.0  2000-01-12
4        B-3         neko    266.0  2000-01-15
5        B-3         neko    844.0  2000-01-18
6        L#2         neko    497.0  2000-01-03
7        L#2         busy    773.0  2000-01-07
8        L#2         neko    722.0  2000-01-09
9        L#2         idle    323.0  2000-01-13
10       L#2         busy    747.0  2000-01-20
11       L#2         neko    891.0  2000-01-21
12       T^1         neko    142.0  2000-01-06
13       T^1         idle    101.0  2000-01-10
14       T^1         neko    488.0  2000-01-14
15       p_0         neko    818.0  2000-01-08
16       p_0         neko    338.0  2000-01-11
17       p_0         neko    937.0  2000-01-16
18       p_0         busy    736.0  2000-01-17
19       p_0         neko    505.0  2000-01-19

下記のようなクエリを実行することで、各デバイスのある時刻におけるログ出力とひとつ前のログ出力とを突き合わせることができます(debugカラム等は正しくJOINされたかの確認で出しています)。

SELECT
    subject.device_id,
    prev.device_id AS debug,
    prev.device_state AS prev_state,
    subject.device_state AS state,
    prev.metrics AS prev_value,
    subject.metrics AS value,
    prev.ts AS prev_ts,
    subject.ts AS ts
  FROM logs AS prev, logs AS subject
 WHERE prev.device_id = subject.device_id
   AND prev.ts = (
    -- subjectのタイムスタンプより小さいもののうち最大のタイムスタンプ（=直前）
    SELECT MAX(precedings.ts) AS just_before FROM logs AS precedings
     WHERE precedings.ts < subject.ts
       AND precedings.device_id = subject.device_id
   )

result:

   device_id debug prev_state state  prev_value  value     prev_ts          ts
0        B-3   B-3       idle  idle        41.0  991.0  2000-01-02  2000-01-04
1        B-3   B-3       idle  idle       991.0  597.0  2000-01-04  2000-01-05
2        L#2   L#2       neko  busy       497.0  773.0  2000-01-03  2000-01-07
..       ...   ...        ...   ...         ...    ...         ...         ...
13       p_0   p_0       busy  neko       736.0  505.0  2000-01-17  2000-01-19
14       L#2   L#2       idle  busy       323.0  747.0  2000-01-13  2000-01-20
15       L#2   L#2       busy  neko       747.0  891.0  2000-01-20  2000-01-21

[16 rows x 8 columns]

急激な変化を検出する

上述のクエリにいくつかAND条件を加えることで、「状態が同じときのメトリクスの急激な変化」を検出できます。

SELECT
    subject.device_id,
    prev.device_id AS debug,
    prev.device_state AS prev_state,
    subject.device_state AS state,
    prev.metrics AS prev_value,
    subject.metrics AS value,
    prev.ts AS prev_ts,
    subject.ts AS ts
  FROM logs AS prev, logs AS subject
 WHERE prev.device_id = subject.device_id
   AND prev.ts = (
    SELECT MAX(precedings.ts) AS just_before FROM logs AS precedings
     WHERE precedings.ts < subject.ts
       AND precedings.device_id = subject.device_id
   )

   AND prev_state = state  -- 状態は同じ
   AND subject.metrics - prev.metrics > 100  -- 変化量が大きい

result:

  device_id debug prev_state state  prev_value  value     prev_ts          ts
0       B-3   B-3       idle  idle        41.0  991.0  2000-01-02  2000-01-04
1       B-3   B-3       neko  neko       103.0  266.0  2000-01-12  2000-01-15
2       p_0   p_0       neko  neko       338.0  937.0  2000-01-11  2000-01-16
3       B-3   B-3       neko  neko       266.0  844.0  2000-01-15  2000-01-18

状態の変化を検出する

SELECT
    subject.device_id,
    prev.device_id AS debug,
    prev.device_state AS prev_state,
    subject.device_state AS state,
    prev.metrics AS prev_value,
    subject.metrics AS value,
    prev.ts AS prev_ts,
    subject.ts AS ts
  FROM logs AS prev, logs AS subject
 WHERE prev.device_id = subject.device_id
   AND prev.ts = (
    SELECT MAX(precedings.ts) AS just_before FROM logs AS precedings
     WHERE precedings.ts < subject.ts
       AND precedings.device_id = subject.device_id
   )

   AND subject.device_state <> prev.device_state  -- 状態が変わった

result:

   device_id debug prev_state state  prev_value  value     prev_ts          ts
0        L#2   L#2       neko  busy       497.0  773.0  2000-01-03  2000-01-07
1        L#2   L#2       busy  neko       773.0  722.0  2000-01-07  2000-01-09
2        T^1   T^1       neko  idle       142.0  101.0  2000-01-06  2000-01-10
..       ...   ...        ...   ...         ...    ...         ...         ...
7        p_0   p_0       busy  neko       736.0  505.0  2000-01-17  2000-01-19
8        L#2   L#2       idle  busy       323.0  747.0  2000-01-13  2000-01-20
9        L#2   L#2       busy  neko       747.0  891.0  2000-01-20  2000-01-21

[10 rows x 8 columns]

ログの間隔が空いてしまったものを除外する

通信エラー等でログ出力の間隔が空いてしまった場合、発生していないイベントを検出してしまう場合があります。そこでログの間隔が空いてしまったものを除外してみます。

SELECT
    subject.device_id,
    prev.device_id AS debug,
    prev.device_state AS prev_state,
    subject.device_state AS state,
    prev.metrics AS prev_value,
    subject.metrics AS value,
    prev.ts AS prev_ts,
    subject.ts AS ts
  FROM logs AS prev, logs AS subject
 WHERE prev.device_id = subject.device_id
   AND prev.ts = (
    SELECT MAX(precedings.ts) AS just_before FROM logs AS precedings
     WHERE precedings.ts < subject.ts
       AND precedings.device_id = subject.device_id
   )

   AND subject.device_state <> prev.device_state
   AND CAST(strftime('%s', subject.ts) as INT) - CAST(strftime('%s', prev.ts) as INT) < 60 * 60 * 24 * 3
   -- ログ間隔が一定期間内のもののみ抽出

result:

  device_id debug prev_state state  prev_value  value     prev_ts          ts
0       L#2   L#2       busy  neko       773.0  722.0  2000-01-07  2000-01-09
1       p_0   p_0       neko  busy       937.0  736.0  2000-01-16  2000-01-17
2       p_0   p_0       busy  neko       736.0  505.0  2000-01-17  2000-01-19
3       L#2   L#2       busy  neko       747.0  891.0  2000-01-20  2000-01-21

実行環境、クエリ実行とMD出力ツール

クエリはsqlite3で実行できることを確認しています。
下記リポジトリのスクリプト（Python3.6）経由でクエリを実行しました。

https://github.com/raiich/sql-memo
- select_state_change.py