SQL 및 PPL 공통 함수

SQL 및 PPL 공통 함수

OpenSearch는 SQL과 PPL 언어에서 공통으로 사용되는 다음 함수들을 지원해요. 대부분의 함수는 SQL(SELECT 함께)과 PPL(source 및 eval/where 함께) 모두에서 동작해요. SQL에서만 사용할 수 있는 함수는 별표(*)로 표시돼요.

출처: 문서

본문

대부분의 문자열 함수가 제대로 동작하려면 문서 매핑에서 필드 데이터(field data)를 활성화해야 해요.

명세에서 함수의 반환 타입을 일반 타입 T로 인자와 함께 표시해요. 예를 들어 abs(number T) -> T는 abs 함수가 number 타입의 하위 타입이 될 수 있는 T 타입의 숫자 인자를 받고, 반환 타입으로 T의 실제 타입을 반환한다는 뜻이에요.

수학 함수(Mathematical)

Function Specification Example
abs abs(number T) -> T abs(0.5)
add add(number T, number T) -> T add(1, 5)
cbrt cbrt(number T) -> double cbrt(8)
ceil ceil(number T) -> T ceil(0.5)
conv conv(string T, integer, integer) -> string conv('2C', 16, 10), conv(1111, 2, 10)
crc32 crc32(string) -> string crc32('MySQL')
divide divide(number T, number T) -> T divide(1, 0.5)
e e() -> double e()
exp exp(number T) -> double exp(0.5)
expm1 expm1(number T) -> double expm1(0.5)
floor floor(number T) -> long floor(0.5)
ln ln(number T) -> double ln(10)
log log(number T) -> double or log(number T, number T) -> double log(10), log(2, 16)
log2 log2(number T) -> double log2(10)
log10 log10(number T) -> double log10(100)
mod mod(number T, number T) -> T mod(10,4)
modulus modulus(number T, number T) -> T modulus(2, 3)
multiply multiply(number T, number T) -> T multiply(2, 3)
pi pi() -> double pi()
pow pow(number T, number T) -> double pow(2, 3)
power power(number T, number T) -> double power(2, 3)
rand rand() -> float or rand(number T) -> float rand(), rand(0.5)
rint rint(number T) -> double rint(1.5)
round round(number T) -> T or round(number T, integer) -> T round(1.5), round(1.175, 2)
sign sign(number T) -> integer sign(1.5)
signum signum(number T) -> integer signum(0.5)
sqrt sqrt(number T) -> double sqrt(0.5)
strcmp* strcmp(string T, string T) -> integer strcmp('hello', 'hello world')
subtract subtract(number T, number T) -> T subtract(3, 2)
truncate truncate(number T, number T) -> T truncate(56.78, 1)
+ number T + number T -> T 1 + 5
- number T - number T -> T 3 - 2
* number T * number T -> T 2 * 3
/ number T / number T -> T 1 / 0.5
% number T % number T -> T 2 % 3

삼각 함수(Trigonometric)

Function Specification Example
acos acos(number T) -> double acos(0.5)
asin asin(number T) -> double asin(0.5)
atan atan(number T) -> double atan(0.5)
atan2 atan2(number T, number T) -> double atan2(1, 0.5)
cos cos(number T) -> double cos(0.5)
cosh cosh(number T) -> double cosh(0.5)
cot cot(number T) -> double cot(0.5)
degrees degrees(number T) -> double degrees(0.5)
radians radians(number T) -> double radians(0.5)
sin sin(number T) -> double sin(0.5)
sinh sinh(number T) -> double sinh(0.5)
tan tan(number T) -> double tan(0.5)

날짜 및 시간 함수(Date and time)

