odps函数

常用函数

系统自带函数

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语句联用)