odps函數(shù)

常用函數(shù)

系統(tǒng)自帶函數(shù)

coalesce()：返回列表中第一個(gè)非NULL的值，如果列表中所有的值都是NULL則返回NULL;

eg:

concat()：字符串連接函數(shù);

eg:

least()：返回輸入?yún)?shù)中最小的一個(gè)

greatest()：返回輸入?yún)?shù)中最大的一個(gè)(var1，var2可以為bigint，double，datetime或者string。若所有值都為NULL則返回NULL。

返回值:輸入?yún)?shù)中的最大值，當(dāng)不存在隱式轉(zhuǎn)換時(shí)返回同輸入?yún)?shù)類型。NULL為最小值。當(dāng)輸入?yún)?shù)類型不同時(shí)，double，bigint，string之間的比較轉(zhuǎn)為double；string，datetime的比較轉(zhuǎn)為datetime。不允許其它的隱式轉(zhuǎn)換)

decode()：實(shí)現(xiàn)分支選擇的功能

eg:select decode(customer_id,

? ? ? ? ? ? 1, 'Taobao',

? ? ? ? ? ? 2, 'Alipay',

? ? ? ? ? ? 3, 'Aliyun',

? ? ? ? ? ? NULL, 'N/A',

? ? ? ? ? ? 'Others') as result

? ? from sale_detail;

上面的decode函數(shù)實(shí)現(xiàn)了下面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函數(shù)：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類型轉(zhuǎn)為對應(yīng)的string類型表示

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

to_char():Datetime類型，要轉(zhuǎn)換的日期值，若輸入為string類型會(huì)隱式轉(zhuǎn)換為datetime類型后參與運(yùn)算，其它類型拋異常。

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

concat（coumn1,',',coumn2）：字符串連接函數(shù)

匹配兩位精度：

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

regexp_extract(coumn,'',number):字符串拆分函數(shù)

如：臨東路與火神塘路交叉口

regexp_extract(inter_name,'(.*?)(路)',1) =臨東

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

regexp_replace:字符串替換函數(shù)

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

split_part字符串拆分函數(shù)

split_part('環(huán)北-密渡橋','-',2)=密渡橋

instr：計(jì)算一個(gè)子串str2在字符串str1中的位置

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

cast

coors_convert(lng,lat,1)：谷歌轉(zhuǎn)高德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

窗口函數(shù)

統(tǒng)計(jì)量：count,sum,avg,max/min,median,stddev,stddev_samp

排名：row_unmber,rank,dense_rank,percent_rank

其他類：lag,lead,cluster_sample

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

基本用法;把數(shù)據(jù)按照一定條件分成多組稱為開窗，每個(gè)組稱為一個(gè)窗口

partition by部分用來指定開窗的列

分區(qū)列的值相同的行被視為在同一個(gè)窗口內(nèi)

order by用來指定數(shù)據(jù)在一個(gè)窗口內(nèi)如何排序

使用限制：只能出現(xiàn)在select子句中

窗口函數(shù)中不要嵌套使用窗口函數(shù)和聚合函數(shù)

不可以和同級別的聚合函數(shù)一起使用

一個(gè)odps sql語句中，可以使用至多5個(gè)窗口函數(shù)

Partition開窗時(shí)，同一窗口內(nèi)最多包含1億行數(shù)據(jù)

用rows開窗時(shí)，x,y必須大于等于0的整數(shù)常量，限定范圍0-10000，值為0時(shí)表示當(dāng)前行

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

并非所有的窗口函數(shù)都可以用rows指定開窗方式，支持這種用法的窗口函數(shù)有avg,count,max,min,stddev和sum

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

舉個(gè)栗子

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

自定義函數(shù)

基于阿里云odps制作相應(yīng)的自定義函數(shù)

說明：本例子中由于odps版本過低：所以創(chuàng)建的時(shí)候沒有采用阿里云example一步一步來maven打包，而是采用自己打包，是由于采用例子的一步一步來出來的jar回有問題(出來的jar沒有類資源，只有配置文件資源)。

名詞解釋：

UDF：用戶自定義標(biāo)量值函數(shù)(user defined scalar function),其輸入與輸出是一對一的關(guān)系，讀入一行數(shù)據(jù)(可以有多個(gè)參數(shù))，寫出一條輸出值

UDTF：自定義表值函數(shù)(user defined table valued function),是用來解決一次函數(shù)調(diào)用輸出多行數(shù)據(jù)場景的，也是唯一能返回多個(gè)字段的自定義函數(shù)

UDAF：自定義聚合函數(shù)(user defined aggregation function)，其輸入和輸出是多對一的關(guān)系，將多條輸入記錄聚合成一條輸出值(可以和group by語句聯(lián)用)