Stream Analyticsで異常なデータを探すときによく使うのが自己結合です。ということで見ていきましょう。

以下のようなサンプルデータがあるとします。

[
  {
    "Id": 0,
    "Value": 10,
    "Time": "2015-08-30T09:00:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 11,
    "Time": "2015-08-30T09:01:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 12,
    "Time": "2015-08-30T09:02:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 13,
    "Time": "2015-08-30T09:03:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 14,
    "Time": "2015-08-30T09:04:00.0000000Z"
  },
  {
    "Id": 1,
    "Value": 20,
    "Time": "2015-08-30T09:04:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 15,
    "Time": "2015-08-30T09:05:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 16,
    "Time": "2015-08-30T09:05:30.0000000Z"
  },
  {
    "Id": 0,
    "Value": 17,
    "Time": "2015-08-30T09:06:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 18,
    "Time": "2015-08-30T09:07:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 19,
    "Time": "2015-08-30T09:08:00.0000000Z"
  },
  {
    "Id": 0,
    "Value": 20,
    "Time": "2015-08-30T09:09:00.0000000Z"
  }
]

ちょっと長いですが、Id: 0とId: 1のデータがあって、基本的に60秒ごとに1ずつ増えて行ってます。よくみると、Id:0のValueは09:05:00～09:06:00の間だけ30秒間隔で値がインクリメントされています。つまり1分に2も値が増えてることになります。

今回はこれを見つけるStream Analyticsのクエリを作ってみたいと思います。

ということでクエリの条件を整理すると「同じIdの値が60秒間に1より多く値が増えている個所を探す」になります。

まず最初に値をスルーパスするクエリを書きます。

SELECT
    *
FROM
    [Input] AS Input1

次に、60秒間の間の自分自身のデータと自己結合します。

SELECT
    Input1.Id,
    Input1.Value AS Input1Value,
    Input2.Value AS Input2Value
FROM
    [Input] AS Input1 TIMESTAMP BY Time
    JOIN [Input] AS Input2 TIMESTAMP BY Time
    ON DateDiff(second, Input1, Input2) BETWEEN 1 AND 60
    AND Input1.Id = Input2.Id

DateDiffでInput2の時間からInput1の時間を引いた秒数が1～60のものを対象に自己結合しています。さらにANDで同じIdのデータのみに条件を絞り込んでいます。

これで出力は以下のようになります。

f:id:okazuki:20150830102646p:plain

あとは、ここからInput2のValueとInput1のValueの差分が2以上のものを抜き出すだけです。

SELECT
    Input1.Id,
    Input1.Value AS Input1Value,
    Input2.Value AS Input2Value,
    System.Timestamp AS Time
FROM
    [Input] AS Input1 TIMESTAMP BY Time
    JOIN [Input] AS Input2 TIMESTAMP BY Time
    ON DateDiff(second, Input1, Input2) BETWEEN 1 AND 60
    AND Input1.Id = Input2.Id
    AND Input2.Value - Input1.Value >= 2