Trino SQL 쿼리 고급 기능 소개

이 장에서는 Trino SQL의 고급 기능 사용 방법을 소개합니다. Trino SQL에 대해 더 자세히 알고 싶다면, Trino의 공식 웹사이트 문서 (opens new window)를 참조하세요.

try 함수와 try_cast 함수

try(expression)

try 기능은 SQL 문에서 잘못된 데이터를 점검하고, 잘못된 값을 null로 반환합니다. try 기능을 사용하지 않으면, SQL 코드에 오류가 발생할 경우 직접 오류가 보고되며, SQL 쿼리가 실패합니다.

또한, coalesce 함수를 사용하여 다음과 같이 NULL 값을 특정 값으로 대체하고, 필드a를 정수로 변환하며, 변환에 실패한 경우 0으로 변환할 수도 있습니다.

coalesce(try(cast("a" as integer)), 0)

위의 변환은 try_cast 기능을 사용하여 구현할 수 있습니다. try_cast의 기능은 값에 대해 변환을 실행하는 cast 기능과 같습니다. 차이점은 try_cast는 타입 변환 오류가 발생했을 때 쿼리의 실패를 방지하기 위해 값을 반환하지 않는 것입니다.

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]')