聚合函数大家都用过，像sum，min，avg等，一般是和group by配合使用。和聚合函数类似，窗口函数也是计算一些行集合（多个行组成的集合，我们称之为窗口window frame）的数据。但和常规的聚集函数不同的是，窗口函数不会将参与计算的行合并成一行输出，而是将计算出来的结果带回到了计算行上。
比如我们要想将员工的工资和该部门的平均工资进行比较。传统的作法是先通过group by 统计出部门平均工资，再用员工表左连一下上面的统计结果。
with dep_avg_salary as (
select dept_name as dept_name, avg(salary) as dep_avg_salary
from emp_salary
group by dept_name
)
select e.dept_name, e.emp_no, e.salary, das.dep_avg_salary
from emp_salary e
left join dep_avg_salary das on das.dept_name = e.dept_name
order by e.dept_name
使用窗口函数可以简化很多
SELECT dept_name, emp_no, salary, avg(salary) OVER (PARTITION BY dept_name) as dep_avg_salary
FROM emp_salary;
我们看到这里列出的还是员工的数据，只是加上了根据depname分组统计的平均工资。
特别注意：窗口函数只能在SELECT和ORDER BY子句中使用，不能在任何其他地方使用，比如GROUP BY、HAVING和WHERE子句
窗口函数必须和over字句配合使用。over子句包含PARTITION BY和ORDER BY两部分，分别用来分组和确定组内输出顺序，PARTITION BY和ORDER BY都是可选的。
如果两个都省略的，整个表会被作为一个分组，查询出来的是所有员工的平均工资，这个还好理解。
如果省略PARTITION BY，而包含ORDER BY呢？
这个结果是不是很诡异？
对比之前的查询结果，第一组”develop”的avg_salary(5660)是“develop”部门的平均工资，第三组“sales”的值(5218.18)是所有员工的平均工资，那第二组“personnel”的值(5114.28)是什么呢？怎么产生的呢？
啊，尽然是“develop”和“personnel”两个部门的平均工资。这样的话第三组是所有员工的平均工资就说的通了，也就是“develop”、“personnel”和“sales”三个部门的平均工资。小伙伴们使用的时候要特别注意呀。
如果一个查询中包含多个窗口函数，那么我们可以写多个OVER子句。但如果这些窗口函数的作用是一样的，那分开写多个很烦人，也容易出错。这种情况下，我们可以将窗口里面的内容写成一个WINDOW子句，然后在多个OVER子句中引用。比如我们列一下部门的平均工资和最高工资：
使用windows字句，可以是这样：
最后说一句：
所有的聚合函数都可以作为窗口函数使用
网友评论