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 |