postgresql从小白到专家 -凯发app官方网站

凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心
  • 博客访问: 593327
  • 博文数量: 486
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 4941
  • 用 户 组: 普通用户
  • 注册时间: 2018-07-05 13:59
个人简介

ocp考试资料群:569933648 验证码:ocp ocp 12c 19c考试题库解析与资料群:钉钉群号:35277291

文章分类

全部博文(486)

文章存档

2024年(3)

2023年(35)

2021年(151)

2020年(37)

2019年(222)

2018年(38)

我的朋友
相关博文
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·
  • ·

分类: mysql/postgresql

2023-08-11 15:51:10


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 等专用窗口函数。

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

上面第二种应用中的函数是标准 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 行”,也就是将作为汇总对象的记录限定为如下的“{banned}最佳靠近的 3 行”。

{banned}最佳靠近的3行=自身(当前记录) 之前第1行的记录 之前第2行的记录


计算移动平均

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

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

使用关键字 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 语句的{banned}最佳后,使用 order by子句进行指定按照 ranking 列进行排列,结果才会顺序显示,但是如果使用了,会打乱原本窗口函数出来的显示结果。

有些 dbms(pg) 也可以按照窗口函数的 order by 子句所指定的顺序对结果进行排序。

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


总结

专用窗口函数 rank()

row_number()

dense_ranking()。

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

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




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

阅读(77) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图