LoginSignup
6

More than 5 years have passed since last update.

BigQueryメモ書き

Last updated at Posted at 2014-12-09

ただのメモ書き。
SQLのTipsなどなど。

月初を出す。

★bigqueryが持っている関数で月初を出して、それ以上を求める。
hogehoge >= TIMESTAMP(DATE(DATE_ADD(CURRENT_TIMESTAMP(),
-DAY(DATE_ADD(CURRENT_TIMESTAMP(),-1,"DAY")),"DAY")))

 ※DATEで変換(時間切り捨て)するSTRINGになる。不便。

★うちが持っているタイムスタンプを変換して=で繋げてみる。
 YEAR(hogehoge) = YEAR(CURRENT_TIMESTAMP()) AND
 MONTH(hogehoge) = MONTH(CURRENT_TIMESTAMP())

日本時間

DATE_ADD(CURRENT_TIMESTAMP(),+9,"HOUR")

同年同月比

TIMESTAMP(DATE(DATE_ADD(DATE_ADD(CURRENT_TIMESTAMP(),-1,"YEAR"),-DAY(DATE_ADD(CURRENT_DATE(),-1,"DAY")),"DAY")))

年齢

BIRTHっていうカラムが生年月日。うるう年はどーしようか・・・。
SELECT
CASE WHEN DAYOFYEAR(CURRENT_DATE()) < DAYOFYEAR(BIRTH) THEN YEAR(CURRENT_DATE()) - YEAR(BIRTH) - 1
ELSE YEAR(CURRENT_DATE()) - YEAR(BIRTH) END AS AGE
FROM [HOGEHOGE.HOGE]

Viewは便利だけど、ある程度にしよう。

Viewは便利なんだど、ある程度にしましょう。
バッチでテーブル作った方がいいよ。

bigquery-public-dataの観測所を洗い出す。

http://googlecloudplatform-japan.blogspot.jp/2016/10/ghcn-bigquery.html?m=1
だいたい人が住んでるであろう日本の観測所を出してみる。
なんてことはないです。日本をすっぽり入れた緯度経度を入れただけです。
沖縄だけ飛んでいるのでUNIONしています。

select *
from
(SELECT
*
FROM
[bigquery-public-data:ghcn_d.ghcnd_stations]
where
latitude between 30 and 45 and
longitude between 130 and 145),
(SELECT
*
FROM
[bigquery-public-data:ghcn_d.ghcnd_stations]
where
latitude between 25 and 30 and
longitude between 125 and 130)

クソな日付で来た場合の置換方法

acs_dateってところに2017/01/01 15:15というなんともならんtimestampが来たときの対処方法。

update `dataset.table_name`
set acs_date_new = timestamp(PARSE_DATETIME("%Y/%m/%d %H:%M",acs_date))
where true

オープンデータメモ書き

何のデータ 管轄してるところ URL
国勢調査 http://www.e-stat.go.jp/SG1/estat/GL08020101.do?_toGL08020101_&tstatCode=000001080615

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
6