BigQueryでログを10分ごとに集計する
10分ごとにBigQueryで集計したかったのでそのやり方をまとめました。
紹介するクエリの一部の変更で「1時間ごと」や「1分ごと」にも対応できます。
サンプルテーブル
本記事では以下のようなログテーブルを例に集計を行います。
| カラム | 型 |
|---|---|
| is_sucsess | BOOLEAN |
| create_time | TIMESTAMP |
| ... | ... |
クエリ
以下のクエリで10分間隔にsucsess(true or falseごとに)を集計を行います。
timestampを文字列に変換し、先頭から15文字(10分単位のところまで)を切り取り、それを利用しGROUP BYします。
SELECT time, is_success, count(1) AS cnt FROM (
SELECT
CONCAT(SUBSTR(FORMAT_TIMESTAMP('%Y-%m-%d %H-%M-%S', create_time, 'Asia/Tokyo'), 0, 15), "0") AS time,
is_success
FROM
`プロジェクト名:データセット.テーブル名`
)
GROUP BY time, is_success
ORDER BY time, is_success
出力例
| time | is_sucsess | cnt |
|---|---|---|
| 2017-09-20 11:00 | true | 53 |
| 2017-09-20 11:00 | false | 2 |
| 2017-09-20 11:10 | true | 63 |
| 2017-09-20 11:10 | false | 3 |
| 2017-09-20 11:20 | true | 51 |
| 2017-09-20 11:20 | false | 1 |
| ... | ... | ... |
まとめ
上の15の部分を適宜変えることで10分間隔だけでなく「1時間ごと」や「1分ごと」などにも対応可能です。
また、Asian/Tokyoの部分を適宜タイムゾーンに合わせる必要があります。
雑SQLなので、3分単位などの単位区切り以外での時間の集計には対応できません。
もっと良い方法、SQLがあったら教えてください！！！！