当前位置: hlflowers->优技培训 > PostgreSQL技术大讲堂 - 第25讲:窗口函数

PostgreSQL技术大讲堂 - 第25讲:窗口函数

2023-08-14作者:hlflowers来源:www.hlflowers.com

PostgreSQL从小白到专家,是从入门逐渐能力提升的一个系列教程,内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容,希望对热爱PG、学习PG的同学们有帮助,欢迎持续关注CUUG PG技术大讲堂。

第25讲:窗口函数

内容1 : 窗口函数如何定义

内容2 : 专用窗口函数的种类

内容3 : 掌握常用的窗口函数

内容4 : 熟练使用聚合函数作为窗口函数

内容5 : 窗口函数的框架来计算移动平均


“窗口”的由来

窗口函数也称为 OLAP 函数。为了让大家快速形成直观印象,才起了这样一个容易理解的名称。

通过 PARTITION BY 分组后的记录集合称为“窗口”。

从词语意思的角度考虑,可能“组”比“窗口”更合适一些,但是在SQL中,“组”更多的是用来特指使用 GROUP BY 分割后的记录集合,因此,为了避免混淆,使用PARTITION BY 时称为窗口。

注意:可以不指定 PARTITION BY ,会将这个表当成一个“大窗口”。


窗口函数应用场景

应用场景: 

(1)用于分区排序

(2)动态Group By

(3)Top N

(4)累计计算

(5)层次查询


窗口函数的种类

窗口函数大体可以分为以下两种:

1、能够作为窗口函数的聚合函数(SUM、AVG、COUNT、MAX、MIN)。

2、RANK、DENSE_RANK、ROW_NUMBER 等专用窗口函数。

上面第一种应用中将聚合函数书写在语法的“< 窗口函数 >”中,就能够当作窗口函数来使用了。聚合函数根据使用语法的不同,可以在聚合函数和窗口函数之间进行转换。

上面第二种应用中的函数是标准 SQL 定义的 OLAP 专用函数,这里将其统称为“专用窗口函数”。从这些函数的名称可以很容易看出其 OLAP 的用途。


专用窗口函数

RANK 函数

计算排序时,如果存在相同位次的记录,则会跳过之后的位次。

比如:有 3 条记录排在第 1 位时:1 位、1 位、1 位、4 位……

ROW_NUMBER 函数

赋予唯一的连续位次。

比如:有 3 条记录排在第 1 位时:1 位、2 位、3 位、4 位……

DENSE_RANK 函数

同样是计算排序,即使存在相同位次的记录,也不会跳过之后的位次。

比如:有 3 条记录排在第 1 位时:1 位、1 位、1 位、2 位……


RANK()函数

--示例:

select ename,job,sal, rank() over (PARTITION BY job ORDER BY sal) as rankin from emp;

PARTITION BY 能够设定分组和排序的对象范围。本例中,为了按照工作进行分组和排序,我们指定了job。

ORDER BY 能够指定按照哪一列、何种顺序进行排序。为了按照工资的升序进行排列,我们指定了sal 。


DENSE_RANK()函数

--示例

select ename,job,sal,DENSE_RANK() over (PARTITION by job ORDER BY SAL ) as dense_rankin from emp;


ROW_NUMBER 函数

--示例:

select ename,job,sal,ROW_NUMBER() over (PARTITION BY job ORDER BY SAL ) as unique_rankin from emp;


专用窗口函数使用技巧

使用 RANK 或 ROW_ NUMBER 时无需任何参数,只需要像 RANK ()或者 ROW_ NUMBER() 这样保持括号中为空就可以了。这也是专用窗口函数通常的使用方式。

select ename,job,sal, RANK() OVER (PARTITION BY job ORDER BY sal) as rankin, DENSE_RANK() OVER (PARTITION BY job ORDER BY sal) as dense_rank, ROW_NUMBER() OVER (PARTITION BY job ORDER BY sal) as row_rankinfrom emp;


