レシピメディアにおいて、たとえば検索数推移のような時系列的なデータを扱っていると、急激に検索数が伸びているワードを捕捉したいシーンがあります。 要因はものによって違いますが、これをSQLだけで完結してなるべく楽したい。が今回の目的です。
要するに異常検知をすることが目的なのですが、「上昇率がX%以上を検知する」ような単純なモデルではないが、ある程度統計的な根拠をもとに検知が可能で、かつPythonのライブラリをつかうほど複雑ではなくSQL上でわかりやすく書けることを主眼に置きます。
方針としては以下です。
- 特定ワードにおける検索数の時系列データが正規分布に従うと仮定する
- 特定ウィンドウにおける検索数の移動平均と、標準偏差を抽出し、有意水準を5%などとし、逸脱したものを異常値とみなす
イメージはこんな感じです。各週ごとに適正な範囲を求めて、そこを逸脱した値を異常値としてみなします
本来は標準化すべきですが、簡単のため標準化処理は行わずに進めます。 と、いうことで早速やっていきます。異常検知までの全体的なステップは以下です。
- 特定ワードにおける週ごとの検索数を抽出する
- 4週間ごとの検索数の移動平均を抽出
- 4週間ごとの検索数の標準偏差(σ)を抽出
- 移動平均±2σを抽出
- 現在週の値を判定する
まず今回想定するデータソースはこんな感じです。仮に search_data というテーブルに入っているとして進めて行きます。
- date: 日付
- query: 検索ワード
- num_of_searches: 検索数
まず特定ワード xxx の検索数を抽出します。今回は最終的に週ごとの移動平均を取りたいので、週ごとの集計にします。
なお実行環境はPrestoとします。適宜ご自身の扱っているSQLに読み替えてご確認ください。
▼検索数の抽出
with
weekly_data as (
select
week(date) as week
, sum(num_of_searches) as total_searches
from
search_data
where
q = 'xxx'
group by
week
)
次に移動平均と標準偏差を抽出します。ウィンドウ関数を使います。
▼移動平均と標準偏差の抽出
stats as ( select week total_searches , avg(total_searches) over (order by week rows between 4 preceding and 1 preceding row) as moving_average , stddev(total_searches) over (order by week rows between 4 preceding and 1 preceding row) as moving_stddev from weekly_data )
次に移動平均±2σを抽出します。先程のクエリで同時に計算してもいいですが、若干わかりづらくなるので、こちらで移動平均±2σを抽出します。これにより、異常値の範囲を明確にすることができます。
▼移動平均±2σの抽出
, bounds as ( select week , total_seaches , moving_average , moving_stddev , moving_average + 2 * moving_stddev as upper_bound , moving_average - 2 * moving_stddev as lower_bound from stats )
これで準備ができました。過去4週間の移動平均±2σと今週の値を見比べてみましょう。現在週の検索数がこの範囲を超えているかどうかを確認することで、異常値を検知することができます。
▼異常検知する
select total_searches > upper_bound or total_searches < lower_bound as is_anomaly from bounds
これで異常値を検出できます！ 今回のクエリを実行することで、現在の検索数が過去のトレンドから逸脱しているかどうかを簡単に確認できます。今回はSQLでやりましたが、どちらかと言うとスプレッドシートのほうがより簡単にできそうな気もしてきました。スプレッドシートでは、関数を使って同様の計算を行うことができるため、視覚的にデータを確認しやすいという利点があります。
是非ご自身で試してみてください。データの異常検知は、ビジネスの意思決定において非常に重要な要素ですので、さまざまな手法を試してみることをお勧めします。
