首页 > 代码库 > orcale和hive常用函数对照表(?代表未证实)

orcale和hive常用函数对照表(?代表未证实)

函数分类oraclehive说明
字符函数upper(‘coolszy‘)upper(string A) ucase(string A)将文本字符串转换成字母全部大写形式
lower(‘KUKA‘)lower(string A) lcase(string A)将文本字符串转换成字母全部小写形式
initcap(‘kuKA aBc‘) 将每个单词的首字母大写,其他位置的字母小写
concat(‘Hello‘,‘ world‘)concat(string A, string B…)连接多个字符串,合并为一个字符串,可以接受任意数量的输入字符串
substr(‘hello‘,2,3)substr(string A, int start, int len) substring(string A, int start, int len)从文本字符串中指定的位置指定长度的字符。                             
length(‘hello‘)length(string A)返回字符串的长度
lpad(‘smith’,10,‘*‘)lpad(string str, int len, string pad)返回指定长度的字符串,给定字符串长度小于指定长度时,由指定字符从左侧填补
rpad(‘smith’,10,‘*‘)rpad(string str, int len, string pad)返回指定长度的字符串,给定字符串长度小于指定长度时,由指定字符从右侧填补。
trim(‘ Mr Simth  ‘)trim(string A)删除字符串两端的空格,字符之间的空格保留
ltrim(‘ Mr Simth  ‘)ltrim(string A)删除字符串左边的空格,其他的空格保留
rtrim(‘ Mr Simth  ‘)rtrim(string A)删除字符串右边的空格,其他的空格保留
reverse(‘abcdef’)reverse(string A)返回倒序字符串
replace(‘HELLO‘,‘L‘,‘x‘)regexp_replace(string A, string B, string C)字符串A中的B字符被C字符替代
ascii(‘A‘) ascii(string str)返回字符串中首字符的数字值
chr(54740)给出整数,返回对应的字符
concat_ws(string SEP, string A, string B…)链接多个字符串,字符串之间以指定的分隔符分开。
parse_url(string urlString, string partToExtract [, string keyToExtract])返回URL指定的部分。parse_url(‘http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1′, ‘HOST’) 返回:’facebook.com’
space(int n)返回指定数量的空格
repeat(string str, int n)重复N次字符串
split(string str, string pat)将字符串转换为数组。
regexp_extract(string subject, string pattern, int index)通过下标返回正则表达式指定的部分。regexp_extract(‘foothebar’, ‘foo(.*?)(bar)’, 2) returns ‘bar.’
sentences(string str, string lang, string locale)将字符串中内容按语句分组,每个单词间以逗号分隔,最后返回数组。 例如sentences(‘Hello there! How are you?’) 返回:( (“Hello”, “there”), (“How”, “are”, “you”) )
e()返回e的值
pi()返回pi的值
instr(‘oracle traning‘,‘ra‘,1,2)find_in_set(string str, string strList)在一个字符串中搜索指定的字符,返回发现指定的字符的位置
get_json_object(string json_string, string path)get_json_object(‘{"store":{"fruit":[{"weight":8,"type":"apple"},{"weight":9,"type":"pear"}],"bicycle":{"price":19.95,"color":"red"}},"email":"amy@only_fo_json_udf_test.net","owner":"amy"}‘, ‘$.store.fruit‘)
数值函数round(double a)round(double a)四舍五入
trunc(412.23,2)舍去小数,但不进位(正数表示小数点后,表示小数点前)

