Postgres窗口函数

Postgres窗口函数简单使用

定义

官方文档对窗口函数的解释：

A window function performs a calculation across a set of table rows that are somehow related to the current row. This is comparable to the type of calculation that can be done with an aggregate function. However, window functions do not cause rows to become grouped into a single output row like non-window aggregate calls would. Instead, the rows retain their separate identities. Behind the scenes, the window function is able to access more than just the current row of the query result.

我的理解：

窗口函数可以在保留明细的情况下获取聚合结果

使用示例

假如存在一张工资表(salary_detail)，这个包含三列：部门，员工编号，工资。如下所示：

department	empno	salary
摸鱼	1	1000
摸鱼	2	2000
摸鱼	3	3000
钓虾	1	4000
钓虾	2	5000
钓虾	3	6000
偷螃蟹	1	7000
偷螃蟹	2	8000
偷螃蟹	3	9000

计算平均月薪

如果我们只希望得到每个部门的平均月薪，那么根据部门分组，然后取月薪的均值即可：

select department,
       avg(salary) as dep_avg_salary
from salary_detail
group by 1;

department	dep_avg_salary
摸鱼	2000
钓虾	5000
偷螃蟹	8000

在不使用窗口函数的情况下，如果需要在每行salary_detail表数据的基础上派生出部门平均工资，我们需要通过子查询实现：

with t1 as
  (select department,
          avg(salary) as avg_salary
   from salary_detail
   group by 1)
select t2.department,
       t2.empno,
       t2.salary,
       t1.dep_avg_salary
from salary_detail t2
left join t1 on t2.department = t1.department;

同样的结果，也可以通过窗口函数搞定：

-- 下面两个sql是等价的
-- 1
select department,
       empno,
       salary,
       (avg(salary) over (partition by department)) as dep_avg_salary
from empsalary;

-- 2
select department,
       empno,
       salary,
       (avg(salary) over w) as dep_avg_salary
from empsalary
window w as (partition by department);

department	empno	salary	dep_avg_salary
摸鱼	1	1000	2000
摸鱼	2	2000	2000
摸鱼	3	3000	2000
钓虾	1	4000	5000
钓虾	2	5000	5000
钓虾	3	6000	5000
偷螃蟹	1	7000	8000
偷螃蟹	2	8000	8000
偷螃蟹	3	9000	8000

找出每个部分月薪前两名的人

with t as (
select department, empno, salary,
       (rank() over (partition by department order by salary desc)) as rank
from empsalary)
select * from t where rank <= 2;

结尾

本文只是窗口函数非常简单的应用，可查阅 官方文档 寻找更多复杂的场景。