17 October 2015
TDW 函数在线手册 - TDW WIKI

TDW 函数在线手册

出自TDW WIKI

跳转到: 导航, 搜索

目录

函数帮助信息

在TDW客户端通过一下命令,可以查看TDW支持的内置函数

显示所有内置函数:

SHOW FUNCTIONS;

显示一个内置函数的简要说明:

DESCRIBE FUNCTION <function_name>;

显示一个内置函数的更详细说明:

DESCRIBE FUNCTION EXTENDED <function_name>;

TDW扩展函数

普通函数

返回值 函数名 说明
string

add_months( date, n )

Returns a date plus n months.

date is the starting date (before the n months have been added). n is the number of months to add to date. 注:目前输入的date格式为"yyyy-MM-dd"或者"yyyyMMdd"(以这个格式开头的都可以接受,否则返回null,下面同上),返回字符串也是这个格式。 注:n为int型整数(Hive中int型范围为正负2147483647区间,bigint类型的范围为正负9223372036854775807,以下同上),表示在date上增减的月份数。 注:输入如“2008-1-33”的情况不会报错,当作“2008-2-2”处理,下面几个函数同上。 本函数将时间年限范围设定为[1900, 9999], 暂不支持公元前纪年,时间输入错误或者是计算溢出则返回null。

string

trunc( date, [ format ] )

Returns a date truncated to a specific unit of measure.

date is the date to truncate. format is the unit of measure to apply for truncating. If the format parameter is omitted, the function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off. 目前函数的date参数的输入格式为"yyyy-MM-dd HH:mm:ss"(字符串以这个格式开头即可,否则返回null),返回字符串也是这个格式。 注:输入如“2008-1-33”的情况不会报错,当作“2008-2-2”处理。

目前支持的format形式有:

Year SYYYY, YYYY, YEAR,SYEAR, YYY, YY, Y

Month MONTH, MON, MM, RM

Week WW

W W

Day DDD, DD, J

Start day of the week DAY, DY, D

Hour HH, HH12, HH24

Minute MI

暂不支持ISO Year,Quarter和IW。例如:

trunc('22-AUG-03 11:11:11', 'YEAR') '01-JAN-03 00:00:00'

trunc('22-AUG-03 11:11:11', 'MONTH') '01-AUG-03 00:00:00'

trunc('22-AUG-03 11:11:11', 'DDD') '22-AUG-03 00:00:00'

