Prestoでの日付の扱い方 - ★データ解析備忘録★

Prestoで日付の処理に躓いたのでメモ。

TreasureData (TD)ってUDF(user defined functions)が多いから、HiveにしろPrestoにしろ結構躓いたりするんですよね。
公式のマニュアルページが手放せません。
docs.treasuredata.com

今回はこのなかで初心者やSQLユーザーが間違いやすい部分と、これを組み合わせた日付の処理をいくつか書きます。

WHERE句での日付の範囲指定

SQLを使ったことがある人が最初に「あれ？」って思うのはここだと思います。
たとえば'2016-01-01 00:00:00'から'2016-01-03 23:59:59'までのデータを取りたいとき、
SQLだと、

WHERE time >=　'2016-01-01'　AND　time < '2016-01-03'

と書けばよいのですが、Prestoだと

WHERE TD_TIME_RANGE(time, '2016-01-01', '2016-01-02','JST')

と書きます。

日付の取得

これもUDFでやります。たとえば年月日がほしい場合、

SELECT TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')

となります。このとき、出力されるのは

2016-01-01

でvarchar型になります。

曜日の取得

日付を取ったら曜日が欲しい時もあるでしょう。
これもSQLのDATE_PART関数は使えないので別の方法でやります。
Prestoでは、

dow(timestamp)

を使うのですが、ここで一つ問題があります。
dow関数は、引数にtimestamp型しかとれないので、
通常int型で格納されている time のカラムをそのまま持ってくるわけにはいきません。
また、上のようなTD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')だけではvarchar型に
なってしまうのでだめです。
そこで変換作業をします。
まず、

TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')

でとったvarchar型を unixtime*1 に変換するため、TD_TIME_PARSE関数を使います。

TD_TIME_PARSE(TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST') )

この時点で、たとえば'2016-01-01　00:00::00'に対しては

1451574000

が返ってきます。これはbigint型です。まだtimestamp型になってません。 unixtime を timestamp に直すのはFROM_UNIXTIME関数です。

FROM_UNIXTIME(TD_TIME_PARSE(TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST')))

これでtimestamp型にになったので、ようやくdow関数で曜日を取得できます。

dow(FROM_UNIXTIME(TD_TIME_PARSE(TD_TIME_FORMAT(time, 'yyyy-MM-dd', 'JST'))))

たとえば'2016-01-01　00:00::00'に対しては金曜日の

が返ってきます。

日付の差を計算する。

1月3日と1月1日の差は2日間です。これをPrestoでやるのにもUDFがあります。date_diff関数です。
これはtimestamp型またはdate型にのみ使えるので、CAST関数で変換します。

SELECT date_diff('day', CAST('2016-01-01' AS DATE), CAST('2016-01-03' AS DATE)) --結果: 2
SELECT date_diff('day', CAST('2016-01-01' AS TIMESTAMP), CAST('2016-01-03' AS TIMESTAMP)) --結果: 2