postgresql笔记004—窗口函数

窗口函数

窗口函数的调用总是包含一个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);