💽
Big Query で時間単位（1時間、10分、1分、10秒、1秒）ごとに集計するための SUBSTR() の使い方
概要
BigQuery でデータを時間ごとに集計する際に、時間単位でのグルーピングが必要になることがあります。本記事では、SUBSTR() 関数を用いて、1時間、10分、1分、10秒、1秒ごとにデータをグルーピングする方法について解説します。SUBSTR() 関数を使って、タイムスタンプから特定の時間単位を抽出し、それを基に集計を行います。
サンプルテーブル
以下のようなサンプルテーブルを用いることを想定しています。このテーブルには、タイムスタンプ (create_time) とそれに関連するデータが含まれています。
| create_time | data |
|---|---|
| 2024-10-07 10:32:15 | 123 |
| 2024-10-07 10:32:45 | 456 |
| 2024-10-07 11:01:05 | 789 |
create_time はタイムスタンプ型のカラムで、これを使って集計を行います。
クエリ
1時間単位での集計
1時間単位で集計を行う場合は、タイムスタンプから「年-月-日 時」の部分だけを抽出します。このために、SUBSTR() を使用し、タイムスタンプをフォーマットして必要な部分を切り出します。
CONCAT(SUBSTR(FORMAT_TIMESTAMP('%Y-%m-%d %H-%M-%S', create_time), 0, 12), "0")
-
%Y-%m-%d %H-%M-%Sのフォーマットでcreate_timeを文字列に変換 -
SUBSTR()で文字列の先頭12文字（年-月-日 時）を抽出 - 末尾に「0」を追加して整形
10分単位での集計
10分単位では、分の十の位を保持し、分の一の位を切り捨てます。
CONCAT(SUBSTR(FORMAT_TIMESTAMP('%Y-%m-%d %H-%M-%S', create_time), 0, 15), "0")
- 先ほどと同様に、文字列として
create_timeをフォーマットし、上位15文字（年-月-日 時:分の十の位）を抽出 - 分の一の位に「0」を補完して、10分単位に整形
1分単位での集計
1分単位で集計を行う場合は、秒の部分を切り捨てて1分単位でグルーピングします。
SUBSTR(FORMAT_TIMESTAMP('%Y-%m-%d %H-%M-%S', create_time), 0, 16)
- 文字列の先頭16文字（年-月-日 時-分）を抽出
10秒単位での集計
10秒単位の集計では、タイムスタンプ全体を使用し、秒以下の部分を切り捨てます。
CONCAT(SUBSTR(FORMAT_TIMESTAMP('%Y-%m-%d %H-%M-%S', create_time), 0, 18), "0")
- 先頭18文字（年-月-日 時:分:秒）を抽出
- 1秒の位に「0」を補完して、10秒単位に整形
1秒単位での集計
1秒単位の集計では、タイムスタンプ全体を使用し、秒より小さい値を切り捨てます。
SUBSTR(FORMAT_TIMESTAMP('%Y-%m-%d %H-%M-%S', create_time), 0, 19)
- 先頭19文字（年-月-日 時:分:秒）を抽出
Discussion