エクスチュア株式会社ブログ

はじめに

Treasure Dataを普通のSQL感覚で使うと何かとエラー起きがちで大変ですよね、、
今回はそんな私の実体験からTreasure Data(以下、TDと呼ぶ)でよく使う関数をまとめてみました。
では、早速みていきましょー！！✨

Treasure Data(トレジャーデータ)でよく使う関数

①「TD_DATE_TRUNC」unixtimeを指定した日付型に丸める
↓TD特有の「time」を日付(日本時間)で丸めた

TD_DATE_TRUNC('day', time, 'JST') 

②「TD_TIME_RANGE」条件式で日付の範囲を決定する
↓TD特有の「time」で日付を2020-05-01から2020-05-31(日本時間)で絞った

WHERE TD_TIME_RANGE(time, '2020-05-01', '2020-05-31', 'JST')

③「APPROX_DISTINCT」TD特有のDISTINCT関数（正確な値ではなく推定値）
↓店舗毎のUU数を重複なしでカウント(高精度の推定)する。OVER句は無くても良い。

APPROX_DISTINCT(user_id) OVER(PARTITION BY shop_name)

④「CAST(x AS DOUBLE)」TDで小数の計算を行う
↓ユーザー数÷店舗数を小数点以下まで求める

CAST(user_num AS DOUBLE)/shop_num
->CAST(user_num / shop_num AS DOUBLE)とすると整数が返ってくることがあるので注意。

⑤「STRPOS」特定の文字が出現する位置を調べる
↓STRPOS(‘文字列’, ‘文字’)

STRPOS('Treasure Data', 'sure')
->5

⑥「LAG」n日前の値を持ってくる
↓前日のUU数を店舗毎に計算

LAG(sum_user) OVER(PARTITION BY shop_name ORDER BY date)

⑦「TD_TIME_PARSE」文字列型で定義されている時間をunixtimeに変換
↓2020-05-04 07:32:33を日本時間unixtimeに変換

TD_TIME_PARSE('2020-05-04 07:32:33', 'JST')
->1588545153

⑧「TD_TIME_FORMAT」unixtimeを日付の文字列に変換
↓unixtimeを日本時間に変換

TD_TIME_FORMAT(1588545153, 'yyyy-MM-dd HH:mm:ss', 'JST')
->2020-05-04 07:32:33

⑨「TD_TIME_ADD」unixtime/文字列日時で時間/日付を進める
↓unixtimeを日本時間に変換して1時間進める

TD_TIME_ADD(1588545153, '1h', 'JST')
->2020-05-04 08:32:33

おわりに

いかがでしたでしょうか。
この記事を通して、TDに関する理解を深めていただければ幸いです。
最後まで目を通していただきありがとうございました🙇‍♂️