13518219792
常用函數
網站建設哪家好，找成都創新互聯公司！專注于網頁設計、網站建設、微信開發、成都微信小程序、集團企業網站建設等服務項目。為回饋新老客戶創新互聯還提供了句容免費建站歡迎大家使用！
系統自帶函數
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語句聯用)
