PostgreSQL数据库中窗口函数的语法与使用
什么是窗口函数?
一个窗口函数在一系列与当前行有某种关联的表行上执行一种计算。这与一个聚集函数所完成的计算有可比之处。但是窗口函数并不会使多行被聚集成一个单独的输出行,这与通常的非窗口聚集函数不同。取而代之,行保留它们独立的标识。在这些现象背后,窗口函数可以访问的不仅仅是查询结果的当前行。
- 可以访问与当前记录相关的多行记录;
- 不会使多行聚集成一行,与聚集函数的区别;
窗口函数语法
窗口函数跟随一个OVER子句,OVER子句决定究竟查询中的哪些行被分离出来由窗口函数处理。
可以包含分区(PARTITIONBY)和排序(ORDERBY)指令,这二者都是可选的。
window_func()OVER([PARTITIONBYfield][ORDERBYfield])
如果没有指定PARTITIONBY和ORDERBY指令,则等同于聚合函数,对全部数据进行计算。
PARTITIONBY子句将查询的行分组成为分区,窗口函数会独立地处理它们。PARTITIONBY工作起来类似于一个查询级别的GROUPBY子句,不过它的表达式总是只是表达式并且不能是输出列的名称或编号。如果没有PARTITIONBY,该查询产生的所有行被当作一个单一分区来处理。
ORDERBY子句决定被窗口函数处理的一个分区中的行的顺序。它工作起来类似于一个查询级别的ORDERBY子句,但是同样不能使用输出列的名称或编号。如果没有ORDERBY,行将被以未指定的顺序被处理。
PostgreSQL中的聚合函数也可以作为窗口函数来使用
除了这些内置的窗口函数外,任何内建的或用户定义的通用或统计聚集(也就是有序集或假想集聚集除外)都可以作为窗口函数。仅当调用跟着OVER子句时,聚集函数才会作为窗口函数;否则它们作为非窗口的聚集并为剩余的集合返回单行。
窗口函数示例
员工工资(emp_salary)表结构如下:
SELECTemp_no,dep_name,salary FROMpublic.emp_salary orderbydep_name,emp_no;
emp_id | dep_name | salary |
---|---|---|
7 | develop | 4200 |
8 | develop | 6000 |
9 | develop | 4500 |
10 | develop | 5200 |
11 | develop | 5200 |
2 | personnel | 3900 |
5 | personnel | 3500 |
1 | sales | 5000 |
3 | sales | 4800 |
4 | sales | 4800 |
如果要将每位员工与其部门的平均工资进行对比,需要这样的结果:
emp_id | dep_name | salary | avg |
---|---|---|---|
7 | develop | 4200 | 5020 |
8 | develop | 6000 | 5020 |
9 | develop | 4500 | 5020 |
10 | develop | 5200 | 5020 |
11 | develop | 5200 | 5020 |
2 | personnel | 3900 | 3700 |
5 | personnel | 3500 | 3700 |
1 | sales | 5000 | 4866.66666666667 |
3 | sales | 4800 | 4866.66666666667 |
4 | sales | 4800 | 4866.66666666667 |
如果不用窗口函数来查询,则比较复杂,当然也能做到,语句如下:
SELECTe0.emp_no,e0.dep_name,e0.salary,e2.avg_salary FROMpublic.emp_salarye0 join( selecte1.dep_name,avg(e1.salary)asavg_salary frompublic.emp_salarye1 groupbye1.dep_name )e2one2.dep_name=e0.dep_name orderbye0.dep_name,e0.emp_no;
如果使用窗口函数进行查询,则很容易做到,sql语句如下:
SELECTemp_no,dep_name,salary, avg(salary)over(partitionbydep_name) FROMpublic.emp_salary orderbydep_name,emp_no;
但是如果要查询随着员工的增加,各部门平均工资的变化,如下表所示的结果,不用窗口函数查询的话就很难做到了。
emp_id | dep_name | salary | avg |
---|---|---|---|
7 | develop | 4200 | 4200 |
8 | develop | 6000 | 5100 |
9 | develop | 4500 | 4900 |
10 | develop | 5200 | 4975 |
11 | develop | 5200 | 5020 |
2 | personnel | 3900 | 3900 |
5 | personnel | 3500 | 3700 |
1 | sales | 5000 | 5000 |
3 | sales | 4800 | 4900 |
4 | sales | 4800 | 4866.66666666667 |
如果使用窗口函数,依然可以轻松完成,语句如下:
SELECTemp_no,dep_name,salary, avg(salary)over(partitionbydep_nameorderbyemp_no) FROMpublic.emp_salary orderbydep_name,emp_no;
可见,窗口函数在需要对查询结果中的相关行进行计算时有很大的优势。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对毛票票的支持。