窗口函数的适用范围

使用窗口函数的位置却有非常大的限制。更确切地说,窗口函数只能书写在一个特定的位置。这个位置就是 SELECT 子句之中。反过来说,就是这类函数不能在WHERE 子句或者 GROUP BY 子句中使用。

为什么窗口函数只能在 SELECT 子句中使用呢?

在 DBMS内部,窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作。大家仔细想一想就会明白,在得到用户想要的结果之前,即使进行了排序处理,结果也是错误的。在得到排序结果之后,如果通过 WHERE 子句中的条件除去了某些记录,或者使用 GROUP BY 子句进行了汇总处理,那好不容易得到的排序结果也无法使用了。


作为窗口函数使用的聚合函数

--计算price值的累计结果

select name,price, SUM(price) over (order by name) as current_sumfrom product;

--计算SAL值的累计结果

select ename,sal,SUM(sal) over (ORDER BY ename) as current_sumfrom emp;

所有的聚合函数都能用作窗口函数,其语法和专用窗口函数完全相同。

使用 SUM 函数时,并不像 RANK 或者 ROW _ NUMBER 那样括号中的内容为空,而是和之前我们学过的一样,需要在括号内指定作为汇总对象的列。


指定框架(汇总范围)

select name,price,avg (price) over (order by name rows 2 preceding) as moving_avg from product;

这里我们使用了 ROWS (“行”)和 PRECEDING (“之前”)两个关键字,将框架指定为“截止到之前 ~ 行”,因此“ ROWS 2 PRECEDING ”就是将框架指定为“截止到之前 2 行”,也就是将作为汇总对象的记录限定为如下的“最靠近的 3 行”。

最靠近的3行=自身(当前记录)+ 之前第1行的记录 + 之前第2行的记录


计算移动平均

由于框架是根据当前记录来确定的,因此和固定的窗口不同,其范围会随着当前记录的变化而变化。

这样的统计方法称为移动平均(moving average)。由于这种方法在希望实时把握“最近状态”时非常方便,因此常常会应用在对股市趋势的实时跟踪当中。

使用关键字 FOLLOWING (“之后”)替换 PRECEDING ,就可以指定“截止到之后 ~ 行”作为框架了。


计算移动平均—同时指定前后行

select name,price,avg (price) over (order by name rows between 1 preceding and 1 following) as moving_avgfrom product;


两个order by

OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的,对结果的排列顺序并没有影响。在 SELECT 语句的最后,使用 ORDER BY子句进行指定按照 ranking 列进行排列,结果才会顺序显示,但是如果使用了,会打乱原本窗口函数出来的显示结果。

有些 DBMS(PG) 也可以按照窗口函数的 ORDER BY 子句所指定的顺序对结果进行排序。

在一条 SELECT 语句中使用两次 ORDER BY 会有点别扭,但是尽管这两个 ORDER BY 看上去是相同的,但其实它们的功能却完全不同。


总结

专用窗口函数 rank()

row_number()

dense_ranking()。

将聚合函数作为窗口函数使用---需要带参数

框架的用法---计算移动平均


