Bigquery | TIMESTAMPで同時刻が GROUP BY 出来ない時

Bigquery は TIMESTMAP にマイクロ秒まで持っているので、
WebConsoleでの見かけ上は同じでも、1マイクロ秒でも違えば、別の時刻扱いになる。

例

SELECT
  time
FROM (
  SELECT
    TIMESTAMP('2017-09-01 00:00:00') AS time
  UNION ALL (
    SELECT
      TIMESTAMP('2017-09-01 00:00:00.000001') AS time) )
GROUP BY
  time

CAST

timestamp型を DatetimeやStringにCASTすると、WebConsoleでミリ秒以下の違いが分かる。

SELECT
  time,
  CAST(time AS DATETIME) datetime,
  CAST(time AS STRING) string_time,
  regexp_replace(CAST(time AS STRING), '[+.][0-9]+', '') as replaced_time
FROM (
  SELECT
    TIMESTAMP('2017-09-01 00:00:00') AS time
  UNION ALL (
    SELECT
      TIMESTAMP('2017-09-01 00:00:00.000001') AS time) )
GROUP BY
  time

表記

StringにCASTした場合、表記はこんな風になる。
マイクロ秒の6桁まで必ず表示するわけではないようだ。

• 2017-09-01 00:00:00+00
• 2017-09-01 00:00:00.001+00
• 2017-09-01 00:00:00.000001+00

秒単位で GROUP BY する

TIMESTMAP型を無理矢理、STRINGの秒単位に置換して、GROUP BY した例。

SELECT
  regexp_replace(CAST(time AS STRING), '[+.][0-9]+', '') as replaced_time
FROM (
  SELECT
    TIMESTAMP('2017-09-01 00:00:00') AS time
  UNION ALL (
    SELECT
      TIMESTAMP('2017-09-01 00:00:00.000001') AS time) )
GROUP BY
  replaced_time

もっといいやり方があれば教えてください

参考

• データ型  |  BigQuery のドキュメント  |  Google Cloud Platform

チャットメンバー募集

何か質問、悩み事、相談などあればLINEオープンチャットもご利用ください。

Twitter