Bigqueryで使ったクエリまとめ #BigQuery

About

久しぶりにSQL触ったので、サンプルとして置いておきます。間違っていたら教えて下さい。。。適宜追加予定です。

UTC -> JST変換

SELECT
  create_time
  , STRFTIME_UTC_USEC(TIMESTAMP_TO_USEC(create_time) + 32400000000, "%Y/%m/%d %H:%M:%S") AS create_time_jst
FROM
  [test.access_log]
LIMIT
  1

テーブルの結合

select 
*
from 
(
  table_date_range(dataset.tablename,
  TIMESTAMP('2016-06-01'),
  TIMESTAMP('2016-06-30')))
  limit 100

ある期間内で曜日ごとに集計する

SELECT CASE
           WHEN DAYOFWEEK(time) = 1 THEN 'Sun'
           WHEN DAYOFWEEK(time) = 2 THEN 'Mon'
           WHEN DAYOFWEEK(time) = 3 THEN 'Tue'
           WHEN DAYOFWEEK(time) = 4 THEN 'Wed'
           WHEN DAYOFWEEK(time) = 5 THEN 'Thu'
           WHEN DAYOFWEEK(time) = 6 THEN 'Fri'
           WHEN DAYOFWEEK(time) = 7 THEN 'Sat'
           ELSE 'none'
       END AS week,
       count(time) as count
       from ( table_date_range(dataset.tablename, TIMESTAMP('2016-06-01'), TIMESTAMP('2016-06-30')))
WHERE context LIKE '%XXX%'
GROUP BY week

6月の期間内で曜日ごとのデータを集計してます。DAYOFWEEK 関数だと数値が取れますが、そのままだとわかりにくいので、CASE 文を使って、英語に修正しています。

文字列を区切り文字で分割して取得する

time	data
2016-07-04 02:30:00 UTC	aaa bbb ccc
2016-07-04 03:30:00 UTC	ddd eee fff

上記のようなテーブルがあった際にdataの中からspaceを区切り文字として、それぞれの値を取りたいとします。

SELECT time, 
  FIRST(SPLIT(data, ' ')) as col1,
  NTH(2,SPLIT(data, ' ')) as col2,
  NTH(3,SPLIT(data, ' ')) as col3
FROM test

とすると

time	col1	col2	col3
2016-07-04 02:30:00 UTC	aaa	bbb	ccc
2016-07-04 03:30:00 UTC	ddd	eee	fff

というようになります。

今月のテーブルを対象にクエリを発行する

テーブルを日付単位で分ける構成を取っているところもあると思います。そんなときに今月の情報を横断的に見たいというときの方法です。

select * from 
(TABLE_QUERY(<dataset_name>,
  'REGEXP_MATCH(table_id, r"^<table_prefix>") AND
   table_id CONTAINS STRFTIME_UTC_USEC(NOW(), "%Y%m")'))

TABLE_QUERYを使って対象月のテーブルを全て取得して、クエリを発行します。

TOP10を出力する

SELECT TOP(ua,10) as ua,
       COUNT(*) AS COUNT 
FROM (TABLE_QUERY(<dataset_name>, 'REGEXP_MATCH(table_id, r"^<table_prefix>") AND table_id CONTAINS STRFTIME_UTC_USEC(NOW(), "%Y%m%d")'))

uaというカラムのカウントが多い順TOP10を出力します。

先月の最終日を出力する

SELECT DATE(DATE_ADD(CURRENT_DATE() , -DAY(CURRENT_DATE()), "DAY"))

Bigqueryで使ったクエリまとめ