trunc(22-AUG-03 11:11:11', 'DAY') '17-AUG-03 00:00:00'

Week 表示日期在这一年的第几个星期。 W表示日期在本月的第几个星期。 Start day of the week表示这个星期的第一天。 其他year,month,day,hour和minute分别是对应于标准格式yyyy-MM-dd HH:mm:ss。 本函数将时间年限范围设定为[1900, 9999], 暂不支持公元前纪年,时间输入错误则返回null。

double

trunc( number, [ decimal_places ] )

Returns a number truncated to a certain number of decimal_places.

Number目前支持double和int,decimal_places必须为整数,如果没有decimal_places则默认去掉小数部分。 例如: trunc(125.815) would return 125

trunc(125.815, 2) would return 125.81

trunc(125.815, -2) would return 1E+2

trunc(125, -2) would return 1E+2

Number也可为整型,用法一致。 注:如出现trunc(125.815,20)的情况则会在125.815后面补充17个0。

double

months_between( date1, date2 )

Returns the number of months between date1 and date2.

If a fractional month is calculated, the function calculates the fraction based on a 31-day month. date格式为"yyyy-MM-dd"或者"yyyyMMdd"的字符串(字符串以这个格式开头即可,否则返回null)。 例如: months_between(‘2003-01-01’,‘2003-03-14’) return -2.41935483870968 如果两个日期位置交换则返回的是正值。 本函数将时间年限范围设定为[1900, 9999], 暂不支持公元前纪年,时间输入错误则返回null。 注:输入如“2008-1-33”的情况不会报错,当作“2008-2-2”处理。

string

convert( string1 , char_set_to , char_set_from)

该函数目前不再使用,070以后版本中insert语句支持utf8和gbk之间的转换,只需要设置table的字符集属性。

int

last_day(date)

Returns the last day of the month extracted from the provided date value argument.

date格式为"yyyy-MM-dd"的字符串(开头为该形式即可),返回的值是1到31之间的值,如果输入不正确则返回NULL。yyyy-MM-dd 是事先约定的输入格式。 本函数将时间年限范围设定为[1900, 9999], 暂不支持公元前纪年,时间输入错误则返回null。 注:输入如“2008-1-33”的情况不会报错,当作“2008-2-2”处理。

string

tz_offset( timezone )

Returns the time zone offset of a value.

timezone can be a valid time zone name, a time zone offset from UTC, or the sessiontimezone function. "America/Los_Angeles" or "PST" or "-08:00" or "+08:00" or "GMT+8" or "GMT-07:00" ,type wrong input will return "+00:00". "08:00"is wrong, must like "+08:00".

String

sessiontimezone()

返回当前会话时区的时区值。
byte

sign( number )

Returns a value indicating the sign of a number.

If number < 0, then sign returns -1.If number = 0, then sign returns 0.If number > 0, then sign returns 1. 如果输入的参数不符合标准则返回NULL。 目前支持byte,short,int,long,float和double。

string

chr( number_code )

Returns the character based on the NUMBER code. number_code is the NUMBER code used to retrieve the character.

该函数的输入范围为[0,127],超出这个范围则会返回NULL,[0,32]范围内的结果是不可见的。

boolean/byte/short/int

/long/double/float/string

least( expr1, expr2, ... expr_n )

Returns the smallest value in a list of expressions.

expr1, expr2, . expr_n are expressions that are evaluated by the least function. Having a NULL value in one of the expressions will return NULL as the least value. 允许不同的数字类型double, float, bigint, int等的比较,但字符串只能与字符串做比较。 如果比较的是字符串,暂不支持转义符,如果字符串中含有"/"需要写成"//"。

boolean/byte/short/int

/long/double/float/string

greatest( expr1, expr2, ... expr_n )

Returns the greatest value in a list of expressions.

expr1, expr2, . expr_n are expressions that are evaluated by the greatest function. Having a NULL value in one of the expressions will return NULL as the greatest value. 允许不同的数字类型double, float, bigint, int等的比较,但字符串只能与字符串做比较。 如果比较的是字符串,暂不支持转义符,如果字符串中含有"/"需要写成"//"。

boolean/byte/short/int

/long/double/float/string

NVL(expr1,expr2 )

如果expr1为NULL,则返回expr2,否则返回expr1,其中expr1和expr2的类型必须一样。参数类型同返回值的类型一致。(若参数类型不一致,函数的行为不可预见,可能执行正确,可能执行失败)
boolean/byte/short/int

/long/double/float/string

NVL2(expr1,expr2, expr3)

expr1为NULL,则返回expr3,否则返回expr2;其中expr1、expr2、expr3的类型要求一致(会做一定兼容,比如float和double,会隐式将float转成double,并不报错)。
string

SYSTIMESTAMP()

返回当前的系统日期、时间(不支持时区);输出时间格式为:yyyy-MM-dd hh24:mi:ss:ff3,当前仅支持24小时制。连续两次调用可能会在毫秒上有一点的偏差。
int

INSTR((string,str[,start][,appear])

返回string中str出现的位置。start代表开始搜索的位置,可选参数,默认为1(1表示第一个字符位置);appear指示搜索第几次出现的,可选参数,默认为1;若没有匹配到,返回0。(start和appear取值为大于0的整数)
int/long/float/double

MOD( n1, n2 )

返回一个n1除以n2的余数。支持的类型有Byte、Short、Integer、Long、Float、Double。返回值的正负和n1相关。使用此函数需要注意的2个问题:

1、对小数执行mod计算可能会产生精度丢失,(如mod(3.1415926535897384626,3.1415926535897384627,返回结果为0.0))此问题由java环境本身导致,若需要高精度的浮点计算,慎用此函数;

2、传入比MAX_LONG还大的整数作为参数,则参数会被自动升级成Double类型,函数也可以正常计算结果,但返回的结果是小数类型。

int

BITAND(expr1, expr2)

指定按位进行 AND 运算的两个数值。当前只支持Byte、Short、Integer、Long类型。如果 expr1和 expr2的位都是 1,相应的结果位就是 1;否则相应的结果位是 0。若expr1和expr2类型不一样,直接进行and计算
String

NEXT_DAY(date,day)

计算给出日期date之后的下一个星期day的日期。day是数字, 1-7分别表示星期日-六;返回日期的格式为“YYYY-MM-DD”
int

TO_NUMBER(string)

将给出的字符转换为数字;string必须为全数字串。(Oracle中的to_number很复杂,可变参数且支持多种类型。当前TDW中仅支持整数类型,包括short、int、long)
String

TO_CHAR(date,format)

将日期data转化为一个字符串;date的格式固定为yyyy-mm-dd hh24:mi:ss:ff3,输出的格式由format指定。

format当前支持的格式如下(不区分大小写):

yyyymmdd, 年月日;

yyyymm,年月;

mm,月

dd,日

yyyy-mm-dd

yyyy-mm

yyyymmddhh24miss,年月日时分秒(24小时制)

yyyy-mm-dd hh24:mi:ss

hh24miss

yyyymmddhh24missff3,年月日时分秒毫秒(24小时制)

String

TO_DATE(date,format)

将字符串转化为一个日期;format指定了date的解析方式,支持的格式同 to_char。不支持默认的format,必须指定format。如果format不正确或者为空,则可能返回null或者执行出错。
Boolean

expr [NOT] IN (expr1[, expr2,...])

该函数通常用于select、where和having语句中,用于检查表达式expr的值是否位于列表(expr1[, expr2,...])中,列表中各值的类型需要与expr的类型一致,允许列

表中有NULL值。该函数与ORACLE中IN函数的区别在于,不允许列表中的表达式为子查询,列表为子查询的功能将由EXIST/NOT EXIST函数实现。

关于IN/NOT IN中NULL值的处理(下面的说明中,用A、B指代任意非NULL值):

1. col IN(A,B,NULL):如果col的值为NULL,则认为col符合IN的条件;

2. col IN(A,B):如果col的值为NULL,则认为col不符合IN的条件;

3. col NOT IN(A,B,NULL):如果col的值为NULL,则认为col不符合NOT IN的条件;

4. col NOT IN(A,B):如果col的值为NULL,则认为col符合NOT IN的条件。

另,自100版本后,IN/NOT IN将支持expr、expr1等表达式的类型在int/bigint类型间的自动转换,用户不再需要使用cast进行强制类型转换。

注意:where子句中分区字段使用in的话是不会做分区剪枝的,如果需要分区剪枝,建议替换成比较操作符。

Boolean

[NOT] EXISTS (SELECT ... FROM ... WHERE ...)

该函数用于WHERE语句中,自100版本后TDW支持在同一个WHERE语句中有两个或以上的EXISTS语句,但是,EXISTS/NOT EXISTS语句不允许位于OR和NOT表达式中。例如,NOT (EXISTS(...) AND ...)或 EXISTS(...) OR ...。EXISTS语句中的子查询而言,目前只允许使用SELECT、FROM、WHERE3个语句,如果涉及到更复杂的形式,请以更深层子查询的形式嵌套处理。EXISTS语句中的WHERE语句只能够允许有AND连接词,不可以包含OR连接词。另,EXISTS语句中的数据源不允许是JOIN数据源。
string

wm_concat(col1[,splitstr[,'desc'[,col2]]])

将列col1的字段连接为一行,col1是需要concat的列名,必选参数;splitstr为连接字段之间的分隔符,可选参数,默认无分隔符;'desc'字段表示升序或者降序,只能取输入字符串'asc'(升序)或者'desc'(降序),可选字段,默认不排序;col2表示排序字段,按照这个字段进行排序对col1进行concat,可选字段,默认是col1字段本身。

以表tbl(a,b,c)为例:

wm_concat(a)                  对a字段无分隔符不排序concat
wm_concat(a,'-')              对a字段使用'-'作为分隔符不排序concat
wm_concat(a,'-','asc')        对a字段使用'-'作为分隔符,按照a的升序concat
wm_concat(a,'-','desc')       对a字段使用'-'作为分隔符,按照a的降序concat
wm_concat(a,'-','asc',b)      对a字段使用'-'作为分隔符,按照b的升序concat
wm_concat(a,'-','desc',b)     对a字段使用'-'作为分隔符,按照b的降序concat
wm_concat(a,' ','asc',b)              对a字段以空格为分隔符,按照b的升序concat
wm_concat(distinct a,' ','asc')       对a字段去重以空格为分隔符,按照a的升序concat
wm_concat(distinct a,' ','asc',b)     结果可能存在一定的不确定性

注1:参数数目最多为4最少为1,参数的位置严格定义:第一个参数表示合并字段,第二个参数表示分隔符,第三个字段表示升降序,第四个字段表示排序字段。如果需要使用后面的参数,则前面的参数也必须列出,例如第二个参数,即使不想使用分隔符,在需要第三个参数的时候第二个参数也必须以' '的形式列出。

注2:a字段可以带Distinct操作,可以以a字段本身进行升序或者降序排序。如果带有另外一个排序字段b,处理策略是,首先对a进行Distinct操作,然后按照b字段排序进行连接,这里面有一个问题,如下面的例子所示:

表tbl

a b

1 3

1 10

2 5

Select wm_concat(distinct a,,'asc',b) from tbl; a=1的记录有两行,在进行Distinct操作的时候会只保留一行,因为记录的输入顺序不定,所以这样的情况下可能导致输出结果不一样。 如果保留了第一行,则输出结果是:12,如果保留了第二行的话则输出结果是21。

注3:连接字段可以为任意类型,但是输出均为string类型,并且输出的string字段长度不能超过32KB,否则报错。

注4:分隔符字段理论上可以使用任意字符,但是换行符和回车符除外。

boolean/byte/short/int

/long/double/float/string

decode(expression , search , result [, search , result]... [, default])

decode函数与一系列嵌套的IF-THEN-ELSE语句相似。expression与search等依次进行比较。如果expression和第i个search项匹配,就返回第i个对应的result。如果expression与任何的search值都不匹配,则返回default。如果default没有,则返回null。该函数参数是变长的,最少3个参数,最多255个参数。所有的result和default需要保持类型一致,expression与所有的search也需要保持类型一致(这两组参数支持int和bigint类型混合使用,混合情况下返回值类型为bigint),参数类型由expression和第一个result所决定。expression和第一个result输入不可以为null(表字段不受该限制)。如果比较的是字符串,暂不支持转义符。示例:SELECT decode(supplier_id,10000,'IBM',10001,'Microsoft',10002,'Hewlett Packard','Gateway') FROM suppliers;

在decode函数中null和null比较是不会匹配的,这点可能和oracle不一样,如有疑问可以联系michealxu

string

a || b 作为concat函数的简化

[R100版本开始支持] 使用select a || b from tbl 来替代 select concat(a,b) from tbl 来简化字符串连接操作。||可以看做一个运算符,其优先级别和+,-相同


byte/short/int/long/float/double/string

between

a between b and c 等同于 a>=b and a<=c,但该函数暂不支持bool类型,如果a,b,c中含有null则该次比较结果为false。

注意:between目前还不支持分区剪枝,也就是说对于分区列使用between,仍然会扫描全部分区。目前分区剪枝只支持 =,>,<,<=,>=这些简单的操作符,未来TDW版本是否支持between以及其他函数的分区剪枝还不确定,所以最保险的办法是对分区列使用上面的简单操作符

string

numformat

numformat(a)将double类型数据a转成字符串,如果a是科学计数法则将其转成原始记法。 numformat(a,b,c,d,e)b表示小数位的最大保留位数,c表示小数位的最小保留位数,d表示整数位的最大保留位数,e表示整数位的最小保留位数 注意:该函数主要给不需要科学计数法的用户使用,而且保留的最大位数一定要大于保留的最小位数,值为非负

string

commonStr(string,string,string)

commonStr(col1,col2,separator): col1和col2为使用separator指定的分隔符连接的字符串,separator为分隔符,该函数找出col1和col2中使用分隔符隔开的相同的单词,并返回使用分隔符连接这些相同单词的字符串。 col1 and col2 必须是字符串类型, col1 and col2 必须使用相同的分隔符。 当col1和col2没有相同字符串时,返回空字符串“”。 当col1和col2有一个为null时,返回null。 函数对分隔符没有限制! 例如:

  > SELECT commonStr('a,b,c,d','d,c',',') FROM src LIMIT 1;
d,c
> SELECT commonStr(' ab,bc,ac','ac,bc',',') FROM src LIMIT 1;
ac,bc
string

md5(long/double/int/string,long/double/int/string)

md5(col1,col2): col1和col2为列名或者常量,支持long/double/int/string类型,该函数把col1和col2作为字符串连接在一起进行md5加密处理,返回加密后的二进制字符串。 例如:

  > SELECT md5(11,2),md5(1,'12'),md5('1','1.2'),md5(1,1.2);
  7f6ffaa6bb0b408017b62254211691b5
  7f6ffaa6bb0b408017b62254211691b5
  17d49ab14f0d4a8bbffe14ad3d6b7b13
  17d49ab14f0d4a8bbffe14ad3d6b7b13

特别说明

TDW中所有关于日期处理函数中date格式的约定:

a、类似如yyyy-mm-dd hh24:mi:ss:ff3的格式中,严格遵循4位数字表示年,2位数字表示月,2为数字表示日等等

b、对于需要返回date类型的函数(如to_date),系统对输入的字符串执行弱校验,同hive保持一致,如能将用户输入的”2010-01-32”正确转换成”2010-02-01”;

c、对于输入date类型的函数(如to_char),系统对输入的date合法性不执行检查,若用户输入的date为不合法的“2010-01-32”则返回的字符串依旧为“2010-01-32”。

d、另外,各个时间处理函数中,只关心需要的精度,若精度不够,则会执行失败;否则忽略多余的精度。 如next_day中只关心到yyyy-mm-dd,而忽略所有的hh24:mi:ss:ff3信息。

e、在没有指定format的时候,缺省使用yyyy-mm-dd hh24:mi:ss:ff3。

高级分析函数

返回值 函数名 说明
返回类型取决于value_expr的类型

LAG ( value_expr [, offset ] [, default] )OVER ( [query_partition_clause] order_by_clause )

通过该函数,可以不通过自连接同时去访问结果集中的其它行,它允许你像处理数组一样的去处理游标。给定一个查询结果集和游标的位置,就可以访问与当前行一起选择的以前的行。其相反的函数是LEAD。

1.offset是一个正整数,在用户没有设置的情况下其默认值为1,表示当前行的前面一行,若索引超出窗口的范围,就返回default值;

2.在用户没有设置default值的情况下,缺省默认返回NULL,用户可以根据所处理列的类型自行设定默认值。例如,如果该列为整型,则可以将默认值设为整数或NULL;

3.如果为浮点型,则可以将默认值设为浮点数或NULL;如果为字符串类型,则可以将默认值设为用单引号或双引号标识的字符串常量或NULL;如果是布尔型,则可以设为布尔值或NULL。

返回类型取决于value_expr的类型

LEAD (value_expr [, offset ] [, default ] ) OVER ( [query_partition_clause] order_by_clause )

含义与LAG相反,只是它返回的是当前行的后面的行,其余部分和LAG函数一模一样。
bigint

RANK ( ) OVER ( [query_partition_clause] order_by_clause )

1.此函数没有参数,可以计算数据项在分区中的排名。在每个分区内,根据ORDER BY子句中表达式的值,计算查询返回的每一行与其它行的相对位置。组内的数据按ORDER BY子句排序,然后给每一行赋一个号,从而形成一个序列,该序列从1开始,往后累加,有同样值的行得到同样的数字序号(认为NULL是相等的)。每次ORDER BY表达式的值发生变化时,该序列也随之增加。然而,如果两行得到同样的排序,则序数将随后跳跃,如两行序数为1,则没有序数

2.序列将给组中的下一行分配值3,DENSE_RANK则没有任何跳跃,这也是它与DENSE_RANK的唯一区别

3.默认情况下,RANK()和DENSE_RANK()在递增排序中将空值指定为最低序号1,在递减排序中将空值指定为最高序号

bigint

DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

1.此函数没有参数,在每个分区内,根据ORDER BY子句中表达式的值,计算查询返回的每一行与其它行的相对位置。组内的数据按ORDER BY子句排序,然后给每一行赋一个号,从而形成一个序列,该序列从1开始,往后累加,有同样值的行得到同样的数字序号(认为NULL是相等的)。每次ORDER BY表达式的值发生变化时,该序号也随之增加,序号返回的时没有间隔的数。

2.RANK和DENSE_RANK的区别在于处理相等数据项的方法;RANK()在出现等级相同的元素时就将排名中的位置留出来,而DENSE_RANK()则不是。

bigint

ROW_NUMBER ( ) OVER ( [query_partition_clause] order_by_clause )

返回每个分区内按照某些表达式排序后的行号,从1开始,为每条分组记录返回一个数字。NULL值被视为最小值。
Bigint或double,具体返回类型取决于value_expr的类型

SUM ( [ DISTINCT ] expr )OVER ( [query_partition_clause] [order_by_clause ] )

1.该函数计算组中表达式的累积和;如果带有distinct,则返回的是该列中不同的值的总和。

2.OVER子句中最多只能有分区子句。

(3.如果应用中需要用到OVER子句为空的SQL语句,则可以通过使用聚集函数SUM来改写SQL实现一样的功能。)100版本后取消该限制。

bigint

COUNT( 1 | [ DISTINCT ] expr)OVER ( [query_partition_clause] [order_by_clause] )

1.用于计算一个查询返回的行数。如果参数是“1”,那么它返回所有行的总数,目前,count(1)仅限带query_partition_clause的情况下使用;如果参数是某个列,那么,它返回这个列的值非NULL的行的总数;如果带有distinct,则返回的是该列中不同值的数目。

2.OVER子句中最多只能有分区子句。(如果应用中需要用到OVER子句为空的SQL语句,则可以通过使用聚集函数COUNT来改写SQL实现一样的功能。)100版本后取消该限制。

double

AVG ( [ DISTINCT ] expr )OVER ([query_partition_clause] [order_by_clause] )

1.用于计算平均值;如果带有distinct,则返回的是该列中不同的值的平均数。

2.OVER子句中最多只能有分区子句。(如果应用中需要用到OVER子句为空的SQL语句,则可以通过使用聚集函数AVG来改写SQL实现一样的功能。)100版本后取消该限制。

返回类型取决于expr的类型

MAX ( [ DISTINCT ] expr )OVER ( [query_partition_clause] [order_by_clause] )

1.在一个分区中查找表达式的最大值;如果带有distinct,则返回的是该列中不同值的最大值。

2.OVER子句中最多只能有分区子句。(如果应用中需要用到OVER子句为空的SQL语句,则可以通过使用聚集函数MAX来改写SQL实现一样的功能。)100版本后取消该限制。

返回类型取决于expr的类型

MIN ( [ DISTINCT ] expr )OVER ( [query_partition_clause] [order_by_clause] )

1.在一个分区中查找表达式的最小值;如果带有distinct,则返回的是该列中不同值的最小值。

2.OVER子句中最多只能有分区子句。(如果应用中需要用到OVER子句为空的SQL语句,则可以通过使用聚集函数MIN来改写SQL实现一样的功能。)100版本后取消该限制。

double

RATIO_TO_REPORT ( expr )OVER ( [query_partition_clause] [order_by_clause] )

1.用来计算某个值在一组值的总和中所占的比率。

2.OVER子句中最多只能有分区子句。(如果OVER语句中没有分区子句,则该分析函数的实现方法与SUM类似,完全可以通过改写SQL实现该功能。改写的具体做法是使用一个SQL计算出某一列的总和,然后,用每行中要计算该分析函数的那列值来除该总和即可。)100版本后取消该限制。

返回类型取决于expr的类型

FIRST_VALUE ( expr ) OVER ( [query_partition_clause] [order_by_clause] )

1.用来计算一组值的第一个值

2.order子句必须包含

返回类型取决于expr的类型

LAST_VALUE ( expr ) OVER ( [query_partition_clause] [order_by_clause] )

1.用来计算一组值的最后一个值

2.order子句必须包含

特别说明

在上表中,用方括号括起来的部分是可选项。 在上表中用到的分区子句query_partition_clause和分区排序子句order_by_clause定义如下:

Query_Partition_Clause

分区子句的语法是:

PARTITION BY (exp1[,exp2,...expN])

分区子句是在逻辑上对前面计算返回的记录集,即经过FROM/WHERE/GROUP BY/JOINS“筛选”后的结果集,按照exp1[,exp2,...expN]进行分组。其实,这里就和SELECT中的GROUP BY子句的功能有些类似,就是按照某些列对结果集分组。因此,在这里,单词“patition”和“group”是同义词。分析函数被独立作用于每个分组,并在每个分组上被重置。由于涉及到计算效率和任务成败,TDW要求所有的分析函数必须包含分区子句。

Order_By_Clause

  分区排序子句的作用是指定一个数据分区内,数据记录是如何排序的。你可以指定多个列来指定排序。该子句功能有点类似SELECT中的ORDER BY子句。在用户没有指定的情况下默认为增序排列。

分区排序子句的语法如下:

 ORDER BY expr [ DESC | ASC ]
         [,expr [ DESC | ASC ]
         ]...


限定

根据应用的需求和实现难度,对同一个SELECT子句中出现的分析函数,我们有如下限定条件。由于分析函数是无损的,对数据分析完成后,数据行数不变,因此,以下限制不会对应用产生太大影响,应用基本可以通过改写SQL实现与ORACLE类似的功能:

分组函数(聚合函数)与分析函数不可以同时存在。比如,SUM(COUNT(col)) OVER(...)之类的语句,可以通过改写SQL,先进行分组聚合分析,然后使用分析函数。例如:

SELECT 
Month, prd_type_id,
SUM(SUM(amount)) OVER (PARTITION BY month) AS total_month_amount,
SUM(SUM(amount)) OVER (PARTITION BY prd_type_id) AS total_product_type_amount
FROM all_sales
GROUP BY month, prd_type_id

上面这个例子融合了限制条件1和限制条件2,可以改写为如下的SQL语句:

SELECT b.month, b.prd_type_id,
  b.total_month_amount,
  SUM(b.sum_month_prd) OVER (PARTITION BY prd_type_id) AS total_product_type_amount,
FROM
(SELECT a.month, a.prd_type_id, a.sum_month_prd,
    SUM(a.sum_month_prd) OVER (PARTITION BY month) AS total_month_amount
    FROM (
           SELECT 
           Month, prd_type_id,
           SUM(amount) AS sum_month_prd,
           FROM all_sales
           GROUP BY month, prd_type_id
          )  a
) b

如果某个分析函数中含有distinct,那么,该SQL子句中的所有分析函数不可以含有分组排序语句。通过分析现有代码发现,没有语句不符合这个限制条件,如果未来有需求,则可以改写,将含有distinct的语句单独处理。

SELECT 
field1, field2, field3,
COUNT(distinct field1) OVER (PARTITION BY field2) as field4,
SUM(field3) OVER (PARTITION BY field2 ORDER BY field1) AS field5
FROM table1

可以用如下SQL改写:

SELECT
a.field1, a.field2, a.field3, a.field4,
SUM(a.field3) OVER (PARTITION BY a.field2 ORDER BY a.field1) AS field5
FROM (
SELECT 
field1, field2, field3,
COUNT(distinct field1) OVER (PARTITION BY field2) as field4,
FROM table1 )  a

与限制条件2的原因相同,TDW将不支持分析函数对不同列的multi-distinct功能,可以通过改写SQL实现同样的功能。但是,如果multi-distinct针对的列相同,则可以执行。也就是说,下面这语句可以执行:

SELECT 
field1, field2, field3,
COUNT(distinct field1) OVER (PARTITION BY field2) as field4,
SUM(distinct field1) OVER (PARTITION BY field2) AS field5
FROM table1

而这个语句只能改写:

SELECT 
field1, field2, field3,
COUNT(distinct field1) OVER (PARTITION BY field2) as field4,
COUNT(distinct field3) OVER (PARTITION BY field2) AS field5
FROM table1

可以用如下SQL改写:

SELECT
a.field1, a.field2, a.field3, a.field4,
COUNT( distinct a.field3) OVER (PARTITION BY a.field2) AS field5
FROM (
SELECT 
field1, field2, field3,
COUNT(distinct field1) OVER (PARTITION BY field2) as field4,
FROM table1 )  a

如果多个分析函数中含有分区子句和分区排序子句,那么这些分析函数中的分区子句和分区排序子句必须相同,否则,将该语句分拆成多个SQL语句。具体示例同限制一。

(分析函数中必须包含分区子句,否则,通过PL和改写SQL实现。)自100版本起,该限制取消,允许用户进行全局分析。但是,如果过于对过大的数据集进行全局分析,可能导致运算速度过慢等问题。

分析函数与select distinct冲突。例如,

From table1
Select distinct a, b, count(c) over(partition by d)

类似上述这种形式是不允许的。

HIVE内置操作符

关系操作符

比较两个操作参数,返回 TRUE或者FALSE。

Operator Operand types Description

A = B

All primitive types

TRUE if expression A is equal to expression B otherwise FALSE

A == B

None!

Fails because of invalid syntax. SQL uses =, not ==

A <> B

All primitive types

NULL if A or B is NULL, TRUE if expression A is NOT equal to expression B otherwise FALSE

A < B

All primitive types

NULL if A or B is NULL, TRUE if expression A is less than expression B otherwise FALSE

A <= B

All primitive types

NULL if A or B is NULL, TRUE if expression A is less than or equal to expression B otherwise FALSE

A > B

All primitive types

NULL if A or B is NULL, TRUE if expression A is greater than expression B otherwise FALSE

A >= B

All primitive types

NULL if A or B is NULL, TRUE if expression A is greater than or equal to expression B otherwise FALSE

A IS NULL

All types

TRUE if expression A evaluates to NULL otherwise FALSE

A IS NOT NULL

All types

TRUE if expression A not evaluates to NULL otherwise FALSE

A LIKE B

strings

NULL if A or B is NULL, TRUE if string A matches the SQL simple regular expression B, otherwise FALSE. The comparison is done character by character. The _ character in B matches any character in A(similar to . in posix regular expressions) while the % character in B matches an arbitrary number of characters in A(similar to .* in posix regular expressions). e.g.
'foobar' like 'foo' evaluates to FALSE where as 'foobar' like 'foo_ _ _' evaluates to TRUE and so does 'foobar' like 'foo%'

允许用户使用 A NOT LIKE B这种形式。

A RLIKE B

strings

NULL if A or B is NULL, TRUE if string A matches the Java regular expression B(See Java regular expressions syntax), otherwise FALSE. e.g.
'foobar' rlike 'foo' evaluates to FALSE where as 'foobar' rlike '^f.*r$' evaluates to TRUE

允许用户使用 A NOT RLIKE B这种形式。

A REGEXP B

strings

Same as RLIKE

算数操作符

对操作参数进行相应的计算,结果为数字类型。如果操作参数之一为NULL,则操作结果也为NULL

Operator Operand types Description

A + B

All number types

Gives the result of adding A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands. e.g. since every integer is a float, therefore float is a containing type of integer so the + operator on a float and an int will result in a float.

A - B

All number types

Gives the result of subtracting B from A. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.

A * B

All number types

Gives the result of multiplying A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands. Note that if the multiplication causing overflow, you will have to cast one of the operators to a type higher in the type hierarchy.

A / B

All number types

Gives the result of dividing B from A. The result is a double type.

A % B

All number types

Gives the reminder resulting from dividing A by B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.

%符号的前后需要加空格使用。

A & B

All number types

Gives the result of bitwise AND of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.

A | B

All number types

Gives the result of bitwise OR of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.

A ^ B

All number types

Gives the result of bitwise XOR of A and B. The type of the result is the same as the common parent(in the type hierarchy) of the types of the operands.

~A

All number types

Gives the result of bitwise NOT of A. The type of the result is the same as the type of A.

A << B

支持tinyint、smallint、int和bigint类型,不支持double和float类型

二进制位左移函数,语法同java,对运算符右侧的参数进行模32运算(左侧参数为bigint类型时模64)

A >> B

支持tinyint、smallint、int和bigint类型,不支持double和float类型

二进制位右移函数,语法同java,对运算符右侧的参数进行模32运算(左侧参数为bigint类型时模64)

逻辑操作符

对参数进行逻辑运算,返回值可能是TRUE,FALSE或者NULL。当操作参数至少有一个NULL时,结果为NULL。

Operator Operand types Description

A AND B

boolean

TRUE if both A and B are TRUE, otherwise FALSE. NULL if A or B is NULL

A && B

boolean

Same as A AND B

A OR B

boolean

TRUE if either A or B or both are TRUE; FALSE OR NULL is NULL; otherwise FALSE

NOT A

boolean

TRUE if A is FALSE or NULL if A is NULL. Otherwise FALSE.

(形如:not not true形式的表达式暂不支持,提供的变通方法是用括号逐层括起,即这里使用not (not true)即可完成相同的功能。)

 !A

boolean Same as NOT A (!操作符已支持,语法与NOT相同)

HIVE内置函数

数学函数

下面是常见的数学函数,大多数函数在输入为NULL时,返回值也是NULL。

Return Type Name(Signature) Description
BIGINT

round(double a)

Returns the rounded BIGINT value of the double
BIGINT

floor(double a)

Returns the maximum BIGINT value that is equal or less than the double
BIGINT

ceil(double a),ceiling(double a)

Returns the minimum BIGINT value that is equal or greater than the double
double

rand(),rand(int seed)

Returns a random number (that changes from row to row) that is distributed uniformly from 0 to 1. Specifiying the seed will make sure the generated random number sequence is deterministic.
double

exp(double a)

Returns e^a where e is the base of the natural logarithm
double

ln(double a)

Returns the natural logarithm of the argument
double

log10(double a)

Returns the base-10 logarithm of the argument
double

log2(double a)

Returns the base-2 logarithm of the argument
double

log(double base,double a)

Return the base "base" logarithm of the argument
double

pow(double a,double p),power(double a,double p)

Return a^p
double

sqrt(double a)

Returns the square root of a
string

bin(BIGINT a)

Returns the number in binary format (see [1])
string

hex(BIGINT a),hex(string a)

If the argument is an int, hex returns the number as a string in hex format. Otherwise if the number is a string, it converts each character into its hex representation and returns the resulting string. (see [2])
string

unhex(string a)

Inverse of hex. Interprets each pair of characters as a hexidecimal number and converts to the character represented by the number.
string

conv(BIGINT num,int from_base,int to_base)

Converts a number from a given base to another (see [3])
double

abs(double a)

Returns the absolute value
int

pmod(int a,int b),pmod(double a,double b)

Returns the positive value of a mod b
double

sin(double a)

Returns the sine of a (a is in radians)
double

asin(double a)

Returns the arc sin of x if -1<=a<=1 or null otherwise
double

cos(double a)

Returns the cosine of a (a is in radians)
double

acos(double a)

Returns the arc cosine of x if -1<=a<=1 or null otherwise
int double

positive(int a),positive(double a)

Returns a
int double

negative(int a),negative(double a)

Returns -a
double

percentile(BIGINT col, p)

求准确的第pth个百分位数,p必须介于0和1之间,但是col字段目前只支持长整数,不支持浮点数类型

语法: percentile(BIGINT col, p)

返回值: double

select percentile(CAST (key AS BIGINT),0.5) from cherry

select percentile(CAST (key AS BIGINT),ARRAY(0.5,0.5)) from cherry

类型转换函数

Return Type Name(Signature) Description
Expected "=" to follow "type"

cast(expr as <type>)

Converts the results of the expression expr to <type> e.g. cast('1' as BIGINT) will convert the string '1' to it integral representation. A null is returned if the conversion does not succeed.

时间函数

Return Type Name(Signature) Description
string

from_unixtime(int unixtime [, pattern])

Converts the number of seconds from unix epoch (1970-01-01 00:00:00 UTC) to a string representing the timestamp of that moment in the current system time zone in the format of "1970-01-01 00:00:00"

注意:该函数的参数unixtime是秒数。 该函数在120版本后支持bigint类型的参数,返回日期的范围为9999-12-31 23:59:59和0001-01-01 00:00:00之间。 注意:pattern的默认值是yyyy-MM-dd HH:mm:ss,其中月份MM一定要大写。

bigint

unix_timestamp()

Gets current time stamp using the default time zone.

注意:该函数返回的是秒数。

bigint

unix_timestamp(string date [, pattern])

Converts time string in format yyyy-MM-dd HH:mm:ss to Unix time stamp, return 0 if fail: unix_timestamp('2009-03-20 11:30:01') = 1237573801

注意:该函数返回的是秒数。

int

year(string date)

Returns the year part of a date or a timestamp string: year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970
int

month(string date)

Returns the month part of a date or a timestamp string: month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11
int

day(string date),dayofmonth(string date)

Return the day part of a date or a timestamp string: day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1
int

hour(string date)

Returns the hour of the timestamp: date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or 'HH:mm:ss'.
 Example:
> SELECT hour('2009-07-30 12:58:59') FROM src LIMIT 1;
12
> SELECT hour('12:58:59') FROM src LIMIT 1;
12
int

minute(string date)

Returns the minute of the timestamp: date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or 'HH:mm:ss'.

Example:

  > SELECT minute('2009-07-30 12:58:59') FROM src LIMIT 1;
58
> SELECT minute('12:58:59') FROM src LIMIT 1;
58
int

second(string date)

Returns the second of the timestamp:date is a string in the format of 'yyyy-MM-dd HH:mm:ss' or 'HH:mm:ss'.

Example:

  > SELECT second('2009-07-30 12:58:59') FROM src LIMIT 1;
59
> SELECT second('12:58:59') FROM src LIMIT 1;
59
int

weekofyear(string date)

Returns the week of the year of the given date. A week is considered to start on a Monday and week 1 is the first week with >3 days.

Examples:

 > SELECT weekofyear('2008-02-20') FROM src LIMIT 1;
8
> SELECT weekofyear('1980-12-31 12:59:59') FROM src LIMIT 1;
1
int

week(string date)

返回一个日期是星期几。参数的形式可以是"YYYY-MM-DD"或者"YYYYMMDD"。返回值为1~7,其中1表示星期天,2~7分别表示星期一~星期六

Examples:

 > select week("2013-07-05");
6
> select week("20130706");
7
int

commonStr(col1,col2,separator)

Returns the common elements of col1 and col2 which separated by separator

col1 and col2 and separator must be String type, they will be casted to Text, col1 and col2 is separated by separatorExample:

  > SELECT commonStr('a,b,c,d','d,c',',') FROM src LIMIT 1;
c,d
> SELECT commonStr(' ab,bc,ac','ac,bc',',') FROM src LIMIT 1;
bc,ac
String

datediff(string enddate,string startdate)

Return the number of days from startdate to enddate: datediff('2009-03-01', '2009-02-27') = 2 Or Return the number of days from startdate to enddate: datediff('20090301', '20090227') = 2
int

date_add(string startdate,int days)

Add a number of days to startdate: date_add('2008-12-31', 1) = '2009-01-01' Or Add a number of days to startdate: date_add('20081231', 1) = '20090101'
int

date_sub(string startdate,int days)

Subtract a number of days to startdate: date_sub('2008-12-31', 1) = '2008-12-30' Or Subtract a number of days to startdate: date_sub('20081231', 1) = '20081230'

条件函数

Return Type Name(Signature) Description
T

if(boolean test Condition, T value TRUE, T value FALSE or NULL)

Return valueTrue when testCondition is TRUE, returns value FALSE or NULL otherwise
T

COALESCE(T v1, T v2, ...)

Return the first v that is not NULL, or NULL if all v's are NULL
T

CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

When a = b, returns c; when a = d, return e; else return f

该函数的用法与decode函数一致,支持int和bigint类型的混合使用,其他类型要求保证一致性。a和c不可为null(表字段不受该限制)。该函数参数是变长的,最少3个参数,最多255个参数。

T

CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

When a = TRUE, returns b; when c = TRUE, return d; else return e

该函数暂时不支持int和bigint类型混合,120版本后会支持。

字符串函数

Return Type Name(Signature) Description
int

length(string A)

Returns the length of the string
string

reverse(string A)

Returns the reversed string
string

concat(string A,string B)

Returns the string resulting from concatenating the strings passed in as parameters in order. e.g.
concat('foo', 'bar') results in 'foobar'. Note that this function can take any number of input strings.
string

substr(string A,int start),substring(string A, int start)

Returns the substring of A starting from start position till the end of string A. e.g.
substr('foobar', 4) results in 'bar' (see [4])
string

substr(string A,int start,int len),substring(string A, int start,int len)

Returns the substring of A starting from start position with length len. e.g.
substr('foobar', 4, 1) results in 'b' (see [5])
string

upper(string A),ucase(string A)

Returns the string resulting from converting all characters of A to upper case. e.g.
upper('fOoBaR') results in 'FOOBAR'
string

lower(string A),lcase(string A)

Returns the string resulting from converting all characters of B to lower case. e.g.
lower('fOoBaR') results in 'foobar'
string

trim(string A)

Returns the string resulting from trimming spaces from both ends of A. e.g.
trim(' foobar ') results in 'foobar'
string

ltrim(string A)

Returns the string resulting from trimming spaces from the beginning(left hand side) of A. e.g.
ltrim(' foobar ') results in 'foobar '
string

rtrim(string A)

Returns the string resulting from trimming spaces from the end(right hand side) of A. e.g.
rtrim(' foobar ') results in ' foobar'
string

regexp_replace(string A, string B, string C)

Returns the string resulting from replacing all substrings in B that match the Java regular expression syntax(See Java regular expressions syntax) with C e.g. regexp_replace("foobar", "oo|ar", "") returns 'fb.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\\s' is necessary to match whitespace, etc. 正则表达式的用法与JAVA兼容,参见JAVA正则表达式

string

regexp_extract(string subject, string pattern, int intex)

Returns the string extracted using the pattern. e.g. regexp_extract('foothebar', 'foo(.*?)(bar)', 2) returns 'bar.' Note that some care is necessary in using predefined character classes: using '\s' as the second argument will match the letter s; '\\s' is necessary to match whitespace, etc.

正则表达式的用法与JAVA兼容,参见JAVA正则表达式

int

regexp_instr(source_char,pattern[,position [,occurrence[,return_option[,match_parameter]]]])

Returns the position the pattern in the source character.

REGEXP_INSTR extends the functionality of the INSTR function by letting you search a string for a regular expression pattern. The function evaluates strings using characters as defined by the input character set. It returns an integer indicating the beginning or ending position of the matched substring, depending on the value of the return_option argument. If no match is found, the function returns 0. You can see the whole definition in

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions129.htm

Example:

SELECT regexp_instr('500 Oracle Parkway, Redwood Shores, CA', '[s|r|p]', 3, 2, 0, 'i') FROM src LIMIT 1;
12 will be return. 
SELECT regexp_instr('500 Oracle Parkway, Redwood Shores, CA', '[s|r|p]', 3, 2, 1, 'i') FROM src LIMIT 1;
13 will be return

正则表达式的用法与JAVA兼容,参见JAVA正则表达式

string

parse_url(string urlString, string partToExtract [, string keyToExtract])

Returns the specified part from the URL. Valid values for partToExtract include HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO. e.g.
parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') returns 'facebook.com'. 

Also a value of a particular key in QUERY can be extracted by providing the key as the third argument, e.g.

parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') returns 'v1'.
string

get_json_object(string json_string, string path)

Extract json object from a json string based on json path specified, and return json string of the extracted json object. It will return null if the input json string is invalid.
string

space(int n)

Return a string of n spaces
string

repeat(string str, int n)

Repeat string n times
int

ascii(string str)

Returns the numeric value of the first character of string
string

lpad(string str, int len, string pad)

Returns string, left-padded with pad to a length of len
string

rpad(string str, int len, string pad)

Returns string, right-padded with pad to a length of len
string

url_decode(string url_string, string encode)

Returns decode url_string
url_decode('http://tdw.boss.com/wiki/index.php?title=TDW_%E5%87%BD%E6%95%B0%E5%9C%A8%E7%BA%BF%E6%89%8B%E5%86%8C&action=edit&section=153','utf-8') returns 'http://tdw.boss.com/wiki/index.php?title=TDW_函数在线手册&action=edit&section=153'.
array

split(string str, string regex)

Splits str around occurances that match
SELECT split('ABCD:C:D:E',':')[2] returns 'D'.
int

size(array arr)

Returns the size of array

SELECT size(split('a,b',',')) returns 2

boolean

array_contains(array arr, element)

Returns if the element is in the array, the element must hava same type as array

SELECT array_CONTAINS(ARRAY(0,1),0)  returns true

SELECT array_CONTAINS(split('0=1','='),'0') returns true

string

get_main_domain(string url_string)

Returns the main domain of url_string

SELECT get_main_domain('www.tencent.com') returns 'tencent.com'

int

parse_xml_count(string xml_string, string root_label, string sub_label ...)

Returns the count of the sub_label in xml_string, the labels must start from the root label layer-by-layer

SELECT parse_xml_count('<la> <lb> <lc></lc> <lc></lc> </lb> <lb></lb> </la>', 'la', 'lb', 'lc') returns 2

array

parse_xml_content(string xml_string, string root_label, string sub_label ...)

Returns the content of the sub_label in xml_string, the labels must start from the root label layer-by-layer

SELECT parse_xml_content('<la> <lb> <lc>hello</lc> <lc>world</lc> </lb> <lb></lb> </la>', 'la', 'lb', 'lc')[0] returns 'hello'

IP函数

Return Type Name(Signature) Description
bigint

inet_aton(string addr)

 returns the integer value of ip address,
 Example: SELECT inet_aton(10.10.10.10) FROM src LIMIT 1
 return    168430090
string

inet_ntoa(bigint addr)

 returns the string value of ip address,
 Example: SELECT inet_aton(168430090) FROM src LIMIT 1
 return    10.10.10.10
string

ipinfo(string ip_table,bigint ip,int index)

 返回IP所在的IP段中的表第index列值。

ip表结构的约定: 1. 前二列必须为start_ip bigint, end_id bigint。 2. 表必须指定为文本非分区表且未压缩即STORED AS TEXTFILE。3.表的数据必须按start_ip,end_ip的升序保存

定义语句如下所示: CREATE TABLE ip_table(

   start_ip BIGINT,
end_ip BIGINT

) STORED AS TEXTFILE; 如果以前的是其他格式的IP表,请使用insert语句将其插入到一张新建按前面约定格式的表。 ipinfo函数的使用: ipinfo(table_name,ip,index) table_name表示ip表的db名和表名。如:imdataoss::02_00_012。 ip表示ip地址,类型为字符串或数字。如:16777216,'10.234.234.45' index表示需要返回的第几列的值 1表示ip_start 2表示ip_end N表示table中的第n列

Example: SELECT ipinfo('imdataoss::02_00_012',16777216,3) FROM src LIMIT 1
 返回ip为16777216在ip表imdataoss::02_00_012中的所属的IP段的第3列值。

注意事项: 由于ip表是load到内存中计算,所以这张表的大小最好不要超过100MB。 ip表里的IP分段(即start_ip和end_ip)最好不要有重叠,且是闭区间(即包括区间内数字本身)。如果ip表出现end_ip大于start_ip,或者数据格式跟定义的ip表不一致,该条数据将会被过滤。 如果ip表里的IP分段出现重复时,后面出现的IP段值会覆盖前面的IP段值。

聚合函数

Return Type Name(Signature) Description
bigint

count(!), count(expr), count(DISTINCT expr[, expr...])

count(*) - Returns the total number of retrieved rows, including rows containing NULL values; count(expr) - Returns the number of rows for which the supplied expression is non-NULL; count(DISTINCT expr[, expr]) - Returns the number of rows for which the supplied expression(s) are unique and non-NULL.
double

sum(col),sum(DISTINCT col)

Returns the sum of the elements in the group or the sum of the distinct values of the column in the group
double

avg(col),avg(DISTINCT col)

Returns the average of the elements in the group or the average of the distinct values of the column in the group
double

min(col)

Returns the minimum of the column in the group
double

max(col)

Returns the maximum value of the column in the group

数据挖掘与统计分析函数

返回值 函数名 说明
array<array<string>>

sentences(string str, string lang, string locale)

将英文句子分词,输入是英文句子,输出是array,array的每个元素对应一个完整的句子。这个完整的句子的分词又在一个array中,array的元素是句子中的单词。不支持小数点和特殊符号比如$。

举例:

SELECT sentences('Hello there! I am a UDF.') FROM src LIMIT 1;"
[ ["Hello", "there"], ["I", "am", "a", "UDF"] ]"

注意: 标点符号等会被忽略。如果有lang存在,则它必需符合ISO-639 l语言简写代码(例如en表示英语,fr表示法语,de表示德语)。Country如果存在,它必须是ISO-3166中定义的国家代码(如us表示美国)。目前只支持英语,法语,德语等字母表为基础的语言和国家,不支持中文分词。如果没有指定lang和country,则会使用JAVA虚拟机默认的lang和country。

测试用例: Udf sentences

array<struct<string,double>>

ngrams(array<array<string>>, int n, int k, int pf)

估计top-k ngrams的值。也支持ngrams(expr array<String>,n int,k int,pf int)形式。

Expr:句子分词后的数组,或者多个句子分词后的数组的数组

n: n=1时是unigrams,n=2时是bigrams,一般n不超过5

K:返回多少个 highest-frequency ngrames

Pf:可选参数,使用多少内存进行计算。内存越多,计算结果约准确,但是JVM越有可能崩溃。默认值是20,表示它维护20*k ngrams,只去最高频率的ngrams。

测试用例: Udaf ngrams

array<struct<string,double>>

context_ngrams(expr array<String>,context array<string1,string2,...>,k int,pf int)

估计符合一定上下文的top-k ngrams。第二个参数指定上下文,使用null来做锚。也支持或context_ngrams(expr array<array<String>>,context array<string1,string2,...>,k int,pf int)形式。

Expr:与ngrams的第一个参数含义一致。

Context:上下文,使用null表示一个占位符。例如arra<'I','love',null>,会将前两个词语是I love的3-grams选出。

K:与ngrams的K含义相同。

Pf:与ngrams的pf含义相同。

测试用例: Udaf covar context ngrams

double

covar_pop( dependent-expression, independent-expression )

返回一组数值对的总体协方差。

参数

dependent-expression 受独立变量影响的变量。

independent-expression 影响结果的变量。

注释

此函数将其参数转换为 DOUBLE 类型,以双精度浮点执行计算,然后返回 DOUBLE 值作为结果。如果将该函数应用于某一空集合,则它会返回 NULL。

dependent-expression 和 independent-expression 都是数字类型。该函数适用于排除所有 dependent-expression 或 independent-expression 之一为空的对后剩下的 dependent-expression 和 independent-expression 对的集合。然后执行以下计算:

(SUM( x * y ) - SUM( y ) * SUM( y ) / n ) / n

其中 x 表示 dependent-expression,y 表示 independent-expression。

测试用例: Udaf covar pop

double

covar_samp( dependent-expression, independent-expression )

返回一组数值对的样本协方差。

参数

dependent-expression 受独立变量影响的变量。

independent-expression 影响结果的变量。

注释

此函数将其参数转换为 DOUBLE 类型,以双精度浮点执行计算,然后返回 DOUBLE 值作为结果。如果将该函数应用于某一空集合,则它会返回 NULL。

dependent-expression 和 independent-expression 都是数字类型。该函数应用于排除所有 dependent-expression 或 independent-expression 之一为空的对后剩下的 dependent-expression 和 independent-expression 对的集合。

测试用例: Udaf covar samp

double

corr(X,Y)

返回一组数字对的相关系数

此函数将其参数转换为 DOUBLE 类型,以双精度浮点执行计算,然后返回 DOUBLE 值作为结果。如果将该函数应用于某一空集合,则它会返回 NULL。x 和 y 都是数字类型。该函数应用于排除所有 x 或 y 之一为 NULL 的对后剩下的 x 和 y 对的集合。然后执行以下计算:

COVAR_POP ( x, y ) / STDDEV_POP ( x) * STDDEV_POP ( y )

如果输入是空集合,或者只有一条记录,则返回为null。 测试用例: Udaf corr