Link Search Menu Expand Document Documentation Menu
文档

函数

对于大多数字符串函数，您必须在文档映射中启用 fielddata 才能使其正常工作。

规范显示函数的返回类型以泛型类型 T 作为参数。例如，abs(number T) -> T 表示函数 abs 接受类型为 T 的数值参数（可以是 number 类型的任何子类型），并返回 T 的实际类型作为返回类型。

SQL 插件支持以下在 SQL 和 PPL 语言中通用的函数。

数学函数

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

三角函数

功能 规范 示例
acos acos(number T) -> double SELECT acos(0.5)
asin asin(number T) -> double SELECT asin(0.5)
atan atan(number T) -> double SELECT atan(0.5)
atan2 atan2(number T, number T) -> double SELECT atan2(1, 0.5)
cos cos(number T) -> double SELECT cos(0.5)
cosh cosh(number T) -> double SELECT cosh(0.5)
cot cot(number T) -> double SELECT cot(0.5)
degrees degrees(number T) -> double SELECT degrees(0.5)
radians radians(number T) -> double SELECT radians(0.5)
sin sin(number T) -> double SELECT sin(0.5)
sinh sinh(number T) -> double SELECT sinh(0.5)
tan tan(number T) -> double SELECT tan(0.5)

日期和时间

标有星号 (*) 的函数仅在 SQL 中可用。

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

字符串

功能 规范 示例
ascii ascii(string) -> integer SELECT ascii('h')
concat concat(string, string) -> string SELECT concat('hello', 'world')
concat_ws concat_ws(separator, string, string…) -> string SELECT concat_ws(" ", "Hello", "World!")
left left(string, integer) -> string SELECT left('hello', 2)
length length(string) -> integer SELECT length('hello')
locate locate(string, string, integer) -> integer 或 locate(string, string) -> integer SELECT locate('o', 'hello'), locate('l', 'hello world', 5)
replace replace(string, string, string) -> string SELECT replace('hello', 'l', 'x')
right right(string, integer) -> string SELECT right('hello', 2)
rtrim rtrim(string) -> string SELECT rtrim('hello ')
substring substring(string, integer, integer) -> string SELECT substring('hello', 2, 2) -> 'el'
trim（修剪） trim(string) -> string SELECT trim(' hello')
upper upper(string) -> string SELECT upper('hello world')

聚合函数

功能 规范 示例
avg avg(number T) -> T SELECT avg(column) FROM my-index
count count(number T) -> T SELECT count(date) FROM my-index
min min(number T) -> T SELECT min(column) FROM my-index
show show(string) -> string SHOW TABLES LIKE my-index

高级函数

功能 规范 示例
if if(boolean, os_type, os_type) -> os_type SELECT if(false, 0, 1),if(true, 0, 1)
ifnull ifnull(os_type, os_type) -> os_type SELECT ifnull(0, 1), ifnull(null, 1)
isnull isnull(os_type) -> integer SELECT isnull(null), isnull(1)

基于相关性的搜索（全文搜索）

这些函数仅在 WHERE 子句中可用。有关它们在 SQL 和 PPL 中的描述和使用示例，请参阅 全文搜索。

剩余 350 字符

有问题？。

想要贡献？ 或 。