窗口函数:窗口类似于窗户,限定一个空间范围,可以理解为记录集合。窗口函数也就是满足某种条件的几率集合上执行特殊函数,对于每条记录都要在此窗口内执行函数,窗口大小都是固定的,这种属于静态窗口;不同的记录对应不同的窗口,这种动态变化的窗口叫滑动窗口
窗口函数的基本用法如下:
(1)函数名 ([expr])over 子句
(2)函数() over()其中,over是关键字,用来指定函数执行的窗口范围,包含三个分析子句:分组(partition by )子句,排序(order by)子句,窗口(rows)子句,如果后面括号中什么都不写,则意味着窗口包含满足where条件的所有行,窗口函数基于所有行进行计算;如果不为空,则支持以下语法来设置窗口:
函数名 ([expr])over (partition by <要分组的列> order by <要排序的列> rows between <数据范围>)
rows between 2 preceding and current row --取当前行和前面两行
rows between unbounded preceding and current row --包括本行和之前所有的行
rows between current row and unbounded following --包括本行和之后所有的行
rows between 3 preceding and current row --包括本行和前面三行
rows between 3 preceding and 1 following --从前面三行和下面一行,总共五行
-- 当order by后面缺少窗口从句条件,窗口规范默认是rows between unbounded preceding and current row.
-- 当order by和窗口从句都缺失, 窗口规范默认是 rows between unbounded preceding and unbounded following
具体例子:
select score,dense_rank() over (order by score desc) as ranking from Scores
select employee_name, department, hours,FIRST_VALUE(employee_name)
over (partition by department order by hours) least_over_time from overtime;
tips:
PARTITION BY
用于窗口函数,提供了在结果集内部不同分区中执行函数的能力。GROUP BY
用于聚合函数,对结果集中的行按指定列进行分组,并对每个组应用聚合函数