예시 SQL UDF
예시 SQL UDF (Example SQL UDFs)
SQL 사용자 정의 함수에 대해 배운 뒤, 유효한 SQL UDF의 다양한 예시를 보여주는 문서예요. 여러 지원 문을 조합해 실제 사용 패턴을 익힐 수 있어요.
출처: 문서
본문
SQL 사용자 정의 함수에 대해 배운 뒤, 다음 섹션들은 유효한 SQL UDF의 수많은 예시를 보여줘요. UDF들은 이름과 예시 호출을 조정하면 인라인 사용자 정의 함수나 카탈로그 사용자 정의 함수로 모두 적합해요.
예시들은 수많은 지원 문을 결합해요. 자세한 내용은 각 문 문서를 참고해요:
- 일반 UDF 선언의
FUNCTION - SQL UDF 블록의
BEGIN과DECLARE - 변수에 값을 할당하는
SET - 결과를 반환하는
RETURN - 조건 흐름의
CASE와IF - 반복 구조의
LOOP,REPEAT,WHILE - 흐름 제어의
ITERATE와LEAVE
인라인 및 카탈로그 UDF
다음 섹션은 간단한 SQL UDF 예시로 인라인과 카탈로그 UDF 사용의 차이를 보여줘요. 같은 패턴이 이후의 모든 섹션에 적용돼요.
입력 없이 정적 값을 반환하는 아주 간단한 SQL UDF:
FUNCTION answer()
RETURNS BIGINT
RETURN 42
이 UDF를 인라인 UDF로 쓴 전체 예시와 문자열 연결에서 CAST와 함께 쓰는 예시:
WITH
FUNCTION answer()
RETURNS BIGINT
RETURN 42
SELECT 'The answer is ' || CAST(answer() as varchar);
-- The answer is 42
카탈로그 example이 default 스키마에서 UDF 저장을 지원한다면 다음을 사용할 수 있어요:
CREATE FUNCTION example.default.answer()
RETURNS BIGINT
RETURN 42;
UDF가 카탈로그에 저장되면, 재정의 없이 UDF를 여러 번 실행할 수 있어요:
SELECT example.default.answer() + 1; -- 43
SELECT 'The answer is ' || CAST(example.default.answer() as varchar); -- The answer is 42
또는 Config 속성에서 SQL PATH를 UDF 저장을 지원하는 카탈로그와 스키마로 구성할 수 있어요:
sql.default-function-catalog=example
sql.default-function-schema=default
sql.path=example.default
이제 전체 경로 없이 UDF를 관리할 수 있어요:
CREATE FUNCTION answer()
RETURNS BIGINT
RETURN 42;
UDF 호출도 전체 경로 없이 동작해요:
SELECT answer() + 5; -- 47
선언 예시
UDF answer() 호출 결과는 항상 동일하므로, 결정적(deterministic)으로 선언하고 다른 정보도 추가할 수 있어요:
FUNCTION answer()
LANGUAGE SQL
DETERMINISTIC
RETURNS BIGINT
COMMENT 'Provide the answer to the question about life, the universe, and everything.'
RETURN 42
주석과 UDF에 대한 다른 정보는 SHOW FUNCTIONS 출력에서 볼 수 있어요.
입력 문자열 fullname에 두 문자열과 입력 값을 연결해 인사말을 반환하는 간단한 UDF:
FUNCTION hello(fullname VARCHAR)
RETURNS VARCHAR
RETURN 'Hello, ' || fullname || '!'
예시 호출:
SELECT hello('Jane Doe'); -- Hello, Jane Doe!
BEGIN 블록에서 여러 문을 사용하는 첫 번째 예시 UDF예요. 입력 정수에 99를 곱한 결과를 계산해요. 모든 변수와 값에 bigint 데이터 타입을 사용해요. 정수 99 값은 변수 x의 기본값 할당에서 bigint로 캐스트돼요:
FUNCTION times_ninety_nine(a bigint)
RETURNS bigint
BEGIN
DECLARE x bigint DEFAULT CAST(99 AS bigint);
RETURN x * a;
END
예시 호출:
SELECT times_ninety_nine(CAST(2 as bigint)); -- 198
조건 흐름
SQL UDF에서 CASE 문을 사용한 조건부 흐름 제어의 첫 번째 예시예요. 단순한 bigint 입력 값이 여러 값과 비교돼요:
FUNCTION simple_case(a bigint)
RETURNS varchar
BEGIN
CASE a
WHEN 0 THEN RETURN 'zero';
WHEN 1 THEN RETURN 'one';
WHEN 10 THEN RETURN 'ten';
WHEN 20 THEN RETURN 'twenty';
ELSE RETURN 'other';
END CASE;
RETURN NULL;
END
결과와 설명이 함께 있는 몇 가지 예시 호출:
SELECT simple_case(0); -- zero
SELECT simple_case(1); -- one
SELECT simple_case(-1); -- other (from else clause)
SELECT simple_case(10); -- ten
SELECT simple_case(11); -- other (from else clause)
SELECT simple_case(20); -- twenty
SELECT simple_case(100); -- other (from else clause)
SELECT simple_case(null); -- null .. but really??
CASE 문이 있는 SQL UDF의 두 번째 예시예요. 이번에는 매개변수가 둘로, 조건 순서의 중요성을 보여줘요:
FUNCTION search_case(a bigint, b bigint)
RETURNS varchar
BEGIN
CASE
WHEN a = 0 THEN RETURN 'zero';
WHEN b = 1 THEN RETURN 'one';
WHEN a = DECIMAL '10.0' THEN RETURN 'ten';
WHEN b = 20.0E0 THEN RETURN 'twenty';
ELSE RETURN 'other';
END CASE;
RETURN NULL;
END
결과와 설명이 함께 있는 몇 가지 예시 호출:
SELECT search_case(0,0); -- zero
SELECT search_case(1,1); -- one
SELECT search_case(0,1); -- zero (not one since the second check is never reached)
SELECT search_case(10,1); -- one (not ten since the third check is never reached)
SELECT search_case(10,2); -- ten
SELECT search_case(10,20); -- ten (not twenty)
SELECT search_case(0,20); -- zero (not twenty)
SELECT search_case(3,20); -- twenty
SELECT search_case(3,21); -- other
SELECT simple_case(null,null); -- null .. but really??
피보나치 예시
이 SQL UDF는 각 숫자가 앞의 두 숫자의 합인 피보나치 수열의 n번째 값을 계산해요. 두 초기 값은 a와 b의 기본값으로 1로 설정돼요. UDF는 IF 문 조건으로 2 이하의 모든 입력 값에 대해 1을 반환해요. WHILE 블록은 a=1과 b=1에서 시작해 n번째 위치에 도달할 때까지 수열의 각 숫자를 계산해요. 각 반복에서 a와 b를 이전 두 값으로 설정해 합을 계산하고 마지막에 반환해요. n 값이 커질수록 UDF 처리가 점점 오래 걸리고, 결과는 결정적이라는 점을 유의해요:
FUNCTION fib(n bigint)
RETURNS bigint
BEGIN
DECLARE a, b bigint DEFAULT 1;
DECLARE c bigint;
IF n <= 2 THEN
RETURN 1;
END IF;
WHILE n > 2 DO
SET n = n - 1;
SET c = a + b;
SET a = b;
SET b = c;
END WHILE;
RETURN c;
END
결과와 설명이 함께 있는 몇 가지 예시 호출:
SELECT fib(-1); -- 1
SELECT fib(0); -- 1
SELECT fib(1); -- 1
SELECT fib(2); -- 1
SELECT fib(3); -- 2
SELECT fib(4); -- 3
SELECT fib(5); -- 5
SELECT fib(6); -- 8
SELECT fib(7); -- 13
SELECT fib(8); -- 21
레이블과 루프
이 SQL UDF는 top 레이블로 WHILE 블록에 이름을 붙이고, 조건 문, ITERATE, LEAVE로 흐름을 제어해요. 루프의 처음 두 반복에서 a=1과 a=2 값에 대해 ITERATE 호출이 b가 증가하기 전에 흐름을 top으로 옮겨요. 그 다음 b가 a=3, a=4, a=5, a=6, a=7 값에 대해 증가해 b=5가 돼요. LEAVE 호출은 a가 10까지 더 증가하기 전에 블록을 빠져나오게 하므로 UDF 결과는 5예요:
FUNCTION labels()
RETURNS bigint
BEGIN
DECLARE a, b int DEFAULT 0;
top: WHILE a < 10 DO
SET a = a + 1;
IF a < 3 THEN
ITERATE top;
END IF;
SET b = b + 1;
IF a > 6 THEN
LEAVE top;
END IF;
END WHILE;
RETURN b;
END
이 SQL UDF는 반복 곱셈으로 n의 p 거듭제곱을 계산하고 수행된 곱셈 횟수를 추적해요. 이 SQL UDF는 p=0일 때 올바른 0을 반환하지 않는데, top 블록이 단지 빠져나올 뿐 n 값이 반환되기 때문이에요. 같은 잘못된 동작이 p의 음수 값에도 생겨요:
FUNCTION power(n int, p int)
RETURNS int
BEGIN
DECLARE r int DEFAULT n;
top: LOOP
IF p <= 1 THEN
LEAVE top;
END IF;
SET r = r * n;
SET p = p - 1;
END LOOP;
RETURN r;
END
결과와 설명이 함께 있는 몇 가지 예시 호출:
SELECT power(2, 2); -- 4
SELECT power(2, 8); -- 256
SELECT power(3, 3); -- 256
SELECT power(3, 0); -- 3, which is wrong
SELECT power(3, -2); -- 3, which is wrong
이 SQL UDF는 a=3에서 a=10까지 루프에서 b가 증가한 결과로 7을 반환해요:
FUNCTION test_repeat_continue()
RETURNS bigint
BEGIN
DECLARE a int DEFAULT 0;
DECLARE b int DEFAULT 0;
top: REPEAT
SET a = a + 1;
IF a <= 3 THEN
ITERATE top;
END IF;
SET b = b + 1;
UNTIL a >= 10
END REPEAT;
RETURN b;
END
이 SQL UDF는 2를 반환하고, 레이블이 반복될 수 있고 블록 안에서 레이블 사용이 그 블록의 레이블을 참조함을 보여줘요:
FUNCTION test()
RETURNS int
BEGIN
DECLARE r int DEFAULT 0;
abc: LOOP
SET r = r + 1;
LEAVE abc;
END LOOP;
abc: LOOP
SET r = r + 1;
LEAVE abc;
END LOOP;
RETURN r;
END
SQL UDF와 내장 함수
이 SQL UDF는 length()와 cardinality() 같은 내장 함수와 여러 데이터 타입을 UDF에서 사용할 수 있음을 보여줘요. 두 중첩 BEGIN 블록은 변수 이름 x가 이 블록들 안에서 지역적이지만, 최상위 블록의 전역 r은 중첩 블록에서 접근할 수 있음을 보여줘요:
FUNCTION test()
RETURNS bigint
BEGIN
DECLARE r bigint DEFAULT 0;
BEGIN
DECLARE x varchar DEFAULT 'hello';
SET r = r + length(x);
END;
BEGIN
DECLARE x array(int) DEFAULT array[1, 2, 3];
SET r = r + cardinality(x);
END;
RETURN r;
END
선택적 매개변수 예시
UDF는 다른 UDF와 다른 함수를 호출할 수 있어요. UDF의 전체 시그니처는 UDF 이름과 매개변수로 구성되며, 사용할 정확한 UDF를 결정해요. 같은 이름이지만 인자 수나 인자 타입이 다른 여러 UDF를 선언할 수 있어요. 하나의 예시 사용 사례는 선택적 매개변수를 구현하는 것이에요.
다음 SQL UDF는 문자열을 지정된 길이로 잘라내고 출력 끝에 점 세 개를 포함해요:
FUNCTION dots(input varchar, length integer)
RETURNS varchar
BEGIN
IF length(input) > length THEN
RETURN substring(input, 1, length-3) || '...';
END IF;
RETURN input;
END;
예시 호출과 출력:
SELECT dots('A long string that will be shortened',15);
-- A long strin...
SELECT dots('A short string',15);
-- A short string
같은 이름이지만 length 매개변수 없는 UDF를 제공하려면, 앞의 UDF를 호출하는 다른 UDF를 만들 수 있어요:
FUNCTION dots(input varchar)
RETURNS varchar
RETURN dots(input, 15);
이제 두 UDF를 모두 사용할 수 있어요. length 매개변수를 생략하면 두 번째 선언의 기본값이 사용돼요.
SELECT dots('A long string that will be shortened',15);
-- A long strin...
SELECT dots('A long string that will be shortened');
-- A long strin...
SELECT dots('A long string that will be shortened',20);
-- A long string tha...
날짜 문자열 파싱 예시
이 예시 SQL UDF는 VARCHAR 타입의 날짜 문자열을 TIMESTAMP WITH TIME ZONE으로 파싱해요. 날짜 문자열은 일반적으로 2023-12-01, 2023-12-01T23 같은 ISO 8601 표준으로 표현돼요. 또한 20230101, 2023010123 같은 YYYYmmdd와 YYYYmmddHH 형식으로도 자주 표현돼요. Hive 테이블은 이 형식을 사용해 일·시간 파티션을 표현할 수 있어요(예: /day=20230101, /hour=2023010123).
이 UDF는 날짜 문자열을 최선 방식으로 파싱하며, date, date_parse, from_iso8601_date, from_iso8601_timestamp 같은 날짜 문자열 조작 함수의 대체로 사용할 수 있어요.
이 UDF는 시간 값을 00:00:00.000으로, 시간대를 세션 시간대로 기본값 설정한다는 점을 유의해요:
FUNCTION from_date_string(date_string VARCHAR)
RETURNS TIMESTAMP WITH TIME ZONE
BEGIN
IF date_string like '%-%' THEN -- ISO 8601
RETURN from_iso8601_timestamp(date_string);
ELSEIF length(date_string) = 8 THEN -- YYYYmmdd
RETURN date_parse(date_string, '%Y%m%d');
ELSEIF length(date_string) = 10 THEN -- YYYYmmddHH
RETURN date_parse(date_string, '%Y%m%d%H');
END IF;
RETURN NULL;
END
결과와 설명이 함께 있는 몇 가지 예시 호출:
SELECT from_date_string('2023-01-01'); -- 2023-01-01 00:00:00.000 UTC (using the ISO 8601 format)
SELECT from_date_string('2023-01-01T23'); -- 2023-01-01 23:00:00.000 UTC (using the ISO 8601 format)
SELECT from_date_string('2023-01-01T23:23:23'); -- 2023-01-01 23:23:23.000 UTC (using the ISO 8601 format)
SELECT from_date_string('20230101'); -- 2023-01-01 00:00:00.000 UTC (using the YYYYmmdd format)
SELECT from_date_string('2023010123'); -- 2023-01-01 23:00:00.000 UTC (using the YYYYmmddHH format)
SELECT from_date_string(NULL); -- NULL (handles NULL string)
SELECT from_date_string('abc'); -- NULL (not matched to any format)
사람이 읽기 좋은 일 수 (Human-readable days)
Trino에는 초 수를 문자열로 포맷하는 내장 함수 human_readable_seconds()가 있어요:
SELECT human_readable_seconds(134823);
-- 1 day, 13 hours, 27 minutes, 3 seconds
예시 SQL UDF hrd는 일 수를 대략적인 연·월 수를 제공하는 사람이 읽기 좋은 텍스트로 포맷해요:
FUNCTION hrd(d integer)
RETURNS VARCHAR
BEGIN
DECLARE answer varchar default 'About ';
DECLARE years real;
DECLARE months real;
SET years = truncate(d/365);
IF years > 0 then
SET answer = answer || format('%1.0f', years) || ' year';
END IF;
IF years > 1 THEN
SET answer = answer || 's';
END IF;
SET d = d - cast( years AS integer) * 365 ;
SET months = truncate(d / 30);
IF months > 0 and years > 0 THEN
SET answer = answer || ' and ';
END IF;
IF months > 0 THEN
set answer = answer || format('%1.0f', months) || ' month';
END IF;
IF months > 1 THEN
SET answer = answer || 's';
END IF;
IF years < 1 and months < 1 THEN
SET answer = 'Less than 1 month';
END IF;
RETURN answer;
END;
다음 예시들은 한 달 미만, 일 년 미만, 그리고 다양한 더 큰 값들의 출력을 보여줘요:
SELECT hrd(10); -- Less than 1 month
SELECT hrd(95); -- About 3 months
SELECT hrd(400); -- About 1 year and 1 month
SELECT hrd(369); -- About 1 year
SELECT hrd(800); -- About 2 years and 2 months
SELECT hrd(1100); -- About 3 years
SELECT hrd(5000); -- About 13 years and 8 months
SQL UDF의 개선점은 다음 수정을 포함할 수 있어요:
- 한 달이 30.4375일임을 고려하기.
- 일 년이 365.25일임을 고려하기.
- 출력에 주(weeks) 추가하기.
- 십 년, 세기, 천 년 단위로 확장하기.
긴 문자열 잘라내기
이 예시 SQL UDF strtrunc은 60자보다 긴 문자열을 잘라내고, 처음 30자와 마지막 25자만 남기고 중간의 추가 문자를 잘라내요:
FUNCTION strtrunc(input VARCHAR)
RETURNS VARCHAR
RETURN
CASE WHEN length(input) > 60
THEN substr(input, 1, 30) || ' ... ' || substr(input, length(input) - 25)
ELSE input
END;
위 선언은 매우 간결하며 CASE 표현식과 여러 함수 호출이 있는 단일 복잡 문 하나로 구성돼요. 따라서 RETURN 절에서 전체 로직을 정의할 수 있어요.
다음 문은 SQL UDF 자체 안에서 같은 기능을 보여줘요. CASE 문 안팎의 중복 RETURN과 필수 END CASE;를 유의해요. 두 번째 RETURN 문이 필요한데, SQL UDF는 RETURN 문으로 끝나야 하기 때문이에요. 그 결과 ELSE 절은 생략할 수 있어요:
FUNCTION strtrunc(input VARCHAR)
RETURNS VARCHAR
BEGIN
CASE WHEN length(input) > 60
THEN
RETURN substr(input, 1, 30) || ' ... ' || substr(input, length(input) - 25);
ELSE
RETURN input;
END CASE;
RETURN input;
END;
다음 예시는 CASE에서 IF 문으로 바꿔 중복 RETURN을 피해요:
FUNCTION strtrunc(input VARCHAR)
RETURNS VARCHAR
BEGIN
IF length(input) > 60 THEN
RETURN substr(input, 1, 30) || ' ... ' || substr(input, length(input) - 25);
END IF;
RETURN input;
END;
위의 모든 예시는 같은 출력을 만들어요. 다음은 잘라낼 긴 문자열을 생성하는 예시 쿼리예요:
WITH
data AS (
SELECT substring('strtrunc truncates strings longer than 60 characters,
leaving the prefix and suffix visible', 1, s.num) AS value
FROM table(sequence(start=>40, stop=>80, step=>5)) AS s(num)
)
SELECT
data.value
, strtrunc(data.value) AS truncated
FROM data
ORDER BY data.value;
위 쿼리는 SQL UDF의 모든 변형과 함께 다음 출력을 만들어요:
value | truncated
----------------------------------------------------------------------------------+---------------------------------------------------------------
strtrunc truncates strings longer than 6 | strtrunc truncates strings longer than 6
strtrunc truncates strings longer than 60 cha | strtrunc truncates strings longer than 60 cha
strtrunc truncates strings longer than 60 characte | strtrunc truncates strings longer than 60 characte
strtrunc truncates strings longer than 60 characters, l | strtrunc truncates strings longer than 60 characters, l
strtrunc truncates strings longer than 60 characters, leavin | strtrunc truncates strings longer than 60 characters, leavin
strtrunc truncates strings longer than 60 characters, leaving the | strtrunc truncates strings lon ... 60 characters, leaving the
strtrunc truncates strings longer than 60 characters, leaving the pref | strtrunc truncates strings lon ... aracters, leaving the pref
strtrunc truncates strings longer than 60 characters, leaving the prefix an | strtrunc truncates strings lon ... ers, leaving the prefix an
strtrunc truncates strings longer than 60 characters, leaving the prefix and suf | strtrunc truncates strings lon ... leaving the prefix and suf
가능한 개선점은 전체 길이에 대한 매개변수를 도입하는 것이에요.
바이트 포맷팅
Trino에는 내장 format_number() 함수가 있어요. 하지만 바이트에는 잘 맞지 않는 단위를 사용해요. 다음 format_data_size SQL UDF는 큰 바이트 값을 사람이 읽기 좋은 문자열로 포맷할 수 있어요:
FUNCTION format_data_size(input BIGINT)
RETURNS VARCHAR
BEGIN
DECLARE value DOUBLE DEFAULT CAST(input AS DOUBLE);
DECLARE result BIGINT;
DECLARE base INT DEFAULT 1024;
DECLARE unit VARCHAR DEFAULT 'B';
DECLARE format VARCHAR;
IF abs(value) >= base THEN
SET value = value / base;
SET unit = 'kB';
END IF;
IF abs(value) >= base THEN
SET value = value / base;
SET unit = 'MB';
END IF;
IF abs(value) >= base THEN
SET value = value / base;
SET unit = 'GB';
END IF;
IF abs(value) >= base THEN
SET value = value / base;
SET unit = 'TB';
END IF;
IF abs(value) >= base THEN
SET value = value / base;
SET unit = 'PB';
END IF;
IF abs(value) >= base THEN
SET value = value / base;
SET unit = 'EB';
END IF;
IF abs(value) >= base THEN
SET value = value / base;
SET unit = 'ZB';
END IF;
IF abs(value) >= base THEN
SET value = value / base;
SET unit = 'YB';
END IF;
IF abs(value) < 10 THEN
SET format = '%.2f';
ELSEIF abs(value) < 100 THEN
SET format = '%.1f';
ELSE
SET format = '%.0f';
END IF;
RETURN format(format, value) || unit;
END;
아래는 넓은 범위의 값을 어떻게 포맷하는지 보여주는 쿼리예요:
WITH
data AS (
SELECT CAST(pow(10, s.p) AS BIGINT) AS num
FROM table(sequence(start=>1, stop=>18)) AS s(p)
UNION ALL
SELECT -CAST(pow(10, s.p) AS BIGINT) AS num
FROM table(sequence(start=>1, stop=>18)) AS s(p)
)
SELECT
data.num
, format_data_size(data.num) AS formatted
FROM data
ORDER BY data.num;
위 쿼리는 다음 출력을 만들어요:
num | formatted
----------------------+-----------
-1000000000000000000 | -888PB
-100000000000000000 | -88.8PB
-10000000000000000 | -8.88PB
-1000000000000000 | -909TB
-100000000000000 | -90.9TB
-10000000000000 | -9.09TB
-1000000000000 | -931GB
-100000000000 | -93.1GB
-10000000000 | -9.31GB
-1000000000 | -954MB
-100000000 | -95.4MB
-10000000 | -9.54MB
-1000000 | -977kB
-100000 | -97.7kB
-10000 | -9.77kB
-1000 | -1000B
-100 | -100B
-10 | -10.0B
0 | 0.00B
10 | 10.0B
100 | 100B
1000 | 1000B
10000 | 9.77kB
100000 | 97.7kB
1000000 | 977kB
10000000 | 9.54MB
100000000 | 95.4MB
1000000000 | 954MB
10000000000 | 9.31GB
100000000000 | 93.1GB
1000000000000 | 931GB
10000000000000 | 9.09TB
100000000000000 | 90.9TB
1000000000000000 | 909TB
10000000000000000 | 8.88PB
100000000000000000 | 88.8PB
1000000000000000000 | 888PB
차트
Trino에는 이미 내장 bar() 색 함수가 있지만, ANSI 이스케이프 코드로 색을 출력하므로 터미널에서 결과를 표시할 때만 사용할 수 있어요. 다음 예시는 ASCII 문자만 사용하는 유사한 SQL UDF를 보여줘요:
FUNCTION ascii_bar(value DOUBLE)
RETURNS VARCHAR
BEGIN
DECLARE max_width DOUBLE DEFAULT 40.0;
RETURN array_join(
repeat('█',
greatest(0, CAST(floor(max_width * value) AS integer) - 1)), '')
|| ARRAY[' ', '▏', '▎', '▍', '▌', '▋', '▊', '▉', '█']
[cast((value % (cast(1 as double) / max_width)) * max_width * 8 + 1 as int)];
END;
값을 시각화하는 데 사용할 수 있어요:
WITH
data AS (
SELECT
cast(s.num as double) / 100.0 AS x,
sin(cast(s.num as double) / 100.0) AS y
FROM table(sequence(start=>0, stop=>314, step=>10)) AS s(num)
)
SELECT
data.x,
round(data.y, 4) AS y,
ascii_bar(data.y) AS chart
FROM data
ORDER BY data.x;
위 쿼리는 다음 출력을 만들어요:
x | y | chart
-----+--------+-----------------------------------------
0.0 | 0.0 |
0.1 | 0.0998 | ███
0.2 | 0.1987 | ███████
0.3 | 0.2955 | ██████████▉
0.4 | 0.3894 | ██████████████▋
0.5 | 0.4794 | ██████████████████▏
0.6 | 0.5646 | █████████████████████▋
0.7 | 0.6442 | ████████████████████████▊
0.8 | 0.7174 | ███████████████████████████▊
0.9 | 0.7833 | ██████████████████████████████▍
1.0 | 0.8415 | ████████████████████████████████▋
1.1 | 0.8912 | ██████████████████████████████████▋
1.2 | 0.932 | ████████████████████████████████████▎
1.3 | 0.9636 | █████████████████████████████████████▌
1.4 | 0.9854 | ██████████████████████████████████████▍
1.5 | 0.9975 | ██████████████████████████████████████▉
1.6 | 0.9996 | ███████████████████████████████████████
1.7 | 0.9917 | ██████████████████████████████████████▋
1.8 | 0.9738 | ██████████████████████████████████████
1.9 | 0.9463 | ████████████████████████████████████▉
2.0 | 0.9093 | ███████████████████████████████████▍
2.1 | 0.8632 | █████████████████████████████████▌
2.2 | 0.8085 | ███████████████████████████████▍
2.3 | 0.7457 | ████████████████████████████▉
2.4 | 0.6755 | ██████████████████████████
2.5 | 0.5985 | ███████████████████████
2.6 | 0.5155 | ███████████████████▋
2.7 | 0.4274 | ████████████████▏
2.8 | 0.335 | ████████████▍
2.9 | 0.2392 | ████████▋
3.0 | 0.1411 | ████▋
3.1 | 0.0416 | ▋
더 압축된 차트를 그리는 것도 가능해요. 수직 막대를 그리는 SQL UDF는 다음과 같아요:
FUNCTION vertical_bar(value DOUBLE)
RETURNS VARCHAR
RETURN ARRAY[' ', '▁', '▂', '▃', '▄', '▅', '▆', '▇', '█'][cast(value * 8 + 1 as int)];
단일 컬럼에서 값의 분포를 그리는 데 사용할 수 있어요:
WITH
measurements(sensor_id, recorded_at, value) AS (
VALUES
('A', date '2023-01-01', 5.0)
, ('A', date '2023-01-03', 7.0)
, ('A', date '2023-01-04', 15.0)
, ('A', date '2023-01-05', 14.0)
, ('A', date '2023-01-08', 10.0)
, ('A', date '2023-01-09', 1.0)
, ('A', date '2023-01-10', 7.0)
, ('A', date '2023-01-11', 8.0)
, ('B', date '2023-01-03', 2.0)
, ('B', date '2023-01-04', 3.0)
, ('B', date '2023-01-05', 2.5)
, ('B', date '2023-01-07', 2.75)
, ('B', date '2023-01-09', 4.0)
, ('B', date '2023-01-10', 1.5)
, ('B', date '2023-01-11', 1.0)
),
days AS (
SELECT date_add('day', s.num, date '2023-01-01') AS day
-- table function arguments need to be constant but range could be calculated
-- using: SELECT date_diff('day', max(recorded_at), min(recorded_at)) FROM measurements
FROM table(sequence(start=>0, stop=>10)) AS s(num)
),
sensors(id) AS (VALUES ('A'), ('B')),
normalized AS (
SELECT
sensors.id AS sensor_id,
days.day,
value,
value / max(value) OVER (PARTITION BY sensor_id) AS normalized
FROM days
CROSS JOIN sensors
LEFT JOIN measurements m ON day = recorded_at AND m.sensor_id = sensors.id
)
SELECT
sensor_id,
min(day) AS start,
max(day) AS stop,
count(value) AS num_values,
min(value) AS min_value,
max(value) AS max_value,
avg(value) AS avg_value,
array_join(array_agg(coalesce(vertical_bar(normalized), ' ') ORDER BY day),
'') AS distribution
FROM normalized
WHERE sensor_id IS NOT NULL
GROUP BY sensor_id
ORDER BY sensor_id;
위 쿼리는 다음 출력을 만들어요:
sensor_id | start | stop | num_values | min_value | max_value | avg_value | distribution
-----------+------------+------------+------------+-----------+-----------+-----------+--------------
A | 2023-01-01 | 2023-01-11 | 8 | 1.00 | 15.00 | 8.38 | ▃ ▄█▇ ▅▁▄▄
B | 2023-01-01 | 2023-01-11 | 7 | 1.00 | 4.00 | 2.39 | ▄▆▅ ▆ █▃▂
Top-N
Trino에는 가장 자주 발생하는 값을 계산할 수 있는 내장 집계 함수 approx_most_frequent()가 이미 있어요. 값이 키이고 발생 횟수가 값인 맵을 반환해요. 맵은 순서가 없으므로, 표시할 때 항목이 같은 쿼리의 이후 실행에서 자리를 바꿀 수 있고, 독자는 가장 빈번한 값을 찾으려 여전히 모든 빈도를 비교해야 해요. 다음은 순서가 있는 결과를 문자열로 반환하는 SQL UDF예요:
FUNCTION format_topn(input map<varchar, bigint>)
RETURNS VARCHAR
NOT DETERMINISTIC
BEGIN
DECLARE freq_separator VARCHAR DEFAULT '=';
DECLARE entry_separator VARCHAR DEFAULT ', ';
RETURN array_join(transform(
reverse(array_sort(transform(
transform(
map_entries(input),
r -> cast(r AS row(key varchar, value bigint))
),
r -> cast(row(r.value, r.key) AS row(value bigint, key varchar)))
)),
r -> r.key || freq_separator || cast(r.value as varchar)),
entry_separator);
END;
생성된 문자열을 세는 예시 쿼리는 다음과 같아요:
WITH
data AS (
SELECT lpad('', 3, chr(65+(s.num / 3))) AS value
FROM table(sequence(start=>1, stop=>10)) AS s(num)
),
aggregated AS (
SELECT
array_agg(data.value ORDER BY data.value) AS all_values,
approx_most_frequent(3, data.value, 1000) AS top3
FROM data
)
SELECT
a.all_values,
a.top3,
format_topn(a.top3) AS top3_formatted
FROM aggregated a;
위 쿼리는 다음 결과를 만들어요:
all_values | top3 | top3_formatted
----------------------------------------------------+-----------------------+---------------------
[AAA, AAA, BBB, BBB, BBB, CCC, CCC, CCC, DDD, DDD] | {AAA=2, CCC=3, BBB=3} | CCC=3, BBB=3, AAA=2
더 알아보기 (Learn more)
SQL UDF 전반과 각 문(BEGIN, CASE, DECLARE, IF, LOOP 등)의 문법은 SQL 사용자 정의 함수 문서와 각 문 문서에서 다루고 있어요.