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 <type>)
将表达式转换为指定类型
|
同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] 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，否则返回该表达式的值
|
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 ] ]
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 |
5.8 其他
剩下就是些各库特有的东西了，不常用，看看官方手册就好。