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;

可见， 窗口函数在需要对查询结果中的相关行进行计算时有很大的优势。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，谢谢大家对我们的支持。

赞 (0)

相关推荐

  • mysql 8.0.15 安装图文教程及数据库基础

    mysql 8.0.15 安装图文教程及数据库基础

    MySQL软件安装及数据库基础,供大家参考,具体内容如下 一.任务 任务一 MySQL 软件安装及数据库基础 任务时间 请于2月26日22:00前完成,在本文章评论打卡.逾期尚未打卡的会被清退. 学习内容 1.软件安装及服务器设置 教程 MySQL 安装 | 菜鸟教程 2.(选做,但是强烈建议) 使用图形界面软件 Navicat for SQL 星球提供Navicat for SQL    简易步骤:       解压缩文件,复制key       打开文件夹中的navicat.exe     

  • 如何合理使用数据库冗余字段的方法

    privot多对多关系的中间表.PT5框架会自动把privot带上. 我们需要隐藏,因为我们不需要privot,而且pritvot也不在我们模型本身,他是中间数据 另外冗余字段,我们有一个表是记录图片的,另一个表是记录商品的. 我们可以在图片你放商品图片里的url 同时商品里放图片id和图片URL 这两个字段是重复的,这就是数据冗余,我们设计数据库是不要出现冗余信息,为啥我们用冗余呢. 主要是为了出于对查询性能的考虑. 我们在这里做了数据冗余,我们就可以减少对图片表的查询,加速查询速度! 不过推

  • 详解数据库_MySQL: mysql函数

    一.内置函数 1.数学函数 rand() round(num) ceil(num) floor(num) 随机 四舍五入 向上取整 向下取整 2.字符串函数 length() 字节长度 char_length() 字符长度 ucase() 大写 lcase() 小写 concat(字符,-,字符n) 连接字符串 replace(字符串,旧字符,新字符)字符串替换 截取字符串 left(字符串,截取长度) right(字符串,截取长度) substring(字符串,开始位置,截取长度) #包含开始

  • PHP单例模式数据库连接类与页面静态化实现方法

    PHP单例模式数据库连接类与页面静态化实现方法

    本文实例讲述了PHP单例模式数据库连接类与页面静态化实现方法.分享给大家供大家参考,具体如下: 数据库test中数据表account内容 单例模式的数据库连接类 db.php <?php //单例模式的数据库连接 class DB { //存储实例的静态成员变量 static public $_instance; //数据库连接静态变量 static public $_connectSource; //连接数据库配置,由于前几天学习了PDO,这里就使用PDO private $_dbConfig

  • 如何在一个千万级的数据库查询中提高查询的效率?

    在实际项目中,当数据库的数据达到千万级别时候,普通查询效率直线下降,而且当使用的where条件较多,其查询效率是让人无法容忍的.假如一个taobao订单查询详情要几十秒,可想而知的用户体验是多差. 下面是一些优化方法: 一.数据库设计方面 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引: 2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t w

  • SQL SERVER 数据库备份代码实例

    本文实例为大家分享SQL SERVER数据库备份的具体代码,供大家参考,具体内容如下 /** 批量循环备份用户数据库,做为数据库迁移临时用 */ SET NOCOUNT ON DECLARE @d varchar(8) DECLARE @Backup_Flag NVARCHAR(10) SET @d=convert(varchar(8),getdate(),112) /***自定义选择备份哪些数据库****/ --SET @Backup_Flag='UserDB' -- 所用的用户数据库 SET

  • MySQL数据库中CAST与CONVERT函数实现类型转换的讲解

    MySQL 的CAST()和CONVERT()函数可用来获取一个类型的值,并产生另一个类型的值. 两者具体的语法如下: CAST(value as type); CONVERT(value, type); 就是CAST(xxx AS 类型), CONVERT(xxx,类型). 可以转换的类型是有限制的.这个类型可以是以下值其中的一个: 二进制,同带binary前缀的效果 : BINARY 例如:当使用 like 模糊搜索日期类型的字段时 语句应该是 Create_Time like binary

  • Mysql主从数据库(Master/Slave)同步配置与常见错误

    Mysql主从数据库(Master/Slave)同步配置与常见错误

    随着访问量的增加,对于一些比较耗时的数据库读取操作,一般采用将写入与读取操作分开来缓解数据库的压力,数据库引擎一般采用Master/Slave架构.实现mysql服务器的主从配置,可以实现读写分离,另外在主数据库崩溃后可以从备用数据库中恢复数据以不至于网站中断访问.下面简单说下mysql主从服务器配置的过程. 首先需要在同一个局域网内的两台机器(当然也可以用一台机器虚拟两台机器出来),都安装上mysql服务. 主机A: 192.168.1.100 从机B: 192.168.1.101 可以有多台

  • sql与各个nosql数据库使用场景的讲解

    sql为主干为什么我这样理解: 单从技术角度来说 关系型网格 充分的体现了现实事务 对事务,审计,闪存等等对数据的重视所以如何一些特别主要的数据,一定要放到sql里面.一个系统里面至少有用户信息是重要的数据. 所以sql必须有,而且数据存储的主干 什么时候引入nosql 先看看sql - > sql + nosql的过程. https://www.jb51.net/article/79236.htm 为什么要使用NoSQL 这些nosql? 对java语言而言: redis:用于缓存 - 读速度

  • 数据库语言分类DDL、DCL、DML详解

    DML(Data Manipulation Language): 它们是SELECT.UPDATE.INSERT.DELETE,就象它的名字一样,这4条命令是用来对数据库里的数据进行操作的语言 DDL(Data Definition Language): DDL比DML要多,主要的命令有CREATE.ALTER.DROP等,DDL主要是用在定义或改变表(TABLE)的结构,数据类型,表之间的链接和约束等初始化工作上,他们大多在建立表时使用 DCL(Data Control Language):

随机推荐