odps函数

登录注册

odps函数

来源:网络
更新日期:2020-10-23

摘要：云计算常用函数系统自带函数coalesce()：返回列表中第一个非NULL的值，如果列表中所有的值都是NULL则返回NULL;eg:concat()：字符串连接函数;e

云计算

常用函数

系统自带函数

coalesce()：返回列表中第一个非NULL的值，如果列表中所有的值都是NULL则返回NULL;

eg:

concat()：字符串连接函数;

eg:

least()：返回输入参数中最小的一个

greatest()：返回输入参数中最大的一个(var1，var2可以为bigint，double，datetime或者string。若所有值都为NULL则返回NULL。

返回值:输入参数中的最大值，当不存在隐式转换时返回同输入参数类型。NULL为最小值。当输入参数类型不同时，double，bigint，string之间的比较转为double；string，datetime的比较转为datetime。不允许其它的隐式转换)

decode()：实现分支选择的功能

eg:select decode(customer_id,

1, \'Taobao\',

2, \'Alipay\',

3, \'Aliyun\',

NULL, \'N/A\',

\'Others\') as result

from sale_detail;

上面的decode函数实现了下面if-then-else语句中的功能：

if customer_id = 1 then

result := \'Taobao\';

elsif customer_id = 2 then

result := \'Alipay\';

elsif customer_id = 3 then

result := \'Aliyun\';

...

else

result := \'Others\';

end if;

if函数：if(逻辑条件,coumn1,coumn2)表示满足条件则输出1，否则输出2的值

eg:if(cap_direction not in(\'0\',\'1\'),null, cast(cap_direction as bigint));

substr():返回字符串str从start_position开始长度为length的子串

eg: substr(abc, 2) = bc;substr(abc, 2, 1) = b;

to_char():将Boolean类型、bigint类型、decimal类型或者double类型转为对应的string类型表示

eg:to_char(123) = \'123\';to_char(true) = \'TRUE\';to_char(1.23) = \'1.23\';to_char(null) = NULL;

to_char():Datetime类型，要转换的日期值，若输入为string类型会隐式转换为datetime类型后参与运算，其它类型抛异常。

eg:to_char(getdate(),\'yyyymmdd\')

concat（coumn1,\',\',coumn2）：字符串连接函数

匹配两位精度：

concat(substr(to_char(lng),1,6),\',\',substr(to_char(lat),1,5)) like \'120.08,30.28\';

regexp_extract(coumn,\'\',number):字符串拆分函数

如：临东路与火神塘路交叉口

regexp_extract(inter_name,\'(.*?)(路)\',1) =临东

regexp_extract(inter_name,\'与(.*?)(交叉口)\',1)=火神塘路

regexp_replace:字符串替换函数

regexp_replace(round_name,\'-\',\'\',1)表示吧-替换成null

split_part字符串拆分函数

split_part(\'环北-密渡桥\',\'-\',2)=密渡桥

instr：计算一个子串str2在字符串str1中的位置

instr(\'Tech on the net\', \'e\') = 2；instr(\'Tech on the net\', \'e\', 1, 1) = 2

cast

coors_convert(lng,lat,1)：谷歌转高德coors_convert(120.2334214,30.21829241,1)

WHERE judge_location(split_part(coors_convert(a.lng,a.lat,1),\',\',1),split_part(coors_convert(a.lng,a.lat,1),\',\',2))=1

窗口函数

统计量：count,sum,avg,max/min,median,stddev,stddev_samp

排名：row_unmber,rank,dense_rank,percent_rank

其他类：lag,lead,cluster_sample

--------------------

基本用法;把数据按照一定条件分成多组称为开窗，每个组称为一个窗口

partition by部分用来指定开窗的列

分区列的值相同的行被视为在同一个窗口内

order by用来指定数据在一个窗口内如何排序

使用限制：只能出现在select子句中

窗口函数中不要嵌套使用窗口函数和聚合函数

不可以和同级别的聚合函数一起使用

一个odps sql语句中，可以使用至多5个窗口函数

Partition开窗时，同一窗口内最多包含1亿行数据

用rows开窗时，x,y必须大于等于0的整数常量，限定范围0-10000，值为0时表示当前行

必须使用order by才可以用rows方式指定窗口范围

并非所有的窗口函数都可以用rows指定开窗方式，支持这种用法的窗口函数有avg,count,max,min,stddev和sum

----------------------

举个栗子

select *,rank() over(partition by monitor_id order by distance) as mindistance_monitor_id from()

自定义函数

基于阿里云odps制作相应的自定义函数

说明：本例子中由于odps版本过低：所以创建的时候没有采用阿里云example一步一步来maven打包，而是采用自己打包，是由于采用例子的一步一步来出来的jar回有问题(出来的jar没有类资源，只有配置文件资源)。

名词解释：

UDF：用户自定义标量值函数(user defined scalar function),其输入与输出是一对一的关系，读入一行数据(可以有多个参数)，写出一条输出值

UDTF：自定义表值函数(user defined table valued function),是用来解决一次函数调用输出多行数据场景的，也是唯一能返回多个字段的自定义函数

UDAF：自定义聚合函数(user defined aggregation function)，其输入和输出是多对一的关系，将多条输入记录聚合成一条输出值(可以和group by语句联用)