Skip to main content
 首页 » 数据库

PostgreSQL 窗口函数教程

2022年07月19日143xiaohuochai

PostgreSQL 窗口函数教程

本文我们学习如何使用PostgreSQL窗口函数，执行与当前行相关分区(窗口)计算。

准备示例表

首先我们创建两个实例表用于演示：products 和 product_groups

图1

创建表语句：

CREATE TABLE product_groups ( 
   group_id serial PRIMARY KEY, 
   group_name VARCHAR (255) NOT NULL 
); 
  
CREATE TABLE products ( 
   product_id serial PRIMARY KEY, 
   product_name VARCHAR (255) NOT NULL, 
   price DECIMAL (11, 2), 
   group_id INT NOT NULL, 
   FOREIGN KEY (group_id) REFERENCES product_groups (group_id) 
);

插入一些示例数据：

INSERT INTO product_groups (group_name) 
VALUES 
   ('Smartphone'), 
   ('Laptop'), 
   ('Tablet'); 
  
INSERT INTO products (product_name, group_id,price) 
VALUES 
   ('Microsoft Lumia', 1, 200), 
   ('HTC One', 1, 400), 
   ('Nexus', 1, 500), 
   ('iPhone', 1, 900), 
   ('HP Elite', 2, 1200), 
   ('Lenovo Thinkpad', 2, 700), 
   ('Sony VAIO', 2, 700), 
   ('Dell Vostro', 2, 800), 
   ('iPad', 3, 700), 
   ('Kindle Fire', 3, 150), 
   ('Samsung Galaxy Tab', 3, 200);

在这里插入图片描述在这里插入图片描述

PostgreSQL窗口函数介绍

最简单理解窗口函数的方式是从重温聚集函数开始。聚集函数把一组记录行聚集为单行数据。下面使用avg()函数计算所有产品的价格。

SELECT 
   AVG (price) 
FROM 
   products;
avg
586.363636

如果需要给子集行应用聚集函数，使用group by子句。下面示例返回每组产品的平均价格：

SELECT 
   group_name, 
   AVG (price) 
FROM 
   products 
INNER JOIN product_groups USING (group_id) 
GROUP BY 
   group_name;
group_name avg
Smartphone 500
Tablet 350
Laptop 850

可以清晰看到结果，avg()函数根据查询结果聚集行。

与聚集函数类似，窗口函数也基于一组行进行操作。然而其返回查询不减少行数。术语窗口描述了窗口函数所操作的一组行。窗口函数从窗口中的行返回值。

举例，下面查询返回产品名称、价格、所属组名以及每组的平均价格：

SELECT 
   product_name, 
   price, 
   group_name, 
   AVG (price) OVER ( 
      PARTITION BY group_name 
   ) 
FROM 
   products 
   INNER JOIN  
      product_groups USING (group_id);

在这里插入图片描述
该查询，avg函数与窗口函数一起作用于一组有over子句指定的行，每组行称为窗口。该查询中新的语法是over子句：

AVG(price) OVER (PARTITION BY group_name)

其中 PARTITION BY 子句给结果行进行分组，并使用avg函数对每组计算平均价格。
注意，窗口函数总是在JOIN, WHERE, GROUP BY 和 HAVING 子句之后，并在最后order by之前，于结果集上执行窗口计算。

PostgreSQL 窗口函数语法

PostgreSQL 窗口函数语法比较复杂，下面描述简单版本：

