分析(开窗)函数中,有一部分是和聚合函数同名的,只要可以带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] ]