欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

Oracle/Hive/ImpalaSQL三者对比讲解

程序员文章站 2023-01-28 15:14:50
5 function 指内置的function,不讨论udf。另外,操作符都不比较了,区别不大。 5.1 数学函数 功能 oracle hive impala abs 绝对值,有 有 有...

5 function 指内置的function,不讨论udf。另外,操作符都不比较了,区别不大。

5.1 数学函数

功能 oracle hive impala
abs 绝对值,有
sin/sinh/asin/cos/cosh/acos/tan/tanh/atan/atan2 三角函数其中atan2接受两个参数(二维平面中的坐标) 没有sinh/cosh/tanh/atan2 同hive
bitand 按位与,有    
ceil 天花板值,有 有,还有个别名ceiling 有,同hive
exp e的多少次,有 有,还有个函数e()返回e 有,同hive
floor 地板值,有
ln 以e为底的log,有
log 以某个double为底的log,有 有,还有两个特殊底的log:log2和log10 有,同hive
mod oracle的mod的计算方式为mod(n2,n1)=n2 - n1 * floor(n2/n1),这与经典取模还不同,比如oracle的mod(-11,4)=-3,但经典取模等于1 在hive里取模用pmod,返回值一定是个正数,比如pmod(-11,4)=1。但这与经典取模还是不同,比如pmod(-11,-4)=1,但经典取模等于-3 impala的pmod和hive的pmod相同,另外impala还有个fmod和oracle的mod相同
power 求幂,有 有,还有个别名pow 有,同hive
remainder 取余,remainder(n2,n1)=n2 - n1 * floor(n2/n1)    
round 舍入,支持1个参数和2个参数两种版本 有另外还有一个bround,使用half_even舍入模式,见官方手册 有,同oracle
sign 符号函数,有
sqrt 开方,有
trunc 截取数值的小数点后多少位(如果是负值则往前推)    
degrees/radians   角度/弧度互转 同hive
positive/negative   相当于在数值前加+/-号(因此没什么卵用) 同hive
pi   返回pi值 同hive
factorial   阶乘,1.2.0以后才有  
cbrt   求立方根,1.2.0以后才有  
shiftleft/shiftright/shiftrightunsigned   按位左移/右移/无符号右移  
greatest/least 返回一串值中的最大/最小值,这串值的类型可以是任意,只要可比较大小(所以其实不是数学函数而是通用比较) 有,1.1.0以后

5.2 字符(串)函数

