系统函数

函数简介

Table API & SQL提供给用户强大的操作数据的方法:函数(Function)。对于Function,可以有两种维度来对其分类。

第一种维度根据是否为系统内置(System)来分类。System Function是Flink提供的内置函数,在任何地方都可以直接拿来使用。非系统内置函数一般注册到一个Catalog下的Database里,该函数有自己的命名空间(Namespace),表示该函数归属于哪个Catalog和Database。例如,使用名为func的函数时需要加上Namespace前缀:mycatalog.mydb.func。由于函数被注册到Catalog中,这种函数被称为表目录函数(Catalog Function)。

第二种维度根据是否为临时函数来分类。临时函数(Temporary Function)只存在于一个Flink Session中,Session结束后就被销毁,其他Session无法使用。非临时函数,又被成为持久化函数(Persistent Function),可以存在于多个Flink Session中,它可以是一个System Function,也可以是一个Catalog Function。

根据这两个维度,Function可以被划分四类:

  • Temporary System Function
  • System Function
  • Temporary Catalog Function
  • Catalog Function

这些函数可以在Table API中使用Java、Scala或Python语言调用,也可以在Flink SQL中以SQL语句的形式调用。这里以SQL为例来介绍如何使用这些函数。绝大多数System Function已经内置在Table API & SQL中,它们也是Persistent Function,本节将主要介绍这部分内容,下节将介绍Catalog Function。由于System Function较多,这里只介绍一些常用的函数并提供一些例子,其他函数的具体使用方法可以参考Flink的官方文档。

标量函数

标量函数(Scalar Function)接收零个、一个或者多个输入,生成一个单值输出。

比较函数

  • value1 = value2

如果value1value2相等,返回TRUE;如果value1value2任何一个值为NULL,返回UNKNOWN

  • value1 <> value2

如果value1value2不相等,返回TRUE;如果value1value2任何一个值为NULL,返回UNKNOWN

  • value1 >= value2

如果value1大于等于value2,返回TRUE;如果value1value2任何一个值为NULL,返回UNKNOWN。其他><、<=比较函数与此相似。

  • value IS NULLvalue IS NOT NULL

判断value是否为NULL

  • value1 BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3

判断value1是否在一个区间。支持DOUBLEBIGINTINTVARCHARDATETIMESTAMPTIME这些类型。

例如,12 BETWEEN 15 AND 12返回FALSE12 BETWEEN SYMMETRIC 15 AND 12 returns TRUESYMMETRIC表示包含区间边界。value1 NOT BETWEEN [ ASYMMETRIC | SYMMETRIC ] value2 AND value3与之相似。

  • string1 LIKE string2

如果string1符合string2的模板,返回TRUELIKE主要用于字符串匹配,string2中可以使用 %来定义通配符。例如,'TEST' LIKE '%EST'返回TRUEstring1 NOT LIKE string2与之类似。

  • string1 SIMILAR TO string2

如果string1符合SQL正则表达式string2,返回TRUE。例如,'TEST' SIMILAR TO '.EST'返回TRUEstring1 NOT SIMILAR TO string2与之类似。

  • value1 IN (value2 [, value3]* ):如果value1在列表中,列表包括value2value3等元素,返回TRUE。例如,'TEST' IN ('west', 'TEST', 'rest')返回TRUE'TEST' IN ('west', 'rest')返回FALSEvalue1 NOT IN (value2 [, value3]* )与之类似。

  • EXISTS (sub-query):如果子查询有至少一行结果,返回TRUE。例如,下面的SQL语句使用了EXISTS,实际上起到了Join的作用:

    SELECT * 
    FROM l 
    WHERE EXISTS (select * from r where l.a = r.c)
    
  • value IN (sub-query):如果value等于子查询中的一行结果,返回TRUEvalue NOT IN (sub-query)与之类似。例如:

    SELECT * 
    FROM tab 
    WHERE a IN (SELECT c FROM r)
    

在流处理模式下,EXISTS(sub-query)value IN (sub-query)都需要使用状态进行计算,我们必须确保配置了状态过期时间,否则状态可能会无限增大。

逻辑函数

  • boolean1 OR boolean2

如果boolean1boolean2任何一个为TRUE,返回TRUE

  • boolean1 AND boolean2

如果boolean1boolean2都为TRUE,返回TRUE

  • NOT boolean

如果booleanTRUE,返回FALSEbooleanFALSE,返回TRUE

  • boolean IS FALSEboolean IS TRUEboolean IS UNKNOWN

根据boolean结果,判断是否为FALSETRUE或者UNKNOWNboolean IS NOT FALSE等与之类似。

数学函数

  • 加减乘除

加(+)减(-)乘(*)除(/)对数字字段做运算。下面的例子以加法为例,其他运算与之类似。

SELECT int1+int2 AS add
FROM tab 
  • ABS(numeric)

返回numeric的绝对值。

  • MOD(numeric1, numeric2)

余数函数,numeric1除以numeric2,返回余数。

  • SQRT(numeric)

平方根函数,返回numeric的平方根。

  • LN(numeric)LOG10(numeric)LOG2(numeric)

对数函数,返回numeric的对数,分别以e为底、以10为底和以2为底。

  • EXP(numeric)

指数函数,返回以e为底numeric的指数。

  • SIN(numeric)COS(numeric)

三角函数,包括SINCOSTAN等。

  • RAND()

返回0到1之间的一个伪随机数。

  • CEIL(numeric)FLOOR(numeric)

向上和向下取整。

字符串函数

  • string1 || string2

连接两个字符串。

  • CONCAT(string1, string2,...)

连接多个字符串。

  • CHAR_LENGTH(string)CHARACTER_LENGTH(string)

