Edited at

Bigqueryで使ったクエリまとめ

More than 1 year has passed since last update.


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

http://itnote-ichi5.com/post-bigquery-utc-jst/


テーブルの結合

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"))

http://stackoverflow.com/questions/38207702/the-last-day-of-the-previous-month-bigquery


リンク