partition by 与 group by 的区别

group by x1,x2 ...

将除了 x1,x2 以外的字段通过聚合函数 “压缩” 成一行。

select department,avg(salary) from employees group by department;

窗口函数() over (partition by 分组列 order by 排序列)

根据分组列进行分组，然后对每个组通过排序列进行排序，最后通过窗口函数计算每列的值。

select name,department,avg(salary) over (partition by department) as '平均工资',salary - avg(salary) over (partition by department) as '和平均差距' from employees;