Function Specification Example
adddate adddate(date, INTERVAL expr unit) -> date adddate(date('2020-08-26'), INTERVAL 1 hour)
addtime addtime(date, date) -> date addtime(date('2008-12-12'), date('2008-12-12'))
convert_tz convert_tz(date, string, string) -> date convert_tz('2008-12-25 05:30:00', '+00:00', 'America/Los_Angeles')
curtime curtime() -> time curtime()
curdate curdate() -> date curdate()
current_date current_date() -> date current_date()
current_time current_time() -> time current_time()
current_timestamp current_timestamp() -> date current_timestamp()
date date(date) -> date date('2000-01-02')
datediff datediff(date, date) -> integer datediff(date('2000-01-02'), date('2000-01-01'))
datetime datetime(string) -> datetime datetime('2008-12-25 00:00:00')
date_add date_add(date, INTERVAL integer UNIT) date_add('2020-08-26', INTERVAL 1 HOUR)
date_format date_format(date, string) -> string or date_format(date, string, string) -> string date_format(date('2020-08-26'), 'Y')
date_sub date_sub(date, INTERVAL expr unit) -> date date_sub(date('2008-01-02'), INTERVAL 31 day)
dayofmonth dayofmonth(date) -> integer dayofmonth(date('2001-05-07'))
day day(date) -> integer day(date('2020-08-25'))
dayname dayname(date) -> string dayname(date('2020-08-26'))
dayofmonth dayofmonth(date) -> integer dayofmonth(date('2020-08-26'))
dayofweek dayofweek(date) -> integer dayofweek(date('2020-08-26'))
dayofyear dayofyear(date) -> integer dayofyear(date('2020-08-26'))
dayofweek dayofweek(date) -> integer dayofweek(date('2020-08-26'))
day_of_month day_of_month(date) -> integer day_of_month(date('2020-08-26'))
day_of_week day_of_week(date) -> integer day_of_week(date('2020-08-26'))
day_of_year day_of_year(date) -> integer day_of_year(date('2020-08-26'))
extract extract(part FROM date) -> integer extract(MONTH FROM datetime('2020-08-26 10:11:12'))
from_days from_days(N) -> integer from_days(733687)
from_unixtime from_unixtime(N) -> date from_unixtime(1220249547)
get_format get_format(PART, string) -> string get_format(DATE, 'USA')
hour hour(time) -> integer hour(time '01:02:03')
hour_of_day hour_of_day(time) -> integer hour_of_day(time '01:02:03')
last_day last_day(date) -> integer last_day(date('2020-08-26'))
localtime localtime() -> date localtime()
localtimestamp localtimestamp() -> date localtimestamp()
makedate makedate(double, double) -> date makedate(1945, 5.9)
maketime maketime(integer, integer, integer) -> date maketime(1, 2, 3)
microsecond microsecond(expr) -> integer microsecond(time '01:02:03.123456')
minute minute(expr) -> integer minute(time '01:02:03')
minute_of_day minute_of_day(expr) -> integer minute_of_day(time '01:02:03')
minute_of_hour minute_of_hour(expr) -> integer minute_of_hour(time '01:02:03')
month month(date) -> integer month(date('2020-08-26'))
month_of_year month_of_year(date) -> integer month_of_year(date('2020-08-26'))
monthname monthname(date) -> string monthname(date('2020-08-26'))
now now() -> date now()
period_add period_add(integer, integer) period_add(200801, 2)
period_diff period_diff(integer, integer) period_diff(200802, 200703)
quarter quarter(date) -> integer quarter(date('2020-08-26'))
second second(time) -> integer second(time '01:02:03')
second_of_minute second_of_minute(time) -> integer second_of_minute(time '01:02:03')
sec_to_time sec_to_time(integer) -> date sec_to_time(10000)
subdate subdate(date, INTERVAL expr unit) -> date, datetime subdate(date('2008-01-02'), INTERVAL 31 day)
subtime subtime(date, date) -> date subtime(date('2008-12-12'), date('2008-11-15'))
str_to_date str_to_date(string, format) -> date str_to_date("01,5,2013", "%d,%m,%Y")
time time(expr) -> time time('13:49:00')
timediff timediff(time, time) -> time timediff(time('23:59:59'), time('13:00:00'))
timestamp timestamp(date) -> date timestamp('2001-05-07 00:00:00')
timestampadd timestampadd(interval, integer, date) -> date) timestampadd(DAY, 17, datetime('2000-01-01 00:00:00'))
timestampdiff timestampdiff(interval, date, date) -> integer timestampdiff(YEAR, '1997-01-01 00:00:00', '2001-03-06 00:00:00')
time_format time_format(date, string) -> string time_format('1998-01-31 13:14:15.012345', '%f %H %h %I %i %p %r %S %s %T')
time_to_sec time_to_sec(time) -> long time_to_sec(time '22:23:00')
to_days to_days(date) -> long to_days(date '2008-10-07')
to_seconds to_seconds(date) -> integer to_seconds(date('2008-10-07'))
unix_timestamp unix_timestamp(date) -> double unix_timestamp(timestamp('1996-11-15 17:05:42'))
utc_date utc_date() -> date utc_date()
utc_time utc_time() -> date utc_time()
utc_timestamp utc_timestamp() -> date utc_timestamp()
week week(date[mode]) -> integer week(date('2008-02-20'))
weekofyear weekofyear(date[mode]) -> integer weekofyear(date('2008-02-20'))
week_of_year week_of_year(date[mode]) -> integer week_of_year(date('2008-02-20'))
year year(date) -> integer year(date('2001-07-05'))
yearweek yearweek(date[mode]) -> integer yearweek(date('2008-02-20'))

문자열 함수(String)

Function Specification Example
ascii* ascii(string) -> integer ascii('h')
concat concat(string, string) -> string concat('hello', 'world')
concat_ws concat_ws(separator, string, string…) -> string concat_ws(" ", "Hello", "World!")
left* left(string, integer) -> string left('hello', 2)
length length(string) -> integer length('hello')
locate locate(string, string, integer) -> integer or locate(string, string) -> integer locate('o', 'hello'), locate('l', 'hello world', 5)
replace replace(string, string, string) -> string replace('hello', 'l', 'x')
right right(string, integer) -> string right('hello', 2)
rtrim rtrim(string) -> string rtrim('hello ')
substring substring(string, integer, integer) -> string substring('hello', 2, 2)
trim trim(string) -> string trim(' hello')
upper upper(string) -> string upper('hello world')

집계 함수(Aggregate)

Function Specification Example
avg avg(number T) -> T avg(column)
count count(number T) -> T count(date)
min min(number T) -> T min(column)
show* show(string) -> string SHOW TABLES LIKE my-index

고급 함수(Advanced)

Function Specification Example
if if(boolean, os_type, os_type) -> os_type if(false, 0, 1), if(true, 0, 1)
ifnull ifnull(os_type, os_type) -> os_type ifnull(0, 1), ifnull(null, 1)
isnull isnull(os_type) -> integer isnull(null), isnull(1)

관련성 기반 검색(Relevance-based search, full-text search)

이 함수들은 WHERE 절에서만 사용할 수 있어요. SQL과 PPL에서의 설명과 사용 예시는 전문 검색(Full-text search)을 참조해요.

더 알아보기 (Learn more)