窗口函数
窗口函数的调用总是包含一个over子句,周免直接跟着窗口函数的名称和参数。这是窗口函数在语法上区别于普通函数或者聚合函数功能的地方。over子句决定如何将查询的行进行拆分,以便给窗口函数处理。over子句内的partition by列表指定将行划分成组或分区,组或分区共享相同的partition by 表达式的值。对于每一行,窗口函数在和当前行落在同一个分区的所有行上进行计算。
建表:
drop table if exits empsalary;
create table empsalary(
depname varchar(255),
empno int8,
empname varchar(255),
salary numeric
);
insert into empsalary values ('develop',1,'aa',15000);
insert into empsalary values ('develop',2,'bb',6000);
insert into empsalary values ('develop',3,'cc',8000);
insert into empsalary values ('develop',4,'aa',8000);
insert into empsalary values ('personnel',5,'cc',6000);
insert into empsalary values ('personnel',6,'aa',9000);
insert into empsalary values ('sales',7,'cc',15000);
insert into empsalary values ('sales',8,'aa',26000);
insert into empsalary values ('sales',9,'cc',15000);
insert into empsalary values ('sales',10,'aa',11000);
select * from empsalary;
案例1:
select depname,empno,salary,avg(salary)
over(partition by depname) as avg_salary from empsalary;
--根据depname列来进行分组
案例2:rank在进行分区排序中,如果有相同的会进行跳跃排序。
select depname,empno,salary,
rank() over(partition by depname order by salary desc) ranka
from empsalary;
rank 不需要明确的参数,因为它的行为完全取决于 OVER 子句
窗口函数的行来自查询的from子句产生,并且如果有的话,经过where,group by 和 having 子句过滤过的 虚拟表。
比如,被移除掉的行,因为不符合where条件,所以是不能被任何窗口函数可见的。一个查询可以包含多个窗口函数,通过不同的over 子句用不同的方式分割数据,但是他们都作用在这个虚拟表定义的同一个行集合。
如果行排序不重要,order by可以省略,而且在只有一个包含了所有的行的分区中,我们甚至可以直接省略partition by.
还有一个重要的关于窗口函数的概念: 对于每一行,有在其分区范围内的行集,又称为它的 window frame. 许多(不是全部)窗口函数,只作用于window frame中的行上,而不是整个分区。默认情况下,如果使用order by ,那么这个frame包含从分区开始到当前行的所有行,以及那些当前行后面的,根据order by 子句等于当前行的所有行,如果省略order by 那么,frame 默认包含分区中的所有行。
案例3:
select salary ,sum(salary) over() from empsalary;
select salary,sum(salary) over(partition by depname order by salary desc) from empsalary;
select salary,sum(salary) over() suma,rank() over(partition by depname order by salary )
from empsalary;
窗口函数仅允许在查询的select列表和order by 子句中使用。在其他地方禁止使用,比如 group by,having,where子句中,禁止使用。这是因为他们逻辑上在处理这些子句之后执行。此外,窗口函数在标准聚合函数之后执行。这意味,在一个窗口函数中的参数中包含一个标准聚合函数的调用是有效地,但是反过来不行。
案例3:
select depname,empno,salary,pos
from
(select depname,empno,salary,rank() over(partition by depname order by
salary desc,empno) as pos from empsalary
) as ss
where pos <3;
查询rank小于3的行。
当查询涉及多个窗口函数时,课携程每一个都带有单独的over 子句,但是如果期待为多个窗口函数采用相同的窗口行为,这样做会产生重复,并且容易出错。作为替代,每个窗口行为可以在 WINDOW子句中进行命名,然后再被over引用。如下案例:
select sum(salary) over w,avg(salary) over w from empsalary
window w as (partition by depname order by salary desc);