PostgreSQL（二十四）窗口函数

//创建一个用于测试查询的表
CREATE TABLE emp(
empno int not null,
ename varchar(10),
job varchar(10),
mgr int,
hiredate date,
sal float4,
comm float4,
deptno int);

INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

1、RANK 函数

使用RANK函数计算排序时，如果存在相同位次的记录，则会跳过之后的位次，并且占位。

比如，有 3 条记录排在第 1 位时，得到的排序结果为：1 位、1 位、1 位、4 位。

例：

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

为了按照工作进行分组和排序，例句里指定job。

ORDER BY：指定按照哪一列、何种顺序进行排序。

为了按照工资升序排列，例句里指定sal。

2、DENSE_RANK 函数

与RANK函数相对，使用DENSE_RANK函数时，同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。

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

例：

3、ROW_NUMBER 函数

赋予唯一的连续位次。

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

例：

4、专用窗口函数使用语法及适用范围

（1）语法

使用 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_rankin
FROM emp;

（2）适用范围

使用窗口函数的位置有非常大的限制。更确切地说，窗口函数只能书写在一个特定的位置：SELECT子句。不能在WHERE 子句或者 GROUP BY 子句中使用。

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

在DBMS内部，窗口函数是对 WHERE 子句或者 GROUP BY 子句处理后的“结果”进行的操作 。且窗口函数也会进行分组和排序。

在得到用户想要的结果之前，即使进行了排序处理，结果也是错误的。在得到排序结果之后，如果通过 WHERE 子句中的条件除去了某些记录，或者使用 GROUP BY 子句进行了汇总处理，那好不容易得到的排序结果也无法使用了。

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

1、聚合函数作为窗口函数的使用说明

所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同，但是需要带上本身对应的参数。

2、例：sum作为窗口函数的使用

使用SUM函数时，并不像RANK或者ROW_NUMBER 那样括号中的内容为空，而是需要在括号内指定作为汇总对象的列。

例：计算sal列值和current_sum列值的累计结果

四、窗口函数的框架计算移动平均

1、指定框架进行汇总计算

指定当前行及前两行进行汇总计算

这里我们使用了ROWS（“行”）和PRECEDING（“之前”）两个关键字，将框架指定为“截止到之前X行”。

“ROWS 2 PRECEDING”就是将框架指定为“截止到之前2行”，也就是将作为汇总对象的记录限定为如下的“最靠近的3行”。最靠近的3行=自身（当前记录）+ 之前第1行的记录 + 之前第2行的记录

2、计算移动平均

由于框架是根据当前记录来确定的，因此和确定的窗口不同，其范围会随着当前记录的变化而变化。这样的统计方法称为移动，例如：移动平均（moving average）、移动汇总，等。

由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。

（1）使用关键字 FOLLOWING （“之后”）替换 PRECEDING（“之前”），就可以指定“截止到之后X行”作为框架了。

（2）使用关键字ROWS、RANGE表示窗口范围的定义，即：当前窗口包含哪些数据。

--ROWS：通过排序后的前后位置选取窗口范围，选择前/后几行。例如：“ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING”表示当前行往前数3行及当前行往后数3行，一共7行数据（或小于7行，如果碰到了边界）。

--RANGE：通过数值的大小选取窗口范围，例如：“RANGE BETWEEN 3 PRECEDING AND 3 FOLLOWING”表示选取其取值在[c-3,c+3]这个范围内的行，其中c为当前行的值。

3、一条select同时出现两个order by

在一条 SELECT 语句中使用两次 ORDER BY 的情况是存在的，即在select子句中使用窗口函数，使用了order by，然后在整个语句最后中使用的ORDER BY。这在语法上是支持的。但对于查询结果来说，这样使用会有点别扭。

尽管这两个 ORDER BY 看上去是相同的，但其实它们的功能却完全不同：

OVER 子句中的 ORDER BY 只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序并没有影响。

在SELECT语句的最后，使用 ORDER BY子句进行指定按照 ranking 列进行排列，结果才会顺序显示，但是如果使用了，会打乱原本窗口函数出来的显示结果，导致窗口函数做了无用功。

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

五、应用案例

1、建表及模拟数据

创建测试表product_sale_all，其中列包含：年份、产品名称、产品所属类别、产品销售额总和。

然后插入测试数据。

CREATE TABLE product_sale_all(
year CHAR(25) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_category VARCHAR(32) NOT NULL,
sale_sum INT
);

