Trino SQL 고급 함수 소개

이 장에서는 Trino SQL의 고급 함수 사용 방법을 소개합니다. Trino SQL에 대해 더 자세히 알고 싶다면 Trino 공식 웹사이트 문서 (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 함수와 동일하게 값을 변환하지만, 변환 오류가 발생했을 때 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')

위 함수의 포맷은 MySQL 포맷을 사용합니다. JAVA 포맷을 사용해야 하는 경우, format_datetime및 parse_datetime함수를 사용할 수 있습니다.

시간 계산 함수

함수 date_add는 시간에 오프셋을 적용합니다. unit은 단위, value는 오프셋 값입니다. 값이 음수일 경우 과거로 오프셋됩니다.

date_add(unit, value, timestamp)

함수 date_diff는 두 시간의 차이를 계산하기 위해 사용됩니다. timestamp2 - timestamp1이며, 반환 값은 unit 단위의 정수입니다.

date_diff(unit, timestamp1, timestamp2)

두 함수의 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]')