功能 oracle hive impala
ascii 输入必须char,返回该字符的ascii数值 输入string,返回该string第一个字符的ascii数值 同hive
base64 / unbase64   将二进制值转为base64的string(un则是反向)  
concat concat(char1, char2)char或char2均可为char,varchar2,nchar,nvarchar2,clob, or nclob之一 concat(string|binary a, string|binary b...)可以有多个参数另外提供concat_ws用于指定特殊分隔符的连接 同hive(除了不支持binary类型),且也有concat_ws
decode/encode   string decode(binary bin, string charset)binary encode(string src, string charset)编码和解码用的,用于支持hive特有的binary类型 (其实oracle和impala也有decode,但作用完全不同,见条件函数中的decode)
find_in_set   find_in_set(string str, string strlist)strlist是用’,’分割的一组string,该函数将寻找strlist中第一个精确匹配的str 同hive
format_number   将数字格式化为string  
get_json_object   抽取json对象,不常用  
in_file   in_file(string str, string filename)检测str是否为filename对应文件中的某行,不常用  
initcap 将每个单词(以空白分隔)转换为首字母大写其余小写的形式 同oracle,1.1.0开始有 同oracle
instr { instr| instrb| instrc| instr2| instr4}(string , substring [, position [, occurrence ] ])搜索子串,不同数据类型调用名不同,最多可带四个参数,其中第三个是开始位置,第四个是出现的第几次 instr(string str, string substr)只接受两个参数 同hive
length { length| lengthb| lengthc| length2| length4}(char)串长,不同数据类型调用名不同 有(仅length) 同hive
levenshtein   返回两个串的levenshtein距离(编辑距离)1.2.0后才有  
locate   特殊情况的instr,可以指定匹配的开始位置。oracle因为本来instr就支持所以并不需要该函数 同hive
lower 转小写 有,且有一个别名叫lcase 同hive
lpad / rpad lpad(expr1, n [, expr2 ])在expr1之前用expr2填充n个字符,如expr2省略默认用n个单空格填充。rpad类似只是在右边 有,但expr2不能省略 同hive
ltrim / rtrim ltrim(char [, set ])去掉char左侧包含在set中的字符,如省略set,则只去除空格符。rtrim类似只是在右边 有,但没有set参数,即只能去除空格符 同hive
parse_url   抽取url,可以指定抽取url的类型和建名 同hive
printf   按格式打印对象数组  
regexp_extract / regexp_substr regexp_substr(source_char, pattern[, position [, occurrence [, match_param [, subexpr] ] ] ] )按正则表达式抽取字符串,并返回其中一部分。注意oracle和hive/impala中函数名不同 regexp_extract(string subject, string pattern, int index)类似oracle,但不能指定起始位置,替换序号和匹配参数 同hive
regexp_replace regexp_replace(source_char, pattern[, replace_string [, position [, occurrence [, match_param ] ] ] ] )按正则表达式替换字符串中的一部分 regexp_replace(string initial_string, string pattern, string replacement)类似oracle,但不能指定起始位置,替换序号和匹配参数 同hive
regexp_count regexp_count (source_char, pattern [, position [, match_param]])按正则表达式对字符串中子串的出现次数计数    
regexp_instr regexp_instr (source_char, pattern[, position [, occurrence [, return_opt [, match_param [, subexpr] ]]]] )在字符串中查找满足正则表达式的子串第一次出现的位置    
repeat   重复n次 同hive
replace 简单替换文本    
reverse   返回逆串 同hive
sentences   简单的分词功能,很奇怪hive为何提供这样的功能  
soundex 取英文中的“谐音”,可能是用于存在脏数据时的匹配吧,诡异的功能 有,1.2.0以后  
space   返回n个空格 同hive
split   使用正则表达式分割字符串  
str_to_map   将字符串转为键值对  
strleft / strright     返回最左/最右的n个字符,是substr的简化版
substr / substring { substr| substrb| substrc| substr2| substr4}(char, position [, substring_length ])不同数据类型调用名不同 类似oracle,只有substr和其别名substring 同hive
substring_index   substring_index(string a, string delim, int count)返回a在delim出现第count次前的部分,1.3.0后才有  
translate translate(expr, from_string, to_string)将expr,按from_string中出现的每个字符替换为to_string中对应序号的字符oracle还有种translate...using语法是其他两个没有的 同oracle 同oracle
trim trim([ { { leading | trailing | both }[ trim_character ] | trim_character}from]trim_source)比较灵活,可指定去除前端还是后端,去除什么字符。如果只留trim_source一个参数则等同于后两者 trim(string a)简单去除a前后的空白 同hive
upper 转大写 有,且有一个别名lcase 同hive

5.3 日期函数

