1、开窗函数语法:
func([all] expr) over ([partition_clause] [order_by_clause] [windowing_clause])
func:
avg | count | max | min | sum
partition_clause:
partition by part_list
order_by_clause:
order by order_list
part_list:
part_list,expr
order_list:
order_list,expr [asc | desc] [nulls first | nulls last]
windowing_clause:
window_type start_window_clause | window_type between_window_clause
window_type:
rows | range
start_window_clause:
unbounded | preceding | current row | value_expr preceding
between_window_clause:
between bound_window_clause and bound_window_clause
bound_window_clause:
sart_window_clause | unbounded following | value_expr following
value_expr:
num | interval expr interval_type
interval_type:
year | month | day | hour | minute | second (暂不支持 microsecond 和 millisecond)
2、说明及约束:
1)between bound and bound
左侧的bound定义起始点,右侧的bound定义结束点;
单独一个bound,认为是起始点的定义,结束点默认为current row。
2)unbounded preceding
指明起始点,即当前partition中的行首,此句不能出现在结束点。
3)unbounded following
指明结束点,即当前partition中的行尾,此句不能出现在起始点。
4)current row
指明当前行;
出现在起始点时,结束点不可为value_expr preceding;
出现在结束点时,起始点不可为value_expr following。
5)当如下四种情况时,order by后可以有多个表达式,否则order by后仅能有一个表达式;rows无此限制;
range between unbounded prededing and current row(= range unbounded preceding)
range between current row and unbounded following
range between current row and current row(= range current row)
range between unbounded preceding and unbounded following
6)value_expr preceding | value_expr following
preceding是减,following是加,统计在这个范围内的数据。
7)rows关键字
value_expr标识了物理行的偏移量,为常量或常量表达式,正整数。
8)range关键字
value_expr标识了逻辑行的偏移量,为常量或常量表达式,正整数或interval子句;order by后的表达式只能有一个;
value_expr为数值时,order by的表达式应为数值或日期时间类;
value_expr为interval子句时,order by的表达式应为日期时间类。