アクセス解析担当者のBigQuery日記

普段はアクセス解析やウェブ広告の運用をしています。主にBigQueryについて、記録していきます。

BigQueryで時系列データから異常部位検出 (EMA 指数移動平均)

異常検知 BigQuery Tips

概要

異常検知系2個目。例により実装しやすそうなので採用。 直近に重みを付けた移動平均です。

こちらの本を参考にしました。 www.kspub.co.jp

その他参考にした記事 www.moneypartners.co.jp

定義

EMA^{(n)}_i = \sum_{k=0}^{n-1} \alpha(1-\alpha)^k f_{i-k}\\ \alpha = \frac{2}{n+1}\\ f_i は時刻iにおけるシグナル量\\ nは重みの係数

なお、

今後も異常検知系を増やし、最終的には統合してなんらか閾値を設定し、異常判定されたデータ・bot生成まで行いたい。

アウトプットのイメージ

データポータルで可視化

f:id:sem-aa-bq:20190308015238p:plain
EMA異常度をデータポータルで可視化

今回のキーワード

  1. EMA
  2. ウィンドウ関数
  3. サンプルデータ生成
  4. UNNEST() WITH OFFSET

サンプルデータの生成は、以下の記事と同じ。

sem-aa-bq.hatenablog.com

クエリ

WITH
  sample1 AS (
    #およそ周期が7のデータacを生成する
  SELECT
    x AS id,
    3 + 10 * SIN( x * 3.14 /3.5) + RAND() AS x
  FROM
    UNNEST(GENERATE_ARRAY(0,100)) AS x ),
    sample2 AS (
    #異常値を載せたデータを作成する
  SELECT
    *,
    IF(id = 50,x + 10,x) AS abnomal_x
  FROM
    sample1
  )
  #####################
  #EMA(指数移動平均) n=7
  #####################
  SELECT
    *,
    (#サブクエリでEMAを計算する
      SELECT
        SUM((2/(7+1))*POW((1-2/(7+1)),k) * f_i_k) #数式部分
       FROM
        UNNEST(part_arr) AS f_i_k WITH OFFSET AS k #ARRAYの位置も一緒に出力。数式で言うkになる。
     ) AS ema
  FROM 
    (
      SELECT
        *,
        ARRAY_REVERSE(ARRAY_AGG(abnomal_x) OVER(ORDER BY id asc ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)) AS part_arr #自身とn-1行前までをARRAYにする。また自身が初めに来るようにソートを逆順にする。
      FROM
        sample2
    ) AS t1

解説

sample生成については省略 。id順で見て真ん中あたりに異常値を乗せています。

t1

EMA計算用に自身と直近データを、ARRAY_AGGをウィンドウ関数をとして使って配列に格納しています。

ARRAY_AGG(abnomal_x ORDER BY id desc)
とせずに
ARRAY_REVERSE(ARRAY_AGG(abnomal_x)
としているのは、ARRAY_AGG(x ORDER BY y) をウィンドウ関数では使えない仕様らしいからです。 ウィンドウ関数についてはこちら

標準 SQL での分析関数のコンセプト  |  BigQuery  |  Google Cloud

サブクエリでEMAを計算

数式部分については特になし。

UNNEST(part_arr) AS f_i_k WITH OFFSET AS k
について、ここでいうf_i_kとkは、数式でいうf_{i-k}とkになり、kは自身からいくつ前のデータであるかを表しています。
UNNEST(array) WITH OFFSET AS alias

と書くことで、配列要素の0始まりの位置を、arrayを展開すると同時に列として出力してくれます。

UNNEST は入力 ARRAY 内の要素の順序を破壊します。配列要素のインデックスを含む 2 番目の列を返すには、オプションの WITH OFFSET 句を使用します

Standard SQL Query Syntax  |  BigQuery  |  Google Cloud

可視化と所感

クエリ結果画面で、データポータルで調べるを押し、グラフを作成。

f:id:sem-aa-bq:20190308015238p:plain
EMA異常度をデータポータルで可視化

一応反応はしているが、今回生成したサンプルのようにはっきりとした周期的なデータに対しては、k近傍法ほど大きくは動かない。
k近傍法もそうだが、今後は別のパターンのsampleデータを生成して試していこうと思う。
また、現状1つの系列にしか対応できない書き方をしているのでその点改めつつ、またスケールが異なる複数の系列を評価できるようにしていきたい。

こんなデータを処理したい。

time category data
1 A 12
1 B 104
2 A 13
2 B 150
3 A 25
2 B 83

以上です。