BigQueryで移動平均を計算したい

目的

ログデータの解析やら、株価の分析などで使える移動平均線を作りたいと思った。とりあえず、日が近いほど重みをつけたりなどをしない、単純移動平均としたい。
今回のケースの場合、1分ごとに1,000件以上のデータがあることを想定する。

データ例

単純なテーブルを作って、データを投入する

field	type	nullable
timestamp	TIMESTAMP	false
response_time	INTEGER	false

レスポンスのイメージ

[{
    "time": "2016-03-01 10:01:00Z",
    "response_time_1mins_avg": 60,
    "response_time_5mins_avg": 50,
    "response_time_10mins_avg": 40,
    "response_time_30mins_avg": 35,
    "response_time_1hours_avg": 30
},
{
    "time": "2016-03-01 10:00:00Z",
    "response_time_1mins_avg": 60,
    "response_time_5mins_avg": 50,
    "response_time_10mins_avg": 40,
    "response_time_30mins_avg": 35,
    "response_time_1hours_avg": 30
}]

クエリを組み立てる

SELECT
-- 1分ごとに情報を分割するため秒の情報を無視するフィールドを作る
SEC_TO_TIMESTAMP(INTEGER(TIMESTAMP_TO_SEC(timestamp) / 60) * 60) AS time,
-- 1分ごとの平均
AVG(response_time) AS response_time_1mins_avg,
-- 5分ごとの移動平均を1分ごとの平均から出す
AVG(response_time_1mins_avg) OVER (ORDER BY time RANGE BETWEEN (60 * 5 * 1000000) PRECEDING AND CURRENT ROW) AS response_time_5mins_avg,
-- 10分ごと
AVG(response_time_1mins_avg) OVER (ORDER BY time RANGE BETWEEN (60 * 10 * 1000000) PRECEDING AND CURRENT ROW) AS response_time_10mins_avg,
-- 30分ごと
AVG(response_time_1mins_avg) OVER (ORDER BY time RANGE BETWEEN (60 * 30 * 1000000) PRECEDING AND CURRENT ROW) AS response_time_30mins_avg,
-- 1時間ごと
AVG(response_time_1mins_avg) OVER (ORDER BY time RANGE BETWEEN (60 * 60 * 1000000) PRECEDING AND CURRENT ROW) AS response_time_60mins_avg
FROM
table
GROUP BY time
ORDER BY time DESC

OVER とは?

• https://cloud.google.com/bigquery/query-reference#windowfunctions

Window Function というモノ。
通常の集計関数だと、GROUP によって集計されるが、特定の行数内にあるもので集計したいというときは、この機能を使うと良いようだ。
上記のクエリでは、time (1分ごとのもの) の今の時間を基準に、time の昇順で現在の行から特定の範囲(1分、10分など) で平均値を集計している。