Knowledge & Column

【SQL】TreasureData独自関数が覚えられない!TD利用歴2年半のエンジニアがよく使うものだけをまとめてみた!【Time系の関数編】

■はじめに

お客様のサポートをする中で、「TreasureDataの独自関数の使い方が分からない」という声をよく聞きます。TreasureDataは、BigQueryやRedshiftとは違い、便利な独自の関数(User Defined Functions)をたくさん用意しているのですが、慣れていないうちは使い方に苦労されている印象を受けます。
この記事では、Time系の関数に焦点を当てて、私自身がよく使うものをまとめたので紹介したいと思います。「TD_TIME_FORMAT」や「TD_TIME_RANGE」など便利なものが多いので、是非活用してみてください!

(参考URL)
初級ハンズオン [TD関数](アクセスログ編)

 

■本記事の内容

・Timeについて前提確認
・TreasureData関数紹介

・まとめ

 

■Timeについて前提確認

Timeの表記は2パターンあります。
この後の説明を理解して頂くためにまずはご紹介します。

・Date型(YMD)
年/月/日/時/分/秒(yyyy/mm/dd/hh/mm/ss)の表記方法で、見た目で何年何月何日が分かる表記です。注意点としては、日本時間(JST)や協定世界時間(UTC)などの地域ごとに時間が異なってくるので、どの地域の時間か考慮する必要があります。

・Unixtime型
コンピューターシステム上の時刻表現の1種で「1585666800」のような10桁の数値です。
1970/01/01を「0」として、1秒毎に1増える仕様になっており、Date型と違い地域ごとに時間を考慮する必要はありません。

 

■TreasureData関数紹介

【①:Unixtime型 → Date型の変換】

TD_TIME_FORMAT(unixtime, 'yyyy-MM-dd HH:mm:ss', 'JST')

Unixtime型をDate型に変換する関数です。

日にち以外にも月や曜日なども集計することができます。

【②Date型 → Unixtime型の変換】

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

Date型をUnixtime型に変換する関数です。
「TD_TIME_PARSE(‘2020’, ‘JST’)」は、「2020-01-01 00:00:00」の時間が変換されるようになっています。

【③:時間範囲指定】

TD_TIME_RANGE(unixtime, 'yyyy-MM-dd(開始時間)', 'yyyy-MM-dd(終了時間)', 'JST')

WHERE句の中に記述して、時間範囲を指定することができます。
注意点として、終了時刻が「この時間を含まないギリギリの時間」ということです。

以下のように「2020-06-01」と記述すれば、「2020-05-31 23:59:59」までが範囲の対象になるので、集計結果の「max_date」も「2020-05-31」となります。

また開始・終了時刻については、「NULL」を入れることで片側範囲指定をすることや、「yyyy-MM-dd HH:mm:ss」のように秒まで入れて細かく時間指定をすることもできます。

TD_TIME_RANGE(unixtime, NULL, 'yyyy-MM-dd(終了時間)', 'JST')
TD_TIME_RANGE(unixtime, 'yyyy-MM-dd(開始時間)', NULL, 'JST')
TD_TIME_RANGE(unixtime, 'yyyy-MM-dd HH:mm:ss(開始時間)', 'yyyy-MM-dd HH:mm:ss(終了時間)', 'JST')

以下のように集計開始時間を「NULL」にすると、「min_date = 2017-07-19」となっています。(ログが「2017年7月19日」からあったためです。)

 

【④:時間の足し算/引き算】

TD_TIME_ADD(unixtime, '1d', 'JST') -- 1日後
TD_TIME_ADD(unixtime, '-1d', 'JST') -- 1日前

時間の足し算、引き算(1日後、1時間前など)を計算できる関数です。
確認を分かりやすくするために「TD_TIME_FORMAT」と合わせて使用しました。

「1d」では1日後が、「-1d」では1日前の時刻になっています。

「1d」以外にもよく使うのは以下があります。
※注意点としては、1ヶ月前や1年前などは選択できません。

TD_TIME_ADD(unixtime, '1h', 'JST') -- 1時間後
TD_TIME_ADD(unixtime, '-1h', 'JST') -- 1時間前
TD_TIME_ADD(unixtime, '1m', 'JST') -- 1分後
TD_TIME_ADD(unixtime, '-1m', 'JST') -- 1分前
TD_TIME_ADD(unixtime, '1s', 'JST') -- 1秒後
TD_TIME_ADD(unixtime, '-1s', 'JST') -- 1秒前


【⑤:時間を指定した時間に丸める】

TD_DATE_TRUNC('day', unixtime, 'JST')
TD_DATE_TRUNC('week', unixtime, 'JST')

unixtime時間を切りの良い時間に丸めてくれます。
特に私が良く使うのは「week」です。年度や日にちは「TD_TIME_FORMAT」を使用して取得することができますが、週のデータに丸めたい場合は、この関数を使うことが多いです。

「day」「week」以外については、以下があります。

TD_DATE_TRUNC('minute', unixtime, 'JST')
TD_DATE_TRUNC('hour', unixtime, 'JST')
TD_DATE_TRUNC('month', unixtime, 'JST')
TD_DATE_TRUNC('quater', unixtime, 'JST')
TD_DATE_TRUNC('year', unixtime, 'JST')

■まとめ

長くなってしましましたが、私がよく使用するTime系の関数は以上になります。
それぞれ関数単独でご紹介しましたが、本来はこれらを組み合わせて集計を実施することが多いです。是非基礎を理解して頂き、バリバリ活用してください!