window_function(arg1, arg2,..) OVER ( 
   [PARTITION BY partition_expression] 
   [ORDER BY sort_expression [ASC | DESC] [NULLS {FIRST | LAST }] 
   [frame_clause] )

其中 window_function(arg1,arg2,...) ，window_function 表示窗口函数的名称，一些窗口函数没有任何参数。

PARTITION BY 子句把行分为多个组或分区应用窗口函数。如上例所示，我们使用产品所属组划分产品至各个组或分区。

PARTITION BY 子句是可选的。如果没有PARTITION BY子句，窗口函数则把所有结果集作为单个分区。

ORDER BY 子句指定每个窗口函数应用的分区中行的顺序。

ORDER BY 子句 使用 NULLS FIRST 或 NULLS LAST 选项指定是否null值放在结果集的最前或最后。缺省是 NULLS LAST 选项.

frame_clause 子句定义当前分区行中的一组子集行，并对其应用窗口函数，该子集行称为帧。

如果查询中使用多个窗口函数：

SELECT 
    wf1() OVER(PARTITION BY c1 ORDER BY c2), 
    wf2() OVER(PARTITION BY c1 ORDER BY c2) 
FROM table_name;

可以使用window子句进行简化：

SELECT  
   wf1() OVER w, 
   wf2() OVER w, 
FROM table_name 
WINDOW w AS (PARTITION BY c1 ORDER BY c2);

即使只有一个窗口函数，也可以使用window子句：

SELECT wf1() OVER w 
FROM table_name  
WINDOW w AS (PARTITION BY c1 ORDER BY c2);

PostgreSQL 窗口函数列表

下表列出了PostgreSQL 中所有窗口函数，注意一些聚集函数如 AVG(), MIN(), MAX(), SUM(), 和 COUNT()也可以作为窗口函数。

Name Description
CUME_DIST 返回分区中当前行的相对等级(0~1).
DENSE_RANK 在分区中计算当前行等级，没有空缺.
FIRST_VALUE 返回分区中第一行值.
LAG 返回分区中当前行指定偏移量前面行的值.
LAST_VALUE 返回分区中的最后一行值.
LEAD 返回分区中当前行指定偏移量后面行的值.
NTILE 把分区中的每行尽可能等分，并给每个等份赋值，从1开始.
NTH_VALUE 返回顺序分区中第几行值
PERCENT_RANK 返回相对等级(0~1)， (当前行-1) / (整个行 – 1)
RANK 在分区中计算当前行等级，有空缺.
ROW_NUMBER 在分区中计算当前的顺序号，从 1 开始.

ROW_NUMBER(), RANK(), DENSE_RANK() 示例

ROW_NUMBER(), RANK(), DENSE_RANK()给每行基于其顺序在结果集中生成一个整数。

ROW_NUMBER() 函数给每个分区中的每行生成一个顺序号。示例：

SELECT 
   product_name, 
   group_name, 
   price, 
   ROW_NUMBER () OVER ( 
      PARTITION BY group_name 
      ORDER BY price 
   ) 
FROM 
   products 
INNER JOIN product_groups USING (group_id);

图

RANK() 函数生成一个排名，如果行值相同，则RANK()生成的值相同，下一个值被跳过。示例：

SELECT 
   product_name, 
   group_name, 
  price, 
   RANK () OVER ( 
      PARTITION BY group_name 
      ORDER BY price 
   ) 
FROM 
   products 
INNER JOIN product_groups USING (group_id);

图

在产品组为laptop 中，Dell Vostro 和 Sony VAIO 产品价格相同，因此都有相同的等级为1，下一行分组为HP Elite的等级为3，因为2跳过了。

与 RANK() 函数类似, DENSE_RANK() 函数基于每个分区的顺序生成一个等级值，但等级没有空缺，即相同等级给多行但后面行等级不跳过。

SELECT 
   product_name, 
   group_name, 
   price, 
   DENSE_RANK () OVER ( 
      PARTITION BY group_name 
      ORDER BY 
         price 
   ) 
FROM 
   products 
INNER JOIN product_groups USING (group_id);

图

在分组为 laptop product 中, 等级 1 生成2次给 Dell Vostro 和 Sony VAIO. 下一个等级 2 分配给 HP Elite。

FIRST_VALUE 和 LAST_VALUE

FIRST_VALUE() 函数在分区中返回第一行的值，而 LAST_VALUE() 函数返回返回分区中的最后一行值.

下面示例通过FIRST_VALUE()函数返回每个分区中的最低价。

SELECT 
   product_name, 
   group_name, 
   price, 
   FIRST_VALUE (price) OVER ( 
      PARTITION BY group_name 
      ORDER BY price 
   ) AS lowest_price_per_group 
FROM 
   products 
INNER JOIN product_groups USING (group_id);

图

利用 LAST_VALUE()函数返回每组中的最高价：

SELECT 
   product_name, 
   group_name, 
   price, 
   LAST_VALUE (price) OVER ( 
      PARTITION BY group_name 
      ORDER BY price  
      RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 
   ) AS highest_price_per_group 
FROM 
   products 
INNER JOIN product_groups USING (group_id);

图

注意我们使用 frame 子句 RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (无限向前和无限向后范围))，因为缺省使用的frame 子句是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW(无限向前和当前行范围).

LAG 和 LEAD

LAG 函数能够访问前一行数据，而LEAD函数可以访问后一行数据。

LAG 和 LEAD两者的语法相同：

LAG  (expression [,offset] [,default]) over_clause; 
LEAD (expression [,offset] [,default]) over_clause;

解释如下：

  • expression – 计算返回值的列或表达式.
  • offset – 相对当前行向前( LAG)/ 向后 ( LEAD) 的行数. 缺省为 1.
  • default – 如果offset超过窗口范围时的缺省返回值. 忽略则缺省为 NULL.

下面语句使用LAG函数返回前一行的价格并计算与当前行的差价。

SELECT 
   product_name, 
   group_name, 
   price, 
   LAG (price, 1) OVER ( 
      PARTITION BY group_name 
      ORDER BY 
         price 
   ) AS prev_price, 
   price - LAG (price, 1) OVER ( 
      PARTITION BY group_name 
      ORDER BY 
         price 
   ) AS cur_prev_diff 
FROM 
   products 
INNER JOIN product_groups USING (group_id);

图

下面语句利用LEAD函数返回下一行的价格，并计算与当前行的差价：

SELECT 
   product_name, 
   group_name, 
   price, 
   LEAD (price, 1) OVER ( 
      PARTITION BY group_name 
      ORDER BY 
         price 
   ) AS next_price, 
   price - LEAD (price, 1) OVER ( 
      PARTITION BY group_name 
      ORDER BY 
         price 
   ) AS cur_next_diff 
FROM 
   products 
INNER JOIN product_groups USING (group_id);

图

总结

本文介绍了PostgreSQL窗口函数，并提供了一些示例加深理解。


本文参考链接：https://blog.csdn.net/neweastsun/article/details/97617348
  文章来源：
  https://www.itguest.com/post/achc3c1.html
阅读延展