功能 oracle hive impala
add_months 在某日期上加上n个月 有,1.1.0以后
current_date 返回当前时间(和session的时区相关),精确到秒 返回当前时间(sql评估时的时间,同一个查询中多次调用该函数值相同),1.2.0以后  
current_timestamp 返回当前时间(和session的时区相关),精确到毫秒,返回类型为timestamp with time zone 返回当前时间(sql评估时的时间,同一个查询中多次调用该函数值相同,精确到毫秒),1.2.0以后 有,另有一个别名now
date_add / date_sub   在某日期上加/减n天 同hive,可接收timestamp或string类型。只接收timestamp类型的该函数有两套,称为days_add/days_sub,adddate/subdate(真不懂impala搞这么多名字雷同的东西干啥)
date_format   用格式字符串格式化日期(可为date/timestamp/string)  
date_part     省略order参数的extract
datediff   求两个日期间差的天数 同hive
day / dayofmonth   返回该日期在月内的日数,两个函数同义 同hive
dayname     返回周间的名字,即’sunday’到’saturday'
dayofweek     返回周间的序号,1(sunday)到7(saturday)
dayofyear     返回是本年第几天
dbtimezone 数据库当前时区    
extract extract( { year | month | day | hour | minute | second | timezone_hour | timezone_minute | timezone_region | timezone_abbr } from { expr })按参数提取日期中的某部分   extract(timestamp, string unit)extract(unit from timestamp)比oracle多一种格式
from_tz 将时间戳和时区合并为带时区的时间戳    
from_unixtime   将unix纪元以来的秒数转化为时间字符串 同hive
from_utc_timestamp   将utc的时间戳值转化为指定时区的时间戳值 同hive
hour   返回时间字符串的小时值 同hive
hours_add / hours_sub     在某日期上加/减n个小时
last_day 返回该日期所在月份的最后一天 同oracle,1.1.0以后  
localtimestamp 返回当前时间(和session的时区相关),精确到毫秒,返回类型为timestamp    
microseconds_add /microseconds_sub     在某日期上加/减n微秒
milliseconds_add / milliseconds_sub     在某日期上加/减n毫秒
minute   返回时间字符串的分钟值  
minutes_add /minutes_sub     在某日期上加/减n分钟
month   返回时间字符串的月份 同hive
months_add /months_sub     在某日期上加/减n个月(其实第一个和add_months重复)
months_between 返回两个日期间相差的月数,注意返回值是个浮点数 同oracle,1.2.0后  
nanoseconds_add /nanoseconds_sub     在某日期上加/减n纳秒(impala搞这么多没用的加减函数真不知道干什么)
new_time 将时区1的时间转换为时区2的时间    
next_day 返回指定日期后下一个星期几的日期 同oracle,1.2.0后  
numtodsinterval /numtoyminterval 生成n时间单位的一个日期间隔。前一函数的间隔可选day,hour,minute,second,后一个的间隔可选month,year    
quater   返回日期的季度值(1-4),1.3.0后  
round 对日期做舍入    
second   返回时间字符串的秒值 同hive
second_add /second_sub     在某日期上加/减n秒
sessiontimezone 返回session的时区    
sys_extract_utc 从日期字符串中抽取utc日期    
sysdate / systimestamp 返回操作日期,前者到秒,后者到微秒    
to_char 将date或timestamp类型转换为varchar2,常用    
to_date   返回时间戳的日期部分 同hive
to_dsinterval /to_yminterval 将一个字符串转换为interval day to second / interval year to month类型的时间间隔    
to_timestamp / to_timestamp_tz 将一个字符串转换为时间戳,前一个不带时区,后一个带时区    
to_utc_timestamp   将带时区的时间戳转换为utc的 同hive
trunc 对日期做舍,语法类似round,支持舍入到年、季度、月、周、日、小时、分钟等精度 1.2.0后有,只支持舍入到年、月 同oracle
tz_offset 返回某个时区和utc间的偏差值    
unix_timestamp   返回秒为单位的时间戳数值,无参数时为当前时间,一个参数时需要传入yyyy-mm-dd hh:mm:ss格式的时间字符串,两个参数时可以自定义传入时间格式 同hive
weekofyear   返回该日期所在的周是年中第几周  
weeks_add /weeks_sub     在某日期上加/减n周
year   返回该日期的年份 同hive
years_add / years_sub     在某日期上加/减n年

5.4 转换函数

功能 oracle hive impala
cast cast({ expr | multiset (subquery) } as type_name)输入可以是表达式也可以是集合 cast(expr as )将表达式转换为指定类型 同hive
binary   将参数转换为binary类型  
其余各种*to* / to_* 都是oracle特有的转换函数,建议看官方手册    

5.5 条件函数

