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