ぽくつなです

BigQuery GENERATE_ARRAY でバケットを作って集計

BigQuery データ

昨日やった作業。

例えばこういうテーブルがあったとして

count timestmap
3 2020-07-07 10:30:00
5 2020-07-07 12:34:50
7 2020-07-07 21:07:10
11 2020-07-07 21:10:00

1時間ごとの count の合計値を出したい場合以下みたいなクエリを書く

WITH data AS (
  SELECT * FROM UNNEST(
    ARRAY<STRUCT<count INT64, timestamp TIMESTAMP>>[
      STRUCT( 3, TIMESTAMP "2020-07-07 10:30:00"),
      STRUCT( 5, TIMESTAMP "2020-07-07 12:34:50"),
      STRUCT( 7, TIMESTAMP "2020-07-07 21:07:10"),
      STRUCT(11, TIMESTAMP "2020-07-07 21:10:00")
    ]
  )
)

SELECT
  TIMESTAMP_TRUNC(timestamp, HOUR) as bucket1h,
  SUM(count) as count
FROM data
GROUP BY bucket1h
ORDER BY bucket1h ASC
bucket1h count
2020-07-07 10:00:00 UTC 3
2020-07-07 12:00:00 UTC 5
2020-07-07 21:00:00 UTC 18

という結果が得られる

これで大抵よいのだけど、他のデータとくっつけたり CSV エクスポートしてなんやかんや操作する際に bucket1h の値が飛び飛びのスッカスカだと扱いにくいことがある。期間を埋めて 0 を入れたい。

そんな時は GENERATE_ARRAY シリーズで解決できる。
標準 SQL の ARRAY 関数  |  BigQuery  |  Google Cloud

  • GENERATE_ARRAY(start_expression, end_expression[, step_expression])
  • GENERATE_DATE_ARRAY(start_date, end_date[, INTERVAL INT64_expr date_part])
  • GENERATE_TIMESTAMP_ARRAY(start_timestamp, end_timestamp, INTERVAL step_expression date_part)

GENERATE_TIMESTAMP_ARRAY で欲しい範囲のバケットを作って UNNEST したものを、LEFT(RIGHT) JOIN

WITH data AS (
  SELECT * FROM UNNEST(
    ARRAY<STRUCT<count INT64, timestamp TIMESTAMP>>[
      STRUCT( 3, TIMESTAMP "2020-07-07 10:30:00"),
      STRUCT( 5, TIMESTAMP "2020-07-07 12:34:50"),
      STRUCT( 7, TIMESTAMP "2020-07-07 21:07:10"),
      STRUCT(11, TIMESTAMP "2020-07-07 21:10:00")
    ]
  )
), bucket AS (
  SELECT * FROM UNNEST(
    GENERATE_TIMESTAMP_ARRAY(
      "2020-07-07 00:00:00", "2020-07-07 23:59:59",
       INTERVAL 1 HOUR
    )
  ) AS bucket1h
)

SELECT
  bucket1h,
  IFNULL(SUM(count), 0) as count
FROM bucket
  LEFT JOIN data ON bucket1h = TIMESTAMP_TRUNC(timestamp, HOUR)
GROUP BY bucket1h
ORDER BY bucket1h ASC
bucket1h count
2020-07-07 00:00:00 UTC 0
2020-07-07 01:00:00 UTC 0
2020-07-07 02:00:00 UTC 0
... ...
2020-07-07 09:00:00 UTC 0
2020-07-07 10:00:00 UTC 3
2020-07-07 11:00:00 UTC 0
2020-07-07 12:00:00 UTC 5
2020-07-07 13:00:00 UTC 0
... ...
2020-07-07 20:00:00 UTC 0
2020-07-07 21:00:00 UTC 18
2020-07-07 22:00:00 UTC 0
2020-07-07 23:00:00 UTC 0

するとバケットに対して 0 埋めしたデータを簡単に作れる。

TIMESTAMP_TRUNC の単位

TIMESTAMP_TRUNC 標準 SQL のタイムスタンプ関数  |  BigQuery  |  Google Cloud

TIMESTAMP_TRUNC や DATETIME_TRUNC は WEEK や DAY, HOUR, MINUTE などの事前定義された粒度で切り詰めることはできるけど、15分単位などでの切り詰めは行えない。

それ以下/以上の粒度で切り詰めるなら、以下のように一旦秒やマイクロ秒にして割って切り詰めるしかなさそう。

(15分で切り詰める例)
TIMESTAMP_SECONDS(DIV(UNIX_SECONDS(timestamp), 15*60) * 15*60)

TIMESTAMP の値域は 0001-01-01 00:00:00~ なのに対し UNIX_SECONDS は 1970-01-01 からの経過秒数を返すので注意が必要だけど、負値が返るので切り詰め自体は 1970 年以前の値でも期待通り行える。ここはもうちょっと BigQuery 側に便利関数がほしい。

RANGE_BUCKET

より直接的にバケット集計を行う関数もあった。

RANGE BUCKET | 標準 SQL の数学関数  |  BigQuery  |  Google Cloud

RANGE_BUCKET(point, boundaries_array) で、boundaries_array の範囲で point の入る index を返す。

例えばテストの点を集計するとして、10点刻みのグループで集計したい、60点以下は赤点なのでまとめる、みたいなときに。

WITH data AS (
  SELECT * FROM UNNEST(
    ARRAY<STRUCT<name STRING, score INT64>>[
      STRUCT("alice", 72),
      STRUCT("bob", 93),
      STRUCT("charlie", 55),
      STRUCT("dave", 80),
      STRUCT("eve", 82),
      STRUCT("frank", 48)
    ]
  )
), score_range AS (
  SELECT GENERATE_ARRAY(60, 100, 10) as bucket
)

SELECT
  name, score,
  RANGE_BUCKET(score, bucket) as score_group, # bucket 中での index
  bucket[OFFSET(RANGE_BUCKET(score, bucket))] as group_score, # bucket 中での対応する値
  COUNT(*) OVER(PARTITION BY RANGE_BUCKET(score, bucket)) as group_population, # bucket 内の人数
  RANK() OVER(PARTITION BY RANGE_BUCKET(score, bucket) ORDER BY score DESC) as rank_in_group # bucket 中での順位
FROM data, score_range
ORDER BY score DESC
name score score_group group_score group_population rank_in_group
bob 93 4 100 1 1
eve 82 3 90 2 1
dave 80 3 90 2 2
alice 72 2 80 1 1
charlie 55 0 60 2 1
frank 48 0 60 2 2

よかったですね