Treasure Data CDP＜第20弾＞Treasure Dataの日時を扱う独自関数をご紹介！

投稿者：大岡、福田

NI+C マーケソリューションチームです：）

本Tech Blogでは、NI+Cで取り扱っているTreasure Data CDPを紹介していきます。

今回はTreasure Dataの独自関数についてご紹介いたします！

1．Treasure Dataの独自関数とは

まず始めに、Treasure Dataについて簡単にご説明いたします。
Treasure Dataは、PlazmaDBという独自のデータベースを使っており、PlazmaDBに対して、オープンソースのSQLであるPrestoとHiveの2種類を使ってアクセスすることができます。
一般的には、各SQLごとの関数があり、それぞれに応じた形に書きかえる必要がありますが、Treasure Dataの独自関数はPrestoとHiveのどちらを使う場合でも書きかえずに利用することが可能です。
そのため、Treasure Dataの独自関数を使うことで、各SQLとの互換性を高める役割や日付の計算がしやすいという点があります！

2．日付のデータについて

次に、日付のデータ型についてご説明いたします。
日付データをDBに保管する際は様々なデータの型があります。大きく分けると、人が見て分かる時刻表現とコンピューターシステム上の時刻表現があります。この後の説明を理解していただくために、今回はその中から日付型を２つご紹介します！

①日付の文字列
年月日時分秒(yyyy-mm-dd hh:mm:ss)といった人が理解しやすい形式のことです。
日本時間（JST）や協定世界時間（UTC）などの地域ごとに時間が異なってくるので、どの地域の時間か考慮する必要があります。

②Unixtime型
「1585666800」のような10桁の数値での表記方法です。
UTC時刻における1970/01/01 0:00:00を「0」として、1秒毎に1増える仕様になっており、日付の文字列と違い地域ごとに時間を考慮する必要はありません。

3．日付の文字列やUnixtime型には用途に応じた変換が必要

Treasure Dataは日付のデータを取り込む際に、Timestampとして取り込まれる仕様となっています。
そのため、用途に応じて変換が必要になっており、主にレポートなどの可視化の際には文字列に変換し、計算が必要な場合はunixtimeに変換を行うと使用しやすくなります。

4．関数の紹介

それではここからTreasure Dataの独自関数についてご紹介いたします！

ご参考：今回は下記のデータを利用しています。

Tresure Dateの独自関数

1.「TD_TIME_FORMAT」
Unixtime型から日付の文字列への変換を行う場合に利用する関数です。

ここでは、Unixtime値が入った「time」というカラムを、年月日時分秒で出力するように記載したところ、下記添付画像のように、「date」という項目名で出力することができました！

2. 「TD_TIME_PARSE」
1.とは逆に、日付の文字列からUnixtime型への変換を行う場合に利用する関数です。

ここでは、日付の値が入った「sales_date」というカラムを、Unixtime型として「date」という項目名で出力することができました！

3. 「TD_DATE_TRUNC」
Unixtimeをいろいろな切りの良い時間単位に変換したい場合に利用する関数です。

ここでは、Unixtime値が入った「time」カラムのレコードを、週にまるめた結果を「col1」という項目名で出力しました。
対象レコードの「time」カラムの値「1709108361」が、実行後「1708873200」として値が返っています。

このUnixtimeの値だけではわかりにくいので、先ほどご紹介した「TD_TIME_FORMAT」を使って日付型に変換してみると、「col2」で日付型として表示されました。

元のデータのUnixtime値「1709108361」は、日付型にすると「2024年02月28日17:19:21」であり、
「TD_DATE_TRUNC」を使うことで、週の始めである「2024年02月26日00:00:00」に変換できています！

今回はweekを例にあげましたが、他にも「hour」（＝時間の始まり）や「month」（＝月初）、「day」（＝日の始まり）などに変換することも可能です。

4. 「TD_TIME_RANGE」
一定の期間に該当するレコードだけを抽出したい場合に利用する関数です。
WHERE句の中に、条件式を使って日付の範囲を指定します。

ここでは、「sales_date_unixtime」カラムに入った値で、2023年3月1日以降、2023年7月2日より前であるレコードが選択されるように記載しており、下記のように範囲に該当するレコードのみが抽出されています！

5. 「TD_TIME_ADD」
レコードで持っている日付や時間に足し算や引き算を行う場合に利用する関数です。

ここでは、Unixtime値の「1692780537」に対して1時間足した時間を、「1h_later_unixtime」という項目名で出力しました。
わかりやすいように前後の行（2、4行）で「TD_DATE_FORMAT」を使い、それぞれのUnixtime値を日付型にして表示させてみたところ、1時間足されていることがわかります！

独自関数と一緒に使うことが多い関数

ここからは、独自関数と一緒に使用されることが多い関数を2つほどご紹介いたします。

１. 「CAST」
データの型を変換したい場合に利用する関数です。

ここでは、long型で入っている「price」カラムのレコードを、double型に変換した結果を「_col1」という項目名で出力しました。「_col1」は小数点になっており、型が変換できたことがわかります！

2. 「LAG」
現在のレコードの前にあるレコードの値を取得する際に利用する関数です。

ここでは、「sales_amount」カラムの1個前のレコードを、「sales_amount_last_month」という項目名で出力しました。
「sales_amount_last_month」の2行目に「sales_amount」の1行目の値が入っていることがわかります！
また、「sales_amount_last_month」の1行目は1個前のレコードがないためNULLになっています。