功能 oracle hive impala
case … when   1,case a when b then c [when d then e]* [else f] endwhen a = b, returns c; when a = d, returns e; else returns f.
2,case when a then b [when c then d]* [else e] endwhen a = true, returns b; when c = true, returns d; else returns e.
同hive(注:因为在oracle中如case...when是表达式,而hive和impala中这些是用函数来处理的,虽然提供了与oracle相似的语法,但语言层面实现机制不同)
coalesce 接收多个值,返回这些值中第一个非null的,如果全是null则返回null 同oracle 同oracle
decode decode(expr, search, result [, search, result ]... [, default ])对expr,如果满足第一个search则返回第一个result,如果满足第二个search则返回第二个result   同oracle
if   if(boolean testcondition, t valuetrue, t valuefalseornull)testcondition如果真则返回valuetrue,如果假或null则返回valuefalseornull 同hive
isnull   isnull(a)如果a为null返回true,否则返回false isnull(type a, type ifnotnull)如果a非null则返回a,否则返回ifnotnull。注意和hive有重大区别,另该函数有别名ifnull和nvl
isnotnull   和isnull相反  
lnnvl lnnvl(condition)如果condition为false或unknown返回true,如果为true返回false    
nanvl nanvl(n2, n1)如果n2是nan返回n1,否则返回n2    
nullif nullif(expr1, expr2)等价与case when expr1 = expr2 then null else expr1 end   同oracle
nullifzero     nullifzero(numeric_expr)如果numeric_expr为0返回null,否则返回该表达式的值
nvl nvl(expr1, expr2)如果expr1为null则返回expr2,否则返回expr1 同oracle 同oracle
nvl2 nvl2(expr1, expr2, expr3)如果expr1非null则返回expr2,如果为null则返回expr3    
zeroifnull     zeroifnull(numeric_expr)如果numeric_expr为null返回0,否则返回该表达式的值

5.6 聚合函数 以上5类函数都是对单行操作的,接下去的两类:聚合函数和分析函数,则是跨行操作的。

功能 oracle hive impala
appx_median     appx_median([distinct | all] expression)以抽样的方式,计算某列大致的中位数值
avg avg([ distinct | all ] expr) [ over(analytic_clause) ]over后可带分析函数子句 同oracle 同oracle
collect collect( [ distinct | unique ] column [ order by expr ] )该语句汇聚某列的值构造一张内嵌表    
collect_set / collect_list   将一组对象组成一个array,其中带set的函数会去重,带list的函数不去重  
corr corr(expr1, expr2) [ over (analytic_clause) ]计算两列的皮尔逊相关系数,over后可带分析函数子句还有两个变种corr_s和corr_k 没有over子句,也没有变种  
count count({ * | [ distinct | all ] expr }) [ over (analytic_clause) ]over后可带分析函数子句 同oracle 同oracle
covar_pop covar_pop(expr1, expr2) [ over (analytic_clause) ]计算总体协方差,over后可带分析函数子句 没有over子句  
covar_samp covar_samp(expr1, expr2) [ over (analytic_clause) ]计算样本协方差,over后可带分析函数子句 没有over子句  
cume_dist 计算一组数据的累积分布,有聚合和分析两种用法,详见官方手册    
dense_rank dense_rank(expr [, expr ]...) within group (order by expr [ desc | asc ] [ nulls { first | last } ] [,expr [ desc | asc ] [ nulls { first | last } ] ]... )和rank的区别是,有并列值时下一位会继续编号,如两个值并列第1,下一个值排第2    
first / last 某数据集进行排序后,可对第一条/最后一条记录进行处理,详见官方手册    
group_id 用于消除group by子句返回的重复记录    
grouping 用于区分是数据库中本来的值还是汇聚后的值    
grouping_id 输入一列或多列,返回grouping位向量的十进制值    
group_concat     将一列的值组合为一个string
histogram_numeric   计算数值列的直方图  
listagg 将一列的值组合为一个string,可指定分组、排序等参数    
max / min max([ distinct | all ] expr) [ over (analytic_clause) ]over后可带分析函数子句 同oracle 同oracle
median median(expr) [ over (query_partition_clause) ]中位数,over后可带分析函数子句    
ndv     类似count(distinct ),但给出的是估算值,计算速度快
ntile   将分区分到x个组上,每个给一个编号,配合percentile等使用  
percent_rank 类似cume_dist,计算一组数的百分位分布,有聚合和分析两种用法,详见官方手册    
percentile_count /percentile_dist 接受一个分位值,返回满足该分位值的插值后数值/集合中原始值,详见官方手册    
percentile   只接受整型,计算p百分位数的值  
percentile_approx   接受double型,计算p百分位数的值  
rank rank(expr [, expr ]...) within group (order by expr [ desc | asc ] [ nulls { first | last } ] [, expr [ desc | asc ] [ nulls { first | last } ] ]... )计算排位值,有聚合和分析两种用法,这里的语法是聚合,重要,详见官方手册    
regr_* 一堆线性回归函数,不重要    
stat_* 一堆统计函数,不重要    
stddev /stddev_pop /stddev_samp 计算样本标准差、总体标准差、累积样本标准差,over后可带分析函数子句 没有stdev函数,没有over子句 没有over子句