abs(-100)
abs(double a)返回指定值的绝对值
asin(0.5)asin(double a)返回x的反正弦,即是X。
acos(-1)acos(double a)返回X的反余弦,即余弦是X,
ceil(3.1415927) ceil(3.1415927) 返回大于或等于给出数字的最小整数
sin(20)sin(double a)返回给定角度的正弦值
cos(-3.1415927)cos(double a)返回余弦
sqrt(64)sqrt(double a)sqrt(double a) 返回数值的平方根
exp(2)exp(double a)返回e的n次方
floor(2345.67)floor(double a)对给定的数字取整数
rand(),rand(int seed)返回大于或等于0且小于1的平均分布随机数(依重新计算而变)
ln(2.7182818)ln(double a)返回一个数字的对数值
LOG(n1,n2)log(double base, double a)返回给定底数及指数返回自然对数
power(2,10)pow(double a, double p) power(double a, double p)返回某数的乘幂
ROUND和TRUNCround(double a)round(double a, int d)按照指定的精度进行舍入
SIGNsign(double a)取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
tan(20)tan(double a)返回数字的正切值
atan(1) atan(1) 返回一个数字的反正切值
conv(BIGINT num, int from_base, int to_base)将指定数值,由原来的度量体系转换为指定的试题体系。例如CONV(‘a’,16,2),返回。参考:’1010′ 
positive(int a) positive(double a)返回A的值,例如positive(2),返回2。
negative(int a) negative(double a)返回A的相反数,例如negative(2),返回-2。
mod(int a, int b) mod(double a, double b)pmod(int a, int b) pmod(double a, double b)返回a除b的余数的绝对值
日期函数sysdate 返回当前日期
months_between(sysdate,‘16-6月 -09‘)返回两个日期之间的月数 
add_months(sysdate,4)返回加上指定月数后的日期
TO_DATE(string,‘format‘)将字符串转化为ORACLE中的一个日期
TO_CHAR(date,‘format‘)将日期格式成所需要的类型
next_day(sysdate,‘星期一‘)返回当前日期之后的下一个星期一的日期
last_day(sysdate)求本月的最后一天 
NEW_TIME(date,‘this‘,‘that‘)给出在this时区=other时区的日期和时间
to_date(string timestamp)返回时间中的年月日: to_date(“1970-01-01 00:00:00″) = “1970-01-01″
unix_timestamp(string date, string pattern)指定时间输入格式,返回到1970年秒数:unix_timestamp(’2009-03-20′, ‘yyyy-MM-dd’) = 1237532400 参考:
unix_timestamp(string date)指定日期参数调用UNIX_TIMESTAMP(),它返回参数值’1970- 01 – 0100:00:00′到指定日期的秒数。
unix_timestamp()如果不带参数的调用,返回一个Unix时间戳(从’1970- 01 – 0100:00:00′到现在的UTC秒数)为无符号整数。
year返回指定时间的年份,范围在1000到9999,或为”零”日期的0。
month(string date)返回指定时间的月份,范围为1至12月,或0一个月的一部分,如’0000-00-00′或’2008-00-00′的日期。
day(string date) dayofmonth(date)返回指定时间的日期
hour(string date)返回指定时间的小时,范围为0到23。
minute(string date)返回指定时间的秒,范围为0到59。
weekofyear(string date)返回指定日期所在一年中的星期号,范围为0到53。
datediff(string enddate, string startdate)两个时间参数的日期之差。
date_sub(string startdate, int days)给定时间,在此基础上减去指定的时间段。
date_add(string startdate, int days)给定时间,在此基础上加上指定的时间段。
聚合函数avgavg求平均值
countcount统计数据
maxmax求最大值
VARIANCE(DISTINCT|ALL)covar_samp(col1, col2)两列数值样本协方差
var_pop(col)返回指定列的方差
STDDEV(distinct|all)var_pop(col)求标准差,ALL表示对所有的值求标准差,DISTINCT表示只对不同的值求标准差
minmin求最小值
sumsum求和
percentile(col, p)返回数值区域的百分比数值点。0<=P<=1,否则返回NULL,不支持浮点型数值。
stddev_pop(col)返回指定列的偏差
corr(col1, col2)返回两列数值的相关系数
转换函数to_number(st[,fmt]) st从字符型数据转换成按指定格式的数值,缺省时数值格式串的大小正好为整个数
to_char(m[,fmt])m从一个数值转换为指定格式的字符串fmt缺省时,fmt值的宽度正好能容纳所有的有效数字
cast(expr as <type>)类型转换。例如将字符”1″转换为整数:cast(’1′ as bigint),如果转换失败返回NULL。
hex(BIGINT a) hex(string a)将整数或字符转换为十六进制格式
unhex(string a)十六进制字符转换由数字表示的字符。
bin(BIGINT a)返回二进制格式
degrees(double a) 将值从弧度到度
radians(double a) 将值从度转换为弧度
nvl(m,n) colalese如果m值为null,返回n,否则返回m
COALESCE(T v1, T v2, …)返回一组数据中,第一个不为NULL的值,如果均为NULL,返回NULL。
RAWTOHEXT将一个二进制构成的字符串转换为十六进制
HEXTORAW将一个十六进制构成的字符串转换为二进制
ROWIDTOCHAR将ROWID数据类型转换为字符类型
TO_MULTI_BYTE将字符串中的单字节字符转化为多字节字符
CHARTOROWID将字符数据类型转换为ROWID类型
其他GREATEST返回一组表达式中的最大值,即比较字符的编码大小.
LEAST返回一组表达式中的最小值 
UID返回标识当前用户的唯一整数
USER返回当前用户的名字
USEREVN返回当前用户环境的信息,opt可以是:
ENTRYID,SESSIONID,TERMINAL,ISDBA,LABLE,LANGUAGE,CLIENT_INFO,LANG,VSIZE
GROUP BYGROUP BY主要用来对一组数进行统计
HAVING无(where子句代替)对分组统计再加限制条件
ORDER BYORDER BY/sort by用于对查询到的结果进行排序输出
DECODEwhen\case当a=b时,返回c;当a=d时,返回e,否则返回f。
?distinct去掉重复的
?desc/asc升序/降序排列
逻辑函数or逻辑或
?and逻辑和
not逻辑非
多表joinjoin等值连接
left semi join半连接
left right outer join外连接
增加的hive函数 TO_DATE(v_txdate,‘YYYYMMDD‘)格式化所需要的日期
ADD_MONTHS(TO_DATE(v_txdate,0)增加月数

LAST_DAY(v_dealyyyymm)
返回每个月最后一天
MONTHS_BETWEEN(v_dealyyyymm1,v_dealyyyymm2)返回两个日期之间的月数 
    
?:未证实   

orcale和hive常用函数对照表(?代表未证实)