一、Aggregate--聚合類
1、avg(col)--返回某一列的平均值
2、count([DISTINCT] col) --對某一列計數(shù)
3、stddev_pop(col) --返回某一列的標(biāo)準(zhǔn)偏差
4、stddev_samp(col) --返回數(shù)據(jù)列無偏樣本的標(biāo)準(zhǔn)偏差
5、sum(col)
6、max(col)
7、min(col)
8、corr(col1, col2) --返回兩列的皮爾遜相關(guān)系數(shù)
9、covar_pop(col1, col2) --返回兩列數(shù)據(jù)的總體協(xié)方差
10、covar_samp(col1, col2)--返回兩列數(shù)據(jù)樣本的協(xié)方差
11、collect_set(col) --返回一組消除了重復(fù)元素的對象,返回的是一個集合
12、collect_list(col)--返回具有重復(fù)項的對象列表
13、histogram_numeric(col, b) --返回array
14、ntile(INT x) --該函數(shù)將已經(jīng)排序的分區(qū)分到x個桶中，并為每行分配一個桶號。這可以容易的計算三分位，四分位，十分位，百分位和其它通用的概要統(tǒng)計
15、percentile(BIGINT col, p) --返回組中列的精確第p個百分點（不適用于浮點類型）。 p必須介于0和1之間。注意：真正的百分位數(shù)只能用整數(shù)值計算。如果您的輸入不是整數(shù)，請使用PERCENTILE_APPROX。
16、percentile(BIGINT col, array(p1 [, p2]...)) --返回array類型，返回組內(nèi)某個列精確的第p1，p2，……位百分?jǐn)?shù)，p必須在0和1之間，同上
16、percentile_approx(DOUBLE col, p [, B]) --返回組中數(shù)值列（包括浮點類型）的近似pth百分點（或百分點p1，p2，..）。 B參數(shù)以內(nèi)存為代價控制逼近精度。較高的值會產(chǎn)生更好的近似值，默認(rèn)值為10,000。當(dāng)col中不同值的數(shù)量小于B時，這會得出確切的百分位數(shù)值。
17、percentile_approx(DOUBLE col, array(p1 [, p2]...) [, B]) --返回array，同上，可以返回多個百分點
18、variance(col)--返回組中數(shù)字列的方差
19、var_pop(col) --返回組中數(shù)字列的方差。
20、var_samp(col) --返回組中數(shù)字列的無偏樣本方差。
21、regr_avgx(T independent, T dependent) --計算回歸線的自變量(expr2)的平均值，去掉了空對(expr1, expr2)后，等于AVG(expr2)。截至Hive 2.2.0。
22、regr_avgy(T independent, T dependent) --計算回歸線的應(yīng)變量(expr1)的平均值，去掉了空對(expr1, expr2)后，等于AVG(expr1)。截至Hive 2.2.0
23、regr_count(T independent, T dependent) --返回用于擬合線性回歸線的非空對數(shù)。截至Hive 2.2.0。
24、regr_intercept(T independent, T dependent) --返回線性回歸的直線的y軸截距，即方程中的b值取決于= a *獨立+ b。截至Hive 2.2.0。
25、regr_r2(T independent, T dependent) --返回回歸的確定系數(shù)。截至Hive 2.2.0。
26、regr_slope(T independent, T dependent)--返回線性回歸線的斜率，即方程式中的a值依賴于=a*獨立+ b。截至Hive 2.2.0。
27、regr_sxx(T independent, T dependent)--返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr2)
28、regr_sxy(T independent, T dependent) --返回值等于REGR_COUNT(expr1, expr2) * COVAR_POP(expr1, expr2)
29、regr_syy(T independent, T dependent) --返回值等于REGR_COUNT(expr1, expr2) * VAR_POP(expr1)
二、Analytic--分析函數(shù)
1、cume_dist() --計算一行在組中的相對位置，CUME_DIST總是返回大于0、小于或等于1的數(shù)，該數(shù)表示該行在N行中的位置
例子：
2、dense_rank() OVER([partition_by_clause] order_by_clause) --返回從1開始的遞增整數(shù)序列。輸出序列為ORDER BY表達(dá)式的重復(fù)值生成重復(fù)整數(shù)。
例：
3、first_value() OVER([partition_by_clause] order_by_clause [window_clause]) --從窗口的第一行返回表達(dá)式值。如果輸入表達(dá)式為NULL，則返回值為NULL。
例：
4、lag(expr [,offset] [,default]) OVER() ([partition_by_clause] order_by_clause) --該函數(shù)使用前一行的列值返回表達(dá)式的值。您可以指定一個整數(shù)偏移量，它指定一個行位置，位于當(dāng)前行之前的某些行數(shù)。表達(dá)式參數(shù)中的任何列引用都引用該前一行中的列值。
例：
5、last_value(expr) OVER([partition_by_clause] order_by_clause [window_clause]) --返回窗口中最后一行的表達(dá)式值。如果輸入表達(dá)式為NULL，返回值為NULL。
例：
6、lead(expr[,offset] [,default]) OVER([partition_by_clause] order_by_clause) --此函數(shù)使用以下行的列值返回表達(dá)式的值。指定一個整數(shù)偏移量，它指定行位置到當(dāng)前行之后的若干行。表達(dá)式參數(shù)中的任何列引用都引用來自后面的行的列值。
例：
7、percent_rank() --按百分比排序
例：
8、rank() OVER([partition_by_clause] order_by_clause) --返回整數(shù)的升序，從1開始。輸出序列為表達(dá)式的重復(fù)值生成重復(fù)整數(shù)。在為“綁定”輸入值生成重復(fù)輸出值之后，函數(shù)通過綁定值的數(shù)量來增加序列。
例：
9、row_number() OVER([partition_by_clause] order_by_clause) --分組排序，同一組內(nèi)相同值會給不同序號
三、Collection--集合函數(shù)
1、array_contains(Arraya,val) --如果數(shù)組內(nèi)包含val,返回True例：
如果fullname這一列不是數(shù)組形式，先用array()函數(shù)轉(zhuǎn)化下
1selectarray_contains(array(fullname),'a') fromods.baseorganize
2、map_keys(Map?a)--返回map中的所有key,返回類型array
1selectmap_keys(complaintDetail) fromtmp.crm_data_customer_complain
3、map_values(Map?a)--返回map中的所有values,返回類型array
4、size(Map|Array?a)--返回映射或數(shù)組類型中的元素數(shù)目。
5、sort_array(Array?a)--根據(jù)數(shù)組元素的自然排序?qū)⑤斎霐?shù)組按升序排序，并返回數(shù)組。
四、Complex Type--復(fù)合類型
1、array(val1，val2....) --創(chuàng)建具有給定元素的數(shù)組2、create_union(tag,val1,val2,...) --用標(biāo)記參數(shù)指向的值創(chuàng)建一個聯(lián)合類型3、map(key1, value1, ...) --創(chuàng)建具有給定鍵/值對的映射。4、named_struct(name1, val1, ...) --用給定的字段名和值創(chuàng)建結(jié)構(gòu)。注意：這里的name和val只能是給定的值，用已有的列不行
5、struct(val1, val2, ...) --創(chuàng)建具有給定字段值的結(jié)構(gòu)。這里的val可以是已有的列，也可以是給定的值
五、Conditional--有條件的函數(shù)
1、assert_true(BOOLEAN condition) --如果成立返回NULL，如果不成立報異常
2、coalesce(T v1, T v2, ...) --返回其參數(shù)中的第一個非空表達(dá)式，當(dāng)你要在n個字段中選取某一個非空值
例：
3、if()
4、isnotnull(a) --如果不是NULL返回TRUE,否則返回FALSE
5、isnull(a) --如果是NULL返回TRUE,否則返回FALSE
6、nullif(a, b) --如果a=b,返回NULL,否則返回a
7、nvl(T value, T default_value) --如果value是空，返回default_value，否則返回value
六、Date---日期函數(shù)
1、add_months(DATE|STRING|TIMESTAMP start_date, INT num_months) --返回開始日期之后的num_months,開始日期可以是date型，string型，TIMESTAMP型的，num_months是整形的
2、current_date --返回當(dāng)前日期，同一個查詢中返回的是同一個日期
3、current_timestamp() --返回當(dāng)前的時間戳，同一個查詢中返回的是同一個時間戳
4、datediff(STRING enddate, STRING startdate) --返回結(jié)束日期和開始日期之間的間隔天數(shù)
5、date_add(DATE startdate, INT days) --開始日期加幾天
6、date_format(DATE|TIMESTAMP|STRING ts, STRING fmt) --按指定格式返回時間date 如：date_format("2016-06-22","MM-dd")=06-22
7、date_sub(DATE startdate, INT days) --開始日期減幾天
8、day(STRING date) --返回一個date或TIMESTAMP的日，例：day('1970-11-01 00:00:00') = 1, day('1970-11-01') = 1
9、dayofmonth(STRING date) --同上
10、extract(field FROM source) --返回日期/時間的單獨部分，比如年、月、日、小時、分鐘等等
支持提取的類型有：day, dayofweek, hour, minute, month, quarter, second, week and year.
11、from_unixtime(BIGINT unixtime [, STRING format]) --將時間的秒值轉(zhuǎn)換成format格式（format可為“yyyy-MM-dd hh:mm:ss”,“yyyy-MM-dd hh”,“yyyy-MM-dd hh:mm”等等）如from_unixtime(1250111000,"yyyy-MM-dd") 得到2009-03-12
12、from_utc_timestamp(T a, STRING timezone) --如果給定的時間戳并非UTC，則將其轉(zhuǎn)化成指定的時區(qū)下時間戳
13、hour(STRING date) --返回時間字符串小時
14、last_day(STRING date) --返回這個月的最后一天的日期，忽略時分秒部分（HH:mm:ss）
15、minute(STRING date) --返回時間字符串的分鐘
16、month(STRING date) --返回時間字符串的月
17、months_between(DATE|TIMESTAMP|STRING date1, DATE|TIMESTAMP|STRING date2)
--返回date1與date2之間相差的月份，如date1>date2，則返回正，如果date1
18、next_day(STRING start_date, STRING day_of_week) --返回當(dāng)前時間的下一個星期X所對應(yīng)的日期 如：next_day('2015-01-14', 'TU') = 2015-01-20 以2015-01-14為開始時間，其下一個星期二所對應(yīng)的日期為2015-01-20
19、quarter(DATE|TIMESTAMP|STRING a) --返回當(dāng)前時間屬于哪個季度
20、second(STRING date) --返回時間字符串的秒
21、to_date(STRING timestamp) --返回字符串的日期部分，結(jié)果1970-01-01這種形式
22、to_utc_timestamp(T a, STRING timezone) --如果給定指定的時區(qū)下時間戳，則將其轉(zhuǎn)化成UTC下的時間戳
23、trunc(STRING date, STRING format)--返回時間的最開始年份或月份 如trunc("2016-06-26",“MM”)=2016-06-01 trunc("2016-06-26",“YY”)=2016-01-01 注意所支持的格式為MONTH/MON/MM, YEAR/YYYY/YY
24、unix_timestamp([STRING date [, STRING pattern]]) --獲取本地時區(qū)下的時間戳
25、weekofyear(STRING date) --返回時間字符串位于一年中的第幾個周內(nèi) 如weekofyear("1970-11-01 00:00:00") = 44, weekofyear("1970-11-01") = 44
26、year(STRING date)--返回時間字符串的年份部分
七、Mathematical--數(shù)學(xué)函數(shù)
1、abs(DOUBLE a) --求絕對值
2、acos(DECIMAL|DOUBLE a) --求反余弦值,a介于-1到1之間，否則返回NULL值
3、asin(DECIMAL|DOUBLE a)--求反正弦值,a介于-1到1之間，否則返回NULL值
4、atan(DECIMAL|DOUBLE a) --求反正切值
5、bin(BIGINT a) --計算二進(jìn)制a的STRING類型，a為BIGINT類型
6、bround(DOUBLE a [, INT decimals]) --銀行家舍入法（1~4：舍，6~9：進(jìn)，5->前位數(shù)是偶：舍，5->前位數(shù)是奇：進(jìn)）
7、cbrt(DOUBLE a) --求a的立方根
8、ceil(DOUBLE a) --求其不小于小給定實數(shù)的最小整數(shù)如：ceil(6) =ceil(6.9) = 6
9、ceiling(DOUBLE a) --同上
10、conv(BIGINT|STRING a, INT from_base, INT to_base) --將GIGINT/STRING類型的num從from_base進(jìn)制轉(zhuǎn)換成to_base進(jìn)制
11、cos(DECIMAL|DOUBLE a) --求余弦值
12、degrees(DECIMAL|DOUBLE a) --將弧度值轉(zhuǎn)換角度值
13、e() --返回常數(shù)e
14、exp(DECIMAL|DOUBLE a) --返回e的a冪次方， a可為小數(shù)
15、factorial(INT a) --求a的階乘
16、floor(DOUBLE a) --向下取整,最數(shù)軸上最接近要求的值的左邊的值 6.10->6 -3.4->-4
17、greatest(T a1, T a2, ...) --求最大值
18、hex(BIGINT|BINARY|STRING a) --計算十六進(jìn)制a的STRING類型，如果a為STRING類型就轉(zhuǎn)換成字符相對應(yīng)的十六進(jìn)制
19、least(T a1, T a2, ...) --求最小值,返回值列表的最小值。當(dāng)一個或多個參數(shù)為NULL時固定為返回null，并且嚴(yán)格的類型限制放寬，與“<”運算符一致。
20、ln(DECIMAL|DOUBLE a) --返回a的自然對數(shù)
21、log(DECIMAL|DOUBLE base, DECIMAL|DOUBLE a) --以base為底的對數(shù)，base 與 a都是DOUBLE類型
22、log10(DECIMAL|DOUBLE a) --返回以10為底a的對數(shù)
23、log2(DECIMAL|DOUBLE a) --返回以2為底a的對數(shù)
24、negative(T?a) --返回-a
25、pi()--返回π
26、pmod(T?a, T b) --a對b取模
27、positive(T?a) --返回a，覺得這個函數(shù)完全沒有意義
28、pow(DOUBLE a, DOUBLE p) --返回a的p次方
29、pow(DOUBLE a, DOUBLE p)--返回a的p次冪，同上
30、radians(DECIMAL|DOUBLE a) --將角度值轉(zhuǎn)換成弧度值
31、rand([INT seed]) --每行返回一個DOUBLE型隨機(jī)數(shù),seed是隨機(jī)因子
32、round(DOUBLE a [, INT d]) --返回對a四舍五入的BIGINT值
33、shiftleft(T?a, INT b) --按位左移
34、shiftright(T?a, INT b) --按位右移
35、shiftrightunsigned(T?a, INT b) --無符號按位右移（<<<）
36、sign(T?a) --如果a是正數(shù)則返回1.0，是負(fù)數(shù)則返回-1.0，否則返回0.0
37、sin(DECIMAL|DOUBLE a) --返回a的正弦
38、sqrt(DECIMAL|DOUBLE a)--返回a的平方根
39、tan(DECIMAL|DOUBLE a) --返回a的正切值
40、unhex(STRING a) --hex的逆方法
41、width_bucket(NUMBER expr, NUMBER min_value, NUMBER max_value, INT num_buckets)
Returns an integer between 0 and num_buckets+1 by mapping expr into the ith equally sized bucket. Buckets are made by piding [min_value, max_value] into equally sized regions. If expr < min_value, return 1, if expr > max_value return num_buckets+1. (as of Hive 3.0.0)
八、Misc--各種各樣的類型
1、aes_decrypt(BINARY input, STRING|BINARY key)
--Decrypt input using AES (as of Hive 1.3.0). Key lengths of 128, 192 or 256 bits can be used. 192 and 256 bits keys can be used if?Java?Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files are installed. If either argument is NULL or the key length is not one of the permitted values, the return value is NULL. Example: aes_decrypt(unbase64('y6Ss+zCYObpCbgfWfyNWTw=='), '1234567890123456') = 'ABC'.
2、aes_encrypt(STRING|BINARY input, STRING|BINARY key)
--Encrypt input using AES (as of Hive 1.3.0). Key lengths of 128, 192 or 256 bits can be used. 192 and 256 bits keys can be used if Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy Files are installed. If either argument is NULL or the key length is not one of the permitted values, the return value is NULL. Example: base64(aes_encrypt('ABC', '1234567890123456')) = 'y6Ss+zCYObpCbgfWfyNWTw=='.
3、crc32(STRING|BINARY a)
--Computes a cyclic redundancy check value for string or binary argument and returns bigint value (as of Hive 1.3.0). Example: crc32('ABC') = 2743272264.
4、current_database() --返回當(dāng)前的數(shù)據(jù)庫名稱
5、current_user() --返回當(dāng)前的用戶名稱
6、get_json_object(STRING json, STRING jsonPath)
--從指定路徑上的JSON字符串抽取出JSON對象，并返回這個對象的JSON格式，如果輸入的JSON是非法的將返回NULL,注意此路徑上JSON字符串只能由數(shù)字 字母 下劃線組成且不能有大寫字母和特殊字符，且key不能由數(shù)字開頭，這是由于Hive對列名的限制
7、hash(a1[, a2...]) --返回參數(shù)的哈希值
8、java_method(class, method[, arg1[, arg2..]]) --所謂的匹配參數(shù)簽名java方法，利用反射。
9、md5(STRING|BINARY a)
--為參數(shù)a計算一個MD5 128位的校驗和。該值作為32個十六進(jìn)制數(shù)字的字符串返回，如果參數(shù)為空，則返回null。
Calculates an MD5 128-bit checksum for the string or binary (as of Hive 1.3.0). The value is returned as a string of 32 hex digits, or NULL if the argument was NULL. Example: md5('ABC') = '902fbdd2b1df0c4f70b4a5d23525e932'.
10、reflect(class, method[, arg1[, arg2..]]) --匹配參數(shù)簽名java方法，利用反射。
11、sha(STRING|BINARY a)
--Calculates the SHA-1 digest for string or binary and returns the value as a hex string (as of Hive 1.3.0). Example: sha1('ABC') = '3c01bdbb26f358bab27f267924aa2c9a03f'
12、sha1(STRING|BINARY a) --同上
13、sha2(STRING|BINARY a, INT b)
--Calculates the SHA-2 family of hash functions (SHA-224, SHA-256, SHA-384, and SHA-512) (as of Hive 1.3.0). The first argument is the string or binary to be hashed. The second argument indicates the desired bit length of the result, which must have a value of 224, 256, 384, 512, or 0 (which is equivalent to 256). SHA-224 is supported starting from Java 8. If either argument is NULL or the hash length is not one of the permitted values, the return value is NULL. Example: sha2('ABC', 256) = 'b5d4045c3f466fa91fe2cc6abe79232a1a'
14、version() --返回hive的使用版本,
--字符串包含2個字段，第一個是生成號，第二個是生成哈希。示例：“"select version();可能返回:2.1.0.2.5.0.0.1245 R027 59B9C5CE1A3D7D6B6D2E6DE23 78FB0C9322。實際結(jié)果將取決于您的構(gòu)建。
15、array?xpath(STRING xml, STRING xpath)
--The xpath family of UDFs are wrappers around the Java XPath library javax.xml.xpath provided by the JDK. The library is based on the XPath 1.0 specification.
16、xpath_boolean(STRING xml, STRING xpath)
--16-22都是這個意思
--UDF的XPath的家庭在java XPath庫包裝javax.xml.xpath通過JDK提供。該庫是基于XPath 1規(guī)范的。
17、xpath_float(STRING xml, STRING xpath)
18、xpath_int(STRING xml, STRING xpath)
19、xpath_long(STRING xml, STRING xpath)
20、xpath_number(STRING xml, STRING xpath)
21、xpath_short(STRING xml, STRING xpath)
22、xpath_string(STRING xml, STRING xpath)
九、String--字符串
十、Data Masking--數(shù)據(jù)屏蔽
十一、Table Generating--表格生成
十二、Type Conversion--類型轉(zhuǎn)化
1、binary(BINARY|STRING a) --將輸入的值轉(zhuǎn)化為二進(jìn)制
2、cast(a as T) --格式轉(zhuǎn)化，如果轉(zhuǎn)化不成功會返回NULL值
