#数学函数
1、log(double base, double a) #以base为底的a的对数
select log(2,10);
2、pow(double a, double p) #计算a的p次幂
select pow(2,3);
3、conv(bigint/string num, #将bigint/string类型的num从from_base进制转换成to_base进制
int from_base, int to_base)
select conv('2',10,2);//把2从十进制转化为2进制
4、pmod(int/double a, int/double b)#a对b取模
select pmod(5,8);//5除8的余数等于5
5、hex(string a) unhex(string a) #求一个字符串a的正反16进制转化，类似于简单的加密
select hex('2');//字符串2的ASCII码是50，转成16进制是32
select unhex('32');//16进制数32，转成10进制是50,50对应的字符串是2
6、positive(int/double a) #返回a
select positive(55);
7、negative(int/double a) #返回a的相反数
select negative(55);
8、sign(double/decimal a) #如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0
select sign(-20);
9、e()/pi() #数学常数e/pi
select e();
select pi();
10、greatest(T v1, T v2, ...) #求最大值
select greatest(1,5,8,10);
11、least(T v1, T v2, ...) #求最小值
select least(1,5,8,10);
12、bround(double a,int d) #double 财务舍入法
13、factorial(int a) #求a 20以内的阶乘
14、shiftleft(int a, int b) #按位左移
15、shiftright(int a, int b) #按拉右移
#集合函数
1、int size(Map<K.V>) #求map的长度
2、int size(Array<T>) #求数组的长度
3、array<K> map_keys(Map<K.V>) #返回map中的所有key
4、array<V> map_values(Map<K.V>)#返回map中的所有value
5、array<T> sort_array(Array<T>)#按自然顺序对数组进行排序并返回
6、boolean array_contains(Array<T>, value)#如该数组Array<T>包含value返回true，否则返回false
#类型转换函数
type cast(expr as <type>)#将expr转换成type类型
select cast('2' as int);//字符串2转换成int类型，如果转换失败将返回NULL
#日期函数
1、string from_unixtime(bigint unixtime, string format)#将时间的秒值转换成format格式
select from_unixtime(15487935451,'yyyy-MM-dd');
2、string date_format(date/timestamp/string date,string format)
select date_format('2460-10-16 15:45:34','MM-dd');
3、string current_date()
select current_date();
4、string to_date(string timestamp) #返回时间字符串的日期部分
select to_date('2460-10-16 15:45:34');
5、string current_timestamp() #返回现在的时间
select current_timestamp();
6、bigint unix_timestamp()#获取本地时区下的时间戳
select unix_timestamp();
7、bigint unix_timestamp(string date)#将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳
select unix_timestamp('2022-08-20 19:25:01');
8、bigint unix_timestamp(string date, string format_pattern)#将指定时间字符串格式字符串转换成Unix时间戳
select unix_timestamp('2022-08','yyyy-MM-dd');//格式不对返回null
select unix_timestamp('2022-08-20 19:25:01','yyyy-MM-dd');//格式正确返回Unix的时间秒数
9、string date_add(string date,int days)
select date_add('2022-08-26',25);
10、string add_months(current_date(),int m);
select add_months('2022-08-26',-5);
11、string next_day(current_date(),string dayofweek); #日期之后的下一个Mo/Tu/WE/Th/Fr/Sa/Su
select next('2020-11-11','Sa');
12、string last_day(current_date());#返回日期该月的最后一天
select last_day('2020-11-11');
13、string trunc(current_date(),'YY/MM');#返回日期的本年/本月的第一天
select trunc('2020-11-12','YY');//返回2020年的第一天
select trunc('2020-11-12','MM');//返回2020年11月的第一天
扩展：
#返回日期的本季度第一天
select concat_ws('-',cast(year('2020-11-11') as string),cast(ceil(month(current_date())/3)*3-2 as string),'01');
#返回日期的本周的第一天(以周日算一周的第一天)
select date_add(next_day('2020-11-11','SU'),-7);
14、int datediff(string enddate, string startdate)#计算开始时间startdate到结束时间enddate相差的天数
select datediff('2020-12-25','2019-05-12');
15、double months_between(string enddate, string startdate)#计算开始时间startdate到结束时间enddate相差的月数
select month_between('2020-12-25','2019-05-12');
#条件函数
1、T if(boolean Condition, T vTrue, T vFalse) #如果Condition 为true就返回vTrue,否则返回vFalse
select if(true,1,0);
2、T nvl(T value, T default_value) #如果value值为NULL就返回default_value,否则返回value
select nvl(null,5);
select nvl(1,5);
3、T coalesce(T v1, T v2, ...) #返回第一非null的值，如果全部都为NULL就返回NULL
select coalesce(null,2,3,null);
4、T case a when b then c [when d then e]* [else f] end #如果a=b就返回c,a=d就返回e，否则返回f
select case 1 when 1 then 'a' when 2 then 'b' else 'c' end;
5、T case when a then b [when c then d]* [else e] end #如果a=ture就返回b,c= ture就返回d，否则返回e
select case when true then 'a' when false then 'b' else 'c' end;
6、boolean isnull( a ) #如果a为null就返回true，否则返回false
select isnull(a);
7、boolean isnotnull ( a ) #如果a为非null就返回true，否则返回false
select isnotnull('a');
#字符函数
1、int ascii(string str)#返回str中首个ASCII字符串的整数值
select ascii('223');//字符串223中首个字符是2，其ASCII是50
2、string concat(string|binary A, string|binary B...)#对二进制字节码或字符串按次序进行拼接
select concat('abc','ABC');
select concat(10,1011);
3、string concat_ws(string SEP, string A, string B...)#与concat()类似，但使用指定的分隔符进行分隔
select concat_ws('-','abc','ABC');
4、string concat_ws(string SEP, array<string>)#拼接Array中的元素并用指定分隔符进行分隔
select concat_ws('-',array('abc','A'));
5、array<array<string>> sentences(string sentence);#字符串sentence将被转换成单词数组，不同符号结果不一样
select sentences(‘hello world,how are you’);
select sentences(‘hello world?how are you’);
select sentences("hello world!how are you");
6、array<struct<string,double>> ngrams(array<array<string>>, int N, int K) #按N个连续的单词出现的频次，倒序取TOP K (若N个单词的频次一样，则随机取K个)
select ngrams(sentences('hello world?how are you'),1,2);
7、array<struct<string,double>> context_ngrams(array<array<string>>, array<string>, int K) #与array中指定单词之后配合出现的频次，倒序取TOP K，array中指定单词后面要加null
select context_ngrams(sentences('hello china?how are you,hello world,are you ok?'),array('you',null),2);
8、string decode(binary bin, string charset)
#使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任意输入参数为NULL都将返回NULL
select encode('我爱中华001abc你们','UTF-16BE');
9、binary encode(string src, string charset)
select decode(encode('我爱中华001abc你们','UTF-16BE'),'UTF-16BE');
10、string format_number(number x, int d) #将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数
select format_number(478493145631.2546,5);
11、string get_json_object(string json_string, string path)
#从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式，如果输入的JSON是非法的将返回NULL,注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制
select get_json_object('{"name":"henry"}','$.name');
select get_json_object('{"name":"henry","info":{"city":"nj"}}','$.info.city');
select get_json_object('{"name":"henry","info":["city","nj"]}','$.info[0]');
12、boolean in_file(string line, string path)#path中指向的文件是否包含line
13、string parse_url(string urlString, string partToExtract [, string keyToExtract])
#返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，
这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO,
例如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') ='facebook.com'，
如果参数partToExtract值为QUERY则必须指定第三个参数key
如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') =‘v1’
14、string printf(String format, T...t)#按照printf风格格式输出字符串
select printf('%s,%d,%.2f','henry',18,23456.789);---henry,18,23456.79
15、Boolean like # %
select * from shop where contact.mobile like '18%'; 16、Boolean rlike # [] {} ? + \d \w ...
select * from shop where contact.mobile rlike '18\\d';//查表中手机号是18开头的select * from shop where contact.mobile rlike '18\\d{9}';//查询手机号18开头的且是九位数的17、string regexp_replace(string src, string regex, string replacement)
#按照Java正则表达式PATTERN将字符串src中符合条件的部分成replacement所指定的字符串，如里replacement这空的话，抽符合正则的部分将被去掉
select regexp_replace('you me he you','you','YOU');
select regexp_replace('you me he y_ou young yuuuuuug','y\\w{2,3}
select regexp_replace('["henry","pola","ariel"]','\\[|\\]|"','');
18、string regexp_extract(string src, string regex, int index)
抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串
select regexp_extract('namehenryokdarlingduck','name(.*?)ok(.*?)duck',2);
19、array<string> split(string str, string regex)
按照正则表达式pat来分割字符串str,并将分割后的数组字符串的形式返回
select split('henry.chen@hotmail.com','\\.|@');
20、map<string,string> str_to_map(string str,string spel1,string spel2)
将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"："
str_to_map('name:henry,age:22,gender:male');select str_to_map('name#henry|age#22|gender#male','\\|','#');
array/struct/map转类型
21、string translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)
将input出现在from中的字符串替换成to中的字符串
select translate('abcabcabaac','ab','*#');
22、initcap 首字母大写
23、string substr(string|binary A, int start) substring(string|binary A, int start) #对于字符串A,从start位置开始截取字符串并返回
select substr('henry',2);
24、string substr(string|binary A, int start, int len) substring(string|binary A, int start, int len)
对于二进制/字符串A,从start位置开始截取长度为length的字符串并返回
select substr('henry',2,3);
25、int locate(string substr, string str[, int pos])#查找字符串str中的pos位置后字符串substr第一次出现的位置，pos从1开始
select locate('en','henry',1);
26、int instr(string str, string substr) #查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的
select instr('henry','en');
md5单向加密
select md5('abc');
String base64（binary bin）将二进制值转换64位的字符串
binary unbase64(string str)将64位的字符串转换二进制值
select base64(cast('henry' as binary));
select unbase64('aGVucnk=');
hex/unhex(双向的可以解密)
aes_encrype/aes_decrype(双向的可以解密)
sha2(string/binary,int(0/1));单向加密int选0或1
select sha2('henry',1);
int levenshtein(string A, string B)
计算两个字符串之间的差异大小
select levenshtein('a','b');
string soundex(string A)
将普通字符串转换成soundex字符串
select soundex('bc');
string initcap(string A)
将字符串A转换第一个字母大写其余字母的字符串
#聚合函数
BIGINT count(*), #统计总行数，包括含有NULL值的行
int count(expr), #统计提供非NULL的expr表达式值的行数
BIGINT count(DISTINCT expr[, expr...])#统计提供非NULL且去重后的expr表达式值的行数
double sum(col), sum(DISTINCT col)
sum(col),表示求指定列的和，sum(DISTINCT col)#表示求去重后的列的和
double avg(col), #表示求指定列的平均值
avg(DISTINCT col)#表示求去重后的列的平均值
double min(col) #求指定列的最小值
double max(col) #求指定列的最大值
double var_pop(col) #求指定列数值的方差:离散程度
double var_samp(col) #求指定列数值的样本方差：变异程度
double stddev_pop(col) #求指定列数值的标准偏差
double stddev_samp(col) #求指定列数值的样本标准偏差
double covar_pop(col1, col2) #求指定列数值的协方差
double covar_samp(col1, col2) #求指定列数值的样本协方差
double corr(col1, col2) #返回两列数值的相关系数
double percentile(BIGINT col, p) #返回col的p%分位数
collect_list(col) #行转列
collect_set(col) #
#表生成函数
1、Array Type explode(array<TYPE> a)#对于a中的每个元素，将生成一行且包含该元素
2、N rows explode(ARRAY<T>) 每行对应数组中的一个元素
select name,city from employee_id lateral view explode(cities) ct as city;
3、N rows explode(MAP<K,v>) 每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值
select name,pos,score from employee_id lateral view explode(scores) st as pos,score;
4、N rows posexplode(ARRAY)#与explode类似，不同的是还返回各元素在数组中的位置
select posexplode(array('aa','bb','cc'));
5、N rows stack(INT n, v_1, v_2, ..., v_k)#把M列转换成N行，每行有M/N个字段，其中n必须是个常数
select stack(2,'aa','bb','cc','dd');
6、tuple json_tuple(jsonStr, k1, k2, ...)#从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值
7、tuple parse_url_tuple(url, p1, p2, ...)#返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,....是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY>
select printf('%s %s',fn,ln) name,age,hobby from jsontuple
lateral view json_tuple(line,'name','hobbies','age') jt as name,hobbies,age
lateral view json_tuple(name,'first','last') jt1 as fn,ln
lateral view explode(split(regexp_replace(hobbies,'\\[|\\]|"',''),',')) hs as hobby;
8、inline(ARRAY<STRUCT[,STRUCT]>)#将结构体数组提取出来并插入到表中
select inline(array(struct('aa','bb','cc'),struct('ee','dd','ff')));