以上就是【PostgreSQL从小白到专家】第25讲 - 窗口函数  的内容,欢迎一起探讨交流钉钉交流群:35,82,24,60,往期视频及文档内容联系CUUG

  • PostgreSQL技术大讲堂 - 第41讲:表空间备份与恢复
  • PostgreSQL技术大讲堂 - 第42讲:pg_rman部署与使用
  • PostgreSQL技术大讲堂 - 第43讲:流复制原理
  • 阿里云PolarDB开发者大会圆满结束,CUUG两次获奖
  • 2024年首张Oracle OCP证书-CUUG胡同学
  • 北京培黎职业学院 - PolarDB开源国产数据库工作室成立揭牌 - CUUG
  • postgresql技术大讲堂 - 第39讲:数据库完全恢复
  • 温州大学 - 开源国产数据库工作室成立揭牌仪式圆满结束
  • 2023年12月,PostgreSQL认证培训红头文件【工信部人才交流中心】
  • 2024年第13届PostgreSQL中国技术大会来啦!
  • DB-Engines:PostgreSQL is the DBMS of the Year 2023
  • PostgreSQL技术大讲堂 - 第44讲:pg流复制部署
  • PostgreSQL技术大讲堂 - 第45讲:poc-tpcc测试
  • PostgreSQL技术大讲堂 - 第46讲:poc-tpch测试
  • PostgreSQL技术大讲堂 - 第47讲:JMETER工具使用
  • PostgreSQL技术大讲堂 - 第48讲:PG高可用实现keepalived
  • PostgreSQL技术大讲堂 - 第50讲:PG分区表管理
  • 5月25日,温州大学49名学生参加工信人才PostgreSQL认证考试!
  • 5月30日,PG中级证书来了!工信人才PostgreSQL管理员认证证书!
  • 6月1日,汇华学院12名学生参加工信人才PostgreSQL认证考试!
  • PG技术大讲堂 - 第55讲:通义大模型+向量数据库实现AI的外脑
  • PostgreSQL技术大讲堂 - 第53讲:老陈与德哥开讲PostgreSQL 17新特性
  • PostgreSQL技术大讲堂 - 第54讲:如何在上线前精准评估PG SQL性能
  • postgresql技术大讲堂 - 第40讲:数据库不完全恢复
  • 1月17日阿里云PolarDB开发者大会PolarDB DevCon
  • 2024-1-12,恭喜CUUG 王同学获得Oracle OCP证书
  • PostgreSQL技术大讲堂 - 第52讲:与德哥背后的男人们聊如何实现自动性能调优
  • PostgreSQL技术大讲堂 - 第51讲:老陈与德哥聊一聊数据库调优
  • 5月16日,开源驱动教育创新研讨会(青岛站)成功举办-CUUG
  • 5月17日,PolarDB开源数据库沙龙(青岛站)成功举办-CUUG
  • 有大奖!第13届PostgreSQL中国技术大会:聚焦云端创新,汇聚智慧共享
  • PolarDB开源社区走进金蝶,开源数据库沙龙成功举办!
  • 阿里云PolarDB再获顶会SIGMOD最佳论文奖
  • 阿里云斩获国际数据库顶会ICDE 2024最佳论文
  • 腾讯云数据库TDSQL荣获深圳市科技进步奖一等奖
  • 2024-02-02,恭喜CUUG 刘同学通过Oracle考试获得OCP 19c证书
  • OCP认证能不能在家中考试,不去VUE考点考试吗?
  • 恭喜CUUG Guo同学以较高分数通过19c OCM认证考试!
  • 3月30日,工信部人才交流中心PostgreSQL认证考试顺利结束
  • 2024年4月8日,工信人才发布红头文件:PostgreSQL数据库管理人才研修与评测班
  • 恭喜CUUG入选2024年工业和信息化重点领域人才能力评价支撑机构
  • 天津职业技术师范大学《PolarDB开源数据库工作室》授牌仪式顺利完成
  • 温州大学国产开源数据库工作室成功举办PostgreSQL技能培训活动
  • Oracle数据库加入AI功能,Database 23c改名为Database 23ai
  • Oracle OCP认证还值得考吗 考OCP证书需要门槛吗
  • 今天(5月6日),CUUG 赵同学收到19c OCM认证考试证书!
  • 11月18日直播!杭州峰会大咖晚宴煮酒论英雄+PG技术大讲堂(34)
  • 11月27日,CUUG新鲜出炉的Oracle DB 19C OCP证书
  • Oracle 19c OCM认证好考吗?CUUG OCM成绩公布
  • oracle ocp证书有效期多长时间