sum
sum([ distinct | all ] expr) [ over (analytic_clause) ]over后可带分析函数子句 同oracle 同oracle
sys_xmlagg /xmlagg 将一列的值组合为一个xml,其中sys_xmlagg课指定xml格式,xmlagg可指定值排序方式    
var_pop / var_samp / variance 计算样本方差、总体方差、累积样本方差,over后可带分析函数子句 没有over子句,且var_pop和variance功能一样 同oracle其中var_pop / var_samp也可写为variance_pop /variance_samp

5.7 分析(开窗)函数

分析(开窗)函数中,有一部分是和聚合函数同名的,只要可以带over子句的都可作为分析(开窗)函数使用,这部分不再重复列举。

此外在列举函数前,需要对比一下三者的over子句和window子句的不同写法(主要差别就在于window子句):

over子句:

oracle:[ query_partition_clause ] [order_by_clause [ windowing_clause ] ]

hive:没找到细节定义,目测和oracle一致

impala:和oracle一致

query_partition_clause:

oracle:partition by { expr[, expr ]...| (expr[, expr ]... ) }

hive:没找到细节定义,目测和oracle一致

impala:没找到细节定义,目测和oracle一致

order_by_clause:

oracle:order [ siblings ] by { expr | position| c_alias } [ asc | desc ] [ nulls first | nulls last ] [, { expr | position | c_alias } [ asc | desc] [ nulls first | nulls last ] ]...

hive:没找到细节定义,目测和oracle一致

impala:没找到细节定义,目测和oracle一致

windowing_clause:

oracle:{ rows | range } { between { unbounded preceding | current row | value_expr { preceding | following } }and { unbounded following | current row | value_expr { preceding | following } } | { unbounded preceding | current row | value_expr preceding } }

hive:rows ((current row) | (unbounded |[num]) preceding) and (unbounded | [num]) following

impala:{ rows | range } between [ { m |unbounded } preceding | current row] [ and [current row | { unbounded | n }following] ]

功能 oracle hive impala
cume_dist 有聚合和分析两种用法(上面已列) 有分析用法  
dense_rank dense_rank( ) over([ query_partition_clause ] order_by_clause)这里的用法是分析 同oracle 同oracle(hive和impala只有分析用法没有聚合用法,故单列)
first_value / last_value first_value { (expr) [ {respect | ignore} nulls ] | (expr [ {respect | ignore} nulls ]) } over (analytic_clause)返回某个排序集合的第一个/最后一个值 first_value(expr) over([partition_by_clause] order_by_clause [window_clause])和oracle相比略简化 同hive
lag lag { ( value_expr [, offset [, default]]) [ { respect | ignore } nulls ] | ( value_expr [ { respect | ignore } nulls ] [, offset [, default]] ) } over ([ query_partition_clause ] order_by_clause)提供了一种同时访问表的多行的方式,即对访问的某行往前推offset行,避免了自连接,参考官方手册中取员工的本月和上月工资的例子 lag (expr [, offset] [, default]) over ([partition_by_clause] order_by_clause)和oracle相比略简化 同hive
lead lead { ( value_expr [, offset [, default]] ) [ { respect | ignore } nulls ] | ( value_expr [ { respect | ignore } nulls ] [, offset [, default]] ) } over ([ query_partition_clause ] order_by_clause)类似lag,不同之处是往后推而不是往前推 lead (expr [, offset] [, default]) over ([partition_by_clause] order_by_clause)和oracle相比略简化 同hive
percent_rank 有聚合和分析两种用法(上面已列) 有分析用法  
rank rank( ) over ([ query_partition_clause ] order_by_clause)计算排位值,这里的用法是分析,有并列值时,下一位会跳开并列的个数再编号,如两个值并列第1,下一个值排第3。重要且常用 同oracle 同oracle(hive和impala只有分析用法没有聚合用法,故单列)
row_number row_number( ) over ([ query_partition_clause ] order_by_clause)编行号,重要且常用 同oracle 同oracle