返回字符串string的长度。

  • SUBSTRING(string FROM start [ FOR length ])

对字符串做截断,返回string的一部分,从start位置开始,默认到字符串结尾结束,填写length参数后,字符串截断到length长度。

  • POSITION(string1 IN string2)

返回string1string2中第一次出现的位置,如果未曾出现则返回0。

  • TRIM([ BOTH | LEADING | TRAILING ] string1 FROM string2)

string2中出现的string1移除。BOTH选项表示移除左右两侧的字符串。一般情况下,如果不指定string1,默认移除空格。例如,TRIM(LEADING 'x' FROM 'xxxxSTRINGxxxx')返回STRINGxxxx

  • REGEXP_REPLACE(string1, string2, string3)

替换函数,将string1中符合正则表达式string2的字符全替换为string3。例如,REGEXP_REPLACE('foobar', 'oo|ar', '')移除了正则表达式oo|ar,返回fb

时间函数

  • DATE stringTIME stringTIMESTAMP string

将字符串string转换为java.sql.Datejava.sql.Timejava.sql.Timestamp。我们可以在WHERE语句中做过滤:

SELECT * FROM tab
WHERE b = DATE '1984-07-12' 
AND c = TIME '14:34:24' 
AND d = TIMESTAMP '1984-07-12 14:34:24

或者应用在SELECT语句中:

SELECT a, b, c,
 DATE '1984-07-12',
 TIME '14:34:24',
 TIMESTAMP '1984-07-12 14:34:24'
FROM tab
  • LOCALTIMELOCALTIMESTAMP

返回当前本地时间,格式为java.sql.Timejava.sql.Timestamp

  • YEAR(date)MONTH(date)DAYOFWEEK(date)

java.sql.Date转化为年月日。例如,YEAR(DATE '1994-09-27')返回为1994,MONTH(DATE '1994-09-27')返回为9,DAYOFYEAR(DATE '1994-09-27')返回为270。

  • HOUR(timestamp)MINUTE(timestamp)SECOND(timestamp)

java.sql.Timestamp转化为时分秒。例如,HOUR(TIMESTAMP '1994-09-27 13:14:15')返回13,MINUTE(TIMESTAMP '1994-09-27 13:14:15')返回14。

  • FLOOR(timepoint TO timeintervalunit)CEIL(timepoint TO timeintervalunit)

向下和向上取整。例如,FLOOR(TIME '12:44:31' TO MINUTE)返回12:44:00,CEIL(TIME '12:44:31' TO MINUTE)返回12:45:00。

判断函数

  • CASE ... WHEN ... END

类似很多编程语言提供的switch ... case ...判断逻辑。在Flink SQL中可以对某个字段进行判断,其模板为:

CASE value
	WHEN value1_1 [, value1_2 ]* THEN result1
	[ WHEN value2_1 [, value2_2 ]* THEN result2 ]*
	[ ELSE resultZ ]
END

例如,对表中字段a进行判断,生成一个新字段correct,SQL语句可以写为:

SELECT 
  CASE a
    WHEN 1 THEN 1
    ELSE 99
  END AS correct
FROM tab

也可以对一个表达式进行判断,其模板为:

CASE
	WHEN condition1 THEN result1
	[ WHEN condition2 THEN result2 ]*
	[ ELSE resultZ ]
END

例如,对表中字段c进行c > 0的判断,为TRUE时生成b,SQL语句可以写为:

SELECT 
	CASE 
		WHEN c > 0 THEN b 
		ELSE NULL 
	END 
FROM tab

类型转化

  • CAST(value AS type)

将字段value转化为类型type。例如,int1字段原本为INT,现将其转化为DOUBLE

SELECT CAST(int1 AS DOUBLE) as aa
FROM tab

集合函数

  • ARRAY ‘[’ value1 [, value2 ]* ‘]’

将多个字段连接成一个列表。例如,某表中两个字段ab均为INT类型,将其连接到一起,后面再添加一个数字99:

SELECT 
	ARRAY[a, b, 99] 
FROM tab
  • CARDINALITY(array)

返回列表中元素的个数。例如前面这个例子中:

SELECT CARDINALITY(arr) 
FROM (
  SELECT ARRAY[a, b, 99] AS arr FROM tab
)

ARRAY[a, b, 99]创建一个3个字段组成的列表,CARDINALITY(arr) 返回值为3。

聚合函数

窗口部分我们重点讲解了GROUP BYOVER WINDOW的窗口划分方式,聚合函数一般应用在窗口上,对窗口内的多行数据进行处理,并生成一个聚合后的结果。

  • COUNT([ ALL ] expression | DISTINCT expression1 [, expression2]*)

返回行数,默认情况下是开启了ALL选项,即返回所有行。使用DISTINCT选项后,对数据做去重处理。

  • AVG([ ALL | DISTINCT ] expression)

返回平均值,默认情况下开启了ALL选项。使用DISTINCT选项后,对数据做去重处理。

  • SUM([ ALL | DISTINCT ] expression)

对数据求和,默认情况下开启了ALL选项。使用DISTINCT选项后,对数据做去重处理。

  • MAX([ ALL | DISTINCT ] expression)MIN([ ALL | DISTINCT ] expression)

求数据中的最大值/最小值,默认情况下开启了ALL选项。使用DISTINCT选项后,对数据做去重处理。

  • STDDEV_POP([ ALL | DISTINCT ] expression)

求数据总体的标准差,默认情况下开启了ALL选项。使用DISTINCT选项后,对数据做去重处理。

时间单位

一些时间相关计算需要使用时间单位,常见的有YEARMONTHWEEKDAYHOURMINUTESECOND等。