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
。
常用窗口函数
以下是一些常用的窗口函数:
- ROW_NUMBER(): 为每一行分配一个唯一的行号。
- RANK(): 为每一行分配一个排名,相同值的行会有相同的排名,后续排名会跳过。
- DENSE_RANK(): 类似于
RANK()
,但不会跳过后续排名。 - SUM(): 计算窗口内行的累积和。
- AVG(): 计算窗口内行的平均值。
- LEAD(): 获取当前行之后的某一行的值。
- 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;
输出结果:
salesperson | amount | row_num |
---|---|---|
Alice | 100 | 1 |
Alice | 150 | 2 |
Alice | 200 | 3 |
Bob | 200 | 1 |
Bob | 300 | 2 |
示例2:使用 SUM()
计算累积和
我们可以使用 SUM()
函数计算每个销售人员的累积销售额:
sql
SELECT
salesperson,
amount,
SUM(amount) OVER (PARTITION BY salesperson ORDER BY amount) AS cumulative_sum
FROM
sales;
输出结果:
salesperson | amount | cumulative_sum |
---|---|---|
Alice | 100 | 100 |
Alice | 150 | 250 |
Alice | 200 | 450 |
Bob | 200 | 200 |
Bob | 300 | 500 |
示例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;
输出结果:
salesperson | amount | rank | dense_rank |
---|---|---|---|
Bob | 300 | 1 | 1 |
Alice | 200 | 2 | 2 |
Bob | 200 | 2 | 2 |
Alice | 150 | 4 | 3 |
Alice | 100 | 5 | 4 |
实际应用场景
场景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官方文档,或者尝试在实际项目中应用这些函数,以加深理解。