SQL
bigquery

BigQueryで10分ごとに集計するSQL

More than 1 year has passed since last update.

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があったら教えてください!!!!

参考