常用函數
系統自帶函數
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語句聯用)
更多關于云服務器，域名注冊，虛擬主機的問題，請訪問三五互聯官網：m.shinetop.cn