Trino SQLの高度な関数の紹介
この章では、Trino SQLの高度な関数の使い方を紹介します。Trino SQLの詳細については、Trinoの公式ドキュメントを参照してください
try関数とtry_cast関数
try(expression)
try関数は、その中の式で発生した例外を捕捉し、例外となった値をNULLとして返します。try関数を使用しない場合、ステートメントで例外が発生すると直接エラーとなり、クエリが失敗します。
coalesce関数と組み合わせて、NULL値を特定の値に置き換えることもできます。たとえば次の例では、フィールドaを整数に変換し、変換に失敗した場合は0に変換します
coalesce(try(cast("a" as integer)), 0)
上記の型変換はtry_cast関数でも実現できます。try_castの役割はcast関数と同じで、どちらも値の型変換を行いますが、try_castは型変換でエラーが発生した場合にNULLを返すため、クエリの失敗を防げる点が異なります。
coalesce(try_cast("a" as integer), 0)
時間/日付関数
current_date、current_time、current_timestamp、localtime、localtimestampを使用する際は丸括弧を付けません。Trinoは丸括弧を付けた書き方にも対応していないため、使用時に注意してください。
文字列と時間の変換
文字列形式の時間式の前にキーワードtimestampを付けると（例：timestamp '2020-01-01 00:00:00'）、対応する時間を直接取得できます
date_parseとdate_formatは、それぞれ文字列から時間への変換と、時間から文字列への変換を行います。どちらも、変換するフィールドと対応するformatを渡して使用します。次の例は、それぞれ文字列$part_dateを時間に変換し、時間#event_timeを文字列に変換するものです：
date_parse("$part_date", '%Y-%m-%d')
date_format("#event_time", '%Y-%m-%d %T')
上記の関数のformatはMySQLの形式を使用します。JAVAの形式を使用する場合は、関数format_datetimeとparse_datetimeを使用できます
時間計算関数
関数date_addは時間をオフセットできます。unitは単位、valueはオフセット量で、valueが負の数の場合は過去方向にオフセットします
date_add(unit, value, timestamp)
関数date_diffは2つの時間の差を計算します。計算方法はtimestamp2 - timestamp1で、unitを単位とする整数を返します
date_diff(unit, timestamp1, timestamp2)
2つの関数のunitに指定できる値は、次の表を参照してください
| 単位 | 説明 |
|---|---|
| millisecond | ミリ秒 |
| second | 秒 |
| minute | 分 |
| hour | 時間 |
| day | 日 |
| week | 週 |
| month | 月 |
| quarter | 四半期 |
| year | 年 |
ウィンドウ関数
Trinoはウィンドウ関数に対応しています。ウィンドウ関数には非常に便利な関数が多くあり、たとえばfirst_valueとlast_valueは、一定期間内に初めて、または最後に何かを行ったときの値を計算するのに適しています。
たとえば、各ユーザーが初めて商品購入行動を行ったときに購入したアイテムを計算する場合：
SELECT user_id,first_purchase_product FROM
(SELECT user_id,first_value(product_name) over(partition by user_id order by time) AS first_purchase_product FROM log.purchase)
GROUP BY user_id,first_purchase_product
first_valueとlast_valueはover句と組み合わせて使用する必要があります。over句のpartition byはgroup byと似ており、指定したフィールドでグループ化します。order byはソートに使用するフィールドを決定します。
JSONの解析
送信したデータやインポートした履歴データにJSON型のフィールドがある場合、格納時にはすべてテキスト型（文字列）に変換されます。クエリ文の中で抽出して使用できます。
文字列からJSONへの変換
json_parseは、JSON形式に準拠した文字列をJSON型のデータに変換できます：
json_parse(JSON '{"abc":[1, 2, 3]}')
JSONから他の型への変換
CASTを使ってJSONを他の型のデータに変換できます。たとえば、先ほどJSONに変換した文字列を再びMAPに変換します：
CAST(json_parse('{"abc":[1, 2, 3]}') AS MAP(varchar,array(integer)))
JSONを再び文字列に変換したい場合は、json_formatを使用できます：
json_format(json_parse('{"abc":[1, 2, 3]}'))
JSONデータを直接抽出する
多くの場合、JSONの一部のデータだけを抽出すれば十分です。その場合はjson_extract_scalarを使用し、json_path式で必要な内容を指定できます：
json_extract_scalar(json, json_path)
json_extract_scalarを使ってJSON文字列から直接抽出することもできます。手動でJSON型に変換する必要はありません。たとえばabcの最初の要素を抽出します：
json_extract_scalar('{"abc":[1, 2, 3]}','$.abc[0]')