Trino SQL高度関数紹介

この章ではTrino SQL の高度な関数の使用方法を紹介します。Trino SQL について詳しく知りたい場合はTrino の公式 Web サイトのドキュメント (opens new window)を参照してください。

try 関数と try_cast 関数

try(expression)

try 関数は式の異常をチェックし、異常値を NULL として返します。try 関数を使用しないと、SQLコードが異常な場合に直接エラーが報告され、クエリが失敗します。

また、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 '2020-01-01 00:00:00' のように、文字列形式の時刻表現の前にキーワード timestampを直接追加でき、時刻を直接取得できます。

date_parse と date_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はオフセットです。値が負の場合は前方オフセットです。

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から他の型へ変換

JSONをCASTを介して型データに変換できます。例えば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 を使用し、abcの最初のエレメントを抽出するなど、手動でJSON 型に変換せずにJSON文字列を直接抽出することもできます。

json_extract_scalar('{"abc":[1, 2, 3]}','$.abc[0]')