資料內容僅供您學習參考，如有不當或者侵權，請聯(lián)系改正或者刪除。函數(shù)分類HIVECLI命令顯示當前會話有多少函數(shù)可用

SHOWFUNCTIONS;顯示函數(shù)的描述信息

DESCFUNCTIONconcat;顯示函數(shù)的擴展描述信息

DESCFUNCTIONEXTENDEDconcat;簡單函數(shù)函數(shù)的計算粒度為單條記錄。

關系運算

數(shù)學運算

邏輯運算

數(shù)值計算

類型轉換

日期函數(shù)

條件函數(shù)

字符串函數(shù)

統(tǒng)計函數(shù)聚合函數(shù)函數(shù)處理的數(shù)據(jù)粒度為多條記錄。

sum()—求和

count()—求數(shù)據(jù)量

avg()—求平均直

distinct—求不同值數(shù)

min—求最小值

max—求最人值集合函數(shù)復合類型構建

復雜類型訪問

復雜類型長度特殊函數(shù)窗口函數(shù)應用場景

用于分區(qū)排序

動態(tài)GroupBy

TopN

累計計算

層次查詢WindowingfunctionsleadlagFIRST_VALUELAST_VALUE分析函數(shù)AnalyticsfunctionsRANKROW_NUMBERDENSE_RANKCUME_DISTPERCENT_RANKNTILE混合函數(shù)java_method(class,method[,arg1[,arg2])reflect(class,method[,arg1[,arg2..]])hash(a1[,a2...])UDTFlateralView:LATERALVIEWudtf(expression)tableAliasAScolumnAlias(‘,‘columnAlias)*fromClause:FROMbaseTable(lateralView)*ateralview用于和split,explode等UDTF一起使用,它能夠將一行數(shù)據(jù)拆成多行數(shù)據(jù),在此基礎上能夠對拆分后的數(shù)據(jù)進行聚合。lateralview首先為原始表的每行調用UDTF,UTDF會把一行拆分成一或者多行,lateralview再把結果組合,產(chǎn)生一個支持別名表的虛擬表。常見函數(shù)Demo:createtableemployee(idstring,moneydouble,typestring)rowformatdelimitedfieldsterminatedby‘\t‘linesterminatedby‘\n‘storedastextfile;loaddatalocalinpath‘/liguodong/hive/data‘intotableemployee;select*fromemployee;優(yōu)先級依次為NOTANDORselectid,moneyfromemployeewhere(id=‘1001‘orid=‘1002‘)andmoney=‘100‘;cast類型轉換selectcast(1.5asint);if判斷if(con,‘‘,‘‘);hive(default)>selectif(2>1,‘YES‘,‘NO‘);YEScasewhenconthen‘‘whenconthen‘‘else‘‘end(‘‘里面類型要一樣)selectcasewhenid=‘1001‘then‘v1001‘whenid=‘1002‘then‘v1002‘else‘v1003‘endfromemployee;get_json_objectget_json_object(json解析函數(shù),用來處理json,必須是json格式)selectget_json_object(‘{"name":"jack","age":"20"}‘,‘$.name‘);URL解析函數(shù)parse_url(stringurlString,stringpartToExtract[,stringkeyToExtract])selectparse_url(‘‘,‘HOST‘)fromemployeelimit1;字符串連接函數(shù):concat

語法:concat(stringA,stringB…)

返回值:string

說明:返回輸入字符串連接后的結果,支持任意個輸入字符串

舉例:hive>selectconcat(‘a(chǎn)bc‘,‘def’,‘gh‘)fromlxw_dual;abcdefgh帶分隔符字符串連接函數(shù):concat_ws

語法:concat_ws(stringSEP,stringA,stringB…)

返回值:string

說明:返回輸入字符串連接后的結果,SEP表示各個字符串間的分隔符concat_ws(stringSEP,array<string>)舉例:hive>selectconcat_ws(‘,‘,‘a(chǎn)bc‘,‘def‘,‘gh‘)fromlxw_dual;abc,def,gh列出該字段所有不重復的值,相當于去重collect_set(id)//返回的是數(shù)組列出該字段所有的值,列出來不去重collect_list(id)//返回的是數(shù)組selectcollect_set(id)fromtaborder;求和sum(money)統(tǒng)計列數(shù)count(*)selectsum(num),count(*)fromtaborder;窗口函數(shù)first_value(第一行值)first_value(money)over(partitionbyidorderbymoney)selectch,num,first_value(num)over(partitionbychorderbynum)fromtaborder;rowsbetween1precedingand1following(當前行以及當前行的前一行與后一行)hive(liguodong)>selectch,num,first_value(num)over(partitionbychorderbynumROWSBETWEEN2PRECEDINGANDCURRENTROW)fromtaborder;last_value最后一行值hive(liguodong)>selectch,num,last_value(num)over(partitionbych)fromtaborder;lead去當前行后面的第二行的值lead(money,2)over(orderbymoney)lag去當前行前面的第二行的值lag(money,2)over(orderbymoney)``````selectch,num,lead(num,2)over(orderbynum)fromtaborder;selectch,num,lag(num,2)over(orderbynum)fromtaborder;rank排名rank()over(partitionbyidorderbymoney)selectch,num,rank()over(partitionbychorderbynum)asrankfromtaborder;selectch,num,dense_rank()over(partitionbychorderbynum)asdense_rankfromtaborder;cume_distcume_dist(相同值的最大行號/行數(shù))cume_dist()over(partitionbyidorderbymoney)percent_rank(相同值的最小行號-1)/(行數(shù)-1)第一個總是從0開始percent_rank()over(partitionbyidorderbymoney)selectch,num,cume_dist()over(partitionbychorderbynum)ascume_dist,percent_rank()over(partitionbychorderbynum)aspercent_rankfromtaborder;ntile分片ntile(2)over(orderbymoneydesc)分兩份selectch,num,ntile(2)over(orderbynumdesc)fromtaborder;混合函數(shù)selectid,java_method("java.lang,Math","sqrt",cast(idasdouble))assqrtfromhiveTest;UDTFselectid,adidfromemployeelateralviewexplode(split(type,‘B‘))ttasadid;explode把一列轉成多行hive(liguodong)>selectid,adid>fromhiveDemo>lateralviewexplode(split(str,‘,‘))ttasadid;正則表示式

使用正則表示式的函數(shù)

regexp_replace(stringsubjectA,stringB,stringC)

regexp_extract(stringsubject,stringpattern,intindex)hive>selectregexp_replace