INSERT INTO product_sale_all VALUES ('2017','iPhone','手机',110000);
INSERT INTO product_sale_all VALUES ('2018','iPhone','手机',115000);
INSERT INTO product_sale_all VALUES ('2018','Huawei','手机',138000);
INSERT INTO product_sale_all VALUES ('2019','Huawei','手机',160000);
INSERT INTO product_sale_all VALUES ('2018','Canon','相机',100000);
INSERT INTO product_sale_all VALUES ('2019','Canon','相机',200000);
INSERT INTO product_sale_all VALUES ('2020','Canon','相机',180000);
INSERT INTO product_sale_all VALUES ('2018','IBM','笔记本电脑',155000);
INSERT INTO product_sale_all VALUES ('2019','DELL','笔记本电脑',1550000);
INSERT INTO product_sale_all VALUES ('2020','IBM','笔记本电脑',140000);

2、计算总销售额

如果要在每一行后面加上当前产品所在类别的总销售额category_sale_sum，以及整体的销售额total_sale_sum，可以使用如下窗口函数：

SELECT *,
SUM(sale_sum) OVER(PARTITION BY product_category) as category_sale_sum,
SUM(sale_sum) OVER() as total_sale_sum
FROM product_sale_all;

3、各产品类别的累计销售额

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

使用窗口函数实现如下：

SELECT *,
SUM(sale_sum) OVER(PARTITION BY product_category ORDER BY sale_sum DESC) as order_sale_sum,
SUM(sale_sum) OVER(PARTITION BY product_category) as category_sale_sum,
SUM(sale_sum) OVER() as total_sale_sum
FROM product_sale_all;

4、各产品类别的平均销售额

如果要在每一行后面加上整体的评价销售额avg_sale_sum，以及当前所属产品类别下的平均销售额category_ayg_sale_sum，可以用窗口函数实现如下：

SELECT *,
AVG(sale_sum) OVER(PARTITION BY product_category) as category_avg_sale_sum,
AVG(sale_sum) OVER() as avg_sale_sum
FROM product_sale_all;

5、各产品类别的最低销售额

如果要在每一行后面加上整体的最高销售额max_sale_sum，以及当前所属类别下的最低销售额category_min_sale_sum，可以用窗口函数实现如下：

SELECT *,
MIN(sale_sum) OVER(PARTITION BY product_category) as category_min_sale_sum,
MAX(sale_sum) OVER() as max_sale_sum
FROM product_sale_all;

6、各产品销售金额排名

如果按照销售额降序的方式对各产品进行排序，可以使用如下查询：

SELECT *,
ROW_NUMBER() OVER(ORDER BY sale_sum DESC) as "顺序排序",
RANK() OVER(ORDER BY sale_sum DESC ) as "秩排序",
DENSE_RANK() OVER(ORDER BY sale_sum DESC) as "数据排序"
FROM product_sale_all;

7、所有产品里销售最多或者最少产品名称统计

如果按照所有产品销量的极值进行展示，可以使用如下查询：

SELECT *,
FIRST_VALUE(product_name) OVER(ORDER BY sale_sum DESC) as all_max_product,
FIRST_VALUE(product_name) OVER(ORDER BY sale_sum ASC) as all_min_product
FROM product_sale_all;

8、分类产品里销售最多或者最少产品名称统计

如果按照每个类别的产品销量的极值进行展示，可以使用如下查询：

SELECT *,
LAST_VALUE(product_name) OVER(
    PARTITION BY product_category 
    ORDER BY sale_sum DESC 
    ROWS BETWEEN unbounded PRECEDING AND unbounded FOLLOWING) as category_min_product
FROM product_sale_all;

9、窗口函数之位移

如果我们按产品类别进行分组，组内按照销售额进行逆序排列，针对每一行，计算销售额排名在当前对象的前1位的lag_ product和后1位产品的lead_ product，窗口函数实现如下：

SELECT *,
LAG(product_name,1,null) OVER(PARTITION BY product_category ORDER BY sale_sum desc) as lag_product,
LEAD(product_name,1,'0') OVER(PARTITION BY product_category ORDER BY sale_sum desc) as lead_product
FROM product_sale_all;

10、窗口函数之分箱

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

ntile(n)可以将每个窗口数据按照顺序划分成n组，返回各个组且带编号，编号从1开始，对于每一行，ntile返回此行所属的组的编号，ntile(2)表示将表切分为2组，ntile可以在PARTITION BY分组后分箱，表示对当前的组内进行分箱。

假如不能均分，ntile会根据情况分配，但会尽量均匀。

SELECT *,
NTILE(2) OVER(PARTITION BY product_category ORDER BY sale_sum DESC) as category_part
FROM product_sale_all;

忧愁的锅盖儿

关注

32
点赞
踩
27

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录