Bigquery(SQL)で５年間分の検索単語数を１日おきに抽出する方法

Question

google big query で検索ワードをSQLで出したいのですが、以下の条件で抽出したいと考えています

・指定期間は５年間
・1時間ごとの単語数をcountし、１列で1時間分の単語数とその時の時間の2行分

要は時系列データとしてshape=(365×5×24,)（（batch,time_window,dim）=(365×5,3,21)になるようにする）
の形で抽出したいと考えています。

期間指定はできるのですが、一時間おきにcountする方法として、何かいい方法はないでしょうか？
ご教授お願いします

ちなみにまだ１日ごとの期間指定はしていませんが、5年分の単語抽出のSQLは以下の通りです

original_keywords
```は単語 

time

は検索ワードが入力された時間です

```SQL
SELECT original_keywords, time AS t
from TABLE_DATE_RANGE(search_logs.search_log_, TIMESTAMP ('2012-6-10'), TIMESTAMP ('2017-6-10') )
WHERE original_keywords IS NOT NULL

Answer 1

集計関数を利用すれば可能だと思います。
timeがTIMESTAMP型ならこんな感じになるかと。

#standardSQL

SELECT
  original_keywords,
  format_time("%Y%m%d%H", time) AS hours,
  COUNT(*) AS cnt
fROM
  search_logs.search_log_*
WHERE
  original_keywords IS NOT NULL
AND
  _TABLE_SUFFIX BETWEEN '20120610' AND '20170610'
GROUP BY
  original_keywords,
  format_timestamp("%Y%m%d%H", time)

Comments

[trafalbad]

#standardSQL

SELECT
format_timestamp("%Y%m%d%H", time) AS hours,
COUNT(original_keywords) AS cnt
FROM `search_logs.search_log_*`
WHERE
original_keywords IS NOT NULL
AND
_TABLE_SUFFIX BETWEEN '20120610' AND '20170610'
GROUP BY hours
ORDER BY hours asc