数据分析利器之窗口函数
聚合函数大家都用过，像sum，min，avg等，一般是和group by语句配合使用。和聚合函数类似，窗口函数也是计算一些行集合（多个行组成的集合，我们称之为窗口window frame）的数据。但和常规的聚集函数不同的是，窗口函数不会将参与计算的行合并成一行输出，而是将计算出来的结果带回到了计算行上。
比如我们要想将员工的工资和该部门的平均工资进行比较。传统的作法是先通过group by 统计出部门平均工资，再用员工表左连一下上面的统计结果。
假设有个工资表, Salary有部门, 工号,工资3个字段. 不想建表的话, 可以使用CTE来模拟表:
with Salary(部门,工号,工资) as(
values ('开发部',1,10000),('开发部',3,12000),('开发部',5,20000),('开发部',7,18000),('开发部',9,15000)
,('人事部',2,8000),('人事部',4,12000)
,('销售部',6,10000),('销售部',8,16000),('销售部',10,26000),('销售部',11,20000)
),Agg as(
select 部门,avg(工资) as 平均工资 from Salary group by 部门
)select s.部门,s.工号,s.工资,Agg.平均工资 from Salary s left join Agg on Agg.部门=s.部门
order by s.部门
运行结果如下:
如果使用窗口函数的话, 就能避免join:
with Salary(部门,工号,工资) as(
values ('开发部',1,10000),('开发部',3,12000),('开发部',5,20000),('开发部',7,18000),('开发部',9,15000)
,('人事部',2,8000),('人事部',4,12000)
,('销售部',6,10000),('销售部',8,16000),('销售部',10,26000),('销售部',11,20000)
)select s.*, avg(s.工资) over(partition by s.部门) as 平均工资 from Salary s order by s.部门
得到的结果跟上面的一模一样, 可以看到还是工资表Salary的数据, 但是追加了一列平均工资, 该列根据部门分组, 并计算出部门的平均工资
需要注意的是: 窗口函数只能在select中使用, 不能在group by, having, where中使用, 此外, 所有的聚合函数, 比如min, max, avg, sum, count等都可以作为窗口函数配个over子句使用. 另外, 窗口函数一定要搭配over子句才能正常工作
窗口函数必须跟over子句配合使用, over()子句中包含partition by 和order by两部分, 分别用来分组和确定组内输出顺序, partition by 和order by两部分都是可选的, 如果两个都省略, 整个查询结果就会当成一个分组, 查询出来的是所有员工的平均工资,这个很好理解
with Salary(部门,工号,工资) as(
values ('开发部',1,10000),('开发部',3,12000),('开发部',5,20000),('开发部',7,18000),('开发部',9,15000)
,('人事部',2,8000),('人事部',4,12000)
,('销售部',6,10000),('销售部',8,16000),('销售部',10,26000),('销售部',11,20000)
)select s.*, avg(s.工资) over() as 平均工资 from Salary s order by s.部门
运行结果:
这相当于是计算开发部+人事部+销售部三个部门的平均工资
再考虑一下, 如果over()中省略了partition by用于分组的字段, 只保留order by子句会是一个什么结果?
with Salary(部门,工号,工资) as(
values ('开发部',1,10000),('开发部',3,12000),('开发部',5,20000),('开发部',7,18000),('开发部',9,15000)
,('人事部',2,8000),('人事部',4,12000)
,('销售部',6,10000),('销售部',8,16000),('销售部',10,26000),('销售部',11,20000)
)select s.*, avg(s.工资) over(order by s.部门) as 平均工资 from Salary s --这里over中省略了partition by
结果如下:
这个平均工资是怎么来的?
按部门排序, 开发部的很好理解 即: (10000+12000+20000+18000+15000)/5, 人事部的不再是(8000+12000)/2, 而是(10000+12000+20000+18000+15000 + 8000 +12000)/7.0 = 13571.4285714286
同样道理: 销售部的就变成整个开发部+人事部+销售部的平均工资
如果一个查询中有多个窗口函数, 那么很自然可以写多个over子句: 比如列出部门的平均工资和最高工资
with Salary(部门,工号,工资) as(
values ('开发部',1,10000),('开发部',3,12000),('开发部',5,20000),('开发部',7,18000),('开发部',9,15000)
,('人事部',2,8000),('人事部',4,12000)
,('销售部',6,10000),('销售部',8,16000),('销售部',10,26000),('销售部',11,20000)
)select s.*, avg(s.工资) over(partition by s.部门) as 平均工资
,max(s.工资) over(partition by s.部门) as 部门最高薪
from Salary s
上面的语句中多个over子句中写重复的partition by有些累赘, 可以在后面使用window子句定义一个窗口即可:
with Salary(部门,工号,工资) as(
values ('开发部',1,10000),('开发部',3,12000),('开发部',5,20000),('开发部',7,18000),('开发部',9,15000)
,('人事部',2,8000),('人事部',4,12000)
,('销售部',6,10000),('销售部',8,16000),('销售部',10,26000),('销售部',11,20000)
)select s.*, avg(s.工资) over by_dept as 平均工资
,max(s.工资) over by_dept as 部门最高薪
from Salary s
window by_dept as(
partition by s.部门
)
上面的语句使用window 定义一个名为by_dept的窗口, 这样, 如果语句中有多个窗口函数, 就可以通过over 窗口名的方式复用它!
以上例子在CockroachDB 和Postgresql中均正常运行!