PostgreSQL数据库中窗口函数的语法与使用
什么是窗口函数?
一个窗口函数在一系列与当前行有某种关联的表行上执行一种计算。这与一个聚集函数所完成的计算有可比之处。但是窗口函数并不会使多行被聚集成一个单独的输出行,这与通常的非窗口聚集函数不同。取而代之,行保留它们独立的标识。在这些现象背后,窗口函数可以访问的不仅仅是查询结果的当前行。
- 可以访问与当前记录相关的多行记录;
- 不会使多行聚集成一行, 与聚集函数的区别;
窗口函数语法
窗口函数跟随一个 OVER 子句, OVER 子句决定究竟查询中的哪些行被分离出来由窗口函数处理。
可以包含分区 (PARTITION BY) 和排序 (ORDER BY) 指令, 这二者都是可选的。
window_func() OVER([PARTITION BY field] [ORDER BY field])
如果没有指定 PARTITION BY 和 ORDER BY 指令, 则等同于聚合函数, 对全部数据进行计算。
PARTITION BY 子句将查询的行分组成为分区, 窗口函数会独立地处理它们。PARTITION BY 工作起来类似于一个查询级别的 GROUP BY 子句, 不过它的表达式总是只是表达式并且不能是输出列的名称或编号。 如果没有 PARTITION BY, 该查询产生的所有行被当作一个单一分区来处理。
ORDER BY 子句决定被窗口函数处理的一个分区中的行的顺序。 它工作起来类似于一个查询级别的 ORDER BY 子句, 但是同样不能使用输出列的名称或编号。 如果没有 ORDER BY, 行将被以未指定的顺序被处理。
PostgreSQL 中的聚合函数也可以作为窗口函数来使用
除了这些内置的窗口函数外,任何内建的或用户定义的通用或统计聚集(也就是有序集或假想集聚集除外)都可以作为窗口函数。仅当调用跟着OVER子句时,聚集函数才会作为窗口函数;否则它们作为非窗口的聚集并为剩余的集合返回单行。
窗口函数示例
员工工资 (emp_salary) 表结构如下:
SELECT emp_no, dep_name, salary FROM public.emp_salary order by dep_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 |
如果不用窗口函数来查询, 则比较复杂, 当然也能做到, 语句如下:
SELECT e0.emp_no, e0.dep_name, e0.salary, e2.avg_salary FROM public.emp_salary e0 join ( select e1.dep_name, avg(e1.salary) as avg_salary from public.emp_salary e1 group by e1.dep_name ) e2 on e2.dep_name = e0.dep_name order by e0.dep_name, e0.emp_no;
如果使用窗口函数进行查询, 则很容易做到, sql 语句如下:
SELECT emp_no, dep_name, salary, avg(salary) over(partition by dep_name) FROM public.emp_salary order by dep_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 |
如果使用窗口函数, 依然可以轻松完成, 语句如下:
SELECT emp_no, dep_name, salary, avg(salary) over(partition by dep_name order by emp_no) FROM public.emp_salary order by dep_name, emp_no;
可见, 窗口函数在需要对查询结果中的相关行进行计算时有很大的优势。
总结
以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,谢谢大家对猪先飞的支持。
相关文章
- eval函数在php中是一个函数并不是系统组件函数,我们在php.ini中的disable_functions是无法禁止它的,因这他不是一个php_function哦。 eval()针对php安全来说具有很...2016-11-25
- 在php中eval是一个函数并且不能直接禁用了,但eval函数又相当的危险了经常会出现一些问题了,今天我们就一起来看看eval函数对数组的操作 例子, <?php $data="array...2016-11-25
Python astype(np.float)函数使用方法解析
这篇文章主要介绍了Python astype(np.float)函数使用方法解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友可以参考下...2020-06-08- 这篇文章主要介绍了PostgreSQL判断字符串是否包含目标字符串的多种方法,本文给大家介绍的非常详细,对大家的学习或工作具有一定的参考借鉴价值,需要的朋友可以参考下...2021-02-23
- 这篇文章主要介绍了Python中的imread()函数用法说明,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-03-16
- 本文主要介绍了C# 中取绝对值的函数。具有很好的参考价值。下面跟着小编一起来看下吧...2020-06-25
- 下面小编就为大家带来一篇C#学习笔记- 随机函数Random()的用法详解。小编觉得挺不错的,现在就分享给大家,也给大家做个参考。一起跟随小编过来看看吧...2020-06-25
- 这篇文章主要介绍了PostgreSQL TIMESTAMP类型 时间戳操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-26
- 这篇文章主要介绍了postgresql 实现多表关联删除操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-02
- CREATE FUNCTION ChangeBigSmall (@ChangeMoney money) RETURNS VarChar(100) AS BEGIN Declare @String1 char(20) Declare @String2 char...2016-11-25
- 这篇文章主要介绍了Postgresl 如何选择正确的关闭模式,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-01-18
Android开发中findViewById()函数用法与简化
findViewById方法在android开发中是获取页面控件的值了,有没有发现我们一个页面控件多了会反复研究写findViewById呢,下面我们一起来看它的简化方法。 Android中Fin...2016-09-20- 这篇文章主要介绍了C++中Sort函数详细解析,sort函数是algorithm库下的一个函数,sort函数是不稳定的,即大小相同的元素在排序后相对顺序可能发生改变...2022-08-18
- 这篇文章主要介绍了postgresql数据添加两个字段联合唯一的操作,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-04
- strstr() 函数搜索一个字符串在另一个字符串中的第一次出现。该函数返回字符串的其余部分(从匹配点)。如果未找到所搜索的字符串,则返回 false。语法:strstr(string,search)参数string,必需。规定被搜索的字符串。 参数sea...2013-10-04
PHP函数分享之curl方式取得数据、模拟登陆、POST数据
废话不多说直接上代码复制代码 代码如下:/********************** curl 系列 ***********************///直接通过curl方式取得数据(包含POST、HEADER等)/* * $url: 如果非数组,则为http;如是数组,则为https * $header:...2014-06-07- Foreach 函数(PHP4/PHP5)foreach 语法结构提供了遍历数组的简单方式。foreach 仅能够应用于数组和对象,如果尝试应用于其他数据类型的变量,或者未初始化的变量将发出错误信息。...2013-09-28
- free函数是释放之前某一次malloc函数申请的空间,而且只是释放空间,并不改变指针的值。下面我们就来详细探讨下...2020-04-25
- PHP 函数 strip_tags 提供了从字符串中去除 HTML 和 PHP 标记的功能,该函数尝试返回给定的字符串 str 去除空字符、HTML 和 PHP 标记后的结果。由于 strip_tags() 无法实际验证 HTML,不完整或者破损标签将导致更多的数...2014-05-31
SQL Server中row_number函数的常见用法示例详解
这篇文章主要给大家介绍了关于SQL Server中row_number函数的常见用法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...2020-12-08