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