功能

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以后

有

功能

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

功能

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

返回该日期所在的周是年中第几周

WEEKS_ADD / WEEKS_SUB

在某日期上加/减n周

YEAR

返回该日期的年份

同hive

YEARS_ADD / YEARS_SUB

在某日期上加/减n年

功能

Oracle

Hive

Impala

CAST

CAST({ expr | MULTISET (subquery) } AS type_name)

输入可以是表达式也可以是集合

cast(expr as <type>)

将表达式转换为指定类型

同hive

BINARY

将参数转换为binary类型

其余各种*TO* / TO_*

都是Oracle特有的转换函数，建议看官方手册

功能

Oracle

Hive

Impala

CASE … WHEN

1，CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

When a = b, returns c; when a = d, returns e; else returns f.

2，CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END

When 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，否则返回该表达式的值

功能

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([ 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

功能

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