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 함수는 두 시간의 차이를 계산하는 데 사용합니다. 계산 방식은 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을 다른 타입으로 변환
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]')