跳到主要内容

PostgreSQL 窗口函数

介绍

在PostgreSQL中,窗口函数(Window Functions)是一种强大的工具,允许你在查询结果集的某个“窗口”内执行计算。与普通的聚合函数不同,窗口函数不会将多行合并为一行,而是为每一行返回一个值。这使得窗口函数非常适合用于排名、累积计算、移动平均等场景。

窗口函数通常与 OVER 子句一起使用,OVER 子句定义了窗口的范围和排序方式。

基本语法

窗口函数的基本语法如下:

sql
function_name (expression) OVER (
[PARTITION BY partition_expression]
[ORDER BY sort_expression]
[frame_clause]
)
  • function_name: 窗口函数的名称,例如 ROW_NUMBER()RANK()SUM() 等。
  • expression: 需要计算的表达式。
  • PARTITION BY: 将数据分成不同的组(分区),在每个分区内分别计算窗口函数。
  • ORDER BY: 定义窗口内的排序方式。
  • frame_clause: 定义窗口的框架范围,例如 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

常用窗口函数

以下是一些常用的窗口函数:

  1. ROW_NUMBER(): 为每一行分配一个唯一的行号。
  2. RANK(): 为每一行分配一个排名,相同值的行会有相同的排名,后续排名会跳过。
  3. DENSE_RANK(): 类似于 RANK(),但不会跳过后续排名。
  4. SUM(): 计算窗口内行的累积和。
  5. AVG(): 计算窗口内行的平均值。
  6. LEAD(): 获取当前行之后的某一行的值。
  7. LAG(): 获取当前行之前的某一行的值。

示例

示例1:使用 ROW_NUMBER() 分配行号

假设我们有一个 sales 表,包含以下数据:

sql
CREATE TABLE sales (
id SERIAL PRIMARY KEY,
salesperson VARCHAR(50),
amount NUMERIC
);

INSERT INTO sales (salesperson, amount) VALUES
('Alice', 100),
('Bob', 200),
('Alice', 150),
('Bob', 300),
('Alice', 200);

我们可以使用 ROW_NUMBER() 为每个销售人员的销售记录分配行号:

sql
SELECT
salesperson,
amount,
ROW_NUMBER() OVER (PARTITION BY salesperson ORDER BY amount) AS row_num
FROM
sales;

输出结果:

salespersonamountrow_num
Alice1001
Alice1502
Alice2003
Bob2001
Bob3002

示例2:使用 SUM() 计算累积和

我们可以使用 SUM() 函数计算每个销售人员的累积销售额:

sql
SELECT
salesperson,
amount,
SUM(amount) OVER (PARTITION BY salesperson ORDER BY amount) AS cumulative_sum
FROM
sales;

输出结果:

salespersonamountcumulative_sum
Alice100100
Alice150250
Alice200450
Bob200200
Bob300500

示例3:使用 RANK()DENSE_RANK() 进行排名

我们可以使用 RANK()DENSE_RANK() 对销售人员的销售额进行排名:

sql
SELECT
salesperson,
amount,
RANK() OVER (ORDER BY amount DESC) AS rank,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rank
FROM
sales;

输出结果:

salespersonamountrankdense_rank
Bob30011
Alice20022
Bob20022
Alice15043
Alice10054

实际应用场景

场景1:计算移动平均

假设我们有一个 stock_prices 表,记录某只股票的每日收盘价。我们可以使用窗口函数计算该股票的5日移动平均价:

sql
SELECT
date,
close_price,
AVG(close_price) OVER (ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
stock_prices;

场景2:查找每个部门的最高薪员工

假设我们有一个 employees 表,包含员工的姓名、部门和薪水。我们可以使用窗口函数查找每个部门薪水最高的员工:

sql
SELECT
name,
department,
salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM
employees
WHERE
rank = 1;

总结

PostgreSQL窗口函数是一种强大的工具,可以帮助你在查询结果集的某个窗口内执行复杂的计算。通过使用 OVER 子句,你可以定义窗口的范围和排序方式,从而实现排名、累积计算、移动平均等功能。

附加资源与练习

  • 练习1: 使用 LEAD()LAG() 函数,计算每个销售人员的销售额与前一天的差异。
  • 练习2: 使用 NTILE() 函数,将销售人员的销售额分成4个等级。
提示

如果你对窗口函数的使用还有疑问,可以查阅PostgreSQL官方文档,或者尝试在实际项目中应用这些函数,以加深理解。