Oracle/Hive/ImpalaSQL三者对比讲解
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 |
上一篇: 清淡的菜是什么,一起来看看