函数

大多数字符串函数需要先在文档映射中启用 fielddata 才能正常工作。

规范展示了函数的返回类型，使用泛型 T 作为参数。 例如，abs(number T) -> T 表示函数 abs 接受一个类型为 T 的数值参数，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	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 中的描述和使用示例，请参阅全文搜索。