PG 窗口函数

一,简介

窗口函数也叫分析函数,也叫OLAP函数,通过partition by分组,这里的窗口表示范围,,可以不指定PARATITION BY,会将这个表当成一个大窗口。

二,应用场景

1)用于分区排序

2)动态Group By

3Top N

4)累计计算

5)层次查询

三,窗口函数的种类

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

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

2、RANK DENSE _ RANK ROW _ NUMBER 等专用窗口函数

专用窗口函数:

(1)RANK函数

RANK 函数
计算排序时,如果存在相同位次的记录,则会跳过之后的位次,
并且占位。
比如:有 3 条记录排在第 1 位时:1 位、1 位、1 位、4 ……

DENSE_RANK 函数
同样是计算排序,即使存在相同位次的记录,也不
会跳过之后的位次。
比如:3 条记录排在第 1 位时:1 位、1 位、1 位、2 ……

记得有一道面试图是排序成绩,会有人并列并列名次,这个场景就需要用到DENSE_RANK函数。

ROW_NUMBER 函数
赋予唯一的连续位次,
不会出现并列名次。
比如:3 条记录排在第 1 位时:1 位、2 位、3 位、4 ……

四,窗口函数语法

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

其中,可以把

    rank() over (PARTITION BY job
                 ORDER BY  sal) 

一块看,然后起了一个别名。

PARTITION BY 能够设定分组和排序的对象范围。

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

五,使用范围:只能在SELECT 子句当中

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

(1)进行累积结果

如:

select ename,sal,
	SUM(sal) over (ORDER BY ename) as current_sum
from 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_avg from product;

九,两个order by

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

所以,一般来说,在窗口函数里面用了order by,外面就不要再用order by了。

十,实操

1.建表语句

CREATE TABLE
testdb=# create table product_sale_all(
year CHAR(25) NOT NULL, -- 年份字段,字符类型CHAR
product_name VARCHAR(100) NOT NULL, -- 产品名称,字符类型VARCHAR
product_category VARCHAR(32) NOT NULL, --产品所属类别,字符类型VARCHAR
sale_num INT -- 产品销售额总和,整数类型INT
);

2.插入测试数据

下面在dbeaver中操作;

3.计算总销售额

4.各产品类别的累积销售额

如果按照产品类别进行分组,按照销售额降序,求累计至当前产品的销售额order_sale_sum。

5.各产品类别产品数

6.各产品类别的平均销售额

6.各产品销售金额排名

7.窗口函数-位移

如果我们按产品类别进行分组,组内按照销售额进行逆序排序,针对每一行,计算销售额排名前1位lag_product 和后1位产品lead_product:

8窗口函数分箱

如果我们要对记录进行分箱,也就是把记录切分为机组,就要用分享箱函数ntile。

ntile(n) 可以奖数据按照顺序划分为N组,各个组有编号,编号从1开始,对于每一行,ntile返回此行所属的组的编号,ntile(2)表示将表切分为2组,ntile也可以在partition by 分组后分箱,表示对当前的组内进行分箱。

上一篇:自适应感兴趣区域的级联多尺度残差注意力CNN用于自动脑肿瘤分割| 文献速递-深度学习肿瘤自动分割


下一篇:Apache Impala 4.4.0正式发布了!