Edited at

BigQueryメモ書き

More than 1 year has passed since last update.

ただのメモ書き。

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